CODEWITHSUNDEEP
javatestng-dataprovider
Sushobhan
Sushobhan

Reputation: 89

How to get the Numneric Value from a cell in an excel sheet

Below is the my excel sheet snapshot and code. I am trying to get the values in numeric format for Dob column.

I have tried to use the line

mapData.put(sheet.getRow(0).getCell(j).toString(), sheet.getRow(i+1).getCell(j).getNumericCellValue());

However, this line is getting exception

java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell

enter image description here

Below is the code, In the third column, there is Dob. I am trying to print the values in the console. 

package Practice;

import java.io.File;
import java.io.FileInputStream;
import java.util.Map;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class DataDrivenTest 
{
    @Test(dataProvider = "TestData")
    public void runTestData(Map<Object, Object> map )
    {
        System.out.println(map.get("Dob"));
    }

    @DataProvider(name = "TestData")
    public Object[][] getDataFromExcel() throws Exception
    {
        String path= "C:\\Users\\kumar.sushobhan\\Documents\\DataDrivenExcelData.xlsx";
        File file= new File(path);
        FileInputStream fis= new FileInputStream(file);     
        XSSFWorkbook wb= new XSSFWorkbook(fis);
        XSSFSheet sheet= wb.getSheetAt(0);
        wb.close();     
        int rowCount= sheet.getLastRowNum();
        int colCount= sheet.getRow(0).getLastCellNum();     
        //Define a object array
        Object[][] obj= new Object[rowCount][1];

        //Get the data from excel sheet
        for(int i=0; i<rowCount;i++)
        {
            Map<Object, Object> mapData= new HashedMap<Object, Object>();
            for(int j=0;j<colCount;j++)
            {               
                mapData.put(sheet.getRow(0).getCell(j).toString(), sheet.getRow(i+1).getCell(j).getNumericCellValue());
            }
            obj[i][0]= mapData;
        }
        return obj;
    }
}

Upvotes: 0

Views: 173

Answers (1)

b.GHILAS
b.GHILAS

Reputation: 2313

Here is from the documentation The documentation of the getNumericCellValue

You can check the type of the cell before you get the value

for(int i=0; i<rowCount;i++)
    {
        Map<Object, Object> mapData= new HashedMap<Object, Object>();
        for(int j=0;j<colCount;j++)
        {   
            if (sheet.getRow(i+1).getCell(j).getCellType() != CellType.STRING) {
                mapData.put(sheet.getRow(0).getCell(j).toString(), sheet.getRow(i+1).getCell(j).getNumericCellValue());
            } else {
                mapData.put(sheet.getRow(0).getCell(j).toString(), Double.parseDouble(sheet.getRow(i+1).getCell(j).getStringCellValue()));
            }
        }
        obj[i][0]= mapData;
    }

Upvotes: 1

Related Questions