Bug 24125 - Currency formated cells are recognized as dates.
Summary: Currency formated cells are recognized as dates.
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 1.5.1
Hardware: Other other
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2003-10-26 02:20 UTC by Holger Schulz
Modified: 2004-11-16 19:05 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Holger Schulz 2003-10-26 02:20:03 UTC
Hello!

Cells in Excel 2000 (German) formated as Currency are recognized as dates.
In Excel they look like "7,00€" and "$7,00".

style.getDataFormat() returns 171 (ab) and 172 (ac) for these cells so 
isInternalDateFormat(..) returns true.

Is it possible to determin with HSSF how the cells look like in Excel?

Greetings from Germany

    Holger

P.S. Here are the fields for HSSFCell and HSSFCell.getCellStyle() of 
cell "$7,00". Hope this information helps debugging:

---- HSSFCell ------
org.apache.poi.hssf.usermodel.HSSFCell [java.lang.Object]
cellNum = 1
cellType = 0
cellStyle :- org.apache.poi.hssf.usermodel.HSSFCellStyle@ae525c
cellValue = 7.0
stringValue :- null
booleanValue = false
errorValue = 0
encoding = 0
book :- org.apache.poi.hssf.model.Workbook@15f5985
sheet :- org.apache.poi.hssf.model.Sheet@ee568c
row = 2
record :- [NUMBER]
    .row            = 2
    .col            = 1
    .xfindex        = 1f
    .value          = 7.0
[/NUMBER]

---- HSSFCell.getCellStyle() ------
org.apache.poi.hssf.usermodel.HSSFCellStyle [java.lang.Object]
format :- [EXTENDEDFORMAT]
 CELL_RECORD_TYPE
    .fontindex       = 0
    .formatindex     = ac
    .celloptions     = 1
          .islocked  = true
          .ishidden  = false
          .recordtype= 0
          .parentidx = 0
    .alignmentoptions= 20
          .alignment = 0
          .wraptext  = false
          .valignment= 2
          .justlast  = 0
          .rotation  = 0
    .indentionoptions= 400
          .indent    = 0
          .shrinktoft= false
          .mergecells= false
          .readngordr= 0
          .formatflag= true
          .fontflag  = false
          .prntalgnmt= false
          .borderflag= false
          .paternflag= false
          .celloption= false
    .borderoptns     = 0
          .lftln     = 0
          .rgtln     = 0
          .topln     = 0
          .btmln     = 0
    .paleteoptns     = 0
          .leftborder= 0
          .rghtborder= 0
          .diag      = 0
    .paleteoptn2     = 0
          .topborder = 0
          .botmborder= 0
          .adtldiag  = 0
          .diaglnstyl= 0
          .fillpattrn= 0
    .fillpaloptn     = 20c0
          .foreground= 40
          .background= 41
[/EXTENDEDFORMAT]

index = 31
fontindex = 0

----- style.getDataFormat() -----
 style.getDataFormat(): 172
Comment 1 copec 2003-12-04 11:38:45 UTC
Also appears to be a problem with percentage formats in Excel 2002 
(10.2614.3311 to be precise):

Percentage formats are as follows (according to number of decimal places):

0 = 9 (09)
1 = 164 (a4)
2 = 10 (0a)
3 = 167 (a7)
4 = 168 (a8)
5 = 169 (a9)
6 = 170 (aa)
7 = 171 (ab)
8 = 176 (b0)
9 = 177 (b1)
10 = 172 (ac)
11 = 173 (ad)
12 = 180 (b4)
13 = 181 (b5)
14 = 182 (b6)
15 = 178 (b2)
16 = 183 (b7)
17 = 184 (b8)
18 = 185 (b9)
19 = 186 (ba)
20 = 174 (ae)
21 = 187 (bb)
22 = 188 (bc)
23 = 189 (bd)
24 = 190 (be)
25 = 179 (b3)
26 = 191 (bf)
27 = 192 (c0)
28 = 193 (c1)
29 = 194 (c2)
30 = 175 (af)


The switch statement in isInternalDateFormat(int format) (from HSSFDateUtil rev 
1.6) contains the following:

// Additional internal date formats found by inspection
// Using Excel v.X 10.1.0 (Mac)
case 0xa4:
case 0xa5:
case 0xa6:
case 0xa7:
case 0xa8:
case 0xa9:
case 0xaa:
case 0xab:
case 0xac:
case 0xad:

So with Excel 2002 percentage formats with 1, 3, 4, 5, 6, 7, 10 and 11 decimal 
points appear to get treated as dates.
Comment 2 Avik Sengupta 2004-01-01 09:02:34 UTC
Fixed. both branch and head. This was due to an addition in bug 14375, which has
been revered.