java.lang.IllegalStateException: Cannot get a numeric value from a text cell

Question

I have seen lot of answers over here regarding my question, but can not find solution for it. I am reading a excel file and storing in mysql database. This is my code

            Sheet mySheet = wb.getSheetAt(0);
            for (Row row : mySheet) {
                int columnNumber = mySheet.getFirstRowNum();
                Cell c = row.getCell(columnNumber);
                String sql = "insert into medtest(FMCODE,FLAG,MCODE,EMPNO,NAME,ADDRESS1,ADDRESS2,ADDRESS3,BALANCE,HOSPITAL_O,HOSPITAL_I,NURSING,GENERAL,PRIVATE,SPLCODE,BKCD,ACCOUNT_NO) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                PreparedStatement ps = (PreparedStatement) con
                        .prepareStatement(sql); 
                ps.setString(1, row.getCell(0).getStringCellValue());
                ps.setString(2, row.getCell(1).getStringCellValue());
                ps.setInt(3, (int) row.getCell(2).getNumericCellValue());
                if (c != null) {
                if (c.getCellType() == Cell.CELL_TYPE_STRING)
                    ps.setString(4, row.getCell(3).getStringCellValue());
                else if(c.getCellType() == Cell.CELL_TYPE_NUMERIC)
                    ps.setInt(4, (int) row.getCell(3).getNumericCellValue());
                }
                ps.setString(5, row.getCell(4).getStringCellValue());
                ps.setString(6, row.getCell(5).getStringCellValue());
                ps.setString(7, row.getCell(6).getStringCellValue());
                ps.setString(8, row.getCell(7).getStringCellValue());
                ps.setFloat(9, (float) row.getCell(8).getNumericCellValue());
                ps.setFloat(10, (float) row.getCell(9).getNumericCellValue());
                ps.setFloat(11, (float) row.getCell(10).getNumericCellValue());
                ps.setFloat(12, (float) row.getCell(11).getNumericCellValue());
                ps.setFloat(13, (float) row.getCell(12).getNumericCellValue());
                ps.setFloat(14, (float) row.getCell(13).getNumericCellValue());
                ps.setString(15, row.getCell(14).getStringCellValue());
                ps.setString(16, row.getCell(15).getStringCellValue());
                ps.setString(17, row.getCell(16).getStringCellValue());
                ps.executeUpdate(); 
            }

The below is my exception occuring at line ps.setString(4, row.getCell(3).getStringCellValue());

java.lang.IllegalStateException: Cannot get a text value from a numeric cell
    at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:643)
    at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:720)
    at org.apache.poi.hssf.usermodel.HSSFCell.getStringCellValue(HSSFCell.java:703)
    at org.stc.action.DataImport.doGet(DataImport.java:94)
    at org.stc.action.DataImport.doPost(DataImport.java:70)

The cell contains of both string and integer values

1.Sample data : D05 (String)

2.Sample data : 3916(Integer)

But in my table i have taken varchar as its datatype

can you attach your excel sheet or a screenshot of it?

Soumitri Pattnaik
– Soumitri Pattnaik

2015-05-21 07:07:09 +00:00
Commented May 21, 2015 at 7:07 — Soumitri Pattnaik
– Soumitri Pattnaik, Commented May 21, 2015 at 7:07

Jens · Accepted Answer · 2015-05-21 08:56:08Z

5

you have to check the type of the returned cell:

if (row.getCell(3).getCellType() == Cell.CELL_TYPE_STRING) 
   ps.setString(4, row.getCell(3).getStringCellValue());
else 
   ps.setString(4, String.valueOf(row.getCell(3).getNumericCellValue()))

edited May 21, 2015 at 8:56

answered May 21, 2015 at 7:08

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

spt Over a year ago

I have changed and run it, showing 'Cannot get a numeric value from a text cell'

spt Over a year ago

reverse of the exception

Jens Over a year ago

@spt Sorry my fault. See my updated answer. You have to check the cell at index 3 not at index 0

Stugal · Accepted Answer · 2015-05-21 07:10:20Z

0

Write a helper method, I had similar issue, sometimes even though you expect string (or int cell) they are of different type if they are empty:

if (cell.getCellType() == Cell.CELL_TYPE_STRING)
{
 //process string value
}
else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
{
 //process numeric value
}
else
{
}

answered May 21, 2015 at 7:10

Stugal

8801 gold badge8 silver badges24 bronze badges

4 Comments

Stugal Over a year ago

are you running all cells of all rows through this method? I had an issue where in one column some cells were text some were numeric.

spt Over a year ago

only particular cell which has text and integer values i.e , once check my code ps.setString(4,getType)

Stugal Over a year ago

you still have ps.setString(4, row.getCell(3).getStringCellValue()); above the check, did you forget to edit or it's still in the code?

Stugal Over a year ago

change ps.setString(4, row.getCell(3).getStringCellValue()); ps.setString(4, c.getStringCellValue()); (just to make sure you're reading correct cell). Show the stack trace with these changes.

Collectives™ on Stack Overflow

java.lang.IllegalStateException: Cannot get a numeric value from a text cell

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related