IllegalStateException: Cannot get a NUMERIC value from a STRING cell

Question

I am creating a java program for converting .xlsx files to .Json. I have verified the cell data format to be numeric using the following condition.

else if (rw.getCell(j).getCellType() == Cell.CELL_TYPE_NUMERIC) {
    key = "" +sht.getRow(0).getCell(j).getNumericCellValue();
}

Still I am getting this error:

IllegalStateException: Cannot get a NUMERIC value from a STRING cell.

How is that possible?

Are you sure `rw` and `sht.getRow(0)` are the same row? check `else if (sht.getRow(0).getCell(j).getCellType() == Cell.CELL_TYPE_NUMERIC)` — Guy, Mar 22 '18 at 06:19
I have an 'else if' block for Numeric, String and formula (numeric and string values) too. Yet it gives error with the same step. Its actually Date format Data where it gets stuck here. Up to date I have properly edited and formatted date values in my excel file, added an else block to get Date: cell.getDateCellValue(); I am only getting some numbers like 45645 in Json file for Date values in Excel. — Alok Chahande, Mar 22 '18 at 09:02

score 0 · Answer 1 · answered Mar 22 '18 at 10:30

If the exception is raised by this line:

key = "" +sht.getRow(0).getCell(j).getNumericCellValue();

It means that the type of sht.getRow(0).getCell(j) is not numeric.

Use the same variable in your if clause and in the if block, or it will be a never ending source of bugs.

Row row = sht.getRow(0);
Cell cell = row.getCell(j);
...
String key = null;
else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
    key = "" +cell.getNumericCellValue();
}

So now 2 cases

If cell is of type NUMERIC then it will work and you will have a key.
If cell is NOT of type NUMERIC then it will still work (not crash) but you will have a null key.

Also I would be curious to see what output you get if you add the following print statements before your if:

System.out.println("Cell.CELL_TYPE_NUMERIC: "+Cell.CELL_TYPE_NUMERIC);
System.out.println("rw.getCell(j).getCellType(): "+rw.getCell(j).getCellType());
System.out.println("sht.getRow(0).getCell(j).getCellType(): "+sht.getRow(0).getCell(j).getCellType());

I expect them to not be all the same.

IllegalStateException: Cannot get a NUMERIC value from a STRING cell

1 Answers1