Reading empty cells in excel for selenium webdriver data driven framework

Question

I have around 5 records of data in an excel sheet in which one record has 5 values, one record has only 4 values and one record has only 2 values. My code is not able to handle the empty cells. I'm getting dataprovider mismatch exception. Can someone help me?

Excel sheet Format:

Here data under barge in column is optional so it will be empty for few records.

Java Code to Read Excel Sheet:

    public static Object[][] getTableArray(String FilePath, String SheetName) throws Exception 
{
    String[][] tabArray = null;
    try
    {
        FileInputStream ExcelFile = new FileInputStream(FilePath);
        ExcelWBook = new XSSFWorkbook(ExcelFile);
        ExcelWSheet = ExcelWBook.getSheet(SheetName);
        int startRow = 1;
        int startCol = 0;
        int ci, cj;
        row = ExcelWSheet.getRow(startRow);
        int totalRows = ExcelWSheet.getLastRowNum();
        int totalCols = row.getLastCellNum();
        tabArray = new String[totalRows][totalCols];
        ci = 0;
        for (int i = startRow; i <= totalRows; i++, ci++)
        {
            cj = 0;
            for (int j = startCol; j < totalCols; j++, cj++) 
            {
                tabArray[ci][cj] = getCellData(i, j);
            }
        }
    }catch (FileNotFoundException e) {
        System.out.println("Could not find the Excel sheet");
        e.printStackTrace();
    }catch (IOException e) {
        System.out.println("Could not read the Excel sheet");
        e.printStackTrace();
    }
    return tabArray;
}
public static String getCellData(int RowNum, int ColNum) throws Exception {
    try {
        Cell = row.getCell(ColNum, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);
        Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
        String CellData = Cell.getStringCellValue();
        return CellData;
    } catch (Exception e) {
        System.out.println(e.getMessage());
        throw e;
    }
}

Java Function to accept the parameters:

@Test(dataProvider = "WelcomeMessage", priority = 2)
public void welcomemsg(String survey,String kw, String name,  String verbiage,  String audiofile, String barge) throws Exception {
    try {
        SurveyBuilderPage sp = PageFactory.initElements(MainConfig.getDriver(), SurveyBuilderPage.class);
        sp.setWelcomeMessage(survey, kw, name, verbiage, audiofile, barge);
    }
    catch (Exception e) {
        e.printStackTrace();
        String stackTrace = new Object(){}.getClass().getEnclosingMethod().getName();
        File screenshotFile = ((TakesScreenshot)MainConfig.getDriver()).getScreenshotAs(OutputType.FILE);
        FileUtils.copyFile(screenshotFile, new File(userdir+"\\Screenshot"+stackTrace+".png"));
    }
}

So here I'm getting error as: Dataprovider mismatch exception.

Here welcomemsg function expecting 6 parameters but 6th parameter is an optional one. So I'm getting dataprovider mismatch exception. Here I need to handle this optional parameter.

Any help is much appreciated.

Thanks in advance.

Answer 1

Apparently i am getting NullpointerException in the method getCellData at the line of code Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum) when one of the row from the spreadsheet as defined in the dataProvider is left blank.

Could you try adding a try/catch block around the call Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum) to return CellData as space/blanks. You can handle it appropriately in the Test Class welcomemsg as desired.

public static String getCellData(int RowNum, int ColNum) throws Exception {
        String CellData;
        try {
            Cell = row.getCell(ColNum, org.apache.poi.ss.usermodel.Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

            try {
                Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            } catch (NullPointerException npe) {
                CellData = " ";
            }
            if (Cell == null) {
                CellData = " ";
            } else {
                CellData = Cell.getStringCellValue();
            }
            return CellData;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw e;
        }
    }