Format Issue while reading excel data into Java

Question

I am reading Excel data using java apache. I got format issue while reading double value such as 869.87929 (in excel) into 869.8792899999999 (in java).

I'm using following files to read excel data.
1. Schema.csv: SheetName,2-int-Double

2. File.xls:

col1 | col2 123 | 869.87929

Sample code:

if(type.equals("Double")){
            Double fval=Double.parseDouble(content[i-1]);
            String sval=fval.toString();
            listObjects.add(new Double(Double.parseDouble(sval)));
        }

Note: type from schema.csv & content [] value from file.xls

[Floating point arithmetic.](http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html) Are you seriously worried about that 1E-13 difference?? — Jean-François Corbett, Jan 20 '15 at 08:39
if(type.equals("Double")){ String str = content[i-1]; //System.out.println(str); BigDecimal d = new BigDecimal(str); listObjects.add(d); } If I print **str**, it shows value as 869.8792899999999. But i need to get **str** value as 869.87929. How can I get it? — Stone, Jan 20 '15 at 09:49
possible duplicate of [Get the cell value as how it was presented in excel](http://stackoverflow.com/questions/19401403/get-the-cell-value-as-how-it-was-presented-in-excel) — Gagravarr, Jan 20 '15 at 11:34

score 1 · Answer 1 · answered Jan 20 '15 at 08:32

1

Double is not good for preserving precision. Preffered is using BigDecimal. I believe this is your problem.

https://blogs.oracle.com/CoreJavaTechTips/entry/the_need_for_bigdecimal

answered Jan 20 '15 at 08:32

sashwat

607
4
10

Peter Lawrey · Accepted Answer · 2015-01-20T11:14:01.783

1

There is no point converting number back and forth to Strings as this shouldn't do any thing useful.

Try doing

listObjects.add(new BigDecimal(content[i-1]));

with rounding you can do

listObjects.add(new BigDecimal(content[i-1]).setScale(9, RoundingMode.HALF_UP));

though I suspect the rounding error has occurred before this point as this should do basically the same thing as

listObjects.add(new Double(content[i-1]));

with rounding you can do

double d = Double.parseDouble(content[i-1]);
double round9 = Math.round(d * 1e9) / 1e9;
listObjects.add((Double) round9);

These are much the same as the number is within the precision of double and there should be no additional error here (i.e. the error is likely to be before this point)

edited Jan 20 '15 at 11:14

answered Jan 20 '15 at 08:38

Peter Lawrey

525,659
79
751
1,130

if(type.equals("Double")){ String str = content[i-1]; //System.out.println(str); BigDecimal d = new BigDecimal(str); listObjects.add(d); } If I print str, it shows value as 869.8792899999999. But i need to get str value as 869.87929. How can I get it? – Stone Jan 20 '15 at 10:22
@Stone as I suspected your number has a rounding error at the source. To remove the `9`s you need to round the result. Can you round to 6 or 9 decimal places? – Peter Lawrey Jan 20 '15 at 10:28
@Stone see my answer for examples of rounding. – Peter Lawrey Jan 20 '15 at 10:31
Thanks Peter. In some cases we need 9 decimal places from excel. In this case it may fail, when we are going to round up 6 decimal places. For example; excel value 0.737837838 and going to round up 6 decimal places, We did not get excel value as it is in java. – Stone Jan 20 '15 at 11:10
@Stone changed the code for 9 decimal places. I suspect excel hide the error when it displays it, I dont know the rules excel uses for this but I suspect it chops off a digit. – Peter Lawrey Jan 20 '15 at 11:15

score 1 · Answer 3 · answered Jan 20 '15 at 08:42

1

If you use Apache POI - you can use getCellType()==Cell.CELL_TYPE_NUMERIC comparison and getNumericCellValue() from Cell interface.

answered Jan 20 '15 at 08:42

Kotodid

879
6
18

Format Issue while reading excel data into Java

3 Answers3