Read data from Excel to DataProvider in Selenium

Please use the following link to install the Katalon Studio:

Read data from excel to dataprovider in selenium will explain how we can get the data from excel sheet and will pass the same to testng dataprovider. While working with the data driven testing we need to pass so much of data to the test methods as parameters. But maintaining the data is very difficult within the method as sometimes we need to pass multiple data sets to same method as parameters to test the same functionality.

To simplify this we will use TestNG DataProvider concept. Will read multiple sets of data from excel and will place the same in dataprovider object array and will pass the same to the test method as parameters. Here, we need to remember one thing is; dataprovider array values and test method parameter values must be same.

We will take one sample program to read data from excel to dataprovider. Here to interact with the excel sheet will use Apache POI api. Will read some sample data from excel to dataprovider and will print the same.

[RituGurnani_India]

Too good . From basics to advance. Very nice

[ashwiintriivedi]

Hello Krishna Sakinala sir, it would be very helpful if you will please provide the file "ExcelApiTest".

[ankursharma]

Hello Krishna,
If we add one more coloumn "Run_Status" and set it as Y. How we will do that ?

[kaeshikerjeyasingh]

Hi Krishna,

Thanks a lot. It is really good and useful... Keep up the Good Work.

[DuongNguyen-khin]

Hello Krishna. Thanks for your share, it's very helpful.

[RaviKumar-hzcf]

gettting error- the data provider 'userData' returned an empty array or iterator, so this test is not doing anything

[Bujji]

hi Krishna,
your effords was tooo good really greate yar but can u answer my question ?
my question is what is difference between to use reading data from excel dataprovider and apage poi ???what is the difference between poi and dataprovider???

[manishmandal]

We can read data from Excel by using Apachi POI.

[saravaneekeerthi]

Sir, your video is very helpful plz could you explain click, double click, and drop down option using through this way means it's very helpful sir

[zeck]

Hi Krishna,
Very good video.
I have one problem. when I write in ExcelAPITest class 'CellTYPE' its giving me compile error, what is solution do i need to import anything?
please Reply
Thanks

[vineetnt]

Hi Krishna, In Dataprovider approach, i have noticed that we need to give same number of arguments in the function as the number of columns we have in the excel. Is there an approach in Test dataprovider in which i can only fetch data from few columns ? Suppose my excel has 10 columns and i only want to fetch data from any 3 columns, would it be possible in TestData provider ?

[sangeethaalla]

Hi Krishna,
I have copied your code and "Excelreadapi" for printing the values in console is working. But when I run the same with webdriver browser, I observed that browser is opening for 4 times and closing. none of the logic is working. could you please suggest me where I am wrong.

public class seleniumex {





public WebDriver driver;

excelapi eat=null;

String

String sheetName="userdata";






public void filluerdetails(String firstname, String lastname, String email, String phone) throws InterruptedException

{









driver.navigate().refresh();

Thread.sleep(3000);



}





public Object[][] UserformData() throws IOException

{

Object[][]data= testdata( xlfilepath, sheetName);

return data;

}



public Object[][] testdata(String xlfilepath, String sheetName) throws IOException

{

Object[][] exceldata=null;

eat=new excelapi(xlfilepath);



int

int ;

exceldata=new Object[rows-1][columns];



for(int i=1;i<rows;i++){

for(int j=0;j<columns;j++)

{

exceldata[i-1][j]=eat.getCellData(sheetName, j, i);

}

}

return exceldata;

}



@BeforeMethod

public void beforeMethod()throws Exception

{

System.setProperty("webdriver.chrome.driver",



driver=new ChromeDriver();






}

@AfterMethod

public void afterMethod() throws Exception{

driver.quit();

}

}

[akshaysharma]

Hello Krishna,

Thanks for the informative videos you have posted. I saw all the excel related videos and while accessing excel through Dataprovider, i copied your code exactly how its shown in the video. But, i am being presented with the following error- "Data provider mismatch". Could you please help me resolve this issue???

[ravik]

please provide solution Dataprovider trying to pass one parameter but method takes 3

[testcp]

hi sir give a ExcelRead data code and share your github link

[sanjayadhakal]

Hello sir, from the class"ExcelToDataProvider" the last section where we have"excelData[i-1][j] = eat.getCellData(sheetName, j, i);" i am getting error on eat.getCellData

When i hover over it says "The method getCellData(string int, int) is undefined for the type ExcelApiTest.
Can you please help

[kashif]

Hi - Can you please share the getCellData() method code?

[Bujji]

"ExcelApiTest" got a compileError .what type of jar file required to avoid this error.