Bug 41478 - HSSFDataFormat builtin values are wrong
Summary: HSSFDataFormat builtin values are wrong
Status: RESOLVED LATER
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: Other other
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-01-26 15:48 UTC by David Christianson
Modified: 2010-06-04 10:50 UTC (History)
0 users



Attachments
Please apply the proposed patch (3.14 KB, text/plain)
2009-01-21 21:43 UTC, Dmitriy Kumshayev
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Christianson 2007-01-26 15:48:35 UTC
I have been playing with code that can format Excel numeric data using Excel
number format strings. My implementation is by no means complete (if something
exists already...), but during the course of vetting it I find that some of the
formats built into the HSSFDataFormat are wrong, specifically:

5, "($#,##0_);($#,##0)"
6, "($#,##0_);[Red]($#,##0)"
7, "($#,##0.00);($#,##0.00)"
8, "($#,##0.00_);[Red]($#,##0.00)"
0x25, "(#,##0_);(#,##0)"
0x26, "(#,##0_);[Red](#,##0)"
0x27, "(#,##0.00_);(#,##0.00)"
0x28, "(#,##0.00_);[Red](#,##0.00)"

According to the docs I can find (OpenOffice.org and playing with my copy of
Excel:mac), it looks like all of these should start with _, not "(". Otherwise,
the "(" is very definitely going to be printed (if you cut and paste the above
formats into Excel you will see this happening).
Comment 1 Dmitriy Kumshayev 2009-01-21 21:43:22 UTC
Created attachment 23155 [details]
Please apply the proposed patch 

Positive numbers should not be in parenthesis
the should all start with "_(" 

 *       5, "_($#,##0_);($#,##0)"<br>
 *       6, "_($#,##0_);[Red]($#,##0)"<br>
 *       7, "_($#,##0.00);($#,##0.00)"<br>
 *       8, "_($#,##0.00_);[Red]($#,##0.00)"<br>
 *       0x25, "_(#,##0_);(#,##0)"<P>
 *       0x26, "_(#,##0_);[Red](#,##0)"<P>
 *       0x27, "_(#,##0.00_);(#,##0.00)"<P>
 *       0x28, "_(#,##0.00_);[Red](#,##0.00)"<P>

This is from MS Excel Help:

To create a space that is the width of a character in a number format, include an underscore, followed by the character. For example, when you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.
Comment 2 Dmitriy Kumshayev 2009-01-21 21:59:52 UTC
The proposed patch does not fix the problem completely 
since if I call the following method on a cell 

new HSSFDataFormatter().formatCellValue(cell)

and the cell is formatted say as format #7 =>  "_($#,##0.00);($#,##0.00)"  
the formatted number contains _ in front for example :   _$38
while excel shows it correctly




Comment 3 Dmitriy Kumshayev 2009-01-21 22:55:12 UTC
since POI anyway does not do anything about "creating a space that is the width of a character", an easy fix could be to remove leading "(" instead of adding "_" the following way:

5, "$#,##0_);($#,##0)"
6, "$#,##0_);[Red]($#,##0)"
7, "$#,##0.00);($#,##0.00)"
8, "$#,##0.00_);[Red]($#,##0.00)"
0x25, "#,##0_);(#,##0)"
0x26, "#,##0_);[Red](#,##0)"
0x27, "#,##0.00_);(#,##0.00)"
0x28, "#,##0.00_);[Red](#,##0.00)"
 
Comment 4 Nick Burch 2010-06-04 10:50:10 UTC
This bug references a very old version of POI. As no new comments have been added in a long time, and a lot of fixes for HSSFDataFormatter have been applied in the mean time, I'm assuming that this bug has now been fixed

If the bug still exists with the latest version of POI (3.7 beta 1 or later), please re-open the bug and add a comment indicating this, ideally also with a failing unit test