1

I have the following code:

Workbook workbook = WorkbookFactory.create( file );
Sheet sheet = workbook.getSheet( "Sheet1" );
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
DataFormatter formatter = new DataFormatter( true );

int rowNum = 0;
int colNum = 0;
Row row = sheet.getRow( rowNum );
Cell cell = row.getCell( colNum );

double rawCellValue = cell.getNumericCellValue();
System.out.println( "raw value: " + rawCellValue );

String cellValue = formatter.formatCellValue( cell, evaluator );
System.out.println( "formatted: " + cellValue );

This produces the output:

raw value: 1713.6
formatted: $1,713

When I open the file in Excel Professional 2010, the value displays as $1,714

Excel screenshot

Any ideas why POI produces a different value than Excel? Is there a way to configure POI so that it returns the same value (i.e. obeys the same rounding algorithm as Excel)?

I'm using POI 3.12 with a xls (Excel 97-2013) file.

EDIT: The java format for this cell is $#,##0 (internal to POI). Here's what Excel shows:

Excel's cell format dialog

EDIT 2: Perhaps this is a problem with Java, not POI. The following code:

double number = 1713.6;
System.out.println( "double   : " + number );

DecimalFormat format = new DecimalFormat("#0");

format.setRoundingMode( RoundingMode.HALF_DOWN );
System.out.println( "HALF_DOWN: " + format.format( number ) );

format.setRoundingMode( RoundingMode.HALF_UP );
System.out.println( "HALF_UP  : " + format.format( number ) );

Produces the result:

double   : 1713.6
HALF_DOWN: 1714
HALF_UP  : 1713

Why would HALF_DOWN round up but HALF_UP round down?

GreenGiant
  • 4,930
  • 1
  • 46
  • 76
  • Related: [Matching Excel's floating point in Java](http://stackoverflow.com/questions/28537614/matching-excels-floating-point-in-java) – Gagravarr Jun 03 '15 at 12:21
  • The format should not be the problem. It's the default currency format in both Excel and POI. I've tried this and can't reproduce. `formatter.formatCellValue( cell, evaluator )` is `$1,714` for me if `cell.getNumericCellValue()`is `1713.6`. What is the Excel cell content in `A1`? A Formula? Which? – Axel Richter Jun 04 '15 at 05:10
  • No formula. The value is exactly 1713.6 in Excel – GreenGiant Jun 04 '15 at 16:43
  • Posted last edit as a separate followup [question](http://stackoverflow.com/q/30650834/539048) – GreenGiant Jun 04 '15 at 17:55

1 Answers1

2

See this answer. Looks like the problem is related to a rounding bug in certain version of java 8. This was fixed with 8u40 (Release Notes).

Community
  • 1
  • 1
GreenGiant
  • 4,930
  • 1
  • 46
  • 76