Java JasperReport 报表中的 Excel 单元格格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2003339/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Cell Format in JasperReport Report
提问by Maksim
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).
我正在研究生成 Excel 文件的 JasperReport 报告。出于某种原因，我的单元格格式/类型不是它们应该的样子。例如，我的单元格中有 Date 对象，但是当我生成 Excel 文件时，它将单元格类型设置为 Number，或者 Long 类型是单元格中的文本，但单元格的格式是数字以及用户编辑日期单元格时（例如日期 11/02 /2012 更改为 11/03/2012）它将日期转换为数字 (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:
这是我的 jasper 报告 xml 文件中前几行的示例：
<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.)
（请不要问我为什么要即时生成 jasperreport 模板文件，这就是我需要的方式。）
采纳答案by Maksim
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.
问题是/是因为我使用 POI 3.5 和 JasperReports 3.7.0 并生成 XLSX Excel 格式。JasperReports 3.7.1 将支持 POI 3.5（或仅从 SVN 获取快照）。所以我所做的我只是回到旧的 Excel 类型 (xls) 文件，它工作得很好。
回答by Shourjya
Just FYI
仅供参考
setParameter(JRXlsExporterParameter.IS_AUTO_DETECT_CELL_TYPE, Boolean.TRUE);
or IS_DETECT_CELL_TYPE, Boolean.TRUEis the one which makes the Date to change to number .
或者IS_DETECT_CELL_TYPE, Boolean.TRUE是使 Date 更改为 number 的那个。
回答by Alex K
In new version of JasperReportsthe parameter net.sf.jasperreports.export.xls.patternwas introduced.
在新版本中引入了JasperReports该参数net.sf.jasperreports.export.xls.pattern。
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.
回答by Rex
JasperReportsversion 4.1.1 net.sf.jasperreports.export.xls.patternwas introduced.
引入了JasperReports4.1.1 版net.sf.jasperreports.export.xls.pattern。
In properties expressions
在属性表达式中
properties name>> net.sf.jasperreports.export.xls.pattern
属性名称>> net.sf.jasperreports.export.xls.pattern
properties value>> @ for text, yyyy-mm-dd for date format, #,##0.00;-#,##0.00 for currency, etc....
属性值>> @ 用于文本，yyyy-mm-dd 用于日期格式，#,##0.00;-#,##0.00 用于货币等....
