Streaming xlsx files

Helga @ March 20, 2010
Filed under: Code — Tags: , ,

We use Apache POI to export lists of drawing deliveries to excel files. To overcome the limitation of 65.535 rows per file with xls (Excel 97) we introduced xlsx (Excel 2007) support which allows for up to 1.048.575 rows. In addition to the increased amount of data which has to be handled, we encountered increased memory consumption by XMLBeans which is used by POI for creation of the xlsx document tree. The bigger memory footprint of xssf compared to hssf model is a well-known problem. In POI 3.5 there was an example included demonstrating a workaround for big tables: BigGridDemo.java

This example amazingly works around the POI library, actually using it only to generate a container xml file and then streaming the content as plain text into the appropriate xml part of the xlsx zip archive. Of course it would be much nicer to leave the knowledge about the file format enclosed in the library, but POI isn’t able to write to streams on the fly. By adapting the big grid demo it is possible to write directly to the http servlet response:

  1. ZipOutputStream zos = new ZipOutputStream(responseStream);
  2. ZipFile templateZip = new ZipFile(template);
  3. Enumeration<ZipEntry> templateEntries = templateZip.entries();
  4. try {
  5.   while (templateEntries.hasMoreElements()) {
  6.     // copy all template content to the ZipOutputStream zos
  7.     // except the sheet itself
  8.   }
  9.   zos.putNextEntry(new ZipEntry(sheetName)); // now the sheet
  10.   OutputStreamWriter sheetOut = new OutputStreamWriter(zos, "UTF-8");
  11.   try {
  12.     sheetOut.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
  13.     sheetOut.write("<worksheet><sheetData>");
  14.     // write the content – rows and cells
  15.     sheetOut.write("</sheetData></worksheet>");
  16.   }
  17.   finally { sheetOut.close(); }
  18. finally { zos.close(); }

Email this Share this on Facebook Share this on LinkedIn Tweet This! RSS feed for comments on this post. TrackBack URL

Leave a comment