CODEWITHSUNDEEP
javaapache-poi
user2323844
user2323844

Reputation: 411

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

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?

Upvotes: 0

Views: 26401

Answers (9)

AlekseyAll
AlekseyAll

Reputation: 1

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

'123

Upvotes: 0

user17016856
user17016856

Reputation: 1

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();
        }
    }
}

Upvotes: 0

parth sahu
parth sahu

Reputation: 1

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

This might help

Upvotes: 0

Bablu Gope
Bablu Gope

Reputation: 9

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.

Upvotes: 0

Ömer Faruk UYAR
Ömer Faruk UYAR

Reputation: 1

You should use it =>

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

Upvotes: 0

gabi
gabi

Reputation: 1376

Check the cell type first and then get its value. 

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

Upvotes: 0

user2323844
user2323844

Reputation: 411

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

Upvotes: 0

myxlptlk
myxlptlk

Reputation: 139

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.

Upvotes: 4

Sk1X1
Sk1X1

Reputation: 1373

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

Upvotes: 0

Related Questions