How to export rich:dataTable to excel

Curt picture Curt · Nov 2, 2012 · Viewed 8k times · Source

I would like to export the contents of a <rich:dataTable> or <rich:extendedDataTable> to excel.

  • I have see that PrimeFaces has the "exporter feature" http://www.primefaces.org/showcase/ui/exporter.jsf

  • I would like to be able to do something similar to this only with out using PrimeFaces, but with richFaces (version 3.3.3)...(I would like to migrate to RichFaces 4 at some point in the future but am stuck with 3.3.3 for now)

  • I have read that it is possible to build your own using http://poi.apache.org/ but I do not know where to start on implementing something like this...

Any thoughts about how best to preform the desired export and examples would be much appreciated!

Answer

BalusC picture BalusC · Nov 2, 2012

Using POI in JSF isn't really different from using POI in plain Java. Just have a collection of items representing each record. You must already have it as you're using a datatable which also takes such a collection. You just have to iterate over exactly the same collection to create an excel sheet in POI.

Here's a kickoff example, where items is a List<Item> which you're also using in the datatable:

Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("sheet title");
int rowIndex = 0;

for (Item item : items) {
    Row row = sheet.createRow(rowIndex++);
    int columnIndex = 0;

    row.createCell(columnIndex++).setCellValue(item.getId());
    row.createCell(columnIndex++).setCellValue(item.getName());
    row.createCell(columnIndex++).setCellValue(item.getValue());
    // ...
}

workbook.write(someOutputStream); // Write the Excel result to some output.

In order to offer this as a download to the JSF response, you need to provide the ExternalContext#getResponseOutputStream() as someOutputStream. You also need to set the response content type (so that the browser knows what application to associate with it) and the response content disposition (so that it's opened as an attachment and that it has a valid filename).

FacesContext context = FacesContext.getCurrentInstance();
ExternalContext externalContext = context.getExternalContext();
externalContext.responseReset(); 
externalContext.setResponseContentType("application/vnd.ms-excel");
externalContext.setResponseHeader("Content-Disposition", "attachment;filename=export.xls");
workbook.write(externalContext.getResponseOutputStream());
context.responseComplete(); // Prevent JSF from performing navigation.

In the end, FacesContext#responseComplete() must be invoked to prevent JSF from performing the default navigation task (which would only corrupt the Excel file by appending some HTML output of the navigated page to the end).

Note that the above JSF example assumes JSF 2.x. If you're actually using JSF 1.x, which lacks several convenience ExternalContext delegate methods, then you'd need to grab the raw HttpServletResponse by ExternalContext#getResponse() and perform the actions on it. See also How to provide a file download from a JSF backing bean?