Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem in reading Numeric value as String fom Excel file using Apache POI

 
james royson
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

I want to import my Excel file into MySQL database using Java. I have used Apache POI for this reason. I have one Column named "Mobile Number" that contains 10 digit numeric values (e.g. 9876543210 ). now when i read this value from excel file it gives me like "9.876543210E9".

My code is like this :


In above code. I am checking for the cell value type. for that Mobile number column it gives me type "Numeric Cell Value", so I have to use cell.getNumericCellValue() .

Can anyone please tell me what i should make change to get that numeric values as string.?

Thanks in Advance.
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could change the type of that column to string/text in the original Excel spreadsheet. I'm actually surprised that you're seeing the value displayed correctly in Excel if the cell type is numeric; I'd have expected it to use the scientific number format as well.
 
William Lawton
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm having the same problem. Have tried formatting the column in the spreadsheet as Text but it makes no difference, calling row.getCell(col1).toString() returns a representation of the number contained by the cell in scientific format regardless.
 
Rob Spoor
Sheriff
Pie
Posts: 20527
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving this thread to Other Open Source Projects.
 
Anbarasu Aladiyan
Ranch Hand
Posts: 182
Chrome Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Once i had the same problem when i tried the same.

Try below code,

double num = cell.getNumericCellValue();
DecimalFormat pattern = new DecimalFormat("#,#,#,#,#,#,#,#,#,#");
NumberFormat testNumberFormat = NumberFormat.getNumberInstance();
String mob = testNumberFormat.format(num);
Number n = null;
try{n = pattern.parse(mob);}
catch ( ParseException e ){e.printStackTrace();}



regards,
A.A.Anbarasu
 
Asif Shahzad
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i did it as follows, its works 100% ...

http://www.asif-shahzad.com/2010/12/how-to-read-numbers-as-string-value-in.html
 
Asif Shahzad
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
... yes, not a good solution ... but works ...
 
Erno Suomi
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This will work:
if(cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
int i = (int)cell.getNumericCellValue();
strCellValue = String.valueOf(i);
} else {
strCellValue = cell.toString();
}
 
Kelly Humbert
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The only thing I would add to Erno's post is that I had to use long instead of int for a full US phone number with area code.
 
Rithil Reghu
Greenhorn
Posts: 1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
cell.setCellType(Cell.CELL_TYPE_STRING);


this will set every cell in xsl sheet as string so we can read any value as String values.
 
Olivier Rihoux
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Facing the same problem, I used :

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic