Apache POI getRow() returns null and .createRow fails

17
votes

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.

3
javaexcelapache-poi
Why are you using SXSSFWorkbook for this case? Why not just use only XSSFWorkbook for a file of this size? – Gagravarr
Yes Gagravarr, I realized that streaming interfaces are not capable of doing what I wanted. I already modified my code and everything works as expected. Thank you. – Plamen Vasilev
Using WorkbookFactory.create(inputStream) will be better which will autodetect and create HSSFWorkbook / XSSFWorkbook from a given inputstream – Sumeet Patil

3 Answers

11
votes

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.

6
votes

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......
......
......
0
votes

Please have a look at this constructor arguments enter image description here

XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(exportExcelTo));
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, -1, Boolean.FALSE, Boolean.TRUE);