19

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.

Plamen Vasilev
  • 304
  • 1
  • 2
  • 6
  • Why are you using SXSSFWorkbook for this case? Why not just use only XSSFWorkbook for a file of this size? – Gagravarr Jun 16 '15 at 14:25
  • 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 Jun 16 '15 at 16:37
  • Using WorkbookFactory.create(inputStream) will be better which will autodetect and create HSSFWorkbook / XSSFWorkbook from a given inputstream – Sumeet Patil Oct 17 '18 at 05:04

3 Answers3

12

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.

IceMan
  • 1,398
  • 16
  • 35
  • Thanks IceMan. This explains the behavior I am experiencing. Thanks for the link to jxls - I will give it a try. I didn't know Apache POI is lacking this functionality. – Plamen Vasilev Jun 16 '15 at 13:34
  • If you want to preserve the template file and not overwrite it, try using jxls, assuming you have the flexibility to do so. You can do it with Apache POI as well but without using the streaming workbook API. – IceMan Jun 16 '15 at 13:39
  • 1
    I am already trying to get rid of jxl library in favor of Apache POI, so I don't really want to introduce another excel access library. Can you give me pointers as to how to achieve this goal using Apache POI, or should I open another question specifically for this? Thanks. – Plamen Vasilev Jun 16 '15 at 13:45
  • It looks like http://stackoverflow.com/questions/714172/using-excel-templates-with-apache-poi will do. Thanks again. – Plamen Vasilev Jun 16 '15 at 13:47
  • Jxls is built on top of Apache POI. This is already answered on SO. Let me try to find it. – IceMan Jun 16 '15 at 13:48
  • Yes, that's the example I was referring to. – IceMan Jun 16 '15 at 13:48
6

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......
......
......
jacky
  • 649
  • 1
  • 8
  • 22
1

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);
Ziaullhaq Savanur
  • 1,848
  • 2
  • 17
  • 20