Error : Cannot get a STRING value from a NUMERIC cell in Selenium

Question

I'm trying to import data from Excel file to automate a large amount of data but I got the error "Cannot get a STRING value from a NUMERIC cell" as some of the cells has a numeric values and others has a text value.I need to extract all data and insert it as inputs for the page fields. i got the error stating from

signin_credentials[i][2] = configuration.getData(0, i, 5);
signin_credentials[i][3] = configuration.getData(0, i, 6);

Here is my code:


import org.apache.poi.ss.usermodel.CellType;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.Assert;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class ExcelExample {
    WebDriver driver;

    @Test(dataProvider = "testdata")
    public void demoClass(String NameEN, String NameAr, String ServiceCode, String min, String max)
            throws InterruptedException {
//System.setProperty("webdriver.chrome.driver", "Path of Chrome Driver");
        driver = new ChromeDriver();
        driver.manage().window().maximize();
        driver.get("https://192.168.1.130/Account/Login");
        driver.findElement(By.name("UsernameOrEmailAddress")).sendKeys("admin");
        driver.findElement(By.name("Password")).sendKeys("P@ssw0rd");
        driver.findElement(By.id("LoginButton")).click();
        Thread.sleep(5000);
        driver.findElement(By.partialLinkText("Services")).click();
        Thread.sleep(500);
//click on service list
        driver.findElement(By.partialLinkText("Service List")).click();
        Thread.sleep(500);
//click on rotate circle
        driver.findElement(By.xpath("/html/body/section[2]/div/div[1]/div/div/div[2]/div[2]/a[4]/span/span")).click();
        Thread.sleep(2000);
//click on +
        driver.findElement(By.xpath("/html/body/section[2]/div/div[1]/div/div/div[2]/div[2]/a[3]/span")).click();
        Thread.sleep(1000);
//write on service name
        driver.findElement(By.id("Name")).sendKeys(NameEN);
        Thread.sleep(500);
//write on service name Arabic
        driver.findElement(By.id("NameAr")).sendKeys(NameAr);
        WebElement drodown = driver.findElement(
                By.xpath("//*[@id=\"ServiceCreateForm\"]/div/div/div/div[2]/div[3]/div[1]/div/div/button"));
        drodown.click();
        WebElement range = driver.findElement(
                By.xpath("//*[@id=\"ServiceCreateForm\"]/div/div/div/div[2]/div[3]/div[1]/div/div/div/ul/li[2]/a"));
        range.click();
//select min amount
        driver.findElement(By.name("MinValue")).sendKeys(min);
//select max amount

        driver.findElement(By.name("MaxValue")).sendKeys(max);
//driver.findElement(By.xpath("//*[@id=\"ServiceCreateForm\"]/div/div/div/div[2]/div[7]/div/button")).click();

    }

    @AfterMethod
    void ProgramTermination() {
//driver.quit();
    }

    @DataProvider(name = "testdata")
    public Object[][] testDataExample() {
        ReadExcelFile configuration = new ReadExcelFile("C:\\Users\\Desktop\\file.xlsx");
        int rows = configuration.getRowCount(0);
        Object[][] signin_credentials = new Object[rows][5];

        for (int i = 0; i < rows; i++) {

            signin_credentials[i][0] = configuration.getData(0, i, 3);
            signin_credentials[i][1] = configuration.getData(0, i, 4);
            signin_credentials[i][2] = configuration.getData(0, i, 5);
            signin_credentials[i][3] = configuration.getData(0, i, 6);
            
        }

        return signin_credentials;
    }
}

and here is my excel class:

package Login;

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile {
    XSSFWorkbook work_book;
    XSSFSheet sheet;

    public ReadExcelFile(String excelfilePath) {
        try {
            File s = new File(excelfilePath);
            FileInputStream stream = new FileInputStream(s);
            work_book = new XSSFWorkbook(stream);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    public String getData(int sheetnumber, int row, int column) {
        sheet = work_book.getSheetAt(sheetnumber);
        String data = sheet.getRow(row).getCell(column).getStringCellValue();

        return data;
    
    
    }

    public int getRowCount(int sheetIndex) {
        int row = work_book.getSheetAt(sheetIndex).getLastRowNum();
        row = row + 1;
        return row;
    }
}

Asjad Azeez · Accepted Answer · 2020-12-23 19:00:07Z

1

try ((xssfcell) sheet.getRow(row).getCell(column)).getRawValue() Which actually worked for me in a differnt way You dont really need to use the multiple if conditions.

answered Dec 23, 2020 at 19:00

Asjad Azeez

531 silver badge13 bronze badges

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

Comments

pburgr · Accepted Answer · 2020-12-21 07:49:44Z

0

Improved method getData(int sheetnumber, int row, int column) that returns always data as a String or null:

// add this two imports
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;

...

public String getData(int sheetnumber, int row, int column) {
    sheet = work_book.getSheetAt(sheetnumber);
    XSSFCell cell = sheet.getRow(row).getCell(column);
    String data = null;
    if (cell != null) {
         CellType cellType = cell.getCellType();
         if (cellType.toString() == "STRING") {
            data = cell.getStringCellValue();
         }
         else if (cellType.toString() == "NUMERIC") {
            data = String.valueOf(cell.getNumericCellValue());
         }
         else if (cellType.toString() == "FORMULA") {
            data = cell.getCellFormula();
         }
         else if (cellType.toString() == "BOOLEAN") {
            data = String.valueOf(cell.getBooleanCellValue());
         }
         else if (cellType.toString() == "ERROR") {
             // let data be null or assign some value like this
             data = "ERROR in sheet nr. " + sheetnumber + " row nr. " + row + " column nr. " + column;
         }
         else if (cellType.toString() == "BLANK") {
             // let data be null or assign some value like this
             data = "BLANK cell in sheet nr. " + sheetnumber + " row nr. " + row + " column nr. " + column;
         }
         else {
             // let data be null or assign some value like this
             data = "UNKNOWN cell type in sheet nr. " + sheetnumber + " row nr. " + row + " column nr. " + column;
         }
    }
    return data;
}

answered Dec 21, 2020 at 7:49

pburgr

1,7781 gold badge15 silver badges30 bronze badges

3 Comments

Ahmed Selim Over a year ago

Many Thanks pburgr for your help and effort, it works excellent. if you allow me is there any way to make the login only once and insert of data recurring.

pburgr Over a year ago

I don't understand your question. In your code I don't see any connection between login and any data insertion.

pburgr Over a year ago

Please mark the answer as useful. Thanks.

Collectives™ on Stack Overflow

Error : Cannot get a STRING value from a NUMERIC cell in Selenium

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related