This method contains the following code at the end: // TODO - when does this occur? return null; The answer is that this occurs whenever a numeric cell is given "Text" format (format string '@') in Excel. Returning null at this point results in the value being formatted with the default numeric format (a decimal place) even though the value displayed in Excel is an integer. I would contribute a patch but don't yet understand enough of the framework to know the best way to fix this.
After more research on Excel 2003, I've discovered the following. If you take a numeric cell and apply the 'Text' format to it, Excel DOES NOT change the cell's internal representation to String. So you end up with something POI reads as numeric but the user believes is a String, because it's left justified. However if you export the spreadsheet as XML at this point you will see that the cell still has a numeric type. While the cell is in the weird 'display as text but really still a number' state, Excel seems to use a left-justified version of General format, and still performs numeric display adjustments such as rounding if the column is too narrow for all the decimal places, or switching to scientific notation if the column is too narrow for the magnitude. If you make any changes to the cell, even a null change such as clicking in the formula bar and then clicking the green checkmark (or pressing Enter), then Excel converts the stored value to text and attaches the green warning triangle to the top left corner of the cell. At this point the cell really contains text. What I think needs to happen is that a numeric cell with format '@' needs to be formatted as "General".
Based on my analysis I've come up with a workaround. At the point in my code where I'm calling HSSFDataFormatter.formatCellValue(cell) I know that I want the numeric cell as a string, so I force the cell style to General as follows: HSSFCellStyle cs = cell.getCellStyle(); cs.setDataFormat((short)-1); cell.setCellStyle(cs); value = new HSSFDataFormatter().formatCellValue(cell); This returns the value formatted as it would be displayed assuming the column width were sufficient -- I.e. it provides the entire value and not a rounded or scientific-notation abbreviation. I'm not sure how POI should handle the situation, so I'll leave that up to more knowledgeable developers.
Any chance you could upload a very small file containing a cell with this type? That'll be useful for testing/fixing against
I've added a unit test for this, and it passes just fine. Both "@" and "General" will cause the text to be formatted as an integer, so this seems to have been fixed at some point since being reported