Selenium is a widely used automation testing tool for web browser testing. The Java programming language provides different classes or interfaces to perform file manipulation actions. The file manipulation action is called import and export of file as in file handling in Selenium.
File IO is a critical part of any software process. We frequently create a file, open it & update something or delete it in our computers. Same is the case with Selenium Automation. We need a process to handle files with Selenium. However, it does not support read and write operations on excel files. Therefore, we use third party APIs like Apache POI.
The Apache POI in Selenium is a widely used API for selenium data driven testing. It is a POI library written in Java that gives users an API for manipulating Microsoft documents like .xls and .xlsx. Users can easily create, modify, and read/write into excel files. POI stands for “Poor Obfuscation Implementation.”
Case study to understand the Apache POI implementation:
Sunshine Systems developed a POI based reporting solution for a price optimization software package which is used by major retail chains.
The solution allowed the retailer’s merchandise planners and managers to request a markdown decision support reports and price change reports using a standard browser The users could specify report type, report options, as well as company, division, and department filter criteria. Report generation took place in the multi-threaded application server and could support many simultaneous report requests.
The reporting application collected business information from the price optimization application’s Oracle database. The data was aggregated and summarized based upon the specific report type and filter criteria requested by the user. The final report was rendered as a Microsoft Excel spreadsheet using the POI HSSF API and was stored on the report database server for that specific user as a BLOB. Reports could be seamlessly and easily viewed using the same browser.
The retailers liked the solution because they had instantaneous access to critical business data through an extremely easy to use browser interface. They did not need to train the broader user community on all the complexities of the optimization application. Furthermore, the reports were generated in an Excel spreadsheet format, which everyone was familiar with, and which also allowed further data analysis using standard Excel features. Therefore, the use of Apache POI helped to save time and helped to do productive data use.
How to handle excel file using POI (Maven POM Dependency)
To Read and Write Excel file in Java, Apache provides a very famous library POI. This library is capable enough to read and write both XLS and XLSX file format of Excel.To read XLS files, an HSSF implementation is provided by POI library.To read XLSX, XSSF implementation of POI library will be the choice.
Let’s study these implementations in detail.
Following is a list of different Java Interfaces and classes in POI for reading XLS and XLSX file-
- Workbook: XSSFWorkbook and HSSFWorkbook classes implement this interface.
- XSSFWorkbook: Is a class representation of XLSX file.
- HSSFWorkbook: Is a class representation of XLS file.
- Sheet: XSSFSheet and HSSFSheet classes implement this interface.
- XSSFSheet: Is a class representing a sheet in an XLSX file.
- HSSFSheet: Is a class representing a sheet in an XLS file.
- Row: XSSFRow and HSSFRow classes implement this interface.
- XSSFRow: Is a class representing a row in the sheet of XLSX file.
- HSSFRow: Is a class representing a row in the sheet of XLS file.
- Cell: XSSFCell and HSSFCell classes implement this interface.
- XSSFCell: Is a class representing a cell in a row of XLSX file.
- HSSFCell: Is a class representing a cell in a row of XLS file.
How to write in excel using Apache POI
We can refer to the below flow diagram for write in excel using Apache POI:
Conclusion:
Input/Output from and to a file is a very critical part of software testing process. Apache POI plays a vital role in making this possible for Selenium Test Automation.
Selenium integrated with Apache POI facilitates you to run your script multiple times with different data sets, with all data maintained at a single location. It saves time and maintenance effort on the test script.
