Cannot access numeric value of cell from a String cell

Question

I am trying to read an excel file and execute my test case accordingly, now the error I am getting is java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell Here is my implementation of the code: You might notice that I have user String.valueOf(); but to no avail.


    public static List<Map<String, String>> getTestDetails()
    {
        List<Map<String, String>> list = null;
        FileInputStream fileInputStream = null;
        try 
        {
           fileInputStream = new FileInputStream(FrameworkConstants.getExcelsheetspath()+"TestCasesToBeExecuted.xlsx");
           XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
           XSSFSheet sheet = workbook.getSheet("RunManager");
                        
           int lastRowNum = sheet.getLastRowNum();
           int lastColNum = sheet.getRow(0).getLastCellNum();
                        
           Map<String, String> map = null;
           list = new ArrayList<Map<String,String>>();
                        
           for(int i=1; i<lastRowNum; i++)
           {
                map = new HashMap<String, String>();
                for(int j=0; j<lastColNum; j++)
                {
                    String key = sheet.getRow(0).getCell(j).getStringCellValue();
                    System.out.println("Value of key:: "+key);
                    String value = String.valueOf(sheet.getRow(i).getCell(j).getStringCellValue());
                    System.out.println("Value of value:: "+value);
                    map.put(key, value);
                }
                list.add(map);
            }
        } 
        catch (FileNotFoundException e) 
        {
            e.printStackTrace();
        }
        catch (IOException e) 
        {
            e.printStackTrace();
        }
                    
        if(Objects.nonNull(fileInputStream))
        {
            try 
            {
                fileInputStream.close();
            } 
            catch (IOException e) 
            {
                e.printStackTrace();
            }
       }
       return list; 
    }

And here is my code for method call:


public List<IMethodInstance> intercept(List<IMethodInstance> methods, ITestContext context) 
{
   List<Map<String, String>> list = ExcelUtils.getTestDetails();
   List<IMethodInstance> result = new ArrayList<IMethodInstance>();
    
   for(int i=0; i<methods.size(); i++)
   {
        for(int j=0; j<list.size(); j++)
        {
            if(methods.get(i).getMethod().equals(list.get(j).get("Test Name")))
            {
                if(list.get(j).get("Execute").equalsIgnoreCase("yes"))
                {
                    methods.get(i).getMethod().setDescription(list.get(j).get("Test Description")); 

methods.get(i).getMethod().setInvocationCount(Integer.parseInt(list.get(j).get("Count")));                          methods.get(i).getMethod().setPriority(Integer.parseInt(list.get(j).get("Priority")));
                result.add(methods.get(i));
                }
            }
        }
    }
 
  return result;
}

My imports are as follows

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

poi.apache.org/components/spreadsheet/…

Axel Richter
– Axel Richter

2021-09-30 16:41:05 +00:00
Commented Sep 30, 2021 at 16:41 — Axel Richter
– Axel Richter, Commented Sep 30, 2021 at 16:41

Nandan A · Accepted Answer · 2021-09-30 16:40:02Z

2

You can have check of Cell_Type before getting the cell value.

String value = null;
if (Sheet.getRow(i).getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC) {
  value = NumberToTextConverter.toText(Sheet.getRow(i).getCell(0).getNumericCellValue());
   } else
      value = Sheet.getRow(i).getCell(0).getStringCellValue();
  }

Imports:

import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.ss.usermodel.Cell;

Maven dependencies:

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>

Remove the below import

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

edited Sep 30, 2021 at 16:40

answered Sep 30, 2021 at 15:34

Nandan A

2,9721 gold badge16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

enthusiasticCoder Over a year ago

I did import the library you mentioned, but the Cell.CELL_TYPE ... is throwing error, Cell to be specific.

enthusiasticCoder Over a year ago

Guess I'm having a bad day sir! Now "CELL_TYPE_NUMERIC cannot be resolved or is not a field"

Nandan A Over a year ago

Can you post your imports in question or here?

enthusiasticCoder Over a year ago

added in code above

PJ Fanning Over a year ago

POI 3.9 is ancient - this code will not work in newer versions of POI - the other answer by Andreea Frincu is up to date

|

Andreea Frincu · Accepted Answer · 2021-09-30 16:58:28Z

1

According to the doc https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFCell.html#getCellType--

You should be able to do:

String value = null;

    if (sheet.getRow(i).getCell(j).getCellType() == CellType.NUMERIC) {
        value=String.valueOf(sheet.getRow(i).getCell(j).getNumericCellValue());
    } else{
        value = sheet.getRow(i).getCell(j).getStringCellValue();
    }

answered Sep 30, 2021 at 16:58

Andreea Frincu

916 bronze badges

Comments

Jayanth Bala · Accepted Answer · 2021-10-08 05:02:27Z

0

In case of cell might have boolean, string, number which we don't know. i usually set the type and then get the value.

tempCell.setCellType(CellType.STRING);
tempCell.getStringCellValue();

answered Oct 8, 2021 at 5:02

Jayanth Bala

8401 gold badge6 silver badges12 bronze badges

Collectives™ on Stack Overflow

Cannot access numeric value of cell from a String cell

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related