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).
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.
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
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)"
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