• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moving this thread to Other Open Source Projects.
 
Ranch Hand
Posts: 182
Eclipse IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
... yes, not a good solution ... but works ...
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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();
}
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 1
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Facing the same problem, I used :

 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic