Reputation: 16931
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.)
Upvotes: 8
Views: 37681
Reputation: 1
I had a similar issue which converts date format Apr 14, 2023, 09:29:00 PM to Oct 23, 2022, 02:34:00 a when export report to excel with jasper report version 8 . I was able to fix it by making following property inside bean id="xlsExportParameters" in applicationContext.xml (WEB-INF/applicationContext.xml) and restart the server.
<property name="detectCellType" value="false"/>
Upvotes: 0
Reputation: 16931
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.
Upvotes: 3
Reputation: 77
JasperReports version 4.1.1 net.sf.jasperreports.export.xls.pattern was introduced.
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....
Upvotes: 2
Reputation: 22857
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.
Upvotes: 10
Reputation: 51
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 .
Upvotes: 2