Error: "Cannot get a STRING value from a NUMERIC cell", when converting Excel

Question

I have excel sheet with date column, when I run my program I got this error:

Exception in thread "AWT-EventQueue-0" java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell

My code that I run:

String date =sheets.getRow(choosenRow).getCell(3).getStringCellValue().toString();

How can I convert numeric value to String? in my case I used .toString(); but seems it didn't worked.

[It's documented behavior](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#getStringCellValue--): "*For numeric cells we throw an exception.*". Use `getNumericCellValue()`, then convert the `double` to a `String` — Vince, Jul 17 '18 at 07:19
@mona You'll have to provide more context, such as the sheet you're working with. The error states that your cell is numeric. You could try `getDateCellValue()` if you're suggesting the cell is a date value. — Vince, Jul 17 '18 at 09:35
@mona if `getNumericCellValue` did not work as you said, why accepting an answer which propose only it ? And not the one giving a solution to deal with date ? — azro, Jul 18 '18 at 09:02

MajikalExplosions · Accepted Answer · 2018-07-17T07:45:22.067

I believe that it is because you are using .getStringCellValue(), when you should be using getNumericCellValue(). .getStringCellValue() can't be used with NUMERIC cells. I'm assuming that you are using Apache POI by the way.

See https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html for more info.

I'm not sure why I was downvoted, can you let me know in the comments about how I can rewrite it to be more clear(if that is the problem) or tell me how it is wrong?

score 0 · Answer 2 · answered Jul 17 '18 at 07:32

i not tested your code..!

but

you should try Object class to get data from that exal file. if data are successfully recevied. then you can convert in to date or String.

like :

Object objdata = sheets.getRow(choosenRow).getCell(3).getStringCellValue();

score 0 · Answer 3 · answered Jul 18 '18 at 06:41

0

Thanks all, it's working perfectly now. I have changed the code to be like that:

Date date =sheets.getRow(choosenRow).getCell(3)getDateCellValue();
dateTextField.setText(""+date);

answered Jul 18 '18 at 06:41

mona

53
1
1
7

azro · Answer 4 · 2018-07-17T08:01:56.983

The error tells you that the cell if type NUMERIC, you may use the approriate method getNumericCellValue()

String date = "" + sheets.getRow(choosenRow).getCell(3).getNumericCellValue();

If you don't know the type before, and need to read some values, you may implment a switch on the type (getCellType()) and from the result of this enum use the right method

Specifically for the numeric type, you can check for date element :

public String getContent(Cell c){
    switch(c.getCellType()){
        case: NONE     :  throw new IllegalArgumentException("NONE");
        case: BLANK    :  return "";
        case: BOOLEAN  :  return ""+c.getBooleanCellValue();
        case: ERROR    :  return ""+c.getErrorCellValue()
        case: FORMULA  :  return c.getCellFormula()
        case: NUMERIC  :  return HSSFDateUtil.isCellDateFormatted(c)) ? 
                                 c.getDateCellValue()) : ""+c.getNumericCellValue();
        case: STRING   :  return c.getStringCellValue()
}

Ref to How to read Excel cell having Date with Apache POI?

Error: "Cannot get a STRING value from a NUMERIC cell", when converting Excel

4 Answers4