How to use java to access excel with selenium webdriver for data for tests

Question

How to access excel files from selenium webdriver using Java ?

I am new to selenium Webdriver, I have a scenario where I have to provide different sets of values to the different test case.

Let, us say in a column there are 100 values first 20 set of values I have to feed to the Testcase1, and next set of 20 values to Testcase 2 and so on till Test case 5.

• Kate Paulk: What framework are you using with WebDriver? Some will allow you to define data sources, where others you'll have to manually code it.

Answer 1

There are lots of thing you need to understand and implement. 1st: are you going to use excel with extenion .xls or .xlsx. If any of them then use the Apache POI jar to read data from excel.

Apache POI majorly have 2 components

1. XSSF : which is for .xlsx extension file.
2. HSSF : which is for .xls extension file.

Now Create a new function using both object(XSSF and HSSF). Function will be like :

public List< Map<String, String>> getExcelDataHSSF(int startRowNo, int EndRowNumber){}
public List< Map<String, String>> getExcelDataXSSF(int startRowNo, int EndRowNumber){}

This function will return your data in List of Map object. Where Map's key is columnName and Value is: your test data respective to column.

Now, You need to write a function which check the extension of excel file and execute the function

if extension is .xlsx then run function getExcelDataXSSF(1,20) else getExcelDataHSSF(1,20).

After that, Go to your Test file and use TestNG dataprovider to fetch data using above function and pass to you test cases.

Answer 2

Selenium is meant to communicate with web applications. To pick data from excel using java you need to have apache poi jars in your project. If it is a maven project, then the correct artifactId in your pom.Once after the jars are successfully installed, you need to use the classes of the downloaded jars and play with the excel.

Answer 3

You need to interface with excel programmatically and pull in the data so you can then assign variables to pass to your tests.

I'm not sure on your language use, but here is the rest API for excel to get you started.

helpful links:

• https://stackoverflow.com/questions/9108394/how-to-read-excel-file-with-java-without-external-jars
• http://jexcelapi.sourceforge.net/

• chandrasekhar gandrakota: I am using TestNG Framework with Java

Answer 4

You will have to make a data driven framework in which u will pick up data for each test case from excel and store it in HashMaps. Then u can loop over the data in HashMap using for Loop and pick specified values against each Key