A popular open-source testing framework for web applications is Selenium. It provides a useful technique for automating web browsers and performing different testing tasks. Understanding how to read data from Excel in Selenium is a typical prerequisite for Selenium test automation. A well-liked Java API called Apache POI (Poor Obfuscation Implementation) offers support for reading and writing Microsoft Office file formats, including Excel. Check out the Selenium training online to learn more about Apache POI.
What is Apache POI?
The Apache Software Foundation created the potent Java API known as Apache POI (Poor Obfuscation Implementation). It provides help with reading, writing, and editing other Microsoft Office file formats in addition to Excel, Word, and PowerPoint file types. Java programmers may interact programmatically with several file formats using Apache POI to create, modify, and extract data from Office documents.
Apache POI is a well-liked choice for data-driven testing in automation frameworks like Selenium because its main objective is to work with Excel files. Developers may handle Excel workbooks, worksheets, rows, and cells in a variety of ways thanks to the access to a wide range of classes and interfaces that abstract away the underlying file formats that are made available to them.
Getting Started with Apache POI
Let’s get started by setting up the necessary dependencies and comprehending the fundamental ideas of Apache POI before delving into how to read data from Excel in Selenium using Apache POI.
The Apache POI dependency must first be added to our project. For use with various Office formats, Apache POI offers a variety of artifacts.
Once the dependency has been added, we can begin utilizing the classes and interfaces that Apache POI offers.
A wide range of classes and interfaces are available through Apache POI to work with Excel files. Here are a few of the most significant:
- Workbook: When reading Excel data from the Selenium Driver using Java’s Apache POI, the Workbook class, which acts as the primary entry point, is used to represent an Excel workbook. It offers ways to make, get to, and manage sheets inside the workbook. Apache POI has a variety of Workbook interface implementations for different Excel file types, including HSSFWorkbook for.xls files and XSSFWorkbook for.xlsx files.
- Sheet: Workbook worksheets are represented by the Sheet class. You can use it to access and change the data on a certain sheet. You can carry out activities using Sheets like building rows and cells, getting cell values, applying formatting, and controlling sheet-level attributes like name, visibility, and order.
- Row: The Row class represents a row on a sheet. It can be used to get access to and control the cells in the row. You can create cells, get and set cell values, format cells inside of a row, and more using the Row class. It also provides options for managing row-level properties like height and visibility.
- Cell: The Cell class represents a single cell in a row. It provides means to format cells, apply formulas to them, and access and change the values of the cells. You can manage cell-specific features like boundaries, alignment, and data validation using Cell. You can also retrieve and set cell data types, format cell values in accordance with their data type, apply styles to cells, and do a lot more.
- DataFormatter: Apache POI offers the DataFormatter class as a convenience class to assist in formatting cell values according to their data type. Dates, integers, and booleans are just a few of the different sorts of cell data that can automatically receive the proper formatting. This class comes in handy when reading cell values to maintain uniform formatting and prevent data type inconsistencies.
- FileInputStream: To read data from Excel in Selenium, the FileInputStream class of the Java I/O API is combined with Apache POI. It allows reading Excel files by giving them an input stream, which can then be transferred to the required Apache POI classes for further processing.
- FileOutputStream: Similar to FileInputStream, the FileOutputStream class is used to output data to a file. Providing the file with an output stream makes it possible to write Excel data and save it in the chosen file location using Apache POI
1. Read Data from Excel in Selenium
There are a few steps you need to take to read data from an Excel sheet using the Java Apache POI Selenium WebDriver. Here is a detailed explanation:
Open the Excel file and create an instance of the Workbook Class.
To read the Excel file from the given path, you build a FileInputStream object in this step. In order to open the workbook, you first build an instance of the Workbook class, especially the XSSFWorkbook implementation for.xlsx files. This instance of the Workbook class is passed the FileInputStream object as a parameter.
2. Get the Desired Sheet from the Workbook.
Here, you may retrieve the required sheet by giving its name to the Workbook class’s getSheet() method. The actual name of the sheet you want to read data from can be substituted for Sheet 1 in the formula.
3. Read the data by iterating over the rows and cells.
Using the iterator() function of the Sheet class, you create an iterator for the rows in the sheet in this step. A while loop is then used to iterate through each row while retrieving the Row object. You make an iterator for the row’s cells inside the row loop and use another while loop to iterate over them. You get the Cell object for every cell.
4. Process the Cell Value Based on its Data Type using the DataFormatter.
You construct a DataFormatter class instance to handle various cell value data types. Based on the data type of the cell values, the DataFormatter class aids in formatting them. To obtain the formatted cell value as a String, utilize the DataFormatter class’s formatCellValue() function.
5. Close the file and workbook input stream.
To free up system resources, you must close the workbook and the FileInputStream after reading the data.
Never forget to handle exceptions and apply the proper error management as required. To deal with IOExceptions that can arise during file operations, try-catch blocks can be added.
These instructions will help you use Apache POI in Selenium to effectively read data from an Excel file. The values of the cells can be handled in accordance with your testing needs, for example by using them as test data or checking them against expected values in your test automation scripts.
Conclusion
For use in Selenium test automation with Microsoft Office file formats, including Excel, Apache POI offers a potent Java API. Workbook, Sheet, Row, and Cell are important classes and interfaces in Apache POI that enable reading and writing data, formatting cells, and handling file structures.
Opening the workbook, gaining access to the sheet, iterating through the rows and cells, and processing the values with DataFormatter are the steps involved in reading data from Excel. Making a workbook, adding sheets, adding rows and cells, entering values, and utilizing FileOutputStream to write to a file are the steps involved in writing data in Excel.
Advanced Excel capabilities including formulas, named ranges, cell styles, hyperlinks, and embedded objects are supported by Apache POI. Data-driven testing, test data generation, report generation, and dynamic test case development are all made possible by using Apache POI in Selenium.
Apache POI’s smooth handling of Excel files improves the speed and efficacy of Selenium-based test automation. You can check out the Selenium certification course to learn more about Apache POI in Selenium.
