Apache POI getRow() returns null and .createRow fails

Question

I have the following problem using Apache POI v3.12: I need to use a XLSX file with 49 rows [0..48] as a template, fill it's cells with data and write it out as a different file, so I can reuse the template again. What I am doing is approximately this:

XSSFWorkbook wbk_template = new XSSFWorkbook(new FileInputStream    (f_wbk_template));
SXSSFWorkbook wbk = new SXSSFWorkbook(wbk_template, 50, true);

Sheet sheet = wbk.getSheet(STR_SHEET_NAME);

/ later on/

Row row = sheet.getRow(rownum);
if (null == row) {
    row = sheet.createRow(rownum);
}

Upon debugging it turns out that getRow() returns null, but the attempt to .createRow() fails with:

java.lang.IllegalArgumentException: Attempting to write a row[2] in the range [0,48] that is already written to disk.
    at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:122)
...

am I missing something here? As far as I have read in the Apache docs and forums, I need to createRow() if getRow() returns null. The sheet does not contain any rows according to .getPhysicalRows(), .getFirstRowNum() and .getLastRowNum()

Thanks.

Answer 1

See the documentation for the SXSSFWorkbook constructor that takes the XSSFWorkbook as param. You cannot override or access the initial rows in the template file. You are trying to overwrite an existing row and the API does not support this. Your exception message reflects this.

https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html#SXSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook)

For your use case, you may want to try http://jxls.sourceforge.net.

Answer 2

If you want to read or edit an exist row, you can firstly do it in xssf type, and then create the sxssf file base on the xssf file.
The code is something like below...

XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file));

//do the read and edit operation with xssf......
......
......

SXSSFWorkbook sXSSFbook = new SXSSFWorkbook(xssfWorkbook); 

//do the write operation with sxssf......
......
......