How to read large xlsm files using apache POI

Question

I am trying to read a large xlsm file for which i am getting heap space error,i am using XSSFWorkbook for the large file but still i am getting this .And also i have set the VM argumets -Xmx1024m to eclipse.Here is my code

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

    OPCPackage pkg = OPCPackage.open(new File("D:\\resources\\1712_Reporting.xlsm"));
        XSSFWorkbook wb_template;
        wb_template = new XSSFWorkbook(
            pkg
        );
    System.out.println("package loaded");
    SXSSFWorkbook wb = new SXSSFWorkbook(wb_template);      wb.dispose();
    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("D:\\new_file.xlsm"));
    wb.write(out);
    out.close();  }

}

score 0 · Answer 1 · answered Feb 13 '18 at 15:04

SXSSFWorkbook is for streaming-writing, not reading. Did you try with XSSFWorkbook instead? This will still require quite some memory so might still go OOM with 1024m, depending on the size of the workbook.

Another approach is a streamed reading approach, see e.g. https://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api for some description of this approach. There will be some features that are not supported there, though, so it might or might not be applicable for your use case.

How to read large xlsm files using apache POI

1 Answers1