使用Apache POI向Excel文件写入来自String的ArrayList的大量数据

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

我有一个字符串的ArrayList非常庞大(大约超过500k和更多的项目)。有没有可能加快将此数组写入excel文件?现在它需要一段时间,我不知道我是否做了我能做的一切(也许这是.get方法的问题?)。请参考以下代码(请不要为详细信息付费 - 此类仅为测试目的而创建):

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

非常感谢任何提示! :)

3 个解决方案

#1

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

请尝试使用SXSSFWorkbook。为了更好地使用它会更有效率尝试查看Apache POI 3.8 API

#2

Just Look at the example

看看这个例子吧

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

#3

Please refer below

请参考下文

How to speed up excel reading/writing

如何加快阅读/写作的速度

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

可能这解决了同样的问题;记录器配置对我来说很有用。

#1