When exporting a jrxml to an excel, why do the numbers not have numeric values?

I'm using openbravo reports, and when I export a report to an excel the numbers inside it don't have any numeric value, so when I select them to make a sum it doesn't work.

Does it have any relation to the type of field I'm using in Jaspersoft?

Solution

First turn on that jasper report should detect cell type net.sf.jasperreports.export.xls.detect.cell.type

Now the result in excel depends on the type that you have in the text fields. It's a common error to format the number in text fields

<textField>
     <reportElement x="0" y="0" width="545" height="30" uuid="20cfbb7d-73d5-4d21-bdf4-7e54b2ccf753"/>
    <textFieldExpression><![CDATA[NumberFormat.getInstance().format(1.23d)]]></textFieldExpression>
</textField>

This converts the Number to a String and the export to excel will not work properly.

Instead the correct solution is to use pattern

<textField pattern="#,##0.###;#,##0.###-">
   <reportElement x="0" y="0" width="545" height="30" uuid="20cfbb7d-73d5-4d21-bdf4-7e54b2ccf753"/>
   <textFieldExpression><![CDATA[1.23d]]></textFieldExpression>
</textField>

This way the export can understand it's a number and know which pattern it should apply when it exports to excel. You can even customize the pattern for excel using the net.sf.jasperreports.xls.number.pattern property.

A small example having excel detect the number and using different patterns in excel export. I have also add a couple of properties to make it look more like a normal excel (removing margins, white background etc).

<?xml version="1.0" encoding="UTF-8"?>
<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="TestRewindableDatasource" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="dc03cf06-2828-447a-9238-63e7ca5bc637">
    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
    <property name="net.sf.jasperreports.export.xls.ignore.cell.background" value="true"/>
    <property name="net.sf.jasperreports.export.xls.ignore.cell.border" value="true"/>
    <property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>
    <title>
        <band height="20" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <textField pattern="#,##0.00;#,##0.00-">
                <reportElement x="0" y="0" width="260" height="20" uuid="20cfbb7d-73d5-4d21-bdf4-7e54b2ccf753">
                    <property name="net.sf.jasperreports.export.xls.pattern" value="#,##0.#;#,##0.#-"/>
                </reportElement>
                <textElement textAlignment="Right" verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[10015.231d]]></textFieldExpression>
            </textField>
        </band>
    </title>
</jasperReport>