Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Excel number format error in MS Office 2007

  1. #1
    Join Date
    Oct 2007
    Posts
    10

    Default Excel number format error in MS Office 2007

    Hi,

    I've an element created using number formatter, to which I've set a decimal format with the Singapore Dollar Currency as prefix. It's toPattern() on sysout is: S$#,##0.00;(S$#,##0.00)

    When the report is exported to Excel, OpenOffice can open this file without any errors, but MS Excel-2007 shows a format error and no formatting is displayed.
    The format in excel file( when I checked using OpenOffice) is:
    "S"$#,##0.00;("S"$#,##0.00).

    Then I changed it within OpenOffice->Format Cells, to S$#,##0.00;(S$#,##0.00) i.e. without any quotes and opened it in MS Excel, it shows a custom format as: "S$"#,##0.00;"(S$"#,##0.00) and works.

    Am I missing something while setting the decimal format on the element?

    But, just with $ or € symbols, there's no problem. So, please advice how this can this be solved?

    Thanks and Regards,
    Prashant

    PS. I'm using POI-3.0.1-Final and Pentaho Reporting-0.8.9.4

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    When exporting to Excel, we try to use the Java Number/Date-Formats as Excel formats. This works in the simple cases, but not in the complexer ones (like yours).

    The Cell-Format string syntax in Excel is totally undocumented and (to make things worse) these strings seem to be locale dependent. So our workaround for that is to use one of the most powerful computing systems of the world to solve this problem: Your brain

    You can specify a explicit "Excel-Format string" on the number-fields, which then overrides the common (Java) formatstring when exporting to Excel. When you specify the quoted version (as produced by OpenOffice) there, you should be fine.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Oct 2007
    Posts
    10

    Default

    Hi Thomas,

    Thanks for the details. I'll try by setting "Excel-Format string" to number elements.

    Regards,
    Prashant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.