Having trouble format decimal point in Excel with JasperReport

Question

I made an excel report, which contains some Double type TextFields with number format pattern ##0.#

In OpenOffice, these cells are formatted correctly (Which is exactly what I need)

• 1.56 -> 1.6
• 0.0 -> 0

However, when I opened report with Microsoft Excel, the decimal point did not disappear like I excpected.

• 1.56 -> 1.6
• 0.0 -> 0.

After some search, I found a post about number format in Excel.

In Excel format number with optional decimal places

I tried [=0]0;.# instead as the post suggested. But Excel complain about about my cell number format are broken.

My question is : What pattern should I use in JasperReport, so Excel won't show the trailing decimal point ?

UPDATE : I just find out the pattern described in link dose not solve all cases, it match only 0, not 1.0, 2.0... etc.

Answer 1

You can try to use net.sf.jasperreports.export.xls.formula property.

The sample:

<textField pattern="##0.#">
    <reportElement x="0" y="0" width="100" height="20">
        <property name="net.sf.jasperreports.export.xls.formula" value="[=0]0;##0,#"/>
    </reportElement>
    <textElement/>
    <textFieldExpression><![CDATA[$F{sum}]]></textFieldExpression>
</textField>

In my case the comma (,) is delimiter in my OS.

The information about net.sf.jasperreports.export.xls.formula property you can find here.

Answer 2

As workaround you may paste in field this code:

new DecimalFormat("0.##").format($F{bigdecValue})

So, processing for xls will be without comma if thera are trailinig zeros.