Export to Excel JSF and PrimeFaces

Question

Using JDK 1.6, JSF 2.1, PrimeFaces 2.2.1, POI 3.2, and Apache Tomcat 7

I am trying to setup a servlet to allow a download of an excel file based on the user selection. The excel document is created at runtime.

No errors and the code does get into the servlet.

I click the button and nothing happens. I am not using the datatable export that PrimeFaces uses because I need to do reordering and custom formatting on the data in the Excel document.

ExportExcelReports.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {       
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=\"my.xls\"");                

    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFSheet sheet = workbook.createSheet();
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellValue(0.0);

    FileOutputStream out = new FileOutputStream("my.xls");
    workbook.write(out);
    out.close();
}

ProjectReportBean.java

public void getReportData() {
    try {
        FacesContext ctx = FacesContext.getCurrentInstance();
        ExternalContext ectx = ctx.getExternalContext();
        HttpServletRequest request = (HttpServletRequest) ectx.getRequest();
        HttpServletResponse response = (HttpServletResponse) ectx.getResponse();
        RequestDispatcher dispatcher = request.getRequestDispatcher("/ExportExcelReports");
        dispatcher.forward(request, response);
        ctx.responseComplete();
    } catch (Exception e) {}
}

index.xhtml

<h:form id="reportsForm">
    <h:outputLabel for="report" value="Reports" /><br />
    <h:selectOneMenu id="report" value="#{projectReportBean.selectedReport}" required="true" requiredMessage="Select Report">
        <f:selectItem itemLabel="---" noSelectionOption="true" />
        <f:selectItems value="#{projectReportBean.reports}" />
    </h:selectOneMenu>

    <p:commandButton action="#{projectReportBean.getReportData}" value="Export" update="revgrid" />                      
</h:form>

Answer 1

There are two problems.

The first problem is that the <p:commandButton> sends by default an Ajax request. This request is fired by JavaScript code. However, JavaScript can't do anything with a response which contains a file download. Due to security restrictions JavaScript can't spawn a Save As dialogue or something. The response is basically totally ignored.

You need to add ajax="false" to <p:commandButton> to turn ajax off so that the button fires a normal synchronous HTTP request, or you need to replace it by standard <h:commandButton>.

<p:commandButton ajax="false" ... />

or

<h:commandButton ... />

The second problem is that your servlet doesn't write the Excel file to the response at all, but instead to a local file which is stored in server's working directory. Basically, the HTTP response contains nothing. You need to pass HttpServletResponse#getOutputStream() to the WorkBook#write() method.

workbook.write(response.getOutputStream());

On an unrelated note, I wonder how the servlet is useful here. Do you want to reuse it outside JSF? If not, you don't necessarily need to dispatch to the servlet at all, but just execute the same code in bean's action method. That empty catch block is also not nice. I'd just declare it as throws in method or at least rethrow it as new FacesException(e).

---

Update as per the comments you seem not be interested in the servlet at all. Here's a minor rewrite how you could send the Excel file programmatically in a JSF action method.

public void getReportData() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellValue(0.0);

    FacesContext facesContext = FacesContext.getCurrentInstance();
    ExternalContext externalContext = facesContext.getExternalContext();
    externalContext.setResponseContentType("application/vnd.ms-excel");
    externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"my.xls\"");

    workbook.write(externalContext.getResponseOutputStream());
    facesContext.responseComplete();
}

Answer 2

Here is the that i wrote before and working case ;

xhtml ;

<h:panelGrid id="viewCommand" style="float:right;" >
                        <p:commandButton value="Export Excel" icon="ui-icon-document"
                            ajax="false" actionListener="#{xxx.export2Excel}"
                            rendered="#{xxx.showTable}">
                            <p:fileDownload value="#{xxx.exportFile}"
                                contentDisposition="attachment" />
                        </p:commandButton></h:panelGrid>

Java side(with POI) ;

protected void lOBExport2Excel(List table) throws Throwable {
    Row row = null;
    Cell cell = null;
    try {

        Workbook wb = new HSSFWorkbook();
        HSSFCellStyle styleHeader = (HSSFCellStyle) wb.createCellStyle();
        HSSFFont fontHeader = (HSSFFont) wb.createFont();
        fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        styleHeader.setFont(fontHeader);
        Sheet sheet = wb.createSheet("sheet");
        row = sheet.createRow((short) 0);

        for (int i = 0; i < columnNames.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(columnNames.get(i));
            cell.setCellStyle(styleHeader);
        }

        int j = 1;

        for (DBData[] temp : tabularData) {
            row = sheet.createRow((short) j);
            for (int k = 0; k < temp.length; k++) {
                HSSFCellStyle styleRow = (HSSFCellStyle) wb.createCellStyle();
                HSSFFont fontRow = (HSSFFont) wb.createFont();
                fontRow.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
                styleRow.setFont(fontRow);
                cell = row.createCell(k);
                setStyleFormat(temp[k].getDataType(), styleRow, wb);
                cell.setCellValue(temp[k].toFullString());
                cell.setCellStyle(styleRow);
            }

            j++;
        }

        String excelFileName = getFileName("xls");

        FileOutputStream fos = new FileOutputStream(excelFileName);
        wb.write(fos);
        fos.flush();
        fos.close();

        InputStream stream = new BufferedInputStream(new FileInputStream(excelFileName));
        exportFile = new DefaultStreamedContent(stream, "application/xls", excelFileName);


    } catch (Exception e) {
        catchError(e);
    }

}

Answer 3

I'd also recommend looking at using the PrimeFaces FileDownload. Depending on your structure it could make this all a whole lot easier. You wouldn't have to create a servlet just a managed bean that can provide a ContentStream.

Since you already have the servlet written though, there's no point to change, just food for thought.