When we want to create follow data driven approach and create a framework then best way is to use Excel to store our data and we can pass the Data as parameters using Data providers which we have seen in our previous post.
Step by step process to Implement Excel with TestNg Data Provider.
Step 1: Create a test case of Login Application with TestNG Data Provider.
Step 2: Create a Test Data sheet.
Step 3: Create functions to Open & Read data from Excel
Step 4: Create a TestNg test case for accepting data from Excel using Data Provider.
Step 5: Run the test against the Test Case name in the Test Data file.
Read Data From Excel Sheet using Apache POI library -
Below is our code to open Excel sheet and read data from it within our Selenium test script. For this purpose, we are using the Apache POI library, which allows us to read, create and edit Microsoft Office-documents using Java. The classes and methods we are going to use to read data from Excel sheet are located in the org.apache.poi.hssf.usermodel.
1. Download the Apache POI from the link http://poi.apache.org/ and add it in your project build path.
2. Use Below Code to read Data from Excel file.
3. Create excelfile with Data which we want to pass to our test case as parameter.
4. Update the Test case for using parameters from excel.
Step by step process to Implement Excel with TestNg Data Provider.
Step 1: Create a test case of Login Application with TestNG Data Provider.
Step 2: Create a Test Data sheet.
Step 3: Create functions to Open & Read data from Excel
Step 4: Create a TestNg test case for accepting data from Excel using Data Provider.
Step 5: Run the test against the Test Case name in the Test Data file.
Read Data From Excel Sheet using Apache POI library -
Below is our code to open Excel sheet and read data from it within our Selenium test script. For this purpose, we are using the Apache POI library, which allows us to read, create and edit Microsoft Office-documents using Java. The classes and methods we are going to use to read data from Excel sheet are located in the org.apache.poi.hssf.usermodel.
1. Download the Apache POI from the link http://poi.apache.org/ and add it in your project build path.
2. Use Below Code to read Data from Excel file.
package test; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtils { private static XSSFSheet ExcelWSheet; private static XSSFWorkbook ExcelWBook; private static XSSFCell Cell; private static XSSFRow Row; public static void setExcelFile(String Path,String SheetName) throws Exception { try { // Open the Excel file FileInputStream ExcelFile = new FileInputStream(Path); // Access the required test data sheet ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); } catch (Exception e){ throw (e); } } public static Object[][] getTableArray(String FilePath, String SheetName) throws Exception { String[][] tabArray = null; try { FileInputStream ExcelFile = new FileInputStream(FilePath); // Access the required test data sheet ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); int startRow = 1; int startCol = 1; int ci, cj; int totalRows = ExcelWSheet.getLastRowNum(); // you can write a function as well to get Column count int totalCols = 2; tabArray = new String[totalRows][totalCols]; ci = 0; for (int i = startRow; i <= totalRows; i++, ci++) { cj = 0; for (int j = startCol; j <= totalCols; j++, cj++) { tabArray[ci][cj] = getCellData(i, j); System.out.println(tabArray[ci][cj]); } } } catch (FileNotFoundException e) { System.out.println("Could not read the Excel sheet"); e.printStackTrace(); } catch (IOException e) { System.out.println("Could not read the Excel sheet"); e.printStackTrace(); } return (tabArray); } public static String getCellData(int RowNum, int ColNum) throws Exception { try{ Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); int dataType = Cell.getCellType(); if (dataType == 3) { return ""; }else{ String CellData = Cell.getStringCellValue(); return CellData; } }catch (Exception e){ System.out.println(e.getMessage()); throw (e); } } public static String getTestCaseName(String sTestCase)throws Exception{ String value = sTestCase; try{ int posi = value.indexOf("@"); value = value.substring(0, posi); posi = value.lastIndexOf("."); value = value.substring(posi + 1); return value; }catch (Exception e){ throw (e); } } public static int getRowContains(String sTestCaseName, int colNum) throws Exception{ int i; try { int rowCount = ExcelUtils.getRowUsed(); for ( i=0 ; i<rowCount; i++){ if (ExcelUtils.getCellData(i,colNum).equalsIgnoreCase(sTestCaseName)){ break; } } return i; }catch (Exception e){ throw(e); } } public static int getRowUsed() throws Exception { try{ int RowCount = ExcelWSheet.getLastRowNum(); return RowCount; }catch (Exception e){ System.out.println(e.getMessage()); throw (e); } } public static Object[][] getTableArray(String FilePath, String SheetName, int iTestCaseRow) throws Exception { String[][] tabArray = null; try{ FileInputStream ExcelFile = new FileInputStream(FilePath); // Access the required test data sheet ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); int startCol = 1; int ci=0,cj=0; int totalRows = 1; int totalCols = 1; tabArray=new String[totalRows][totalCols]; for (int j=startCol;j<=totalCols;j++, cj++) { tabArray[ci][cj]=getCellData(iTestCaseRow,j); System.out.println(tabArray[ci][cj]); } } catch (FileNotFoundException e) { System.out.println("Could not read the Excel sheet"); e.printStackTrace(); } catch (IOException e) { System.out.println("Could not read the Excel sheet"); e.printStackTrace(); } return(tabArray); } }
3. Create excelfile with Data which we want to pass to our test case as parameter.
4. Update the Test case for using parameters from excel.
package test; import java.util.concurrent.TimeUnit; import org.openqa.selenium.By; import org.openqa.selenium.WebDriver; import org.openqa.selenium.chrome.ChromeDriver; import org.openqa.selenium.firefox.FirefoxDriver; import org.testng.annotations.Test; import org.testng.annotations.BeforeMethod; import org.testng.annotations.AfterMethod; import org.testng.annotations.DataProvider; public class TestCaseExcelData { private String sTestCaseName; private int iTestCaseRow; WebDriver driver; @BeforeMethod public void beforeMethod() throws Exception { System.setProperty("webdriver.chrome.driver", "C:\\Softwares\\chromedriver_win32\\chromedriver.exe"); driver=new ChromeDriver(); driver.get("http://www.qaautomated.com"); Thread.sleep(5000); } @Test(dataProvider = "search") public void test(String searchtext) throws Exception { driver.findElement(By.xpath("*//input[@class='search-field']")).sendKeys(searchtext); driver.findElement(By.xpath("*//input[@class='search-submit']")).click(); } @AfterMethod public void afterMethod() { driver.close(); } @DataProvider public Object[][] search() throws Exception{ // Setting up the Test Data Excel file ExcelUtils.setExcelFile("C:\\Users\\manojjai\\Documents\\dataprovider.xlsx","Sheet1"); sTestCaseName = this.toString(); // From above method we get long test case name including package and class name etc. // The below method will refine your test case name, exactly the name use have used sTestCaseName = ExcelUtils.getTestCaseName(this.toString()); // Fetching the Test Case row number from the Test Data Sheet // Getting the Test Case name to get the TestCase row from the Test Data Excel sheet iTestCaseRow = ExcelUtils.getRowContains(sTestCaseName,0); Object[][] testObjArray = ExcelUtils.getTableArray("C:\\Users\\manojjai\\Documents\\dataprovider.xlsx","Sheet1",iTestCaseRow); return (testObjArray); } }
Thanks a lot for this information. I loves to read your blog. Keep it up
ReplyDeleteBy the way, visit the new post at my blog Best books for iit jee preparation to get free ebooks in reference of academic engineering field and highly competitive engineering exams.
Regards,
Sachin
myTectra Placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance
ReplyDeleteI really enjoy reading of your article. I wanted to inform you that you have people like me who appreciate your work. Definitely a great post I would like to read this.
ReplyDeleteappvn
tutuapp apk ios
Great information
ReplyDeletesalesforce training in Marathahalli
salesforce admin training in Marathahalli
salesforce developer training in Marathahalli
Thanks for sharing this information
ReplyDeleteselenium training centers in Bangalore
best software testing training institutes in Bangalore with placements
automation testing courses in Bangalore
selenium testing course in Bangalore
software testing institutes in Bangalore
selenium training in Bangalore
best selenium training in Bangalore
selenium course in Bangalore
Nice post..
ReplyDeleteDOT NET training in btm
dot net training institute in btm
dot net course in btm
best dot net training institute in btm
Nice blog
ReplyDeleteselenium training centers in Marathahalli
best software testing training institutes in Marathahalli with placements
automation testing courses in Marathahalli
selenium testing course in Marathahalli
software testing institutes in Marathahalli
selenium training in Marathahalli
best selenium training in Marathahalli
selenium course in Marathahalli
Get the Best Selenium Training in Bangalore from TIB Academy with 100% Placement Assistance
ReplyDeleteI read Many Post about Excel and Other Courses but I really Impressed about your Writing Way and How to Express to words.. It’s really helpful for us Thanks for sharing,, keep writing
ReplyDeleteAdvanced excel training in delhi
Advanced excel training in Noida
Advanced excel training in Gurgaon
There is lots of Writer but your writing way is so good and different. It’s really helpful for us and knowledgeable so thanks for sharing...
ReplyDeleteAdvanced Excel Training in Delhi
Advanced Excel Training in Noida
Advanced Excel Training in Gurgaon
There is lots of Writer but your writing way is so good and different. It’s really helpful for us and knowledgeable so thanks for sharing...
ReplyDeleteAdvanced Excel Training in Delhi
Advanced Excel Training in Noida
Advanced Excel Training in Gurgaon
ReplyDeleteYou have been delivered this post is very interesting and very presentable. Thank you and keep posting...!!!
Pega Training in Chennai
Pega Course
Primavera Training in Chennai
Tableau Training in Chennai
Unix Training in Chennai
Placement in Chennai
Linux Training in Chennai
Spark Training in Chennai
Pega Training in T Nagar
Pega Training in Anna Nagar
Thanks for the info. Its very useful.
ReplyDeleteI have a question here as I see you used class name as a test case name in your excel.
sTestCaseName = this.toString();
But i want to use actual test case method name(ex: "test" is the testcase name in your example.)I may have 20 test cases in single class and each test case has different set of data. Please let me know if you have answer for this
Wonderful blog!!! Thanks for sharing this great information with us...
ReplyDeleteSEO Training in Chennai
SEO Course in Chennai
SEO Training Institute in Chennai
Best seo training in chennai
SEO training in Velachery
SEO training in Adyar
Python Training in Chennai
Software testing training in chennai
JAVA Training in Chennai
I got some clear information from this blog.. Thanks for taking a time to share this blog...
ReplyDeleteAWS Training in Chennai
AWS Training in Bangalore
AWS Training in Coimbatore
AWS Training in Hyderabad
AWS Training Institutes in Bangalore
AWS Training in BTM
AWS Course in Bangalore
AWS Training Institutes in Bangalore
Spoken English Classes in Bangalore
Data Science Courses in Bangalore
Nice Blog, Very Informative Content,waiting for next update...
ReplyDeletecore java training in chennai
core java classes
core java training in chennai
core java Training in OMR
core java training in Porur
C C++ Training in Chennai
javascript training in chennai
Hibernate Training in Chennai
LoadRunner Training in Chennai
Mobile Testing Training in Chennai
Nice Blog...Thanks for sharing the article waiting for next update...
ReplyDeleteArtificial Intelligence Course in Chennai
best artificial intelligence training in chennai
C C++ Training in Chennai
javascript training in chennai
Html5 Training in Chennai
QTP Training in Chennai
Spring Training in Chennai
DOT NET Training in Chennai
Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point. keep it up
ReplyDeleteAi & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.
ReplyDeleteProjects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.
Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.
Excellent article for the people who need information about this course.
ReplyDeletepython real time examples
job opportunities after ccna certification
why python is better
career in machine learning
data scientist interview questions and answers