I am getting a null pointer exception while getting the row count from excel, though excel have values in appropriate format

Exception in thread “main” java.lang.NullPointerException
at utility.ExcelUtils.getRowCount(ExcelUtils.java:36)
at executionEngine.DriverScript.execute_TestCase(DriverScript.java:42)
at executionEngine.DriverScript.main(DriverScript.java:37)
————————————————————————————————————-
package utility;

import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import config.Constants;
public class ExcelUtils {
private static XSSFSheet ExcelWSheet;
private static XSSFWorkbook ExcelWBook;
private static XSSFCell Cell;
//private static XSSFRow Row;

public static void setExcelFile(String Path) throws Exception {
FileInputStream ExcelFile = new FileInputStream(Path);
ExcelWBook = new XSSFWorkbook(ExcelFile);
}

public static String getCellData(int RowNum, int ColNum, String SheetName ) throws Exception{
ExcelWSheet = ExcelWBook.getSheet(SheetName);
try{
Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
String CellData = Cell.getStringCellValue();
return CellData;
}catch (Exception e){
return “”;
}
}

public static int getRowCount(String SheetName)
{

ExcelWSheet = ExcelWBook.getSheet(SheetName);
int number=ExcelWSheet.getPhysicalNumberOfRows();
return number;

}

public static int getRowContains(String sTestCaseName, int colNum,String SheetName) throws Exception{
int i;
ExcelWSheet = ExcelWBook.getSheet(SheetName);
int rowCount = ExcelUtils.getRowCount(SheetName);
for (i=0 ; i<rowCount; i++){
if (ExcelUtils.getCellData(i,colNum,SheetName).equalsIgnoreCase(sTestCaseName)){
break;
}
}
return i;
}

public static int getTestStepsCount(String SheetName, String sTestCaseID, int iTestCaseStart) throws Exception{
for(int i=iTestCaseStart;i<=ExcelUtils.getRowCount(SheetName);i++){
if(!sTestCaseID.equals(ExcelUtils.getCellData(i, Constants.Col_TestCaseID, SheetName))){
int number = i;
return number;
}
}
ExcelWSheet = ExcelWBook.getSheet(SheetName);
int number=ExcelWSheet.getLastRowNum()+1;
return number;
}

}
————————————————————————————————————-
package executionEngine;

import java.lang.reflect.Method;

import config.ActionKeywords;
import config.Constants;
import utility.ExcelUtils;

public class DriverScript {

public static ActionKeywords actionKeywords;
public static String sActionKeyword;
public static String sLocator;
public static String sValue;

public static Method method[];

public static int iTestStep;
public static int iTestLastStep;
public static int iTotalBrowsers;

public static String sTestCaseID;
public static String sRunMode;
public static String sBrowserName;
public static String sBrowserRunMode;

public DriverScript() throws NoSuchMethodException, SecurityException{
actionKeywords = new ActionKeywords();
method = actionKeywords.getClass().getMethods();
}

public static void main(String[] args) throws Exception {
ExcelUtils.setExcelFile(Constants.Path_TestData);

DriverScript startEngine = new DriverScript();
startEngine.execute_TestCase();
}

private void execute_TestCase() throws Exception
{
int iTotalBrowser=ExcelUtils.getRowCount(Constants.Sheet_Browsers);
for(int iBrowser=1;iBrowser<=iTotalBrowser;iBrowser++)
{
sBrowserName=ExcelUtils.getCellData(iBrowser, Constants.Col_Browser, Constants.Sheet_Browsers);
sBrowserRunMode=ExcelUtils.getCellData(iBrowser, Constants.Col_BrowserRunMode, Constants.Sheet_Browsers);
if(sBrowserRunMode.equals(“Yes”))
{

int iTotalTestCases = ExcelUtils.getRowCount(Constants.Sheet_TestCases);
for(int iTestcase=1;iTestcase<=iTotalTestCases;iTestcase++){
sTestCaseID = ExcelUtils.getCellData(iTestcase, Constants.Col_TestCaseID, Constants.Sheet_TestCases);
sRunMode = ExcelUtils.getCellData(iTestcase, Constants.Col_RunMode,Constants.Sheet_TestCases);
if (sRunMode.equals(“Yes”)){
iTestStep = ExcelUtils.getRowContains(sTestCaseID, Constants.Col_TestCaseID, Constants.Sheet_TestSteps);
iTestLastStep = ExcelUtils.getTestStepsCount(Constants.Sheet_TestSteps, sTestCaseID, iTestStep);
for (;iTestStep<=iTestLastStep;iTestStep++){
sActionKeyword = ExcelUtils.getCellData(iTestStep, Constants.Col_ActionKeyword,Constants.Sheet_TestSteps);
sLocator = ExcelUtils.getCellData(iTestStep, Constants.Col_Locator,Constants.Sheet_TestSteps);
sValue= ExcelUtils.getCellData(iTestStep, Constants.Col_Value,Constants.Sheet_TestSteps);
execute_Actions();
}
}
}
}

}
}

private static void execute_Actions() throws Exception {

for(int i=0;i<method.length;i++){
if(method[i].getName().equals(sActionKeyword)){
method[i].invoke(actionKeywords,sLocator,sValue,sBrowserName);
break;
}
}
}
}

———————————————————————————————–

Ashwin Train Asked on April 1, 2015 in Selenium WebDriver.
Add Comment
4 Answer(s)

Ashwin,

Check for the sheet name that you are passing. It might be not correct, check for casing as well.

Virender

Virender Singh Professor Answered on April 1, 2015.
Add Comment

Hi Virender,
I  checked the sheet name as well as casing, they are perfect but still I am facing the same issue.
Could you please help?

Ashwin Train Answered on April 1, 2015.
Add Comment

Only thing I can think of right now is that you should recreate you sheet. Or deleted all the rows and recreate them.

Virender Singh Professor Answered on April 2, 2015.
Add Comment

Hi Virender,
I just created new project and used the same code and excel and it worked for me. But was not able to find the root cause for the issue.
Just to let you know my project is in pen drive, will that cause such issues?

Ashwin Train Answered on April 2, 2015.

It shouldn’t cause any problem, but I have seen that excel files sometimes cause problems. Glad that your problem is solved.

on April 2, 2015.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.