Xls is null | Selenium Forum
C
Cheong Kah Seng Posted on 16/11/2020

DummyTestC can get data from xls file but DummyTestB cannot get data from xls files (NullPointerException). Hope to receive your reply soon. Thanks

Error Code: 

[RemoteTestNG] detected TestNG version 7.0.0
Test starts from row - 7
Total rows are - 2
Total cols are - 5
log4j:WARN No appenders could be found for logger (freemarker.cache).
log4j:WARN Please initialize the log4j system properly.
FAILED: testB({Col4=B4, Col3=B3, Col2=B2, Col1=B1, Runmode=Y})
java.lang.NullPointerException: Cannot invoke "com.qtpselenium.core.ddf.util.Xls_Reader.getRowCount(String)" because "xls" is null
at com.qtpselenium.core.ddf.util.DataUtil.isRunnable(DataUtil.java:56)
at com.qtpselenium.core.ddf.testcases.DummyTestB.testB(DummyTestB.java:36)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:133)
at org.testng.internal.TestInvoker.invokeMethod(TestInvoker.java:584)
at org.testng.internal.TestInvoker.invokeTestMethod(TestInvoker.java:172)
at org.testng.internal.MethodRunner.runInSequence(MethodRunner.java:46)
at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:804)
at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:145)
at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
at org.testng.TestRunner.privateRun(TestRunner.java:770)
at org.testng.TestRunner.run(TestRunner.java:591)
at org.testng.SuiteRunner.runTest(SuiteRunner.java:402)
at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:396)
at org.testng.SuiteRunner.privateRun(SuiteRunner.java:355)
at org.testng.SuiteRunner.run(SuiteRunner.java:304)
at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
at org.testng.TestNG.runSuitesSequentially(TestNG.java:1180)
at org.testng.TestNG.runSuitesLocally(TestNG.java:1102)
at org.testng.TestNG.runSuites(TestNG.java:1032)
at org.testng.TestNG.run(TestNG.java:1000)
at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)

FAILED: testB({Col4=B8, Col3=B7, Col2=B6, Col1=B5, Runmode=N})
java.lang.NullPointerException: Cannot invoke "com.qtpselenium.core.ddf.util.Xls_Reader.getRowCount(String)" because "xls" is null
at com.qtpselenium.core.ddf.util.DataUtil.isRunnable(DataUtil.java:56)
at com.qtpselenium.core.ddf.testcases.DummyTestB.testB(DummyTestB.java:36)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:133)
at org.testng.internal.TestInvoker.invokeMethod(TestInvoker.java:584)
at org.testng.internal.TestInvoker.invokeTestMethod(TestInvoker.java:172)
at org.testng.internal.MethodRunner.runInSequence(MethodRunner.java:46)
at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:804)
at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:145)
at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
at org.testng.TestRunner.privateRun(TestRunner.java:770)
at org.testng.TestRunner.run(TestRunner.java:591)
at org.testng.SuiteRunner.runTest(SuiteRunner.java:402)
at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:396)
at org.testng.SuiteRunner.privateRun(SuiteRunner.java:355)
at org.testng.SuiteRunner.run(SuiteRunner.java:304)
at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
at org.testng.TestNG.runSuitesSequentially(TestNG.java:1180)
at org.testng.TestNG.runSuitesLocally(TestNG.java:1102)
at org.testng.TestNG.runSuites(TestNG.java:1032)
at org.testng.TestNG.run(TestNG.java:1000)
at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)


===============================================
Default test
Tests run: 2, Failures: 2, Skips: 0
===============================================


===============================================
Default suite
Total tests run: 2, Passes: 0, Failures: 2, Skips: 0
===============================================

DummyTestB.java: 

package com.qtpselenium.core.ddf.testcases;

import java.io.IOException;
import java.util.Hashtable;

import org.testng.SkipException;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import org.testng.asserts.SoftAssert;

import com.qtpselenium.core.ddf.base.BaseTest;
import com.qtpselenium.core.ddf.util.DataUtil;
import com.qtpselenium.core.ddf.util.ExtentManager;
import com.qtpselenium.core.ddf.util.Xls_Reader;
import com.relevantcodes.extentreports.ExtentReports;
import com.relevantcodes.extentreports.ExtentTest;
import com.relevantcodes.extentreports.LogStatus;


public class DummyTestB extends BaseTest{
String testCaseName="TestB";

SoftAssert softAssert;
Xls_Reader xls;

@Test(dataProvider="getData")
public void testB(Hashtable<String, String> data) throws IOException, InterruptedException {

test = rep.startTest("DummyTestB");
test.log(LogStatus.INFO, "Starting the test B");
test.log(LogStatus.INFO, data.toString());

//add datautil.runnable
System.out.println(DataUtil.isRunnable(testCaseName, xls)); //Note: Failed to get data from xls file


 if(!DataUtil.isRunnable(testCaseName, xls) || data.get("Runmode").equals("N")) { //Error comes when "!DataUtil.isRunnable(testCaseName, xls)" was added
 test.log(LogStatus.SKIP, "Skipping the test as runmode is N");
 throw new SkipException("Skipping the test as run is N");
 }


openBrowser("Chrome");
test.log(LogStatus.INFO, "Opened Browser");
navigate("appurl");

//verify signin Text
/*
if(!verifyText("signintext_xpath", "signinText"))
reportFailure("Text did not match"); //non-critical failure
*/

/*
//Small Error will not stop the whole program
//All the failed soft assertions will be reported at the AfterMethod (quit())
softAssert.assertTrue(verifyText("signintext_path","signinText"), "Text did not match");
softAssert.assertTrue(false, "Err 2");
softAssert.assertTrue(true, "Err 3");
softAssert.assertTrue(false, "Err 4");
*/

//check if email field is present
if(!isElementPresent("email_xpath"))
reportFailure("Email field not present"); //Critical failure

//reportFailure("Email field not present"); 
test.log(LogStatus.INFO, "Landed on gmail login page");
takeScreenshot();
type("email_xpath", "cheongtestacc@gmail.com");
test.log(LogStatus.PASS, "Email has been entered successfully");
takeScreenshot();
click("next_button_xpath");
test.log(LogStatus.INFO, "Landed on Gmail password page");
type("passwd_xpath", "DummyGmail12345");
test.log(LogStatus.PASS, "Password has been entered successfully");
takeScreenshot();
click("pw_next_button_xpath");
test.log(LogStatus.PASS, "Logged in successfully");
Thread.sleep(2000);
takeScreenshot();




//id, name
//not hardcode things
//data from xls
verifyTitle();
//reportFailure("title does not match"); 
test.log(LogStatus.PASS, "Test B Passed");
//screenshots
takeScreenshot();

}

@BeforeMethod
public void init() {
softAssert = new SoftAssert();

}

@AfterMethod
public void quit() {

try {
softAssert.assertAll();
}catch(Error e) {
test.log(LogStatus.FAIL, e.getMessage());
}

rep.endTest(test);
rep.flush();
}

@DataProvider
public Object[][] getData() throws IOException{
super.init();
Xls_Reader xls = new Xls_Reader(prop.getProperty("xls_path"));
return DataUtil.getTestData(xls, testCaseName);

}

}

DummyTestC.java: 

package com.qtpselenium.core.ddf.testcases;

import java.io.IOException;
import java.util.Hashtable;

import org.testng.SkipException;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import com.qtpselenium.core.ddf.base.BaseTest;
import com.qtpselenium.core.ddf.util.DataUtil;
import com.qtpselenium.core.ddf.util.ExtentManager;
import com.qtpselenium.core.ddf.util.Xls_Reader;
import com.relevantcodes.extentreports.ExtentReports;
import com.relevantcodes.extentreports.ExtentTest;
import com.relevantcodes.extentreports.LogStatus;

public class DummyTestC extends BaseTest{
/**
Every test file must have these two lines, in this case, this two lines
not needed anymore after pasting this 2 lines to BaseTest.java
**/

// ExtentReports rep = ExtentManager.getInstance();
// ExtentTest test;
String testCaseName="TestC";
Xls_Reader xls;

@Test(dataProvider="getData")
public void testC(Hashtable<String, String> data) {
test=rep.startTest("DummyTestC");
System.out.println(DataUtil.isRunnable(testCaseName, xls)); //Able to print out boolean value

if(!DataUtil.isRunnable(testCaseName, xls) || data.get("Runmode").equals("N")) {
test.log(LogStatus.SKIP, "Skipping the test as runmode is N");
throw new SkipException("Skipping the test as run is N");
}

//test=rep.startTest("DummyTestC");
test.log(LogStatus.INFO, "Starting DummyTestC");
test.log(LogStatus.FAIL, "Failed DummyTestC");
test.log(LogStatus.FAIL, "Screenshot-> " + test.addScreenCapture("C:\\screenshots\\gmailSS.png"));
}


@AfterMethod
public void quit() {
rep.endTest(test);
rep.flush();
}

@DataProvider
public Object[][] getData() throws IOException{
super.init();
xls = new Xls_Reader(prop.getProperty("xls_path"));
return DataUtil.getTestData(xls, testCaseName);

}

}

DataUtil.java: 

package com.qtpselenium.core.ddf.util;

import java.util.Hashtable;

public class DataUtil {

public static Object[][] getTestData(Xls_Reader xls, String testCaseName){
String sheetName="Data";
//String testCaseName="TestB"; 
//read data for only testCaseName

int testStartRowNum=1;
while(!xls.getCellData(sheetName, 0, testStartRowNum).equals(testCaseName)) {
testStartRowNum++;
}
System.out.println("Test starts from row - " + testStartRowNum);
int colStartRowNum=testStartRowNum+1;
int dataStartRowNum=testStartRowNum+2;

//calculate rows of data
int rows=0;
while(!xls.getCellData(sheetName, 0, dataStartRowNum+rows).equals("")) {
rows++;
}
System.out.println("Total rows are - "+rows);

///calculate total cols
int cols=0;
while(!xls.getCellData(sheetName, cols, colStartRowNum).equals("")) { //cols-1 because cols start from 0, colStartRowNum -> testStartRowNum+1
cols++;
}
System.out.println("Total cols are - " + cols);

Object[][] data = new Object[rows][1];//Hashtable only got 1 column in this case
//read the data
int dataRow = 0;
Hashtable<String, String> table = null;
for(int rNum=dataStartRowNum;rNum<dataStartRowNum+rows;rNum++) {
table = new Hashtable<String, String>();
for(int cNum=0;cNum<cols;cNum++) {
String key = xls.getCellData(sheetName, cNum, colStartRowNum);
String value = xls.getCellData(sheetName, cNum, rNum);
table.put(key, value);
//0,0 0,1 0,2
//1,0 1,1
}
data[dataRow][0]=table;///put table in two dimensional object, 0 because only one colume, instead of having many column (number of rows will be the same, just the number of column become always 1)
dataRow++;
}

return data;
}

public static boolean isRunnable(String testName, Xls_Reader xls) {
String sheet = "TestCases";
int rows = xls.getRowCount(sheet);
for(int r=2; r<=rows; r++) {
String tName=xls.getCellData(sheet, "TCID", r);
if(tName.equals(testName)) {
String runmode = xls.getCellData(sheet, "Runmodes", r);
if(runmode.equals("Y"))
return true;
else
return false;
}
}
return false;
}

}