How to specify custom units format in pattern when exporting to excel?

Using jasper reports I want to generate an excel report that has the option of having units/currencies in the column.

Like column 1: 5,74 €/h and column 2: 24,7 kg.

In excel I can enter this pattern as #.##0,00" €/h"

However using same pattern in jrxml

<textField isBlankWhenNull="true">
    <reportElement height="125" style="detail">
            <property name="net.sf.jasperreports.export.xls.pattern" value="#,##0&quot; €/h&quot;"/>
     </reportElement>
     <textFieldExpression><![CDATA[$F{attributeWithUnit}]]></textFieldExpression>
 </textField>

does not work, excel cannot understand it and therefore deformate the whole column or sheet and gives error.

How can/should I format this jasper export for excel to understand how to format it?

Solution

The issue you have is that to the export engine you actually need to pass #,##0" €/h" and not #,##0" €/h" and since attributes is parsed it's converted to #,##0" €/h"

before you start be sure to enable xls.detect.cell.type

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

and make sure you have a font that can display the Euro symbol correctly.

With jasper-reports versione 6.4 o above

Use the CDATA tag

<textField>
  <reportElement x="0" y="0" width="180" height="30" uuid="a5698aa9-a36e-444d-b590-3340250059a1">
    <property name="net.sf.jasperreports.export.xls.pattern">
        <![CDATA[#,##0.0&quot; €/h&quot;]]>
    </property>
  </reportElement>
  <textFieldExpression><![CDATA[1235]]></textFieldExpression>
</textField>

Versione previous to 6.4

You need to do escape the & so it is correctly & when passed to export hence the format string becomes #,##0.0&quot; €/h&quot;

<textField>
    <reportElement x="0" y="0" width="180" height="30" uuid="a5698aa9-a36e-444d-b590-3340250059a1">
        <property name="net.sf.jasperreports.export.xls.pattern" value="#,##0.0&amp;quot; €/h&amp;quot;"/>
    </reportElement>
    <textFieldExpression><![CDATA[1236]]></textFieldExpression>
</textField>