Bug 52684 - Reading Numeric Cell Values with Custom Formats
Summary: Reading Numeric Cell Values with Custom Formats
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-02-16 16:04 UTC by Tom Cole
Modified: 2016-02-17 21:36 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Tom Cole 2012-02-16 16:04:11 UTC
I am unable to read a formatted numeric value that has a custom format.


In Excel 2007, enter value 12312345123 in a cell.

Format the cell with a custom format of 000-00000-000.

The cell now contains the formatted value 123-12345-123.

From POI read the contents of the cell using:

//load Workbook as workbook...
DataFormatter formatter = new DataFormatter();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
//obtain Cell as cell...
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
     if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) { 
          data[i][j] = "#VALUE!"; 
     else { 
          data[i][j] = formatter.formatCellValue(cell, evaluator); 
else {
     data[i][j] = formatter.formatCellValue(cell);

This sets data[i][j] to "12312345123"

Running the following on the cell returns:

cell.getCellStyle().getDataFormatString() returns "000\-00000\-000"
cell.getCellStyle().getDataFormat() returns 164

These values are correct and indicate that the format is loaded, but is not applied properly to the "formatted" output.
Comment 1 Dominik Stadler 2016-02-17 21:36:27 UTC
In r1730948 I added a unit test which shows how this can be done with the latest version of POI. So it either got fixed or was not used correctly in this bug report. Please discuss on the mailing list or report new issues for anything that still does not work for you.