Converting exponential value in java to a number format

Question

I am trying to read the values from excel sheet using java. When i type more than 10 letters in a cell in excel it is displaying in exponential form like "9.78313E+2". but this is not the real number what i given. Can any body help me out in this. How can i convert the above exponential form to original number using java language.

Thanks in advance

You should show us the number you have in the excel and the result in Java. — looper, Nov 26 '12 at 11:23
`9.78313E+2` = `978.313` Are you confusing value with format. `double` doesn't preserve the format only the value. — Peter Lawrey, Nov 26 '12 at 11:32

score 22 · Answer 1 · edited Nov 13 '18 at 12:24

22

You can convert as follows, for example:

new BigDecimal("406770000244E+12").toBigInteger();

edited Nov 13 '18 at 12:24

piet.t

11,718
21
43
52

answered Aug 07 '14 at 11:43

user2996442

231
2
5

Brian Agnew · Answer 2 · 2012-11-26T11:34:24.520

11

Double.parseDouble("9.78313E+2");

gives me

978.313

For more info see the doc.

Following your further queries below, if you've entered 4256411411 and Excel is presenting this as 4.26E+09, putting that value into parseDouble() will only give you 4260000000. If you want the original, perhaps you need to output the Excel file in a fuller format for your Java program, and/or query it using a Java/Excel API (e.g. POI)

edited Nov 26 '12 at 11:34

answered Nov 26 '12 at 11:20

Brian Agnew

268,207
37
334
440

1

yes. it gives but this is not the original number what i given in the excel sheet – user1853173 Nov 26 '12 at 11:21
1

I tried this one with BigDecimal also. It is also giving same answer – user1853173 Nov 26 '12 at 11:22
Can you show us what the exact number is? And are you fetching the number from excel sheet in your Java code? – Rohit Jain Nov 26 '12 at 11:24
1

this is the value i given in the cell in excel sheet "4256411411" but it displaying "4.26E+09". By using java i need to convert that exponential value to original value. yes i am fetching the values from excel sheet using java code – user1853173 Nov 26 '12 at 11:24

score 6 · Answer 3 · edited May 23 '17 at 12:24

Sorry, but none of the answers above Double.parseDouble() and Double.valueOf()... solved my problem, and I continued to get the exponential 'E' value...

This link has a much better approach for the problem, and as I've written there - there is a very good solution:

I needed to convert some double to currency values, and fount that most to the solution are OK but not for me.

The DecimalFormat was eventually the way for me, so here is what I've done:

   public String foo(double value) //Got here 6.743240136E7 or something..
    {
        DecimalFormat formatter;

        if(value - (int)value > 0.0)
            formatter = new DecimalFormat("0.00"); //Here you can also deal with rounding if you wish..
        else
            formatter = new DecimalFormat("0");

        return formatter.format(value);
    }

As you can see, if the number is natural I get - say - 20000000 instead of 2E7 (etc) - without any decimal point.

and if it's decimal, I get only 2 decimal digits.

Hope this will help.

Rohit Jain · Answer 4 · 2012-11-26T11:27:27.287

5

You can use BigDecimal, if you want the exact value that you have in Excel Sheet: -

BigDecimal bd = new BigDecimal("4256411411");
System.out.println(bd.doubleValue());

// If you are sure that's not a floating point number, then use
System.out.println(bd.longValue());

Prints: -

4.256411411E9  
4256411411

edited Nov 26 '12 at 11:27

answered Nov 26 '12 at 11:21

Rohit Jain

209,639
45
409
525

The input from excel sheet i am getting is in exponential form – user1853173 Nov 26 '12 at 11:28
@user1853173. Can't you fetch the value in string form and just convert it to `BigDecimal`? – Rohit Jain Nov 26 '12 at 11:30
@user1853173.. Don't store the fetched value in a `double` type. But in String type. – Rohit Jain Nov 26 '12 at 11:31
yeah i am able to convert that to BigDecimal but it is not giving the original value. please find the below code. BigDecimal bd = new BigDecimal("4.26E+09"); long val = bd.longValue(); – user1853173 Nov 26 '12 at 11:31
1

@user1853173.. Problem is you already don't have the exact value, so BigDecimal can't give you that value. It will only give the value which you pass to it. – Rohit Jain Nov 26 '12 at 11:33
@user1853173.. In what format the value is stored in Excel Sheet? And when you are fetching it, where are you storing it? – Rohit Jain Nov 26 '12 at 11:33
In the excel sheet i given "4256411411"value but while reading i am getting 4.62E+09. so i need to convert this valuwe – user1853173 Nov 26 '12 at 11:33
@user1853173 In that case you are extracting the value from excel incorrectly, you cannot not expect to parse an incorrect value to correct it. – Peter Lawrey Nov 26 '12 at 11:33
@user1853173.. Show us the code where you are retrieving the value from Excel Sheet. – Rohit Jain Nov 26 '12 at 11:35
@user1853173.. Understand that we can't help you much until you show us some code. – Rohit Jain Nov 26 '12 at 11:42
In the above code for scancode i given the value as 4256411411 and formta of he cell is general if i save that one as number and read it will come first time correct value but when i just open the excel file and close then it is taking the format of the cell as general and displaying in exponential form – user1853173 Nov 26 '12 at 11:50
@user1853173.. What is the datatype of `scanCode`? – Rohit Jain Nov 26 '12 at 11:50
i mentioned above. the value is present in second cell for scancode and that is string type in java – user1853173 Nov 26 '12 at 11:52

score 5 · Answer 5 · answered Apr 22 '20 at 13:02

5

Try this definitely gona work
double value = 2.06E //real 205809104.13
BigDecimal.valueOf(value)
work for me

answered Apr 22 '20 at 13:02

Narender Gusain

2,220
17
13

score 4 · Answer 6 · edited May 23 '17 at 11:53

4

Before you read the value from excel sheet format your Column to number.

This may be helps to you

UPDATED

HSSFCell cellE1 = row1.getCell((short) 4);
cellE1.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Double e1Val = cellE1.getNumericCellValue();
BigDecimal bd = new BigDecimal(e1Val.toString());
long lonVal = bd.longValue();
System.out.println(lonVal);

edited May 23 '17 at 11:53

Community

1
1

answered Nov 26 '12 at 11:34

someone

6,577
7
37
60

You should do it pragmatically. Before read it you program should change it to number type. Also what is your excel reading API? – someone Nov 26 '12 at 11:50
In java before reading the values from excel i need to change the format of the cell to number?? – user1853173 Nov 26 '12 at 11:53
if you use Apache POI you could do this but I'm not aware of CSVReader. – someone Nov 26 '12 at 12:00
I am not aware of Apache POI. Is this an API for reading excel?? – user1853173 Nov 26 '12 at 12:01
Set your cell type to NUMERIC like this before you read the value myCell.setCellType(Cell.CELL_TYPE_NUMERIC); – someone Nov 27 '12 at 10:39
i used this code. HSSFCell cellE1 = row1.getCell((short) 4); cellE1.setCellType(HSSFCell.CELL_TYPE_NUMERIC); Double e1Val = cellE1.getNumericCellValue();but this is not working as expected – user1853173 Nov 27 '12 at 10:47
give me few mins I'll come back to you – someone Nov 27 '12 at 10:56
what is your expected value? – someone Nov 27 '12 at 11:24
this is the output "2.3432432423423E13" am getting for the "23432432423423". int he excel sheet format of the cell is general – user1853173 Nov 27 '12 at 11:29
Check my updated answer. I added few line to your code. try it and Let me know what happened. – someone Nov 27 '12 at 11:31
Thank you so much. its working fine. i got the actual value what i expected. – user1853173 Nov 27 '12 at 11:33
Because others will get to know there is correct answer for same kind of questions. – someone Nov 28 '12 at 04:59

score 2 · Answer 7 · answered Nov 26 '12 at 11:24

2

You can convert easily with the following methods:

Double.valueOf("9.78313E+2").longValue() or

BigDecimal bd = new BigDecimal("9.78313E+2");
long val = bd.longValue();

Assuming that the given number is in a String form.

answered Nov 26 '12 at 11:24

Endox

126
3

1

The above code gives output as 4260000000. but this is not the original value for 4.26E+09. – user1853173 Nov 26 '12 at 11:30

score 0 · Answer 8 · edited Jan 21 '18 at 10:59

0

You can also use wrapper classes :

Double bd=new Double(4445566622);
System.out.println(bd.longValue());

Outputs -4445566622

edited Jan 21 '18 at 10:59

Syscall

19,327
10
37
52

answered Jan 21 '18 at 10:42

Karan Bora

1

Criss · Answer 9 · 2019-10-18T01:43:41.233

i had same problem when i only needed String Data that is "1744949451" but it give "1.744949451E9" so this worked for me

          XSSFCell cell = cells.getCell(j);
          String value = cell.toString();
          if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            //cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            value = cell.getRawValue();
          }
          Log.i("LOG", value + " " + cell.getCellType());

score 0 · Answer 10 · edited Jan 27 '20 at 18:40

0

This answer worked for me:

Double bd = new Double(4445566622);
System.out.println(bd.longValue());
// Outputs -4445566622

edited Jan 27 '20 at 18:40

meyi

7,574
1
15
28

answered Jan 27 '20 at 17:03

Unni kannan

1

score 0 · Answer 11 · answered Feb 23 '21 at 13:37

`Double value = double value ;
    Long longValue = value.longValue();  String strCellValue1 = new String(longValue.toString().format("%f",value).replaceAll("\\,?0*$", ""));`

declare a double value and convert to long convert to string and formated to float the double value finally replace all the value like 123456789,0000 to 123456789

score -1 · Answer 12 · answered Oct 24 '17 at 15:54

Have to convert the cell into number format before reading the cell value. Below is the code snippet that is used to get the actual value that is in exponential format:

nextCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
Double doubleValue = nextCell.getNumericCellValue();
BigDecimal bd = new BigDecimal(doubleValue.toString());
long lonVal = bd.longValue();
String phoneNumber = Long.toString(lonVal).trim();
System.out.print("PhoneNumber " + phoneNumber);

Blog has been wirtten to showcase the actual result.

Regards, Ankur

score -1 · Answer 13 · answered Feb 07 '20 at 13:42

-1

Try the following pattern:

Double dblValue = Double.parseDouble("1.99E+07");       
String str = String.format("%.2f", dblValue);
System.out.println(str);

Output:

run:
19900000,00
BUILD SUCCESSFUL (total time: 0 seconds)

answered Feb 07 '20 at 13:42

Рамзес Жданов

109
3
7

This doesn't seem to add anything that's not already better explained in [Brian Agnew's answer](/a/13563784/4850040). – Toby Speight Feb 07 '20 at 14:04

Converting exponential value in java to a number format

13 Answers13

Linked