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

Question

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

@Abdull Your comment did more than the official answer :) Solved my problem — pouyan021, Nov 25 '17 at 06:37
try cell.getRawValue() if you need string form numeric type : https://stackoverflow.com/questions/13563747 — Criss, Oct 18 '19 at 01:40

score 95 · Accepted Answer · edited May 17 '18 at 10:44

95

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

edited May 17 '18 at 10:44

chris

356
1
5
13

answered May 04 '17 at 06:17

Manish

1,085
9
5

3

this thing doesn't work for me though, the out put of it something like: SUM(I38+D39+F39-G39-H39) – S Gaber Mar 13 '18 at 08:31
@SGaber, you need to provide a formula evaluator as a second argument to the `formatCellValue` method, then it correctly formats even the formulas. – Andy Aug 21 '20 at 12:13

Anatoly · Answer 2 · 2015-05-09T12:32:27.377

58

    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.

edited May 09 '15 at 12:32

answered May 08 '15 at 13:53

Anatoly

5,056
9
62
136

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(); ?!?! – Paulo Roberto May 08 '15 at 13:55
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 – Paulo Roberto May 08 '15 at 13:59
there is only numbers in the spreadsheet 89550536100002595717 – Paulo Roberto May 08 '15 at 14:05
Don't do this! The [POI javadocs are very clear](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29) on this not being the right way! – Gagravarr May 09 '15 at 10:39
@Gagravarr, you're right. I didn't know about this, will fix my answer later. – Anatoly May 09 '15 at 12:18
this thing doesn't work for me though, the out put of it something like: SUM(I38+D39+F39-G39-H39) – S Gaber Mar 13 '18 at 08:28
This fails for cells which have formulas. – saran3h Mar 17 '21 at 11:35
@saran3h, read comments to the accepted answer, they might help you. – Anatoly Mar 17 '21 at 13:18

score 14 · Answer 3 · answered May 09 '15 at 10:45

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

this thing doesn't work for me though, the out put of it something like: SUM(I38+D39+F39-G39-H39) — S Gaber, Mar 13 '18 at 08:28

score 9 · Answer 4 · edited Feb 22 '19 at 07:12

9

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..

edited Feb 22 '19 at 07:12

Kiran S

78
1
10

answered Aug 30 '18 at 17:59

Kiran Antony

190
1
15

score 6 · Answer 5 · edited Jan 05 '17 at 19:22

6

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

DataFormatter formatter = new DataFormatter();

String empno = formatter.formatCellValue(cell0);

edited Jan 05 '17 at 19:22

rfornal

5,072
5
30
42

answered Jan 05 '17 at 18:57

Coder

69
1
1

1

this thing doesn't work for me though, the out put of it something like: SUM(I38+D39+F39-G39-H39) – S Gaber Mar 13 '18 at 08:30

score 4 · Answer 6 · edited Nov 13 '18 at 07:58

4

CellType cell = row.getCell(j).getCellTypeEnum();

switch(cell) {
    case NUMERIC:
        intVal = row.getCell(j).getNumericCellValue();
        System.out.print(intVal);
        break;
    case STRING:
        stringVal = row.getCell(j).getStringCellValue();
        System.out.print(stringVal);
        break;
}

edited Nov 13 '18 at 07:58

quant

2,184
2
19
29

answered Aug 03 '18 at 13:30

ZoranS

51
4

seinecle · Answer 7 · 2023-03-30T11:51:32.350

Helper method for different cell types:

private static String returnStringValue(Cell cell) {
    CellType cellType = cell.getCellType();

    switch (cellType) {
        case NUMERIC -> {
            double doubleVal = cell.getNumericCellValue();
            if (doubleVal == (int) doubleVal) {
                int value = Double.valueOf(doubleVal).intValue();
                return String.valueOf(value);
            } else {
                return String.valueOf(doubleVal);
            }
        }
        case STRING -> {
            return cell.getStringCellValue();
        }
        case ERROR -> {
            return String.valueOf(cell.getErrorCellValue());
        }
        case BLANK -> {
            return "";
        }
        case FORMULA -> {
            return cell.getCellFormula();
        }
        case BOOLEAN -> {
            return String.valueOf(cell.getBooleanCellValue());
        }
    }
    return "error decoding string value of the cell";

}

score 1 · Answer 8 · edited Mar 08 '16 at 06:30

Use that code it definitely works and I modified it.

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
//import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.*;

public class TestApp {

    public static void main(String[] args) throws Exception {

        try {

            Class forName = Class.forName("com.mysql.jdbc.Driver");
            Connection con = null;
            con = DriverManager.getConnection("jdbc:mysql://localhost/tables", "root", "root");
            con.setAutoCommit(false);
            PreparedStatement pstm = null;
            FileInputStream input = new FileInputStream("C:\\Users\\Desktop\\a1.xls");
            POIFSFileSystem fs = new POIFSFileSystem(input);
            Workbook workbook;
            workbook = WorkbookFactory.create(fs);
            Sheet sheet = workbook.getSheetAt(0);
            Row row;
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                row = (Row) sheet.getRow(i);
                String name = row.getCell(0).getStringCellValue();
                String add = row.getCell(1).getStringCellValue();

                int  contact = (int) row.getCell(2).getNumericCellValue();

                String email = row.getCell(3).getStringCellValue();

                String sql = "INSERT INTO employee (name, address, contactNo, email) VALUES('" + name + "','" + add + "'," + contact + ",'" + email + "')";
                pstm = (PreparedStatement) con.prepareStatement(sql);
                pstm.execute();
                System.out.println("Import rows " + i);
            }
            con.commit();
            pstm.close();
            con.close();
            input.close();
            System.out.println("Success import excel to mysql table");
        } catch (IOException e) {
        }
    }

}

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 — Harshil Kulkarni, Mar 08 '16 at 09:36

score 1 · Answer 9 · answered Nov 13 '18 at 07:00

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. Now Try to Run the Script

Hope works Fine...!

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

virtuvious · Answer 10 · 2019-01-30T18:39:10.610

If you are processing in rows with cellIterator....then this worked for me ....

  DataFormatter formatter = new DataFormatter();   
  while(cellIterator.hasNext())
  {                         
        cell = cellIterator.next();
        String val = "";            
        switch(cell.getCellType()) 
        {
            case Cell.CELL_TYPE_NUMERIC:
                val = String.valueOf(formatter.formatCellValue(cell));
                break;
            case Cell.CELL_TYPE_STRING:
                val = formatter.formatCellValue(cell);
                break;
        }
    .....
    .....
  }

score 1 · Answer 11 · edited Feb 19 '19 at 14:01

public class B3PassingExcelDataBase {


    @Test()
    //Import the data::row start at 3 and column at 1: 
    public static void imortingData () throws IOException {

        FileInputStream  file=new         FileInputStream("/Users/Downloads/Book2.xlsx");
        XSSFWorkbook book=new XSSFWorkbook(file);
        XSSFSheet sheet=book.getSheet("Sheet1");

        int rowNum=sheet.getLastRowNum();
        System.out.println(rowNum);

        //get the row and value and assigned to variable to use in application
        for (int r=3;r<rowNum;r++) {

            // Rows stays same but column num changes and this is for only one person. It iterate for other.
             XSSFRow currentRow=sheet.getRow(r);

             String fName=currentRow.getCell(1).toString();
             String lName=currentRow.getCell(2).toString();
             String phone=currentRow.getCell(3).toString();
             String email=currentRow.getCell(4).toString()

               //passing the data
                yogen.findElement(By.name("firstName")).sendKeys(fName);    ;
                yogen.findElement(By.name("lastName")).sendKeys(lName); ;
                yogen.findElement(By.name("phone")).sendKeys(phone);    ;

        }

        yogen.close();


    }

}

score 1 · Answer 12 · answered Dec 13 '22 at 12:24

You could add the cell.setCellType ( Cell.CELL_TYPE_STRING ); inside the loop, so it would treat the cell as per your requirement.

Following would be the 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++){
        // Row will be iterated and Cell will be 0 and 1 as per the example:   
        row.getCell(0).setCellType(CellType.STRING); // set Cell Type as String
        row.getCell(1).setCellType(CellType.STRING); // set Cell Type as String

        String j_username = sheet.getRow(i).getCell(0).getStringCellValue();
        String j_password = sheet.getRow(i).getCell(1).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();

score 0 · Answer 13 · edited Jun 23 '16 at 12:05

This will work:

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++){

            HSSFCell j_username = sheet.getRow(i).getCell(0)
            HSSFCell j_password = sheet.getRow(i).getCell(0)

            //Setting the Cell type as String
            j_username.setCellType(j_username.CELL_TYPE_STRING)
            j_password.setCellType(j_password.CELL_TYPE_STRING)

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


            searchbox.submit();       

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

    }

      workbook.close();
      file.close();

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

As explained [in the Apache POI javadocs, you *should not* use setCellType to get the string value, it looses formatting](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29) — Gagravarr, Jun 23 '16 at 11:42

score 0 · Answer 14 · edited Jul 18 '22 at 11:31

public static List<SupplierSubmittedDataMapperModel> convertExcelToList(String NAME){

    List<SupplierSubmittedDataMapperModel> list = new ArrayList<>();
    try {
        FileInputStream file = new FileInputStream(new File(NAME));
        DataFormatter dataFormatter = new DataFormatter();
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(2);
        
        int rowNumber = 2;
          Iterator<Row> iterator = sheet.iterator();
          while(iterator.hasNext()) {
              Row row=iterator.next();
              
              
              Iterator<Cell> cells = row.iterator();
              int cid = 0;
              SupplierSubmittedDataMapperModel sp = new SupplierSubmittedDataMapperModel();
             
              while(cells.hasNext()) {
                  Cell cell = cells.next();
                  String cellValue = dataFormatter.formatCellValue(cell.getRow().getCell(cid));
                  
                  switch(cid) {
                  case 0:
                      sp.setSectorType(cellValue);
                      break;
                  case 1:
                      sp.setPlatformFamily(cellValue);
                      break;
                  case 2:
                      sp.setT1Supplier(cellValue);
                      break;
                  case 3:
                      sp.setT2Supplier(cellValue);
                      break;
                  case 4:
                      sp.setDistribotor(cellValue);
                      break;
                  case 5:
                      sp.setT2PartCodeAtT1(cellValue);
                      break;
                  case 6:
                      sp.setT2PartNumber(cellValue);
                      break;
                  case 7:
                      sp.setLoA(cellValue);
                      break;
                  case 8:
                      sp.setMultiSource(cellValue);
                      break;
                  case 9:
                      sp.setQtyUsage(cellValue);
                      break;
                  case 10:
                      sp.setDataType(cellValue);
                      break;
                  case 11:
                      sp.setWeek22(cellValue);
                      break;
                  case 12:
                      sp.setWeek23(cellValue);
                      break;
                  case 13:
                      sp.setWeek23(cellValue);
                      break;
                  case 14:
                      sp.setWeek24(cellValue);
                      break;
                  case 15:
                      sp.setWeek25(cellValue);
                      break;
                  case 16:
                      sp.setWeek26(cellValue);
                      break;
                  case 17:
                      sp.setWeek27(cellValue);
                      break;
                  case 18:
                      sp.setWeek28(cellValue);
                      break;
                  case 19:
                      sp.setWeek29(cellValue);
                      break;
                  case 20:
                      sp.setWeek30(cellValue);
                      break;
                  case 21:
                      sp.setWeek31(cellValue);
                      break;
                  case 22:
                      sp.setWeek32(cellValue);
                      break;
                  case 23:
                      sp.setWeek33(cellValue);
                      break;
                  case 24:
                      sp.setWeek34(cellValue);
                      break;
                  case 25:
                      sp.setWeek35(cellValue);
                      break;
                  case 26:
                      sp.setWeek36(cellValue);
                      break;
                  case 27:
                      sp.setWeek37(cellValue);
                      break;
                  case 28:
                      sp.setWeek38(cellValue);
                      break;
                  case 29:
                      sp.setWeek39(cellValue);
                      break;
                  case 30:
                      sp.setWeek40(cellValue);
                      break;
                  case 31:
                      sp.setWeek41(cellValue);
                      break;
                  case 32:
                      sp.setWeek42(cellValue);
                      break;
                  case 33:
                      sp.setWeek43(cellValue);
                      break;
                  case 34:
                      sp.setWeek44(cellValue);
                      break;
                  case 35:
                      sp.setWeek45(cellValue);
                      break;
                  case 36:
                      sp.setWeek46(cellValue);
                      break;
                  case 37:
                      sp.setWeek47(cellValue);
                      break;
                  case 38:
                      sp.setWeek48(cellValue);
                      break;
                  case 39:
                      sp.setWeek49(cellValue);
                      break;
                  case 40:
                      sp.setWeek50(cellValue);
                      break;
                  case 41:
                      sp.setWeek51(cellValue);
                      break;
                  case 42:
                      sp.setWeek52(cellValue);
                      break;
                  case 43:
                      sp.setYear2023(cellValue);
                      break;
                  case 44:
                      sp.setYear2024(cellValue);
                      break;
                  case 45:
                      sp.setYear2059(cellValue);
                      break;
                  default :
                      break;
                      
                  
                  }
                  
                  cid++;
                  
              }
              list.add(sp);
             System.out.println(sp);
               }
          
          workbook.close();
        }catch(Exception e) {
        
        e.printStackTrace();
        }   
    return list;

}

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

14 Answers14

Linked