jasperreports大型excel文件 [英] jasperreports large excel file
本文介绍了jasperreports大型excel文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一些报告，我使用 JasperReports 库导出它们。除excel文件（xls）外，一切都很好。例如，当我尝试将包含751行的精确报告导出为excel文件时，没有问题，excel文件将被正确下载，但是当它超过800行时，它将显示一些不相关的字符。上面提到的行大小仅适用于此报告，而对于其他报告，限制是针对不同的数字。在将库从版本2.3升级到6.1.0之前，此问题不存在，现在我尝试了6.2.2。
I have some reports which I export them using JasperReports library. It is all fine except excel files (xls). For example when I try to export an exact report with 751 rows as an excel file, there are no problems and the excel file will be downloaded correctly, but when it exceeds 800 rows, it will show some unrelated characters. The row size mentioned above is just for this report and for other reports the limit is on different numbers. This problem does not exist before I upgrade the libraries from version 2.3 to 6.1.0 and now I tried 6.2.2.
这是我创建excel文件的方式：
This is how I create my excel file:
JasperPrint jasperPrint;
try {
jasperPrint = getReportPrint(request, baseForm, "excel");
} catch (ReportNoDataException e) {
return handleNoData(mapping, request, e);
}
try {
JRXlsExporter jrXlsExporter = new JRXlsExporter();
try {
jrXlsExporter.setExporterInput(new SimpleExporterInput(jasperPrint));
jrXlsExporter.setExporterOutput(new SimpleOutputStreamExporterOutput(response.getOutputStream()));
SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
configuration.setIgnoreCellBorder(true);
configuration.setWrapText(true);
configuration.setWhitePageBackground(false);
configuration.setRemoveEmptySpaceBetweenColumns(true);
configuration.setRemoveEmptySpaceBetweenRows(true);
configuration.setAutoFitPageHeight(true);
configuration.setDetectCellType(true);
configuration.setSheetDirection(RunDirectionEnum.RTL);
jrXlsExporter.setConfiguration(configuration);
jrXlsExporter.exportReport();
} catch (IOException e) {
log.error("IOException occured" + e);
e.printStackTrace();
} catch (JRException e) {
log.error("JRException occured while exporting for xls format", e);
e.printStackTrace();
}
} catch (Exception e) {
throw e;
}
String filename="report";
response.setHeader("Content-disposition","attachment; filename=\""+filename+".xls\"");
putSearchForm(request, baseForm);
return null;
这里是报告代码：
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="mtdTankhahEdaratMojoodiExcelReport" pageWidth="595" pageHeight="60" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" scriptletClass="report.webapp.tlmReportScriptlet" uuid="4354b1d9-63b2-4236-b190-ac5b2fb0f464">
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<queryString>
<![CDATA[]]>
</queryString>
<field name="no" class="java.lang.String">
<fieldDescription><![CDATA[no]]></fieldDescription>
</field>
<field name="baseDate" class="java.lang.String">
<fieldDescription><![CDATA[baseDate]]></fieldDescription>
</field>
<field name="pardakht" class="java.lang.Double">
<fieldDescription><![CDATA[pardakht]]></fieldDescription>
</field>
<field name="daryaft" class="java.lang.Double">
<fieldDescription><![CDATA[daryaft]]></fieldDescription>
</field>
<field name="zinaf" class="java.lang.String">
<fieldDescription><![CDATA[zinaf]]></fieldDescription>
</field>
<field name="sanadType" class="java.lang.String">
<fieldDescription><![CDATA[sanadType]]></fieldDescription>
</field>
<variable name="mande" class="java.lang.Double">
<variableExpression><![CDATA[($F{pardakht}==null || $F{daryaft}==null) ? new Double(0) :
(new Double(($F{daryaft}.doubleValue()-$F{pardakht}.doubleValue())+
$V{mande}.doubleValue()))]]></variableExpression>
<initialValueExpression><![CDATA[new Double("0")]]></initialValueExpression>
</variable>
<title>
<band height="30" splitType="Stretch">
<staticText>
<reportElement key="staticText-4" x="0" y="0" width="30" height="30" uuid="36cd81f4-073b-4023-b5b8-716d43a4d377">
<property name="com.jaspersoft.studio.unit.y" value="pixel"/>
<property name="com.jaspersoft.studio.unit.x" value="pixel"/>
</reportElement>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12" isBold="true"/>
</textElement>
<text><![CDATA[ردیف]]></text>
</staticText>
<staticText>
<reportElement key="staticText-8" x="275" y="0" width="80" height="30" uuid="0827df09-4353-424e-94fa-dd8cc7d5c6ac">
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
</reportElement>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12" isBold="true"/>
</textElement>
<text><![CDATA[نوع سند]]></text>
</staticText>
<staticText>
<reportElement key="staticText-9" x="190" y="0" width="85" height="30" uuid="13490ca9-0ba0-4315-b5a0-745ce020f9e1">
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
</reportElement>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12" isBold="true"/>
</textElement>
<text><![CDATA[تاریخ سند]]></text>
</staticText>
<staticText>
<reportElement key="staticText-12" x="110" y="0" width="80" height="30" uuid="544625a2-83d3-405d-a117-c0fa6820813a">
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
</reportElement>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12" isBold="true"/>
</textElement>
<text><![CDATA[شماره سند]]></text>
</staticText>
<staticText>
<reportElement key="staticText-14" x="355" y="0" width="80" height="30" uuid="fc76869a-b6b1-4223-8ab3-26a870a23e08">
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
</reportElement>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12" isBold="true"/>
</textElement>
<text><![CDATA[دریافت]]></text>
</staticText>
<staticText>
<reportElement key="staticText-14" x="515" y="0" width="80" height="30" uuid="f53ebc1b-eaaa-487b-bd23-ee62a1534caa"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12" isBold="true"/>
</textElement>
<text><![CDATA[مانده]]></text>
</staticText>
<staticText>
<reportElement key="staticText-14" x="435" y="0" width="80" height="30" uuid="aa3bda32-9b4c-4e53-927a-f6750256fb51"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12" isBold="true"/>
</textElement>
<text><![CDATA[پرداخت]]></text>
</staticText>
<staticText>
<reportElement key="staticText-12" x="30" y="0" width="80" height="30" uuid="b8c6b055-1b5e-4923-954b-a4eed5bab543">
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
</reportElement>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12" isBold="true"/>
</textElement>
<text><![CDATA[ذینفع]]></text>
</staticText>
</band>
</title>
<detail>
<band height="30" splitType="Stretch">
<property name="com.jaspersoft.studio.unit.height" value="pixel"/>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
<reportElement key="textField-1" stretchType="RelativeToTallestObject" x="0" y="0" width="30" height="30" isPrintWhenDetailOverflows="true" uuid="263cfc8f-7812-4797-843c-42fe00045f98">
<property name="com.jaspersoft.studio.unit.width" value="pixel"/>
</reportElement>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12"/>
</textElement>
<textFieldExpression><![CDATA[$V{REPORT_COUNT}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
<reportElement key="textField-5" stretchType="RelativeToTallestObject" x="355" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="baaa6584-8b9d-4523-a698-a68d065b8892"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12"/>
</textElement>
<textFieldExpression><![CDATA[($F{daryaft} == null)? new Double("0"): $F{daryaft}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
<reportElement key="textField-6" stretchType="RelativeToTallestObject" x="190" y="0" width="85" height="30" isPrintWhenDetailOverflows="true" uuid="073a34ff-2e81-4265-a580-beb8fb293ae0"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12"/>
</textElement>
<textFieldExpression><![CDATA[($F{baseDate} == null)? "---" : $F{baseDate}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
<reportElement key="textField-9" stretchType="RelativeToTallestObject" x="110" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="e1c072ca-5d06-453c-a739-c797507a92b0"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12"/>
</textElement>
<textFieldExpression><![CDATA[($F{no} == null)? "---" : $F{no}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
<reportElement key="textField-11" stretchType="RelativeToTallestObject" x="435" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="a5778bf8-71da-4c84-82b8-219f27f04065"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12"/>
</textElement>
<textFieldExpression><![CDATA[($F{pardakht} == null)? new Double("0"): $F{pardakht}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
<reportElement key="textField-11" stretchType="RelativeToTallestObject" x="515" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="a762bdfa-4d3f-4975-b70d-d25875d15af8"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12"/>
</textElement>
<textFieldExpression><![CDATA[($V{mande} == null)? new Double("0"): $V{mande}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
<reportElement key="textField-5" stretchType="RelativeToTallestObject" x="275" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="d8c12462-e172-47eb-a301-905e356465b2"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12"/>
</textElement>
<textFieldExpression><![CDATA[($F{sanadType} == null)? "---": $F{sanadType}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true" isBlankWhenNull="true">
<reportElement key="textField-9" stretchType="RelativeToTallestObject" x="30" y="0" width="80" height="30" isPrintWhenDetailOverflows="true" uuid="7eae6f3a-173e-479d-90d7-38b6c5a5b99f"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="bnazanin" size="12"/>
</textElement>
<textFieldExpression><![CDATA[($F{zinaf} == null)? "---" : $F{zinaf}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>
以下是报告的屏幕截图：
and here is the screenshot from the report:
有什么想法可以解决这个问题吗？
Is there any idea how can I fix the problem?
推荐答案
您正在查看的输出是Excel输出。这是Excel文件在文本编辑器中打开时的样子（我随机抽取了一个XLS文件并在Notepad ++中打开）：
The output you are looking at is the Excel output. This is what an Excel file looks like if you open it in a text editor (I took a random XLS file and opened it in Notepad++):
这意味着，您的回复不会将内容作为Excel文件提供，而是直接下载，直接在浏览器中显示。
This means, that your response is not providing the content as a Excel file but as a direct download, which is shown directly in the Browser.
尝试在回复中添加其他参数（从此处并设置为适合Java）更明确：
Try adding additional parameters to your response (from here and set to fit Java) to be more explicit:
response.setHeader("Content-Type: application/vnd.ms-excel; charset=utf-8");
response.setHeader("Expires: 0");
response.setHeader("Cache-Control: must-revalidate, post-check=0, pre-check=0");
response.setHeader("Cache-Control: private");
编辑
如果更大的文件发生这种情况，请查看描述同一问题的此答案。一般来说，你应该尽早发送响应头，然后生成报告文件。
If this happens to bigger files, take a look at this answer which describes the same problem. In general it says, you should send response headers as early as possible and then afterwards generate the report file.
简而言之：你应该先将所有内容写入标题（我怀疑你在响应更高的位置上有更多功能），然后尝试生成该文件。因此，浏览器获得结果，看到应用程序文件即将到来并将提供下载。否则，据我所知，生成文件后会设置响应标头 - 浏览器等待
Put short: You should first write all contents to the header first (I suspect you do have more on response higher up) and then try to generate the file. So the browser gets a result, sees that an application file is coming and will provide a download. Otherwise, as far as I understand it, the response header is set after the file is generated - which could be too long for the browser to wait for the Content-Type.
这篇关于jasperreports大型excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
