POI performance

Question

I am using POI in my J2EE web application to generate a workbook. However, i find that POI takes around 3 mins to create a workbook with 25K rows(with around 15 columns each). Is this a POI performance issue , or is it justified to take that much of time? Are there other APIs known for better performance ?

Answer 1

The performance of writing large files with POI can be heavily reduced if you used the 'streaming' POI API instead of the standard one. Indeed by default POI will keep all your data in memory before writing all in one go at the end. The memory footprint of this can be ridiculously large for big files. Instead using the streaming API you can control how memory is used and data is written to disk progressively.

In order to create a streaming workbook, use something like :

  SXSSFWorkbook book = new SXSSFWorkbook();    book.setCompressTempFiles(true);    SXSSFSheet sheet = (SXSSFSheet) book.createSheet();   sheet.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk   // ... 

Answer 2

I would be very surprised to see POI take that much time to generate such a file. I just generated a sheet with 30000 rows x 10 cells in about 18s (no formatting, to be fair). The cause might be one of the following:

• POI logging might be turned on, as described here
• you are running from swap memory
• your VM available heap might be very low