Data Driven Framework in Selenium Web.Driver Using Apache POI Software Testing Help.How to work on Data Driven Framework in Selenium Using Apache POIData Driven Framework is one of the popular Automation Testing Framework in the current market.Data Driven automated testing is a method in which the test data set is created in the excel sheet, and is then imported into automation testing tools to feed to the software under test.Selenium Webdriver is a great tool to automate web based applications.But it does not support read and write operations on excel files.Therefore, we use third party APIs like Apache POI.What you will learn in this tutorial What is data driven framework in Selenium Web.Driver using excel example.How to read and write data from excel sheet in Selenium Web.Driver using Apache POIWhat is Apache POI Apache POI Poor Obfuscation Implementation is an API written in Java to support read and write operations modifying office files.This is the most common API used for Selenium data driven tests.There are several ways to implement a Data Driven Framework, and each differs in the effort required to develop the framework and maintenance.Developing Data Driven framework in Selenium using POI helps reduce maintenance, improve test coverage thus providing a good return on investment.Recommended reads Why data drive tests Often there might be may be a number of data sets that have to be used to test a feature of an application.Now running the same test with different data manually is time consuming, error prone and a boring task.Let us understand this scenario with an example.Suppose we need to test the loginRegister Any form with multiple input fields with 1.To test this you have three different approaches 1 Create 1.Change the data in the script and execute it multiple times.Import the data from the excel sheet and execute the script multiple times with different data.Java Read Write Excel file in Java with Apache POI.This tutorial shows how to read write excel spreadsheet using Apache POI library.First two scenarios are laborious, time consuming implying low ROI.Hence, we must follow the third approach.In the third approach, are implementing the Data Driven framework, where all our data resides in an excel sheet, where it is imported from and used to test the features of the application.Want to learn more about Data Driven Framework We have a detailed article you can check here.Some useful POI classes.Apache POI main classes usually start with either HSSF, XSSF or SXSSF.HSSF is the POI Projects pure Java implementation of the Excel.Data Driven testing in Selenium using Apache POI, you will learn how to read and write data from excel sheet in Selenium WebDriver using Apache POI.For OOXML to work you need the POIOOXML jar which is separately packaged from the POI jar.Download the POIOOXML jar from the following location.What do we need to implement Data Driven Framework In order to follow this approach we must have Eclipse, Test.NG properly configured.Once done, we will look at Various interfaces of Apache POI.Integration of Apache POI in the Eclipse.Read Data from the Excel Sheet.FPerz44ckXQ/VA8O14kuLRI/AAAAAAAAB3o/8Y0oas947Ok/s1600/XLS%2BSample%2BFile%2BData.png' alt='Apache Poi Xssf Jar Free Download' title='Apache Poi Xssf Jar Free Download' />Write data to the Excel Sheet.Advantages of using Apache POI with Selenium.Interface in POIOne of the most remarkable features of Apache POI is that it supports read and write operations on both.Below mentioned are some of the interfaces of POI.XSSFWorkbook Represents workbook in xlsx file.HSSFWorkbook Represents workbook in xls file.XSSFSheet Represents a sheet in XLSX file.HSSFSheet Represents a sheet in XLS file.XSSFRow Represents a row in a sheet of XLSX file.HSSFRow Represents a row in a sheet of XLS file.XSSFCell Represents a cell in a row of XLSX file.HSSFCell Represents a cell in a row of XLS file.Fields available in a cell CELLTYPEBLANK Represents a blank cell.CELLTYPEBOOLEAN Represents a Boolean cell true or false.CELLTYPEERROR Represents an error value in a cell.CELLTYPEFORMULA Represents a formula result on a cell.CELLTYPENUMERIC Represents numeric data in a cell.CELLTYPESTRING Represents string in a cell.The steps to use Selenium with Apache POILet us create an automation script to test the login process of a web based applications.Here, I have taken Linked.Inas an example. We import data from an excel sheet and then use it to log into the application and after execution, we write the result in the excel sheet.We need the following software installed on our system to carry on with the steps to execute the framework Java JDK 1.Eclipse IDETest. NGSelenium jars.Microsoft Office Open Office.Step 1Firstly, we need to configure Eclipse with Apache POI.Download jar files for Apache POI.Step 2Unzip the jar file, and add the following jars to your project and configure them.FINAL 2. 01. 40. FINAL 2.FINAL 2. 01. 40. Step 3After configuring the respective jars, create an excel sheet enter some data in it and save it as Test.Data. xlsx at your preferred location.Step 4Now let us follow the sample code to read data from the excel sheet and use it to login to linkedin.Framework. import java.File. import java. How To Fix Microsoft Windows Problems . File. Input. Stream.File. Output. Stream.IOException. import java.Time. Unit. import org.HSSFCell. import org.HSSFSheet. import org.HSSFWorkbook. import org.Cell. import org.By. import org. openqa.Web. Driver. import org.Firefox. Driver. import org.Web. Driver. Wait.Before. Test. import org.Test. author Admin.Read. Write. Excel.Web. Driver driver.Web. Driver. Wait wait.HSSFWorkbook workbook.HSSFSheet sheet. HSSFCell cell.Test. Setup. Set the path of the Firefox driver.System. set. Propertywebdriver.C Usersgeckodriver.Firefox. Driver. Web.Driver. Waitdriver,3.Wait3. 0, Time. Unit.SECONDS. public void Read.Data throws IOException.Import excel sheet.File srcnew FileC UsersAdminDesktopTest.Data. xls. Load the file.File. Input. Stream finput new File.Input. Streamsrc.Load he workbook.HSSFWorkbookfinput.Load the sheet in which data is stored.Sheet. At0. forint i1 i amp lt sheet.Last. Row. Num i.Import data for Email.Rowi. get. Cell1.Cell. TypeCell. CELLTYPESTRING.ElementBy. idlogin email.Keyscell. get. String.Cell. Value. Import data for password.Rowi. get. Cell2.Cell. TypeCell. CELLTYPESTRING.ElementBy. idlogin password.Keyscell. get. String.Cell. Value. Step 5Right click on the test case class and click on Run as Test.NG Test. Apache POI imports data from the excel sheet and uses it to log into our application.Now that we saw how to read data from the excel sheet, lets look at how to write to the sheet.Framework. import java.File. import java.File. Input. Stream.File. Output. Stream.IOException. import java.Time. Unit. import org.HSSFCell. import org.HSSFSheet. import org.HSSFWorkbook. import org.Cell. import org.By. import org. openqa.Web. Driver. import org.Firefox. Driver. import org.Web. Driver. Wait.Before. Test. import org.Test. author Admin.Read. Write. Excel.Web. Driver driver.Web. Driver. Wait wait.HSSFWorkbook workbook.HSSFSheet sheet. HSSFCell cell.Test. Setup. Set the path of the Firefox driver.System. set. Propertywebdriver.C Usersgeckodriver.Firefox. Driver. Web.Driver. Waitdriver,3.Load he workbook.HSSFWorkbookfinput.Load the sheet in which data is stored.Sheet. At0. forint i1 i amp lt sheet.Last. Row. Num i.Import data for Email.Rowi. get. Cell1.Cell. TypeCell. CELLTYPESTRING.ElementBy. idlogin email.Keyscell. get. String.Cell. Value. Import data for password.Rowi. get. Cell2.Cell. TypeCell. CELLTYPESTRING.ElementBy. idlogin password.Keyscell. get. String.Cell. Value. Write data in the excel.File. Output. Stream foutputnew File.Output. Streamsrc.Specify the message needs to be written.String message Data Imported Successfully.Create cell where data needs to be written.Rowi. create. Cell3.Cell. Valuemessage.Specify the file in which data needs to be written.File. Output. Stream file.Output new File. Output.Streamsrc. Output.Output. close. Note If you encounter any problems during this process, please check the following points.Make sure all the mentioned jars are added to the project and are properly configured.Required software is correctly installed.Proper use of an interface with respect to excel file, like HSSF for.XSSF for. xlsx. Valid row and column index is used.Excel file must be closed before execution.Proper classes used for the excel file like XSSF used for.HSSF used for. xls files.Advantages of using Data Driven Framework.Improves test coverage.Re usability of code.Less maintenance.Faster Execution.Permits better error handling.Readingwriting Excel Files in Java POI tutorial.If you are building a software for HR or finance domain, there is usually requirement for generating excel reports which are usually across management levels.Apart from reports, you can expect input data for application coming in form of excel sheets and application is expected to support it.These are many open source APIs to handle such scenarios.Apache POI is one of them and is well trusted over time.In short, you can read and write MS Excel files using Java.In addition, you can read and write MS Word and MS Power.Point files using Java.In this post, I am discussing some common activities required to do in real life application.Sections in this post Apache POI runtime dependencies.Some useful common classes.Writing an excel file.Reading an excel file.Using formulas in excel sheet.Formatting the cells.Sourcecode download.Apache POI runtime dependencies.If you are working on a maven project, you can include the POI dependency in pom.Id org. apache. Id.Id poilt artifact.Id. lt version 3.If you are not using maven, then you can download maven jar files from POI download page.Include following jar files minimum to run the sample code dom.Some useful POI classes.Apache POI main classes usually start with either HSSF, XSSF or SXSSF.HSSF is the POI Projects pure Java implementation of the Excel 9.HSSFWorkbook, HSSFSheet.XSSF is the POI Projects pure Java implementation of the Excel 2.OOXML. xlsx file format.XSSFWorkbook, XSSFSheet.SXSSF since 3. 8 beta.API compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited.SXSSFWorkbook, SXSSFSheet.SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document.Apart from above classes, Row and Cell are used to interact with a particular row and a particular cell in excel sheet.Another useful class Formula.Evaluator is used to evaluate the formula cells in excel sheet.A wide range of classes like Cell.Style, Builtin. Formats, Comparison.Operator, Conditional.Formatting. Rule, Font.Formatting, Indexed.Colors, Pattern. Formatting, Sheet.Conditional. Formatting etc.We will see the usage of above classes in coming examples.Writing an excel file.I am taking this example first so that we can reuse the excel sheet created by this code to read back in next example.Writing a file using POI is very simple and involve following steps Create a workbook.Create a sheet in workbook.Create a row in sheet.Add cells in sheet.Repeat step 3 and 4 to write more data.It seems very simple, right Lets have a look at the code doing these steps.Write. Excel. Demo.String args. Blank workbook.XSSFWorkbook workbook new XSSFWorkbook.Create a blank sheet.XSSFSheet sheet workbook.SheetEmployee Data.This data needs to be written Object.Maplt String, Object data new Tree.Maplt String, Object.Object ID, NAME, LASTNAME.Object 1, Amit, Shukla.Object 2, Lokesh, Gupta.Object 3, John, Adwards.Object 4, Brian, Schultz.Iterate over data and write to sheet.Setlt String keyset data.Set. int rownum 0.String key keyset.Row row sheet. create.Rowrownum. Object obj.Arr data. getkey.Object obj obj. Arr.Cell cell row. Cellcellnum.String. Cell. ValueStringobj.Integer. cell. set.Cell. ValueIntegerobj.Write the workbook in file system.File. Output. Stream out new File.Output. Streamnew Filehowtodoinjavademo.System. out. printlnhowtodoinjavademo.Exception e. e. print.Stack. Trace. Reading an excel file.Reading an excel file is also very simple if we divide this in steps.Create workbook instance from excel sheet.Get to the desired sheet.Increment row numberiterate over all cells in a rowrepeat step 3 and 4 until all data is read.Lets see all above steps in code.I am writing the code to read the excel file created in above example.Read. Excel. Demo.String args. File.Input. Stream file new File.Input. Streamnew Filehowtodoinjavademo.Create Workbook instance holding reference to.XSSFWorkbook workbook new XSSFWorkbookfile.Get firstdesired sheet from the workbook.XSSFSheet sheet workbook.Sheet. At0. Iterate through each rows one by one.Iteratorlt Row row.Iterator sheet. Iterator.Next. Row row row.Iterator. For each row, iterate through all the columns.Iteratorlt Cell cell.Iterator row. cell.Iterator. while cell.Iterator. has. Next.Cell cell cell. Iterator.Check the cell type and format accordingly.Cell. Type. case Cell.CELLTYPENUMERIC. System.Numeric. Cell. Value t.Cell. CELLTYPESTRING.System. out. printcell.String. Cell. Value t.System. out. println.Exception e. e. print.Stack. Trace. ID NAME LASTNAME.Amit Shukla. 2. 0 Lokesh Gupta.John Adwards. 4.Brian Schultz. Using formulas in excel sheet.When working on complex excel sheets, we encounter many cells which have formula to calculate their values.These are formula cells.Apache POI has excellent support for adding formula cells and evaluating already present formula cells also.Les see one example of how to set formula cells in excel In this code, there are four cells in a row and fourth one in multiplication of all previous 3 rows.So the formula will be A222 in second row.String args. XSSFWorkbook workbook new XSSFWorkbook.XSSFSheet sheet workbook.SheetCalculate Simple Interest.Row header sheet.Row0. header. create.Cell0. set. Cell.ValuePricipal. Cell1.Cell. ValueRo. I.Cell2. set. Cell.ValueT. header. Cell3.Cell. ValueInterest P r t.Row data. Row sheet.Row1. data. Row. Cell0.Cell. Value1. 45.Row. create. Cell1.Cell. Value9. 2. Row.Cell2. Cell. Value3d.Row. create. Cell3.Cell. FormulaA222.File. Output. Stream out new File.Output. Streamnew Fileformula.Demo. xlsx. workbook.System. out. printlnExcel with foumula cells written successfully.File. Not. Found.Exception e. e. Stack.Trace. catch IOException e.Stack. Trace. Similarly, I you want to read a file which have formula cells in it, use following logic to evaluate the formula cells.Sheet. With. Formula.File. Input. Stream file new File.Input. Streamnew Fileformula.Demo. xlsx. Create Workbook instance holding reference to.XSSFWorkbook workbook new XSSFWorkbookfile.Formula. Evaluator evaluator workbook.Creation. Helper.Formula. Evaluator.Get firstdesired sheet from the workbook.XSSFSheet sheet workbook.Sheet. At0. Iterate through each rows one by one.Iteratorlt Row row.Iterator sheet. Iterator.Next. Row row row.Iterator. For each row, iterate through all the columns.Iteratorlt Cell cell.Iterator row. cell.Iterator. while cell.Iterator. has. Next.Cell cell cell. Iterator.Check the cell type after eveluating formulae.If it is formula cell, it will be evaluated otherwise no change will happen.In. Cellcell. get.Cell. Type. case Cell.CELLTYPENUMERIC. System.Numeric. Cell. Value tt.Cell. CELLTYPESTRING.System. out. printcell.String. Cell. Value tt.Cell. CELLTYPEFORMULA.Not again. System.Exception e. e. print.Stack. Trace. Pricipal Ro.I T Interest P r t.Formatting the cells.So for we have seen the examples of reading writing and excel file using apache POI.But, when we are creating a report in excel file and it becomes utmost important to add formatting on cells which fit into any per determined criteria.This formatting can be a different coloring based on certain value range, based on expiry date limit etc.In below examples, I am taking couple of such formatting examples for various purposes.Cell value is in between a certain range.This piece of code will color any cell in range whose value is between a configured range.On. ValueSheet sheet.Creating some random values.Row0. create. Cell0.Cell. Value8. 4. Row1.Cell0. Cell. Value7.Row2. create. Cell0.Cell. Value5. 0. Row3.Cell0. Cell. Value5.Row4. create. Cell0.Cell. Value4. 9. Row5.Cell0. Cell. Value4.Sheet. Conditional.Formatting sheet.CF sheet. get. Sheet.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |