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

[myphuong]

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!

[lknueve]

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:

 

<textField>

<reportElement x= y= width= height= >

<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["=SUM()"]]>

</propertyExpression>

</reportElement>

<textElement />

<textFieldExpression > <![CDATA[<whatever you want to display when in pdf or rtf format>]]></textFieldExpression>

</textField>

[yashaswiniverma397]

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.