Null pointer exception while returning cell value from excel

Question

I have the following code

private void execute_TestCase() throws Exception {

    int iTotalTestCases = ExcelUtils.getRowCount(Constants.Sheet_TestCases);
    System.out.println("Total TC count" + iTotalTestCases);
    **formname=ExcelUtils.getCellData(13, 6, Constants.Sheet_TestCases);**
    System.out.println("F"+formname);

    for(int iTestcase=1;iTestcase<iTotalTestCases;iTestcase++){
        bResult = true;
        sTestCaseID = ExcelUtils.getCellData(iTestcase, Constants.Col_TestCaseID, Constants.Sheet_TestCases); 
        sRunMode = ExcelUtils.getCellData(iTestcase, Constants.Col_RunMode,Constants.Sheet_TestCases);
        if (sRunMode.equals("Yes")){
            Log.startTestCase(sTestCaseID);
            iTestStep = ExcelUtils.getRowContains(sTestCaseID, Constants.Col_TestCaseID, Constants.Sheet_TestSteps);
            iTestLastStep = ExcelUtils.getTestStepsCount(Constants.Sheet_TestSteps, sTestCaseID, iTestStep);
            bResult=true;
            System.out.println("sTestCaseID"+ sTestCaseID);
            System.out.println("iTestStep"+ iTestStep);
            System.out.println("iTestLastStep"+ iTestLastStep);
            for (;iTestStep<iTestLastStep;iTestStep++){
                sActionKeyword = ExcelUtils.getCellData(iTestStep, Constants.Col_ActionKeyword,Constants.Sheet_TestSteps);
                sPageObject = ExcelUtils.getCellData(iTestStep, Constants.Col_PageObject, Constants.Sheet_TestSteps);
                sData = ExcelUtils.getCellData(iTestStep, Constants.Col_DataSet, Constants.Sheet_TestSteps);
                execute_Actions();
                if(bResult==false){
                    ExcelUtils.setCellData(Constants.KEYWORD_FAIL,iTestcase,Constants.Col_Result,Constants.Sheet_TestCases);
                    Log.endTestCase(sTestCaseID);
                    break;
                    }                       
                }
            if(bResult==true){
            ExcelUtils.setCellData(Constants.KEYWORD_PASS,iTestcase,Constants.Col_Result,Constants.Sheet_TestCases);
            Log.endTestCase(sTestCaseID);   
                }                   
            }
        }
    }

formname=ExcelUtils.getCellData(13, 6, Constants.Sheet_TestCases). When calling the NullPointerException in getcelldata. But with same (13,6) pair it works fine at the bottom. Find below the getCellData code

 public static String getCellData(int RowNum, int ColNum, String SheetName ) throws Exception{
                try{
                    System.out.println("sheet"+SheetName);
                    ExcelWSheet = ExcelWBook.getSheet(SheetName);
                    System.out.println("Excelwsheet"+ExcelWSheet);
                    Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
                    System.out.println("test here2");
                    String CellData = Cell.getStringCellValue();
                    System.out.println("Celldata"+CellData);
                    return CellData;
                 }catch (Exception e){
                     System.out.println("Exception" + e);
                     Log.error("Class Utils | Method getCellData | Exception desc : "+e.getMessage());
                     DriverScript.bResult = true;
                     return"";
                     }
                 }

score 0 · Answer 1 · answered Feb 13 '15 at 15:38

From the Sheet.getRow(int) Apache POI javadocs

Returns - Row representing the rownumber or null if its not defined on the sheet

You therefore need to change this line with no null checking:

Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);

For something more like this, which does check + handle the row (or cell!) being null:

 Row row = ExcelWSheet.getRow(RowNum);
 if (row == null) {
    // Handle empty row
    return "";
 }
 Cell cell = row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);
 if (cell == null) {
    // Handle empty cell
    return "";
 }

You might also want to handle cells which contain things other than strings, and a few other common problems too... Have a read through the Apache POI Spreadsheet docs to learn what you need to be doing!

Null pointer exception while returning cell value from excel

1 Answers1