reading excel file and saving to database?

Question

I am reading an Excel file and saving it to database.

I am iterating each row and cell and saving it to vector object, and then using loops, saving it to database. While I am saving it database, I am doing this:

id = ((XSSFCell) cellStoreVector.get(0)).toString()==null?"":((XSSFCell) cellStoreVector.get(0)).toString();

for every column. Everything else fine except for this one where I am getting 2.01356229E8 as id. I don't know why I am getting 2.01356229E8 in my id..is should be like this 201356229.

Thank you.

Why are you turning your numbers into strings before writing them to the database? Shouldn't you be storing them as a number instead? — Gagravarr
– Gagravarr, Commented Apr 10, 2013 at 9:09

rgettman · Accepted Answer · 2013-04-09 17:59:16Z

This isn't an Excel display problem, because the value has already been extracted into Java with Apache POI. But when XSSFCell#toString() is called, the Apache POI code uses the following expression to convert a cell with type CELL_TYPE_NUMERIC to a string in toString():

return getNumericCellValue() + "";

The XSSFCell#getNumericCellValue() method returns a double, which when converted to a string yields the string "2.01356229E8". This is the expected Java conversion from a double to a String because "2.01356229E8" is the scientific notation representation for 201356229. (In math, the representations 201356229 and 2.01356229E8 are equivalent.)

If the column in the Oracle database that you're saving to is a VARCHAR2, then you'll need to format the String value before saving it to the database. This will store "201356229" in id:

DecimalFormat df = new DecimalFormat("#");
id = df.format( ((XSSFCell) cellStoreVector.get(0)).getNumericCellValue() );

If the column is a NUMBER, then don't bother converting it to a String first, just use the numeric cell value as a double to pass on to the database.

double numericId = ((XSSFCell) cellStoreVector.get(0)).getNumericCellValue();

rajah9 · Accepted Answer · 2013-04-09 15:08:09Z

0

This sounds like an Excel display problem. Excel doesn't know that you want this to be an integer.

You might want to make this wider (select the whole column, right click, Column width... and enter 10).

You might also select the column, right-click, Format cells..., select Number, change Decimal places to 0).

answered Apr 9, 2013 at 15:08

rajah9

12.5k5 gold badges49 silver badges59 bronze badges

Collectives™ on Stack Overflow

reading excel file and saving to database?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related