Writing to an Excel file using Apache POI a huge data from ArrayList of Strings

Question

I have an ArrayList of Strings which is quite huge(approximately over 500k and more of items). Is there any possibility to speed up writing this array to an excel file? Right now it takes a while and I have no idea if I did everything that I could(Maybe it is a problem with .get method?). Please refer to the following code(please do not pay attation to details- this class is created only for test purposes):

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Tests {

private static final String ADDRESS = "./result/file.xlsx";

public static void readAndWriteAData(int counterOfExecutions, List<String> listOfResults) throws IOException{

    File file = new File(ADDRESS);
    if(file.exists()&&!file.isDirectory()){
        file.delete();
    }

    @SuppressWarnings("resource")
    Workbook wb = new XSSFWorkbook();

    FileOutputStream fileOut = new FileOutputStream(ADDRESS);
    wb.write(fileOut);
    fileOut.close();

    Sheet sheet = wb.createSheet("1");
    Row row;
    Cell cell;
    int add = 0;
    System.out.println("Start of filling excel file");

    for(int i=0; i<counterOfExecutions;i++){
        row = sheet.createRow(i);

        for(int j=0; j<5; j++){
            cell = row.createCell(j);
            cell.setCellValue(listOfResults.get(j+add));
        }

        add+=5;
    }


    FileOutputStream fileOutputSecond = new FileOutputStream(file);
    wb.write(fileOutputSecond);
    fileOutputSecond.close();
    System.out.println("File "+ADDRESS+" has been created.");
}

public static void main(String[] args) throws IOException {

    System.out.println("Start of creating ArrayList");
    List<String> lista = new ArrayList<>();
    for(int i = 1 ; i<550000;i++){
        lista.add("nic "+i);
    }
    System.out.println("ArrayList has been filled");


    readAndWriteAData(100999, lista);
    System.out.println("The End");

}

}

Thanks a lot in advance for any hints! :)

score 3 · Accepted Answer · answered Jan 27 '16 at 10:09

3

Please try to use SXSSFWorkbook. which will be more efficient for the better usage of it try to see the Apache POI 3.8 API

answered Jan 27 '16 at 10:09

BValluri

916
1
6
10

Thanks a lot! That was it. I reduced the time of operation more than 27 times. – JMK Jan 27 '16 at 11:44

score 1 · Answer 2 · answered Jan 27 '16 at 10:52

1

Just Look at the example

how-to-read-write-xlsx-file-in-java-apache-poi-example

answered Jan 27 '16 at 10:52

Rahul Bhawar

450
7
17

score 0 · Answer 3 · edited May 23 '17 at 12:09

0

Please refer below

How to speed up excel reading/writing

Probably this addresses the same problem; The logger configuration did the trick for me.

edited May 23 '17 at 12:09

Community

1
1

answered Jan 27 '16 at 11:10

Anirudh

150
3

Writing to an Excel file using Apache POI a huge data from ArrayList of Strings

3 Answers3