CODEWITHSUNDEEP
javaexcelarraylistapache-poi
JMK
JMK

Reputation: 239

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

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! :)

Upvotes: 3

Views: 5083

Answers (3)

Anirudh
Anirudh

Reputation: 160

Please refer below

How to speed up excel reading/writing

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

Upvotes: 0

Rahul Bhawar
Rahul Bhawar

Reputation: 450

Just Look at the example

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

Upvotes: 1

BValluri
BValluri

Reputation: 956

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

Upvotes: 5

Related Questions