Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell

Question

I have written code as below to fetch value from Excel.

String CID = s1.getRow(i).getCell(0).getStringCellValue();

but in excel, 1st cell is a numeric value, but in above code I am trying to fetch String cell value. thats why I am getting error as :

Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell

Can anyone please provide a solution for this. how to fetch the numeric value from excel?

possible duplicate of [When getting cell content using Apache-POI Library, I get both "Cannot get a numeric value from a text cell" and the reverse of that. How do I fix it?](http://stackoverflow.com/questions/6508203/when-getting-cell-content-using-apache-poi-library-i-get-both-cannot-get-a-num) — Gagravarr, Jul 02 '14 at 20:21

score 4 · Answer 1 · answered Jul 02 '14 at 11:23

getCellType() for any cell gives you the type of the cell.The types are:

Cell.CELL_TYPE_BLANK
Cell.CELL_TYPE_NUMERIC
Cell.CELL_TYPE_STRING
Cell.CELL_TYPE_FORMULA
Cell.CELL_TYPE_BOOLEAN
Cell.CELL_TYPE_ERROR

It is better to use a switch statement and collect the correct type of cell value. There exists getNumericCellValue() and getStringCellValue() functions but it is safer with types.

score 0 · Answer 2 · answered Jul 02 '14 at 11:12

0

I'm not sure, but I think that exists getNumericalCellValue() or getIntegerCellValue() which returns what you want.

answered Jul 02 '14 at 11:12

Sk1X1

1,305
5
22
50

the mentioned command is there, but after fetching this value, i need to pass it as an argument to sendkeys() which accepts only string – user2323844 Jul 02 '14 at 11:19
1

You can always use something like `String cell = Integer.toString(numericCell)` or `String cell = String.valueOf(numericCell)` – Sk1X1 Jul 02 '14 at 23:08

score 0 · Answer 3 · answered Jul 03 '14 at 06:40

0

Add apostrophe as prefix to that number in cell, automatically it will be converted as text. it had worked for me

answered Jul 03 '14 at 06:40

user2323844

401
3
8
9

score 0 · Answer 4 · answered Dec 10 '15 at 12:16

0

Check the cell type first and then get its value.

           row.getCell(index).getCellType();
Number ==> row.getCell(index).getNumericCellValue();
String ==> row.getCell(index).getStringCellValue();

answered Dec 10 '15 at 12:16

gabi

1,324
4
22
47

score 0 · Answer 5 · answered Jan 13 '17 at 13:36

0

You should use it =>

String CID = new BigDecimal(s1.getRow(i).getCell(0).getNumericCellValue()).toString();

answered Jan 13 '17 at 13:36

Ömer Faruk UYAR

1
1

score 0 · Answer 6 · answered Sep 25 '19 at 11:25

String unitPrice = new Float(wb.getSheetAt(0).getRow(0).getCell(7).getNumericCellValue()).toString();

Note: its the best way to get the data in string if the value is stored in the numerical type in excel file. where we are getting the data from 7th cell of 1st row.

score 0 · Answer 7 · answered May 14 '21 at 07:32

0

final DataFormatter df = new DataFormatter();
final XSSFCell cell = row.getCell(cellIndex);
String valueAsString = df.formatCellValue(cell);

This might help

answered May 14 '21 at 07:32

parth sahu

1
2

score 0 · Answer 8 · answered Sep 27 '21 at 14:05

Below solution worked for me

package dummy;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Assert;

public class read_excel {

    public static void main(String[] args) throws Exception {
        String filename = "abP.xlsx";

        try (FileInputStream fis = new FileInputStream(filename)) {
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            //XSSFWorkbook workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet = workbook.getSheetAt(0);

            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                XSSFRow row = (XSSFRow) rows.next();
                Iterator cells = row.cellIterator();
                while (cells.hasNext()) {
                    XSSFCell cell = (XSSFCell) cells.next();

                    CellType type = cell.getCellTypeEnum();
                    if (type == CellType.STRING) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                            + cell.getColumnIndex() + "] = STRING; Value = "
                            + cell.getRichStringCellValue().toString());
                    } else if (type == CellType.NUMERIC) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                                     + cell.getColumnIndex() + "] = NUMERIC; Value = "
                                      + cell.getRawValue());
                        
                      //  System.out.println("[" + cell.getRowIndex() + ", "
                      //      + cell.getColumnIndex() + "] = NUMERIC; Value = "
                       //     + cell.getNumericCellValue());
                    } else if (type == CellType.BOOLEAN) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                            + cell.getColumnIndex() + "] = BOOLEAN; Value = "
                            + cell.getBooleanCellValue());
                    } else if (type == CellType.BLANK) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                            + cell.getColumnIndex() + "] = BLANK CELL");
                    }
                    else if (type == CellType.FORMULA) {
                        System.out.println("[" + cell.getRowIndex() + ", "
                            + cell.getColumnIndex() + "] = BLANK CELL");
                    }
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }
}

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Sep 27 '21 at 15:45

score 0 · Answer 9 · answered Jan 12 '22 at 08:56

0

Please add an apostrophe before number in Excel cell. That solved my similar problem when using Revit API. E.g:

'123

answered Jan 12 '22 at 08:56

AlekseyAll

1

Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell

9 Answers9

Linked