I'm using Apache POI library to import a large amount of data to Excel files. I can't use HSSF model, because of it needs too much memory and Java throws memory heap exception. The way I found is SXSSF workbook model, that writes every N rows to the file instead of storing all workbook in the memory. But I can't find the way, how to save it to .xls (Excel 2003 and lower format) instead of .xlsx.
File f = new File("file.xlsx");
f.createNewFile();
SXSSFWorkbook wb = new SXSSFWorkbook(200);
//inputing values here
FileOutputStream fileOut = new FileOutputStream(f);
wb.write(fileOut);
fileOut.close();
You can't.
As explained on the POI Components page, HSSF is for working with .xls files (OLE2 based), and XSSF/SXSSF is for working with .xlsx files (.xlsx)
If you want to generate a .xls file, you'll have to use the HSSF UserModel. There is no streaming write support for HSSF, only streaming read.
The .xls file format isn't so well suited to streaming write as .xlsx (more back/forwards references, offsets etc), so there's no streaming write support in HSSF. Just bump up your Java heap size, or switch to SXSSF for .xlsx, or just use something simple like CSV!
.
Update
If you need to test how much memory you ought to be giving to your system for writing with HSSF, or even XSSF, I'd suggest you try using the SSPerformanceTest example, which can be run on the command line. Fire that up with all the jars, and a given heap size, and see if it completes. It takes arguments of the number of rows and columns, and the type of file to create. Using a nightly build and trying for a 40k row / 10 columns, I can get it to complete quickly in a 90mb heap:
$ java -Xmx90m -classpath poi-3.10-beta3-20131219.jar:poi-examples-3.10-beta3-20131219.jar:poi-ooxml-3.10-beta3-20131219.jar \
org.apache.poi.ss.examples.SSPerformanceTest HSSF 40000 10 1
Elapsed 1 seconds
