Automatically Converting Numbers stored as text to numbers when export excel in ireport


hi, everyone. I'm a new member. I have two problems when export excel in ireport

- with double value, i use formatter to format it into String with my pattern (#.###,00), export pdf is OK. But when export excel, it recognized this cell as text. I want it to be converted into number when export into excel automatically.

- after convert to number, i want to set formula SUM for range values in one column. How do I set it to export both excel and pdf?

Hope everyone to help me!

Note: I use code java to export pdf, excel, rtf. I'm not good at English. If I have any mistake, please ignore them, thank you!

myphuong's picture
Joined: Sep 26 2011 - 7:22pm
Last seen: 7 years 12 months ago

1 Answer:

For excel to automatically detect what type of format the cell should be, add the following to your jrxml file at the top in the report properties section:

<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>

To have excel show formula when highlighting a cell and do the calculation, in your jrxml file, format the field you want to have the SUM as follows:

<reportElement x= y= width= height= >
<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["=SUM()"]]>
<textElement />
<textFieldExpression > <![CDATA[<whatever you want to display when in pdf or rtf format>]]></textFieldExpression>
lknueve's picture
Joined: Jan 10 2011 - 3:00pm
Last seen: 8 years 8 months ago