Currency Formatter Issue when Exporting to Excel (XLSX)

0

When exporting my rpeort field to XLSX fields with a currency formatter are not showing as a currency format in excel. Note that I am using:

property name="net.sf.jasperreports.export.xls.pattern" value="$#,##0.00"

And I also have the property at the report level:

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

The issue is that in my excel file, the formatter shows as Custom with the formatter "\$#,##0.00". So it seems like an excape character is being added before the "$" which screws up the formatter. Is there a way to remove this escape character somehow?

Note that I am not having this issue with xls exports.

I am using TIBCO Jaspersoft® Studio 6.10.0

<textField isStretchWithOverflow="true" evaluationTime="Group" evaluationGroup="Consolidated_Detail" pattern="$#,##0.00" isBlankWhenNull="true">
<reportElement stretchType="RelativeToTallestObject" x="1290" y="0" width="120" height="20" forecolor="#000000" uuid="d1f31dd0-3c27-4812-b55a-0baa29e8ee9c">
<property name="local_mesure_unity" value="pixel"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="net.sf.jasperreports.export.xls.pattern" value="$#,##0.00"/>
</reportElement>
<box>
<topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle"/>
<textFieldExpression><![CDATA[$V{InvoiceAmountSum}]]></textFieldExpression>
</textField>

anjohnston's picture
Joined: Apr 11 2017 - 9:24am
Last seen: 2 days 4 hours ago

I face this problem too. But it's very easy to add this in EXCEL. 
For that, I ignore this issue :)

mahadiosb - 2 days 9 hours ago

Thanks mahadiosb. I guess for a little more background, I'm trying to automate this as much as possible so less human intervention needed the better. The file XLSX (and it has be XLSX) needs to be uploaded into a client oracle system which is VERY picky about formats (scheduled jasper download to SFTP which oracle scoops up daily) - it throws an exception even with this extra slash in the formatter and the whole process fails. And since it's a client system, I unfortunately can't exactly change how the system handles formatting. 

Good to know it's not just me/ something with my specific setup!

anjohnston - 2 days 8 hours ago

0 Answers:

No answers yet
Feedback
randomness