Excel cell format in JasperReport report

Question

I am working on a JasperReport post that generates an Excel file. For some reason, cell formats / types are not what they should be. For example, I have a Date object in my cell, but when I create the Excel file, it sets the cell type to Number or Long type is the text in the cell, but the cell format is the number, and also when the custom cell is the date of editing (for example, with the date 02/11/2012 changed to 03/11/2012), it converts the date to a number (41581.00).

Here is my code (it just displays a pop-up stream in a browser window with a 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 an example of the first few lines in my jasper XML report 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 do not ask me why I create the jasperreport template file on the fly, as I need it.)

Answer 1

The problem is that I am using POI 3.5 and JasperReports 3.7.0 and generating Excel XLSX format. POI 3.5 will be supported in JasperReports 3.7.1 (or just get a snapshot from SVN). So what I did, I just went back to the old Excel type (xls) file and it worked fine.

Answer 2

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

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 . Sample use here .

Answer 3

Just fyi

 setParameter(JRXlsExporterParameter.IS_AUTO_DETECT_CELL_TYPE, Boolean.TRUE); 

or IS_DETECT_CELL_TYPE, Boolean.TRUE is the one that causes Date to change the number.

Answer 4

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

check here

In property expressions

property name -> net.sf.jasperreports.export.xls.pattern

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