Cannot get a text value from a numeric cell “Poi”

how to convert cell value to string in java
cannot get a numeric value from a text cell r
how to read integer value from excel in java
apache poi check if cell is numeric
type mismatch cannot convert from celltype to int
getstringcellvalue
xssfcell getcelltype
how to set cell type as string in excel using java

I'm trying to consume data from a spreadsheet in Excel, but always of this error, already tried formatting the worksheet to text and number and still the error persists.

I saw a person using it resolved cell.setCellType ( Cell.CELL_TYPE_STRING ) ; but I do not know where I fit this passage in my code.

WebElement searchbox = driver.findElement(By.name("j_username"));
WebElement searchbox2 = driver.findElement(By.name("j_password"));         

try {
    FileInputStream file = new FileInputStream(new File("C:\\paulo.xls")); 
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    HSSFSheet sheet = workbook.getSheetAt(0);

    for (int i=1; i <= sheet.getLastRowNum(); i++){
        String j_username = sheet.getRow(i).getCell(0).getStringCellValue();
        String j_password = sheet.getRow(i).getCell(0).getStringCellValue();

        searchbox.sendKeys(j_username);
        searchbox2.sendKeys(j_password);
        searchbox.submit();  

        driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS);
    }

    workbook.close();
    file.close();
} catch (FileNotFoundException fnfe) {
    fnfe.printStackTrace();
} catch (IOException ioe) {
    ioe.printStackTrace();

Formatter will work fine in this case.

import org.apache.poi.ss.usermodel.DataFormatter;

FileInputStream fis = new FileInputStream(workbookName);
Workbook workbook = WorkbookFactory.create(fis);
Sheet sheet = workbook.getSheet(sheetName);
DataFormatter formatter = new DataFormatter();
String val = formatter.formatCellValue(sheet.getRow(row).getCell(col));
list.add(val);   //Adding value to list

Cannot get a STRING value from a NUMERIC cell?, getNumericCellValue()); else It is a better way to retrieve the cell content as text​. Thanks to @AxelRichter, the manual show a more complete  This is one of the other method to solve the Error: "Cannot get a text value from a numeric cell “Poi”" Go to the Excel Sheet. Drag and Select the Numerics which you are importing Data from the Excel sheet. Go to Format > Number > Then Select "Plain Text" Then Export as.xlsx.

    Cell cell = sheet.getRow(i).getCell(0);
    cell.setCellType ( Cell.CELL_TYPE_STRING );
    String j_username = cell.getStringCellValue();

UPDATE

Ok, as have been said in comments, despite this works it isn't correct method of retrieving data from an Excel's cell.

According to the manual here:

If what you want to do is get a String value for your numeric cell, stop!. This is not the way to do it. Instead, for fetching the string value of a numeric or boolean or date cell, use DataFormatter instead.

And according to the DataFormatter API

DataFormatter contains methods for formatting the value stored in an Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel. Supported formats include currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.

So, right way to show numeric cell's value is as following:

 DataFormatter formatter = new DataFormatter(); //creating formatter using the default locale
 Cell cell = sheet.getRow(i).getCell(0);
 String j_username = formatter.formatCellValue(cell); //Returns the formatted value of a cell as a String regardless of the cell type.

Cannot get a STRING value from a NUMERIC cell. How can I , 7 Answers. 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 This exception is occurred when code try to read Numeric value as string. Apache POI will not convert from Numeric to String. Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:781) at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:199) I know for a fact that the excel spreadsheet column is in fact a string.

As explained in the Apache POI Javadocs, you should not use cell.setCellType(Cell.CELL_TYPE_STRING) to get the string value of a numeric cell, as you'll loose all the formatting

Instead, as the javadocs explain, you should use DataFormatter

What DataFormatter does is take the floating point value representing the cell is stored in the file, along with the formatting rules applied to it, and returns you a string that look like it the cell does in Excel.

So, if you're after a String of the cell, looking much as you had it looking in Excel, just do:

 // Create a formatter, do this once
 DataFormatter formatter = new DataFormatter(Locale.US);

 .....

 for (int i=1; i <= sheet.getLastRowNum(); i++) {
        Row r = sheet.getRow(i);
        if (r == null) { 
           // empty row, skip
        } else {
           String j_username = formatter.formatCellValue(row.getCell(0));
           String j_password =  formatter.formatCellValue(row.getCell(1));

           // Use these
        }
 }

The formatter will return String cells as-is, and for Numeric cells will apply the formatting rules on the style to the number of the cell

Let's Read Numeric Data from Excel for Selenium Framework using , Can not get a string value from a numeric cell? The result returned by MID function add zero for each value in array, if the value is non-numeric value, then the ISERR function return a #VALUE error. Or return the numeric text values will be converted to number.

Using the DataFormatter this issue is resolved. Thanks to "Gagravarr" for the initial post.

DataFormatter formatter = new DataFormatter();

String empno = formatter.formatCellValue(cell0);

Format Cells using GetStyle and SetStyle Methods, How do I get the value of a cell in Excel using poi? In the Rules section, make sure the Number stored as text rule is selected, and then click OK. To do this in Microsoft Office Excel 2007, follow these steps: Click the Microsoft Office Button , and then click Excel Options .

use the code cell.setCellType(Cell.CELL_TYPE_STRING); before reading the string value, Which can help you. I am using POI version 3.17 Beta1 version, sure the version compatibility also..

Cannot get a STRING value from a NUMERIC cell · Issue #4, Which method is used to get the format of the particular cell? 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.

"java.lang.IllegalStateException: Cannot get a STRING value from a , java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell at org.apache.poi.xssf.usermodel.XSSFCell. If the data is not a number, or the cell is empty, a FALSE value is returned — the example in rows 2 in the image above. Additional examples show how this function is often used in conjunction with other Excel functions to test the outcome of calculations.

Cannot get a STRING value from a NUMERIC cell. How , saadali5997 mentioned this issue on Aug 5, 2019. ERROR: java.lang.​IllegalStateException: Cannot get a text value from a numeric cell #7. This is one of the other method to solve the Error: "Cannot get a text value from a numeric cell “Poi”" Go to the Excel Sheet. Drag and Select the Numerics which you are importing Data from the Excel sheet. Go to Format > Number > Then Select "Plain Text" Then Export as.xlsx.

56702 – Get one exception "java.lang.IllegalStateException: Cannot , Cannot get a STRING value from a NUMERIC cell. How can I resolve this issue in selenium webdriver using POI? Exception in thread "main" java 

Comments
  • Can you place full stacktrace error message?
  • What about sheet.getRow(i).getCell(0).toString()?
  • @Abdull Your comment did more than the official answer :) Solved my problem
  • try cell.getRawValue() if you need string form numeric type : stackoverflow.com/questions/13563747
  • this thing doesn't work for me though, the out put of it something like: SUM(I38+D39+F39-G39-H39)
  • for (int i=1; i <= sheet.getLastRowNum(); i++){ Cell cell = sheet.getRow(i).getCell(0); cell.setCellType ( Cell.CELL_TYPE_STRING ); String j_username = cell.getStringCellValue(); ?!?!
  • made three erros Type mismatch: cannot convert from HSSFCell to Table.Cell CELL_TYPE_STRING cannot be resolved or is not a field The method getStringCellValue() is undefined for the type Table.Cell
  • there is only numbers in the spreadsheet 89550536100002595717
  • Don't do this! The POI javadocs are very clear on this not being the right way!
  • @Gagravarr, you're right. I didn't know about this, will fix my answer later.
  • this thing doesn't work for me though, the out put of it something like: SUM(I38+D39+F39-G39-H39)
  • this thing doesn't work for me though, the out put of it something like: SUM(I38+D39+F39-G39-H39)
  • most probably it happen if you not add libraries files like 1) dom4j-1.6.jar 2) xmlbeans-2.3.0.jar 3) poi-ooxml-schemas-3.9.jar 4) poi-ooxml-3.9.jar 5) poi-3.10-FINAL.jar
  • As explained in the Apache POI javadocs, you should not use setCellType to get the string value, it looses formatting