Win a copy of Reactive Streams in Java: Concurrency with RxJava, Reactor, and Akka Streams this week in the Reactive Progamming forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

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: 43011
76
  • 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
Posts: 21803
104
Eclipse IDE Spring VI Editor Chrome Java Ubuntu 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
Eclipse IDE Chrome 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 :

 
and POOF! You're gone! But look, this tiny ad is still here:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!