File handling in Selenium with Apache POI

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. 

The Apache POI, where POI stands for (Poor Obfuscation Implementation) is an API which offers a collection of Java libraries that helps us to manipulate different Microsoft files such as .xls and .xlsx.  excel sheets files. The file handling is an essential part of any software process, where various activities like read, open, and update such actions taken place in day-to-day basis.  However, Selenium does not support file handling on excels, hence there is a third-party API like Apache POI which helps to fulfill the essential file handling with Selenium.

Case study to understand the Apache POI 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. 

Let’s understand in detail with below references:

  1. Java Interfaces and classes for POI file handling:

Below mentioned different Java Interfaces and classes which facilitates to handle excel files –

  • HSSFWorkbook- This class methods are used to read/write data to Microsoft Excel file in .xls format. 
  • XSSFWorkbook- This class methods are used to read-write data to Microsoft Excel in .xls or .xlsx format. 
  • HSSFSheet – This class is used to create a new sheet in the HSSFWorkbook, ie, the older format of Excel.
  • XSSFSheet – This class is used to create a new sheet in the XSSFWorkbook.
  • HSSFRow – This displays a row in the HSSFSheet.
  • XSSFRow – This displays a row in the XSSFSheet.
  • HSSFCell – This is used to work with cells of HSSFRow.
  • XSSFCell – This is used to work with cells of XSSFRow.
Java Interfaces and classes for POI file handling:
  1. A.  Steps to be followed to read Excel file using Apache POI-
Steps to be followed to read Excel file using Apache POI-

2.  B. How to write in Excel using Apache POI

We can refer to the below flow diagram for writing in excel using Apache POI:

excel using Apache POI

Conclusion:

Input/Output from and to a file is a very critical part of the 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.

Leave a Reply

Your email address will not be published. Required fields are marked *