Cannot get a STRING value from a NUMERIC cell. How can I resolve this issue in selenium webdriver using POI?

Question

I am trying to read data from an excel sheet and write on my application.But shows an error while trying to pass a numeric value using string. Please suggest me how can I resolve the issue. Please see my code and error screenshot below:

package ReadExcelData;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Locale;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {

public static void main(String[] args) throws Exception {
    File src=new File("E:\\QA_Automation\\TestData_Excel\\TestInputData.xlsx");

    FileInputStream fis=new FileInputStream(src);

    XSSFWorkbook wb=new XSSFWorkbook(fis);

    XSSFSheet sheet1=wb.getSheetAt(0);
    int rowcount=sheet1.getLastRowNum();
    System.out.println("Total Row" + rowcount);

    for(int i=0;i<rowcount;i++) {
        String data0= sheet1.getRow(i).getCell(0).getStringCellValue();
        System.out.println("Test Data From Excel"+data0);
    }

    wb.close();


}

}

Please check below image for the exception :

Answer 1

This may be helpful to you..

Open the Excel sheet and select all (Ctrl+a) then right click and click Format cells then in number tab change the category to Text and click Ok...

Answer 2

This exception is occurred when code try to read Numeric value as string. Apache POI will not convert from Numeric to String.

---

There are two solution :

1. First convert cell type to string and Read it. Please check below code :

    import java.io.File;
        import java.io.FileInputStream;
        import java.io.FileNotFoundException;
        import java.util.Locale;
        
        import org.apache.poi.ss.usermodel.Cell;
        import org.apache.poi.ss.usermodel.DataFormatter;
        import org.apache.poi.xssf.usermodel.XSSFSheet;
        import org.apache.poi.xssf.usermodel.XSSFWorkbook;
        
        public class ReadExcel {
        
        public static void main(String[] args) throws Exception {
            File src=new File("C:\\Users\\Sagar\\Desktop\\TestInputData.xlsx");
        
            FileInputStream fis=new FileInputStream(src);
        
            XSSFWorkbook wb=new XSSFWorkbook(fis);
        
            XSSFSheet sheet1=wb.getSheetAt(0);
            int rowcount=sheet1.getLastRowNum();
            System.out.println("Total Row" + rowcount);
        
            for(int i=0;i<rowcount+1;i++) {
        
                System.out.println(i);
                //GET CELL
                Cell cell1 = sheet1.getRow(i).getCell(0);   
                //SET AS STRING TYPE
                cell1.setCellType(Cell.CELL_TYPE_STRING);
                String data0= cell1.getStringCellValue();
                System.out.println("Test Data From Excel : "+data0);
            }
        
            wb.close();
          }
        }

2. Get type first and print by its option. Please see the code:

        import java.io.File;
        import java.io.FileInputStream;
        import java.io.FileNotFoundException;
        import java.util.Locale;
        
        import org.apache.poi.ss.usermodel.Cell;
        import org.apache.poi.ss.usermodel.DataFormatter;
        import org.apache.poi.ss.usermodel.DateUtil;
        import org.apache.poi.xssf.usermodel.XSSFSheet;
        import org.apache.poi.xssf.usermodel.XSSFWorkbook;
        
        public class ReadExcel {
        
        public static void main(String[] args) throws Exception {
            File src=new File("C:\\Users\\Sagar\\Desktop\\TestInputData.xlsx");
        
            FileInputStream fis=new FileInputStream(src);
        
            XSSFWorkbook wb=new XSSFWorkbook(fis);
        
            XSSFSheet sheet1=wb.getSheetAt(0);
            int rowcount=sheet1.getLastRowNum();
            System.out.println("Total Row " + rowcount);
        
            for(int i=0;i<rowcount+1;i++) {
        
                
                Cell cell1 = sheet1.getRow(i).getCell(0);   
                
                switch (cell1.getCellType()) {
                
                case Cell.CELL_TYPE_STRING:
                    System.out.println(cell1.getRichStringCellValue().getString());
                    break;
                    
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell1)) {
                        System.out.println(cell1.getDateCellValue());
                    } else {
                        System.out.println(cell1.getNumericCellValue());
                    }
                    break;
                    
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell1.getBooleanCellValue());
                    break;
                    
                case Cell.CELL_TYPE_FORMULA:
                    System.out.println(cell1.getCellFormula());
                    break;
                    
                default:
                    System.out.println();
            }
                
                //String data0= cell1.getStringCellValue();
                //System.out.println("Test Data From Excel : "+data0);
            }
        
            wb.close();
        
        
        }
        
        }

---

Note : Please check loop condition.It is also wrong. My code will not skip last row.

Answer 3

You Can Check if cell is numeric or String through this code

     if(MytempCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
       your code ...
     else if(MytempCell.getCellType() == Cell.CELL_TYPE_STRING)
      your code ..

Answer 4

Assuming there is the column holding only numeric cells you can approach with this:

for(int i=0;i<rowcount;i++) {
    String data0 = String.valueOf(sheet1.getRow(i).getCell(0).getNumericCellValue());
    System.out.println("Test Data From Excel" + data0);
}

The approach above is a pure java way to display data of double type. However if you need the formatting like you would have in Excel you should check this doc: https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html

Answer 5

*CELLNAME*.setCellType(CellType.NUMERIC);

This works wonderfully and converts the data in the cell as numeric.

For operating on that value use:

*CELLNAME*.getNumericCellValue()

Answer 6

Another way is adding the apostrophe before number in Excel cell --> Excel will treat it as text.

'123456

Answer 7

If you don't care what datatype the cell is, you can use DataFormatter.formatCellValue(Cell). This returns the default string format for each cell type, and it won't execute Excel formulas (it returns the formula text).

[See \\ modified below]

package ReadExcelData;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Locale;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {

public static void main(String[] args) throws Exception {
    File src=new File("E:\\QA_Automation\\TestData_Excel\\TestInputData.xlsx");

    FileInputStream fis=new FileInputStream(src);

    XSSFWorkbook wb=new XSSFWorkbook(fis);

    XSSFSheet sheet1=wb.getSheetAt(0);
    int rowcount=sheet1.getLastRowNum();
    System.out.println("Total Row" + rowcount);

    for(int i=0;i<rowcount;i++) {
        String data0= new DataFormatter().formatCellValue(sheet1.getRow(i).getCell(0)); \\ modified
        System.out.println("Test Data From Excel"+data0);
    }

    wb.close();


}

}

Answer 8

driver.findElement(By.id("field on screen")).sendKeys(String.valueOf(cell.getNumericCellValue()));

Answer 9

The easiest way to remove the error:: Cannot get a STRING value from a NUMERIC cell --> Go to excel --> Put an apostrophe at start of numeric data cell. When the code will run, it will consider the cell value as String and will not throw any such error.