Cannot get a NUMERIC value from a STRING cell error

Question

I have tried many SO answers on this regard but still I could not resolve. My java file is as follows:

public class ReadExcelFileAndStore {

    public List getTheFileAsObject(String filePath){
        List <Employee> employeeList = new ArrayList<>();
        try {
            FileInputStream file = new FileInputStream(new File(filePath));

            // Get the workbook instance for XLS file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            int numberOfSheets = workbook.getNumberOfSheets();
            //System.out.println(numberOfSheets);
            //loop through each of the sheets
           for(int i = 0; i < numberOfSheets; i++) {

               String sheetName = workbook.getSheetName(i);
               System.out.println(sheetName);
               // Get first sheet from the workbook
               XSSFSheet sheet = workbook.getSheetAt(i);


               // Iterate through each rows from first sheet
               Iterator<Row> rowIterator = sheet.iterator();
               while (rowIterator.hasNext()) {

                   // Get Each Row
                   Row row = rowIterator.next();

                   //Leaving the first row alone as it is header
                   if (row.getRowNum() == 0) {
                       continue;
                   }
                   // For each row, iterate through each columns
                   Iterator<Cell> cellIterator = row.cellIterator();

                   Employee employee = new Employee();

                   while (cellIterator.hasNext()) {

                        Cell cell = cellIterator.next();


                        int columnIndex = cell.getColumnIndex();

                        switch (columnIndex + 1) {

                            case 1:
                                employee.setEmpName(cell.getStringCellValue());
                                break;

                            case 2:
                                employee.setExtCode((int) cell.getNumericCellValue());
                                break;

                            case 3:
                                employee.setDepartment(cell.getStringCellValue());
                                break;
                        }
                    }
                    employeeList.add(employee);
                }
            }
            file.close();
        } 
        catch (FileNotFoundException e) {
            e.printStackTrace();
        } 
        catch (IOException e) {
            e.printStackTrace();
        }
        return employeeList;
    }
}

I have the model as follows:

package com.restfapi.demo.Model;

public class Employee {

    private String empName;
    private int extCode;
    private String department;

    public Employee(){
    }

    public Employee(String empName,int extCode, String department){
        this.empName = empName;
        this.extCode = extCode;
        this.department = department;
    }

    //all getters and setters are followed
}

This problem is because the header of extCode is string and the values in that column are integers. Even if I skip the header and read the rest, the error appears.When I changed the datatype of extCode to String the error is reversed and getting "Cannot get a STRING cell from a NUMERIC cell". Please somebody help me to get rid of this error

In case 2 in your `getTheFileAsObject()` method, try doing `employee.setExtCode(Integer.parseInt(cell.getStringCellValue()));` From the look of things, you are trying to get a Numberic value from a cell which is a String. — archilius, Feb 02 '18 at 06:02
Can you add a paste link to a stack trace? Ideally if your cell value is numerical then it should work as suggested. — archilius, Feb 02 '18 at 11:08
Do your columns have headings? Can you paste a link to a sample spreadsheet? — jmarkmurphy, Feb 02 '18 at 14:20

Amit Bera · Answer 1 · 2018-02-02T14:29:44.273

Put a check when you are getting value from the cell. It should be something like below.

case 2:
    if(cell. getCellType() == CellType.NUMERIC){
        employee.setExtCode((int) cell.getNumericCellValue());
    }else{
    employee.setExtCode(Integer.valueOf(cell.getStringCellValue()));
    }
    break;

There are several supported cell type POI API. For more info visit the link

Edited:

If the above solution not work you can simply do

cell.setCellType(Cell.CELL_TYPE_STRING); 
employee.setExtCode(Integer.valueOf(cell.getStringCellValue()));

Before reading the value from the cell. However, POI documentation clearly says to use setCellType to read the value from a cell as you might be lost original formatting of the cell. Instead of using that you can use DataFormatter. The code should be like below (I did not complied and run).

String strValue = new DataFormatter().formatCellValue(cell);

this does not work. i have tried this out already. I am getting the same error still. suggest me some other ideas please! — , Feb 02 '18 at 09:15
@hema I have edited my answer. Please go through it, it may help you. — Amit Bera, Feb 02 '18 at 14:27

Cannot get a NUMERIC value from a STRING cell error

1 Answers1