Java – excel cell format in jasperreport Report
I'm studying the jasperreport report that generates Excel files For some reason, my cell format / type is not how they should be For example, I have a date object in the cell, but when I generate an excel file, it sets the cell type to number, or the long type is the text in the cell, but the cell format is a number, and when the user edits the date cell (for example, the date is 11 / 02 / 2012 is changed to 11 / 03 / 2012), it converts the date to a number (41581.00)
This is my code (it just outputs the pop-up 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; }
The following are examples of the 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 generate jasperreport template files dynamically, which is the way I need.)
Solution
The parameter net. Net has been introduced in the new version of Jasper reports sf. jasperreports. export. xls. pattern.
example:
<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>
The information about this parameter is here The sample used is here