cell-formattingexport-to-excelireport
I'm trying to export a report as xls, but when i open my exported file i notice that all cell formats are Number and all data are wrote with ' like text data (integer fields and double fields also…) so i cant apply any formula like SUM in Excel.
How can i keep cell format in according to my iReport TextField class expression?
I'm using iReport 4.0.2.
Thanks to all.
Simply Add the following line before your HTML code, it will resolve the issue. This style sheet will be applied on every cell and no need to format the cell as string manually.
Response.Write("<style> TD { mso-number-format:\@; } </style>");
You can solve this issue with help of net.sf.jasperreports.export.xls.break.after.row property.
You can set this net.sf.jasperreports.export.xls.break.after.row property for the "sheets break" element in report (for example, line or staticText element).
In my sample below I've put line element to the Group Footer band for generating new sheet in Excel for a new group.
Here is my sample, master report:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ..>
<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
<defaultValueExpression><![CDATA["path_to_subreport"]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[SELECT id, addressId FROM document ORDER BY addressId]]>
</queryString>
<field name="ID" class="java.lang.Integer"/>
<field name="ADDRESSID" class="java.lang.Integer"/>
<group name="addressGroup">
<groupExpression><![CDATA[$F{ADDRESSID}]]></groupExpression>
<groupHeader>
<band height="53">
<subreport>
<reportElement x="189" y="0" width="200" height="31"/>
<subreportParameter name="addressId">
<subreportParameterExpression><![CDATA[$F{ADDRESSID}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "subreport_new_sheet_for_excel.jasper"]]></subreportExpression>
</subreport>
<staticText>
<reportElement x="0" y="33" width="100" height="20"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font isItalic="true"/>
</textElement>
<text><![CDATA[ID]]></text>
</staticText>
</band>
</groupHeader>
<groupFooter>
<band height="9">
<line>
<reportElement x="1" y="5" width="100" height="1">
<property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>
</reportElement>
<graphicElement>
<pen lineWidth="0.0"/>
</graphicElement>
</line>
</band>
</groupFooter>
</group>
<detail>
<band height="20" splitType="Stretch">
<textField>
<reportElement x="0" y="0" width="100" height="20"/>
<box>
<pen lineWidth="1.0"/>
</box>
<textElement/>
<textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>
My subreport:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ...>
<parameter name="addressId" class="java.lang.Integer" isForPrompting="false"/>
<queryString>
<![CDATA[SELECT city, street FROM address WHERE id = $P{addressId}]]>
</queryString>
<field name="CITY" class="java.lang.String"/>
<field name="STREET" class="java.lang.String"/>
<detail>
<band height="20" splitType="Stretch">
<textField>
<reportElement x="0" y="0" width="258" height="20"/>
<textElement/>
<textFieldExpression><![CDATA["City: " + $F{CITY}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="258" y="0" width="297" height="20"/>
<textElement/>
<textFieldExpression><![CDATA["Street: " + $F{STREET}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>
The result will be:
The result without using of "break" element (I removed the line with net.sf.jasperreports.export.xls.break.after.row property) will be:
You can find more about info in Advanced Excel Features post.
Best Answer
i've been looking for the same problem and found the following:
http://jasperreports.sourceforge.net/sample.reference/xlsformula/index.html
you can set up the "detect cell type" to default true under export excel settings in ireport.
regards