When we want to implement data driven testing using Excel, you need to understand how to read from an Excel sheet. You can read an Excel sheet by using Apache POI.
You need to use the below jars for reading from an excel file.
An example excel file(either .xslx or .xls formats) is given below:
The below self explanatory class is used for reading any cell of an excel sheet.
//Keywords class DataDrivenExcel.java
//Usage in the TestNG test class DataDrivenTest.java
Important Note:
In the above test class we used
The output after executing the tests looks like below:
TestNG Console
Console output
Note: While you are running the tests if you encounter any problem recheck about the below:
You need to use the below jars for reading from an excel file.
- poi-3.7-20101029.jar
- poi-ooxml-3.7-20101029.jar
- poi-ooxml-schemas-3.7-20101029.jar
- xmlbeans-2.3.0.jar
- dom4j-1.6.1.jar
An example excel file(either .xslx or .xls formats) is given below:
The below self explanatory class is used for reading any cell of an excel sheet.
//Keywords class DataDrivenExcel.java
package example.aks.data.util; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * This class allows us the use of an Excel spreadsheet to provide input data to * a test or set of tests. * * @author A. K. Sahu * */ public class DataDrivenExcel { private Workbook wb; private Sheet ws; /** * Opens a excel sheet * * @param fileName * name of the file where you want data * @param sheetName * name of the sheet in the excel file */ public DataDrivenExcel(String fileName, String sheetName) { try { if (fileName.indexOf("xlsx") < 0) { //for .xls format wb = new HSSFWorkbook(new FileInputStream(new File(fileName))); ws = wb.getSheet(sheetName); } else { //for .xlsx format wb = new XSSFWorkbook(fileName); ws = (XSSFSheet) wb.getSheet(sheetName); } } catch (IOException io) { System.err.println("Invalid file '" + fileName + "' or incorrect sheet '" + sheetName + "', enter a valid one"); } } /** * Gets a cell value from the opened sheet * * @param rowIndex * starting with 0 index * @param columnIndex * starting with 0 index * @return */ public String getCell(int rowIndex, int columnIndex) { Cell cell = null; try { cell = ws.getRow(rowIndex).getCell(columnIndex); } catch (Exception e) { System.err.println("The cell with row '" + rowIndex + "' and column '" + columnIndex + "' doesn't exist in the sheet"); } return new DataFormatter().formatCellValue(cell); } }The test class that uses the above keywords of the DataDrivenExcel class looks like below which is also self explanatory.
//Usage in the TestNG test class DataDrivenTest.java
package example.aks.data.test; import java.util.ArrayList; import org.openqa.selenium.WebDriver; import org.openqa.selenium.firefox.FirefoxDriver; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; import example.aks.data.util.DataDrivenExcel; /** * This class explains the usage of data driven excel * * @author A. K. Sahu * */ public class DataDrivenTest { @DataProvider(name = "loginToAppWithAllRoles") public Object[][] getLoginDataForAllRoles() throws Exception { DataDrivenExcel userData = new DataDrivenExcel( "D:/workspace/DataDriven/data/SampleExcel.xlsx", "Sheet1"); ArrayList<Object> dataList = new ArrayList<Object>(); int i = 1;// excluding header row int totalRows = 6; while (i < totalRows) { System.out.println("loginToAppWithAllRoles : line : " + i); Object[] dataLine = new Object[4]; dataLine[0] = userData.getCell(i, 0); dataLine[1] = userData.getCell(i, 1); dataLine[2] = userData.getCell(i, 2); dataLine[3] = userData.getCell(i, 3); dataList.add(dataLine); i++; } Object[][] data = new Object[dataList.size()][]; for (i = 0; i < dataList.size(); i++) data[i] = dataList.get(i); return data; } @Test(dataProvider = "loginToAppWithAllRoles", description = "Login with different roles") public void testLogin(String userID, String username, String password, String role) { WebDriver driver = new FirefoxDriver(); driver.get("http://yoursiteurl.com"); // Do required actions using the input data, i am just printing here System.out.println("userID:" + userID); System.out.println("username:" + username); System.out.println("password:" + password); System.out.println("role:" + role); driver.quit(); } }Similarly, you can use an excel file with .xls format.
Important Note:
In the above test class we used
Object[] dataLine = new Object[4];Number here should match with the number of parameters for the test case that using this dataprovider.
The output after executing the tests looks like below:
TestNG Console
Console output
Note: While you are running the tests if you encounter any problem recheck about the below:
- Make sure all the above mentioned jars are added to your build path.
- The object size defined in the data provider is equal to the number of parameters defined in the test method.
- You are reading an existing cell value with a valid row and column index.
how to get string and numerical value in excel For Example-->
ReplyDeleteIn Excel like--> mani, 0001 , mani123
You can do something like below but make sure the cell value is of the type(by some condition check for evaluating its type) when you are using the getXXXX() functions:
DeleteCell cell = ws.getRow(rowIndex).getCell(columnIndex);
cell.getBooleanCellValue();// for boolean cell value
cell.getNumericCellValue();// for numeric cell value
cell.getStringCellValue();// for string cell value
Hello,
DeleteNow you can get data from excel cell without custom formatting. There is a formatter class available in Apache POI.
Example,
Cell cell = ws.getRow(rowIndex).getCell(columnIndex);
new DataFormatter().formatCellValue(cell);// get the data here
I have updated my example in the post too, you can have a look.
Thanks!
Hi aswini kumar
Deletethis mahesh ineed the code of TESTNG frame work for multiple data using data provider
suppose we want open the facebook or any other site using chrome browser but not mention eclipse we want to take the data from excel sheet only please aswini
ex:In data driven frame where we wrote the code with out parameters i nedd with parameter code please sir
Hi, I need some help, I want to read class name from excel sheet which need to be run and only run that class,What i want is i want to copy this class name which needs to be run in testNg.xml file. can you tell how to achieve that ? archanasingh82@ymail.com
ReplyDeleteHi ,Nice explanation and i have implemented with the help of your code and help of this site code
ReplyDeletehttp://www.wikishown.com/selenium-tutorials/data-driven-framework-with-selenium/ but receiving some error in code
data[i] = dataList.get(i);
ReplyDeletegives error in line and when click on the error it give--add cast to 'object[]'....????
Hello,
DeleteMay be you are not using the same jars as I mentioned.
Looks like with the latest jars the type casting is required.
Try like below, it should work
Object[][] data = new Object[dataList.size()][];
for (int i = 0; i < dataList.size(); i++)
{
data[i] = (Object[]) dataList.get(i);
}
Thanks!
very informative. login for excellence also provide Python Training in velachery with the help of experienced professionals.
ReplyDeleteVery informative.
ReplyDeletePYTHON Training in Chennai