CODEWITHSUNDEEP
jasper-reportsexport-to-excel
Jose Antonio Garcia Panadero
Jose Antonio Garcia Panadero

Reputation: 47

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?

Upvotes: 1

Views: 5022

Answers (1)

Petter Friberg
Petter Friberg

Reputation: 21710

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>

Upvotes: 2

Related Questions