File Handling in Selenium with Apache POI

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. 

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)

  1. Classes and Interfaces in POI
  2. Read/Write operation

How to export Excel File in Selenium Webdriver using Aapache POI

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.

  1. Classes and Interfaces in POI

Classes and Interfaces in Apache POI

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.

2. Read/Write operation

Steps for reading excel file in java using POI API

How to write in excel using Apache POI

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

Steps to write excel file in Java using Apache POI API


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.

Leave a Reply

SOAIS - Worksoft Newsletter

To view on your browser, click here
Facebook Twitter LinkedIn
Dear Default Value,

Welcome to SOAIS Newsletter of September 2021!

Continuous Testing with Remote Execution
The speed of innovation continues to increase, driving rapid and relentless change for today’s ever-evolving IT landscapes, creating greater risk as IT and business teams scramble to ensure timely delivery. How can your organization keep pace? Test more, worry less. With Worksoft’s Connective Automation Platform, you can easily build and maintain automated tests, accelerating testing time without losing scope or volume. You can schedule and execute remote, continuous tests to intercept defects sooner and prioritize remediation - without sacrificing your nights and weekends. Explore how continuous test automation and remote execution can empower your organization.

Click here to connect with us to get more information on our services.

Skip Costly Rework with Dynamic Change Resiliency​

Change resiliency is imperative in ever-evolving IT environments. Our patented object action framework streamlines change management by assigning object definitions to your shared assets. The same object may be used in a thousand automation steps, but it can be easily updated by making one simple change to the model definition. The change automatically propagates to every single instance where that object may have been used without a single line of code or manual human involvement. For more change readiness you can also engage our Impact Analysis for SAP to predict how changes in SAP transports will affect your business processes. 

Please click here to watch the video to get a gist.

SOAIS Blog – Nuts and bolts of Certify Database Maintenance​

One of the key thing, which is often missed by the organizations, who have invested in using Worksoft Certify for automating their Business Process Validation initiatives, is implementing a Database Maintenance Plan. While the business and the test automation consultants get excited about the shiny new thing that they have got and start building the regression suite; planning and executing a database maintenance plan for most of the customers gets pushed down the priority list. However, since all the test assets in Certify are stored in a Database, a robust database maintenance plan is very important to maintain smooth operation of Certify with acceptable performance criteria. The customers usually start facing issues once they have built significant number of Certify processes which they have started executing on regular basis. Such executions add a lot of data to the tables storing results data and increase the overall size of the Certify database.

Please click here to read the complete blog.

Worksoft Blog – Process Intelligence: A Multi-Dimensional Approach

The ability to extract process knowledge has become easier through the years. Technology has evolved to the point where we can deploy capabilities that connect at multiple levels to extract different types of process insight. In the past, organizations were forced to spend enormous energy extracting data manually from different applications and databases. Then, they would have to use things like spreadsheets to transform the data and convert it into meaningful information. 

Please click here and read the complete blog.
Unit 9, Level 5, Navigator, ITPL,
Bangalore - 560 066.
Phone: +91 80 40071234
Suite 101, 1979, N Mill St,
Naperville, IL 60563
Phone 1-800-262-2427
Please click here to Unsubscribe / Unsubscribe Preferences

Leave us your info