Created attachment 35444 [details] Screenshot of data within the excel file Greetings, first of all I shall say thank you to show my appreciation for this great product, it really helped a lot on projects I've involved throughout my career. However recently I've found an issue with the result returned from function DataFormatter.formatCellValue(Cell cell). I have this excel sheet filled with "166609647" in one of the cell, and I applied format "60#########" on it. "60166609647" appears on my Excel program afterwards, as screenshot attached. Using below code I'm trying to get what's appeared on the screen: Cell cell = getTheCell(); DataFormatter df = new DataFormatter(); String displayValue = df.formatCellValue(cell); What I captured in displayValue is only "166609647", the leading "60" is nowhere to be found. If I change the format to "60000000000", the code above works fine, I can get "60166609647". I'm already using latest library 3.17 and this issue still occurs.
For this data format, POI is using the java.text.DecimalFormat class. This class treats format "60#########" as illegal. Formats like "AB########" work but the Java class does not like the attempt to prefix the number with another number. This format "60#########" does work in Excel. I still think that the Java behaviour is better than the Excel behaviour in the case. I think there are better ways to prepend 60 onto a number, eg using a formula like `=CONCATENATE("60", A1)`.
The Java DecimalFormat version of "60#########" is "'60'000000000" Note that we have to escape the initial 60 to force it to always display, then swap the #s for 0s as the # in Java means "display if exists" while the 60 prefix means we always need to treat it as existing Putting that translation in may not be that easy, as I believe the following monster is valid in excel... [$-809][>=100][Red]60#########;[$-410][<-100][Green]92##,####,###;[Yellow]0,000 (Interestingly, OpenOffice gets it slightly wrong too - it shows the leading 60 but not the "missing" 0s)
One situation worth noting though, ### is not always the same as 000. for example, for an input of 12345678 if the format is 60000000000, output will be 60012345678. if the format is 60#########, output will be 6012345678.
I voted for this as I'm running into the same underlying issue. I'm using Apache Tika to parse spreadsheets of varying layouts. Format strings such as "0##" cause IllegalArgumentException to be caught in DataFormatter.createNumberFormat() and the default formatter to be used in place. In my case I cannot predict the incoming layout nor the value of such a format to the creator of the spreadsheet. I simply must parse it as intended. It seems POI should parse these the same way Excel would.