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. Are you seriously worried about that 1E-13 difference?? — Jean-François Corbett
– Jean-François Corbett, Commented Jan 20, 2015 at 8: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
– Stone, Commented Jan 20, 2015 at 9:49
possible duplicate of Get the cell value as how it was presented in excel — Gagravarr
– Gagravarr, Commented Jan 20, 2015 at 11:34

Peter Lawrey · Accepted Answer · 2015-01-20 11:14:01Z

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, 2015 at 11:14

answered Jan 20, 2015 at 8:38

Peter Lawrey

535k83 gold badges770 silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stone Over a year ago

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?

Peter Lawrey Over a year ago

@Stone as I suspected your number has a rounding error at the source. To remove the 9s you need to round the result. Can you round to 6 or 9 decimal places?

Peter Lawrey Over a year ago

@Stone see my answer for examples of rounding.

Stone Over a year ago

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.

Peter Lawrey Over a year ago

@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.

sashwat · Accepted Answer · 2015-01-20 08:32:54Z

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, 2015 at 8:32

sashwat

6474 silver badges11 bronze badges

Comments

Kotodid · Accepted Answer · 2015-01-20 08:42:00Z

1

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

answered Jan 20, 2015 at 8:42

Kotodid

1,2899 silver badges18 bronze badges

Collectives™ on Stack Overflow

Format Issue while reading excel data into Java

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related