How to write to an existing file using SXSSF?

Question

I have an .xlsx file with multiple sheets containing different data. Of all the sheets one sheet needs to accommodate close to 100,000 rows of data, and the data needs to be written using Java with poi.

This seems quite fast and simple with SXSSFWorkbook, where I can keep only 100 rows in memory, but the disadvantage is that I can only write to a new file (or overwrite existing file).

Also, I am not allowed to 'load' an existing file, i.e

 SXSSFWorkbook wb = new SXSSFWorkbook(file_input_stream) 

is not allowed.

I can use Workbook factory:

Workbook workbook = new SXSSFWorkbook();
workbook = WorkbookFactory.create(file_input_stream);

but when the time comes for me to flush the rows,

 ((SXSSFSheet)sheet).flushRows(100); 

I get the error that type conversion is not allowed from XSSFSheet to SXSSFSheet.

I tried to see if there was any way to copy sheets across different workbooks, but so far it seems it has to be done cell by cell.

Any insights on how to approach this problem?

Solution

You are probably having a template to which you want to add large data. You need to use the SXSSFWorkbook(XSSFWorkbook) constructor:

XSSFWorkbook wb = new XSSFWorkbook(new File("template.xlsx"));
SXSSFWorkbook wbss = new SXSSFWorkbook(wb, 100);
Sheet sheet = wbss.createSheet("sheet1");
// now add rows to sheet

Comments

[Gagravarr]

As explained in the docs, don't use an InputStream if you have a File! Files are lower memory

[N.Neupane]

But make sure you get the file from one path and write it on another path.