| Summary: | Currency formated cells are recognized as dates. | ||
|---|---|---|---|
| Product: | POI | Reporter: | Holger Schulz <hsapache> |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | ||
| Priority: | P3 | ||
| Version: | 1.5.1 | ||
| Target Milestone: | --- | ||
| Hardware: | Other | ||
| OS: | other | ||
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. |
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