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: 9 years 5 days ago

2 Answers:

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: 9 years 8 months ago

Hi Iknueve,

I tried this property while exporting in xlsx format. However, I am getting a pop up that the data may have been lost. My data is huge so it is getting this error.

Is there any property for xlsx? without pop up issue or can handle this?


Kindly help.


yashaswiniverma397's picture
Joined: Nov 7 2019 - 5:38am
Last seen: 7 months 1 week ago