读取模板xlsm并使用SXSSF（POI）将大数据写入现有工作表

I am able to read existing xlsm using XSSF and write data into worksheet using SXSSF. Finally output it as another xlsm with Outputstream.
SXSSF is mentioned for writing xlsx in documentation
Is it right approach to read and write xlsm for huge data or the file will get corrupted if done by this solution ?
This is the sample code that works,

我能够使用XSSF读取现有的xlsm，并使用SXSSF将数据写入工作表。最后使用Outputstream将其输出为另一个xlsm。提到SXSSF用于在文档中编写xlsx是不是正确的方法来读取和写入大数据的xlsm，否则如果由此解决方案完成，文件将被破坏？这是有效的示例代码，

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

    OPCPackage pkg = OPCPackage.open(new File("sample.xlsm"));
        XSSFWorkbook wb_template;
        wb_template = new XSSFWorkbook(
            pkg
        );
        System.out.println("package loaded");
    SXSSFWorkbook wb = new SXSSFWorkbook(wb_template); 
    wb.setCompressTempFiles(true);

    SXSSFSheet sh = (SXSSFSheet) wb.createSheet();
    sh.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk
    for(int rownum = 4; rownum < 5000; rownum++){
       Row row = sh.createRow(rownum);
       for(int cellnum = 0; cellnum < 10; cellnum++){
        Cell cell = row.createCell(cellnum);
        String address = new CellReference(cell).formatAsString();
        cell.setCellValue(address);
       }

    }
    FileOutputStream out = new FileOutputStream(new File("C:\\ouput\\new_file.xlsm"));
    wb.write(out);
    out.close();  }

1 个解决方案

#1

I don't see much wrong with your current code, however on the apache website it says that SXSSF leaves temporary files on your computer, and that you must call dispose as below:

我对你当前的代码没有太大的错误，但是在apache网站上它说SXSSF会在你的计算机上留下临时文件，你必须按如下方式调用dispose：

Note that SXSSF allocates temporary files that you must always clean up explicitly, by calling the dispose method.
SXSSFWorkbook wb = new SXSSFWorkbook(100);
// dispose of temporary files backing this workbook on disk
wb.dispose();

I would recommend performing the dispose as it seems to prevent leftover information being stored. On whether it is the right approach, I would suggest taking a test file and giving it a couple of goes, how well this code executes will will be affected by your system (cpu power, memory e.t.c) and the size of the documents you are processing.

我建议执行dispose，因为它似乎可以防止存储剩余的信息。关于它是否是正确的方法，我建议拿一个测试文件并给它几个去，这个代码执行的程度将受到你的系统（CPU功率，内存等）以及你的文档大小的影响处理。

Good Luck!

祝你好运！

#1