I am trying to read data from excel file and pass it to web page form using data driven framework, In my framework I am having baseclass and in which I am invoking browser instance and then destroying it by using custom methods and Further I have a seprate module named "excelutility" which contains two java files the first one is called "excelSetup" to define all methods such method to read data from cells, to find total rows and columns the class code is given below:-
public class ExcelSetup {
FileInputStream fis;
XSSFWorkbook wb;
XSSFSheet sheet;
CellType type;
XSSFCell cell;
public ExcelSetup(String excelPath, int sheetIndex){
try{
fis=new FileInputStream(excelPath);
wb= new XSSFWorkbook(fis);
sheet=wb.getSheetAt(sheetIndex);
}catch (IOException e){
System.out.println("The error message is "+e.getMessage());
System.out.println("The cause of error is "+e.getCause());
e.getStackTrace();
}
}
public int getcolumncount(){
int colcount=0;
try{
colcount=sheet.getRow(0).getLastCellNum();
System.out.println("The total number of columns are "+colcount);
}catch (Exception e){
System.out.println("The error message is "+e.getMessage());
System.out.println("The cause of error is "+e.getCause());
e.getStackTrace();
}
return colcount;
}
public int getRowcount() {
int rowCount=0;
try {
rowCount=sheet.getPhysicalNumberOfRows();
System.out.println("The total number of rows are "+rowCount);
}catch (Exception e){
System.out.println("The error message is "+e.getMessage());
System.out.println("The cause of error is "+e.getCause());
e.getStackTrace();
}
return rowCount;
}
public String getCellData(int rowNo, int colNo){
String strdata = null;
Double data=0.0;
cell=sheet.getRow(rowNo).getCell(colNo);
type=cell.getCellType();
if (type==CellType.STRING){
strdata=cell.getStringCellValue();
}
else if (type==CellType.NUMERIC){
cell.setCellType(CellType.STRING);
strdata=cell.getStringCellValue();
}
return strdata;
}
}
The other class is named as "excelDataprovider" which is used to create and setup dataprovider which can be used to supply data in testclass
The code is given below:-
public class ExcelDataprovider {
@DataProvider(name = "namedata")
public Object[][] createnameData(){
String sheetpath=System.getProperty("user.dir")+"/configurations/data.xlsx";
Object data[][]=getData(sheetpath,0);
return data;
}
public Object[][] getData(String excelPath,int sheetindex){
ExcelSetup obj=new ExcelSetup(excelPath,sheetindex);
int rowCount=obj.getRowcount();
int colCount=obj.getcolumncount();
Object[][]data=new Object[rowCount][colCount];
for (int i = 1; i <rowCount ; i++) {
for (int j = 0; j <colCount ; j++) {
String cellData=obj.getCellData(i,j);
System.out.print(cellData+" | ");
data[i-1][j]=cellData;
}
System.out.println(" ");
}
return data;
}
}
The class where dataprovider is used is testclass whose code is given below:-
public class VerifyRegisterpage {
String title="Register: Mercury Tours";
@Test(dataProviderClass = ExcelDataprovider.class,dataProvider =
"namedata",priority=1,description="This test will submit by entering name")
public void checknamesubmit(String firstname,String lastname) throws
IOException
{
WebDriver driver=PagefactoryBase.startbrowser();
Registration register=PageFactory.initElements(driver,
Registration.class);
register.clickregisterlink();
register.checkregisterpagetitle(title);
register.checkregisterpageimage();
register.submitbyentername(firstname,lastname);
}
}
The problem is that I am able to read one set or row of data written in the excel but after reading one row It reads an empty row and supply null value to dataprovider as a result I am getting nullpointerexception.The console output is:-
The total number of rows are 2
The total number of columns are 2
srikant | chaturvedi |
Browser Instance Created
Starting ChromeDriver 2.37.544315 (730aa6a5fdba159ac9f4c1e8cbc59bf1b5ce12b7) on port 39207
Only local connections are allowed.
Dec 02, 2018 10:47:16 PM org.openqa.selenium.remote.ProtocolHandshake createSession
Chrome driver instance initialized
INFO: Detected dialect: OSS
INFO 2018-12-02 22:47:21,191 Registration.java [main] com.pageobjects.Registration:55 - Registration page title checked
INFO 2018-12-02 22:47:21,231 Registration.java [main] com.pageobjects.Registration:63 - Register page image has been validated
INFO 2018-12-02 22:47:26,961 Registration.java [main] com.pageobjects.Registration:108 - Registration URL has been validated successfully
INFO 2018-12-02 22:47:26,961 Registration.java [main] com.pageobjects.Registration:79 - submit by entering name is done
INFO 2018-12-02 22:47:28,565 Registration.java [main] com.pageobjects.Registration:55 - Registration page title checked
INFO 2018-12-02 22:47:28,605 Registration.java [main] com.pageobjects.Registration:63 - Register page image has been validated
org.openqa.selenium.WebDriverException: unknown error: keys should be a string
(Session info: chrome=70.0.3538.110)
(Driver info: chromedriver=2.37.544315 (730aa6a5fdba159ac9f4c1e8cbc59bf1b5ce12b7),platform=Windows NT 10.0.17134 x86_64) (WARNING: The server did not provide any stacktrace information)
Command duration or timeout: 0 milliseconds
Build info: version: '3.10.0', revision: '176b4a9', time: '2018-03-02T19:03:16.397Z'
System info: host: 'KESHAVPC', ip: '192.168.228.2', os.name: 'Windows 10', os.arch: 'amd64', os.version: '10.0', java.version: '1.8.0_144'
Driver info: org.openqa.selenium.chrome.ChromeDriver
Capabilities {acceptInsecureCerts: false, acceptSslCerts: false, applicationCacheEnabled: false, browserConnectionEnabled: false, browserName: chrome, chrome: {chromedriverVersion: 2.37.544315 (730aa6a5fdba15..., userDataDir: C:\Users\HP\AppData\Local\T...}, cssSelectorsEnabled: true, databaseEnabled: false, handlesAlerts: true, hasTouchScreen: false, javascriptEnabled: true, locationContextEnabled: true, mobileEmulationEnabled: false, nativeEvents: true, networkConnectionEnabled: false, pageLoadStrategy: normal, platform: XP, platformName: XP, rotatable: false, setWindowRect: true, takesHeapSnapshot: true, takesScreenshot: true, unexpectedAlertBehaviour: , unhandledPromptBehavior: , version: 70.0.3538.110, webStorageEnabled: true}
Session ID: 307437e89f8c3d905d650384f734db4c
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.openqa.selenium.remote.ErrorHandler.createThrowable(ErrorHandler.java:214)
at org.openqa.selenium.remote.ErrorHandler.throwIfResponseFailed(ErrorHandler.java:166)
at org.openqa.selenium.remote.http.JsonHttpResponseCodec.reconstructValue(JsonHttpResponseCodec.java:40)
at org.openqa.selenium.remote.http.AbstractHttpResponseCodec.decode(AbstractHttpResponseCodec.java:80)
at org.openqa.selenium.remote.http.AbstractHttpResponseCodec.decode(AbstractHttpResponseCodec.java:44)
at org.openqa.selenium.remote.HttpCommandExecutor.execute(HttpCommandExecutor.java:160)
at org.openqa.selenium.remote.service.DriverCommandExecutor.execute(DriverCommandExecutor.java:83)
at org.openqa.selenium.remote.RemoteWebDriver.execute(RemoteWebDriver.java:601)
at org.openqa.selenium.remote.RemoteWebElement.execute(RemoteWebElement.java:279)
at org.openqa.selenium.remote.RemoteWebElement.sendKeys(RemoteWebElement.java:100)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.openqa.selenium.support.pagefactory.internal.LocatingElementHandler.invoke(LocatingElementHandler.java:51)
at com.sun.proxy.$Proxy8.sendKeys(Unknown Source)
at com.pageobjects.Registration.Entername(Registration.java:116)
at com.pageobjects.Registration.submitbyentername(Registration.java:75)
at com.register.VerifyRegisterpage.checknamesubmit(VerifyRegisterpage.java:36)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:124)
at org.testng.internal.Invoker.invokeMethod(Invoker.java:580)
at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:716)
at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:988)
at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109)
at org.testng.TestRunner.privateRun(TestRunner.java:648)
at org.testng.TestRunner.run(TestRunner.java:505)
at org.testng.SuiteRunner.runTest(SuiteRunner.java:455)
at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:450)
at org.testng.SuiteRunner.privateRun(SuiteRunner.java:415)
at org.testng.SuiteRunner.run(SuiteRunner.java:364)
at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:84)
at org.testng.TestNG.runSuitesSequentially(TestNG.java:1208)
at org.testng.TestNG.runSuitesLocally(TestNG.java:1137)
at org.testng.TestNG.runSuites(TestNG.java:1049)
at org.testng.TestNG.run(TestNG.java:1017)
at org.testng.IDEARemoteTestNG.run(IDEARemoteTestNG.java:72)
at org.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:123)
===============================================
Default Suite
Total tests run: 2, Failures: 1, Skips: 0
===============================================
Please refer screenshot of project structure 
Please refer screenshot of excel sheet data 
Please refer screenshot of null data value 
