Streaming a POI workbook to the servlet output stream

I build a very large POI workbook, on my web server. Holding the entire workbook in memory , will not scale for multiple concurrent requests. Is there a way i can progressively write the workbook to the servlet output stream. That should reduce the response time , as well as mak开发者_C百科e the process memory efficient.

---

If you are about to generate Excel 2007 (xslx) then you could adapt the approach of BigGridDemo.java as described here: http://web.archive.org/web/20110821054135/http://www.realdevelopers.com/blog/code/excel

The solution is to let POI generate a container xslx as a template only and stream the actual spreadsheet data as XML into a zip output stream. Streamlining the XML generation is then up to you.

---

The situation has improved considerably since the rest of the answers were written - Streaming is now part of Apache Poi.

See the SXSSFWorkbook class, and the documentation here. It uses a streaming window over the sheet, flushing old rows outside the window to temporary files.

This is based on the BigGridDemo approach used in hlg's answer, but now part of the official distribution.

Here's the example from the documentation:

public static void main(String[] args) throws Throwable {
    // keep 100 rows in memory, exceeding rows will be flushed to disk
    SXSSFWorkbook wb = new SXSSFWorkbook(100); 
    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));
    }

    // ther 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();
}

---

Unfortunately, that's impossible when there's no means of sequential data. I'd suggest to look for another format, e.g. CSV or XML. Both can be written out sequentially. If it's coming from a DB, it can even be done more efficient since a decent DB has builtin facilities to efficiently export to those formats. You just have to stream the bytes from one to other side.

---

Did you tried with the write method direct to the HttpServletResponse.getOutputStream()?

Please take a look at the following example:

 HSSFWorkbook wb = new HSSFWorkbook();
 HSSFSheet sheet = wb.createSheet("new sheet");
 ...
 OutputStream out = response.getOutputStream();
 wb.write(out);
 out.close();

---

If you use JExcel It has sample code to read stream code to and from a Servlet. http://jexcelapi.sourceforge.net/resources/faq/

The only downside to this API looks like it only supports up to Excel 2003 inclusive.

Using POI - Can you not create the file and serve the file's bytes to the servlet output stream ?