How to read null value as empty from excel in selenium java

Question

I am using Apache POI to read the excel.While reading the null value from excel, selenium script is stopped and its not continue.

@Test(priority = 1)

public void Signin() throws IOException, InterruptedException {

SoftAssert softAssert = new SoftAssert();

ExcelRead();

sheet = workbook.getSheetAt(0);

for (int i = 1; i <= sheet.getLastRowNum(); i++) {

try {

cell = getCellValue(i, 1);

String cellValue = cell.getStringCellValue();

wait.until(ExpectedConditions.elementToBeClickable(By.id("LG")));

driver.findElement(By.id("LG")).clear();

driver.findElement(By.id("LG")).sendKeys(cellValue);

cell = getCellValue(i, 2);

String cellValue1 = cell.getStringCellValue();

driver.findElement(By.id("PWD")).clear();

driver.findElement(By.id("PWD")).sendKeys(cellValue1);

driver.findElement(By.id("btnSubmit")).click();

Here

String cellValue = cell.getStringCellValue(); is not accept the values.

Answer 1

In Apache POI, you should use the switch case to read the value.

cell=row.getCell(c, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );
switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        System.out.println(cell.getRichStringCellValue().getString());
        break;
    case Cell.CELL_TYPE_NUMERIC:                                                    
        System.out.println(cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.println(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        System.out.println(cell.getCellFormula());
        break;  
    case Cell.CELL_TYPE_BLANK:
        System.out.println("The cell is blank");
        break;                                            

    default:
        System.out.println("Inside default");
}

above is how I read data from Excel with blank values.

Comments

Thank you for your input. I am using selenium webdriver and apache POI 3.17 to read from excel. But ,In this
Cell.CELL_TYPE_STRING:
Cell.CELL_TYPE_NUMERIC:  
Cell.CELL_TYPE_BOOLEAN:
Cell.CELL_TYPE_FORMULA:
Cell.CELL_TYPE_BLANK:
All are deprecated.

---

Can you try using

XSSFCell.CELL_TYPE_STRING
XSSFCell.CELL_TYPE_NUMERIC
XSSFCell.CELL_TYPE_BOOLEAN
XSSFCell.CELL_TYPE_FORMULA
XSSFCell.CELL_TYPE_BLANK

let us know your findings.

---

I am assuming that you are reading from xlsx file.

---

Yes sir.I am using POI version 3.17.

---

Sir,
XSSFCell.CELL_TYPE_STRING is also deprecated

---

okay, you can try using CellType here then.

Try rewriting the code as below.

cell=row.getCell(c, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );
switch (cell.getCellTypeEnum()) { //you should see depricated waning here. you can ignore it if it shows.
    case CellType.STRING:
        System.out.println(cell.getRichStringCellValue().getString());
        break;
    case CellType.NUMERIC:                                                    
        System.out.println(cell.getNumericCellValue());
        break;
    case CellType.BOOLEAN:
        System.out.println(cell.getBooleanCellValue());
        break;
    case CellType.FORMULA:
        System.out.println(cell.getCellFormula());
        break;  
    case CellType.BLANK:
        System.out.println("The cell is blank");
        break;                                            

    default:
        System.out.println("Inside default");
}

---

Hi, I am getting error for  cell=row.getCell(c, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );

error is c cannot be resolved to a variable. How can I solve it?