Excel Cell Format in JasperReport Report

Question

I'm working on JasperReport Report that generates Excel file. For some reason my cell formats/types are not how they should be. For example I have Date object in my cell but when I generate Excel file it sets cell type to Number, or Long type is text in the cell but cell's format is number and also when user edit date cell (for example with date 11/02/2012 changed to 11/03/2012) it converts date to number (41581.00).

Here is my code (it just outputs the popup stream to the browser window with the report):

public void generateXLSPopup(String tmpltFileLocation, Map<String, Object> params, Collection vo) {
    log.fine("ReportEngine: Start Generate XLS Popup Report Function!");

    Filename f = new Filename(tmpltFileLocation);

    String xlsFileName = f.getFileName() +  "_" + sDateFormated + ".xlsx";

    try {
        JasperPrint jasperPrint = getJRPrint(tmpltFileLocation, params, new JRBeanCollectionDataSource(vo));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        JRXlsxExporter exporter = getCommonXlsxExporter();

        exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
        exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, baos); // fill byte array output stream

        exporter.exportReport();

        FacesContext context = FacesContext.getCurrentInstance();
        HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getResponse();

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-disposition", "attachment; filename=" + xlsFileName);
        response.setContentLength(baos.size());
        response.getOutputStream().write(baos.toByteArray());
        context.responseComplete();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    log.fine("ReportEngine: Finish Generate XLS Popup Report Function!");
}


private JRXlsxExporter getCommonXlsxExporter(){
    JRXlsxExporter exporter = new JRXlsxExporter();
    exporter.setParameter(JRXlsExporterParameter.IGNORE_PAGE_MARGINS, Boolean.TRUE);
    exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
    exporter.setParameter(JRXlsExporterParameter.IS_AUTO_DETECT_CELL_TYPE, Boolean.TRUE);
    exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
    exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
    exporter.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
    //exporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);


    return exporter;
}

And here is the example of first few lines in my jasper report xml file:

<textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement style="alternateStyle" stretchType="RelativeToBandHeight" x="0" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression class="java.lang.Long"><![CDATA[$F{id}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement style="alternateStyle" stretchType="RelativeToBandHeight" x="200" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression class="java.lang.String"><![CDATA[$F{emsProdNo}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement style="alternateStyle" stretchType="RelativeToBandHeight" x="100" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression class="java.lang.String"><![CDATA[$F{courseName}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" pattern="MMMMM dd, yyyy" isBlankWhenNull="true">
                <reportElement style="alternateStyle" stretchType="RelativeToBandHeight" x="300" y="0" width="98" height="20"/>
                <textElement>
                    <font isUnderline="true"/>
                </textElement>
                <textFieldExpression class="java.util.Date"><![CDATA[$F{startDate}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement style="alternateStyle" stretchType="RelativeToBandHeight" x="474" y="0" width="81" height="20"/>
                <textElement/>
                <textFieldExpression class="java.lang.String"><![CDATA[$F{endDateStr}]]></textFieldExpression>
            </textField>

(Please don't ask me why I'm generating jasperreport template file on the fly, that's how I need it.)

Answer 1

In new version of JasperReports the parameter net.sf.jasperreports.export.xls.pattern was introduced.

The sample:

<textField pattern="EEE, MMM d, yyyy">
    <reportElement x="100" y="12" width="75" height="11">
        <property name="net.sf.jasperreports.export.xls.pattern" value="ddd, mmm d, yyyy"/>
    </reportElement>
    <textElement textAlignment="Right"/>
    <textFieldExpression class="java.sql.Timestamp"><![CDATA[$F{dateField}]]>
    </textFieldExpression>
</textField>

Information about this parameter is here. The sample of using is here.

Answer 2

The problem is/was that because I'm using POI 3.5 and JasperReports 3.7.0 and Generating XLSX Excel format. POI 3.5 will be supported in JasperReports 3.7.1 (or just get snapshot from SVN). So what I did I just went back to the old Excel type (xls) file and it worked perfect.

Answer 3

JasperReports version 4.1.1 net.sf.jasperreports.export.xls.pattern was introduced.

check here

In properties expressions

properties name >> net.sf.jasperreports.export.xls.pattern

properties value >> @ for text, yyyy-mm-dd for date format, #,##0.00;-#,##0.00 for currency, etc....

Answer 4

Just FYI

setParameter(JRXlsExporterParameter.IS_AUTO_DETECT_CELL_TYPE, Boolean.TRUE); 

or IS_DETECT_CELL_TYPE, Boolean.TRUE is the one which makes the Date to change to number .