Bug 54318

Summary: BuiltinFormats; Changes in the order of predefined formats
Product: POI Reporter: mathias
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal CC: dominik.stadler, eric, seebass
Priority: P2    
Version: 3.8-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description mathias 2012-12-17 10:36:30 UTC
I used POI (HSSF) for many years to export tables to excel. Since the update to POI 3.8, I have a bug by formatting the cells of the table in Excel.

Environment:
Windows XP; Excel 2002, SP3; 

The problem:
When I create a table, open it in Excel und click at the thousand separator, the cell gets a ‘$’-Character in front of the number (formatted as currency). 
Next, when I click on the button currency, Excel format the cell with the thousands separator. 
I get this problem with the SXSSF-libraries as well as with the HSSF-libraries.

Reason:
The order of the predefined formats in the class BuiltinFormats (package: org.apache.poi.ss.usermodel) has changed.

3.8 (wrong)
putFormat(m, 0x29, "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)");
putFormat(m, 0x2a, "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)");
putFormat(m, 0x2b, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)");
putFormat(m, 0x2c, "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)");
		
3.7 (right)
putFormat(m, 0x29, "_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)");
putFormat(m, 0x2a, "_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)");
putFormat(m, 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)");
putFormat(m, 0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)");
Comment 1 Nick Burch 2013-06-14 12:50:14 UTC
http://support.microsoft.com/kb/147942 claims to be the canonical, corrected list of the built in formats. 

It does look like BuiltinFormats doesn't match the corrected list, so probably it needs changing. (My hunch is someone updated based on the list somewhere that featured the mistake mentioned in the kb article)
Comment 2 Bernhard Seebass 2014-04-23 13:29:43 UTC
*** Bug 55730 has been marked as a duplicate of this bug. ***