wood burning stoves*
The moose likes Other Open Source Projects and the fly likes Problem in reading Numeric value as String fom Excel file using Apache POI Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Problem in reading Numeric value as String fom Excel file using Apache POI" Watch "Problem in reading Numeric value as String fom Excel file using Apache POI" New topic
Author

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

james royson
Greenhorn

Joined: Dec 24, 2008
Posts: 4
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
Marshal

Joined: Mar 22, 2005
Posts: 41635
    
  55
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.


Ping & DNS - my free Android networking tools app
William Lawton
Greenhorn

Joined: Jan 18, 2009
Posts: 1
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

Joined: Oct 27, 2005
Posts: 19685
    
  20

Moving this thread to Other Open Source Projects.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Anbarasu Aladiyan
Ranch Hand

Joined: Jun 02, 2009
Posts: 182

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


A.A.Anbarasu
Asif Shahzad
Greenhorn

Joined: Jun 06, 2008
Posts: 4
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

Joined: Jun 06, 2008
Posts: 4
... yes, not a good solution ... but works ...
Erno Suomi
Greenhorn

Joined: Aug 24, 2011
Posts: 1
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

Joined: Mar 04, 2010
Posts: 1
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

Joined: Mar 06, 2013
Posts: 1

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

Joined: Jun 03, 2013
Posts: 8
Facing the same problem, I used :

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem in reading Numeric value as String fom Excel file using Apache POI