0

I am getting 500k records per request from the Database then mapping the data to a list of POJO classes then writing the records to an excel sheet (I have the requirement to export the data to an excel sheet), is there any better way to generate ".xlsx " file? Here I need to iterate the list (loop) (500k times) get the record and then write the record to .xlsx file.

Is there any better way to do it, for better performance and leat memory use?

James Z
  • 12,209
  • 10
  • 24
  • 44
Bapu
  • 59
  • 1
  • 10

2 Answers2

1

The performance of Apache POI can be greatly improved if you use the streaming API instead of the normal one. POI will keep all your data in the memory while you are writing, and with large files, this can be quite expensive. What you could do is use the streaming API and instruct POI to flush data progressively.

Here is an example from POI's site:

  public static void main(String[] args) throws Throwable {
       SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
       Sheet sh = wb.createSheet();
       for(int rownum = 0; rownum < 1000; rownum++){
           Row row = sh.createRow(rownum);
           for(int cellnum = 0; cellnum < 10; cellnum++){
               Cell cell = row.createCell(cellnum);
               String address = new CellReference(cell).formatAsString();
               cell.setCellValue(address);
           }
       }
       // Rows with rownum < 900 are flushed and not accessible
       for(int rownum = 0; rownum < 900; rownum++){
         Assert.assertNull(sh.getRow(rownum));
       }
       // the last 100 rows are still in memory
       for(int rownum = 900; rownum < 1000; rownum++){
           Assert.assertNotNull(sh.getRow(rownum));
       }
       FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
       wb.write(out);
       out.close();
       // dispose of temporary files backing this workbook on disk
       wb.dispose();
   }

Check Apache POI's website for more. (under SXSSF (Streaming Usermodel API))

Renis1235
  • 4,116
  • 3
  • 15
  • 27
0

Using POI for streaming data is a pain, because the best it can do is to spool the data to disc and then combine it with the rest when you've finished - it can't stream to the output as the input arrives. There are other issues with POI:

  • It's a memory hog.
  • It has quite a large set of dependencies for something that I would expect to be quite a small part of your overall requirements.
  • It's not quick.
  • It allows you to produce broken files quite easily.

The counter to those deficiencies is that it lets you do anything at all with OOXML files in Java, and is the only thing to do so.

I have a couple of (in house) projects that use POI for exporting XLSX data and am working on another and was keen to avoid using POI. I came across this question: How to create and write to Excel file (.xlsx)? and was inspired by the answers given to produce this: https://github.com/Yaytay/streaming-xlsx-writer

All it does is stream XLSX files with a single sheet to an OutputStream as data comes in. There is some basic formatting available (if you need more feel free to ask). The memory overhead is almost nothing and it has no dependencies.

Yaytay
  • 493
  • 4
  • 13