Search code examples
jasper-reports

Ireport to CSV is appending an  to all currency cells

It seems when I run my report and export it to Excel .CSV all my currency values seem to append an  at the beginning.

My XML is as follows:

</textField>
<textField isStretchWithOverflow="true" pattern="£ #,##0.00" isBlankWhenNull="false">
    <reportElement x="760" y="0" width="100" height="20" uuid="cba1b317-f85f-4b3b-9549-66389c451cfa"/>
    <textElement>
        <font size="10" pdfEncoding="Cp1252"/>
    </textElement>
<textFieldExpression><![CDATA[$F{ItemRateBilled}]]></textFieldExpression>

I've changed the pattern numerous times to no avail. It seems the following is the issue however, I can't figure out how to resolve:

The pound symbol is Unicode codepoint 163. If it's written to a UTF-8 encoded file, it's written as the sequence 194, 163. When the file is imported in Excel, a different encoding is assumed, probably the default ANSI code page. Therefore, the sequence 194, 163 is not interpreted as one Unicode codepoint. Instead, it's interpreted as the sequence  and £.

So, either make Excel interpret the file as a UTF-8 encoded file, or write the file yourself by specifying the System.Text.Encoding.Default codepage.

Current error as a CSV:

CSV issue

Solution

  • This is not an issue originating from Jasper but from Excel.

    If you take a look into the file with a program able to interpret UTF-8 (like Notepad++), this should not happen at all.

    CSV files exported from Jasper should not be opened directly. Instead, Excel should be opened and in the data tab the import from file function should be used.

    When importing the CSV file, change the input encoding to UTF-8. Then Excel will interpret the file correctly.