Created attachment 26025 [details] A cell containing £1, that is formatted by POI as $1 Steps to reproduce: 1. Using a machine set to the English locale, create an xlsx file using Excel 2007. 2. Type "£1" into a cell and observe that Excel converts it to a currency type cell with the "£" symbol. (See attachment). 3. Use POI to read the cell from step 2; call [new org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(Cell)] on the cell. 4. Observe that the returned value is "$1", not "£1" as expected.
I'm pretty sure you're being bitten by an excel "feature". Excel is probably treating your £ as a generic currency symbol, and actually storing a $ in the file Can you please try opening your sample file on a computer set to be in the US, and see what you get in excel? I've just created a file with formattings of both $00.00 and £00.00, and POI is able to retrieve both symbols properly. So, I think the problem is with how you're setting the format, and not with POI
> Can you please try opening your sample file on a computer set to be in the US, and see what you get in excel? I have set my locale to US, and when I open the sample file I am shown "£1". I have tried this on two different systems - running Vista and Windows 7. For reference, in the following posts I will attach screenshots of how the formatting dialog looks when opening the sample file in an English locale, and in a US locale.
Created attachment 26116 [details] The Excel format dialog in an English locale
Created attachment 26117 [details] The Excel format dialog in a US locale
OK, this is odd In the english locale, it shows up as the default currency symbol. This is what I'd expect, as excel is actually writing the file with a $ and not a £ In the US locale, excel seems to know that it really does need to be a £, and not be the default currency. Thus, it shows up as a custom format with a £ in it Quite how Excel is managing to know this I've no idea... I'll need to do some more digging when I have a minute
Fixed in r1005726, junit added. The problem workbook demonstrates an unexpected behavior. Excel has a set of built-in formats referenced by an integer id in the range [0, 164]. If a custom format is applied then it is created with a new id above 164. This behavior is observed in Excel 2003 and Excel 2007, both for the .xls and .xlsx formats. In your case a custom format was applied, but its formatId was 6, i.e. in the built-in range. As the result, DataFormatter picked up a wrong built-in format with Dollar instead of Pound. I fixed the order in which formats are checked, now overridden formats have higher priority than built-in ones regardless from formatId. Yegor
I am re-opening this, because although the fix works for .xlsx files, it does not work for .xls files.
Created attachment 26427 [details] The original test-case file, saved as .xls This file was created from the originally posted test-case by using Excel to save-as .xls. The original test case file does not exhibit the bug any more, but this file does.
> The original test case file does not exhibit the bug any more, but this file does. To be clear, I mean that in version 3.7 the bug has been fixed for .xlsx/XSSF, but not for .xls/HSSF.
Looks like the .xls has overridden some standard formats as well: Offset=0x000003DA(986) recno=44 sid=0x041E size=0x0018(24) [FORMAT] .indexcode = 0x0005 .isUnicode = false .formatstring = "£"#,##0;\-"£"#,##0 [/FORMAT] Offset=0x000003F6(1014) recno=45 sid=0x041E size=0x001D(29) [FORMAT] .indexcode = 0x0006 .isUnicode = false .formatstring = "£"#,##0;[Red]\-"£"#,##0 [/FORMAT]
Should be fixed for HSSF too in r1061288 - needed a similar fix