Java & Excel: value being read wrong

Question

I am parsing data from an excel file and adding it to my data base using HSSFCell to parse the Excel file, I am using the following code to read the file

public void printCellDataToConsole(Vector dataHolder) throws SQLException {
            // this is for the rows in the file, set to one to skip the headings
            List<String> studentInformation = new ArrayList<>();
            for (int i = 1; i < dataHolder.size(); i++) {
                    Vector cellStoreVector = (Vector) dataHolder.elementAt(i);
                    // this is for the colums in the table
                    for (int j = 0; j < cellStoreVector.size(); j++) {
                            HSSFCell myCell = (HSSFCell) cellStoreVector.elementAt(j);
                            String stringCellValue = myCell.toString();
                            System.out.print(stringCellValue + "\t");                  
                    }
                    connectToDatabase.ammendTableInDatabase("INSERT INTO Student VALUES (" + cellStoreVector.get(0) + ",'" + cellStoreVector.get(1) + "','" + cellStoreVector.get(2) + "','" + cellStoreVector.get(3) + "','" + cellStoreVector.get(4) + "'," + cellStoreVector.get(5) + ",'" + cellStoreVector.get(6) + "')");
                    System.out.println();
            }
    }

Everything is working but when i try to read any value like the following

It reads them as the following

1.11223361E8
1.11223362E8
1.11223364E8
1.11223363E8
1.11223366E8

Why is this happening and how do i solve it?

Because I am also interesseted in reading values from Excel files, what framework do you use for doing that? — Reporter
– Reporter, Commented Sep 10, 2012 at 10:06
POI is my guess, since HSSFCell was mentioned. I'd recommend Andy Khan's JExcel over POI. — duffymo
– duffymo, Commented Sep 10, 2012 at 10:07
@reporter: well i am using poi-3.8-20120326.jar to parse the excel files, do you want to know anything else? — Hip Hip Array
– Hip Hip Array, Commented Sep 10, 2012 at 10:09
@ duffymo : Does JExcel support new data format? stackoverflow.com/questions/4763624/… mentions JExel cannot support new version. (May be the answer need a tweek) — Jayan
– Jayan, Commented Sep 10, 2012 at 10:10

sp00m · Accepted Answer · 2012-09-10 10:15:45Z

3

1.11223361E8 means 1.11223361 x (10 ^ 8), so 111223361 = 1.11223361E8...

If you want to get it well formatted, use Double.valueOf("1.11223361E8").longValue().

edited Sep 10, 2012 at 10:15

answered Sep 10, 2012 at 10:07

sp00m

49k31 gold badges152 silver badges263 bronze badges

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

1 Comment

Hip Hip Array Over a year ago

yes this solved the problem and thanks for explaining why the numbers where appearing in the format they where appearing in :)

Daniel André · Accepted Answer · 2012-09-10 10:22:09Z

1

Try using:

cellValueLong = Long.valueOf(myCell.toString());

I faced the same problem myself a few weeks ago. This happens because the number you are extrating as a string, comes in scientific notation format. And since you get it as a string and not as a long/int/(whatever) it returns the string value of that number in that format.

edited Sep 10, 2012 at 10:22

answered Sep 10, 2012 at 10:08

Daniel André

1,2481 gold badge17 silver badges28 bronze badges

1 Comment

Hip Hip Array Over a year ago

I tried this but myCell.Value gives the following: cannot find symbol symbol: variable Value location: variable myCell of type org.apache.poi.hssf.usermodel.HSSFCell

Community · Accepted Answer · 2017-05-23 12:12:14Z

0

for getting values from excel sheet.this is the best solution.It may help u.

https://stackoverflow.com/a/12067137/1211000

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Sep 10, 2012 at 12:49

swamy

1,21011 silver badges24 bronze badges

Collectives™ on Stack Overflow

Java & Excel: value being read wrong

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related