Read & Write Data from Excel in Selenium with Example : Apache POI

Read & Write Data from Excel in Selenium with Example : Apache POI

Apache POI provides various classes and methods to read and write data in Excel in Selenium tests. Let's unerstand with examples.

Selenium WebDriver is the most used automation tool for the automation of web applications. Now, we know that these web applications are used by multiple users, and each one of those uses the applications as per their own data. So, considering the usage, it becomes the primary responsibility of the QAs also to test the web applications with varying data sets. Now the user journeys will be the same, but the data set will be different. Therefore, it makes more sense to execute the same test case with different data, instead of writing a separate test case for each user journey with each data set. This is where Microsoft Excel comes in handy, which is one of the favorite tools for storing test data. Excel in Selenium is one of the most used combinations for storing test data and then running the same test case against various data sets.

There are various libraries in JAVA which helps in reading/writing data from Excel files. But, Apache POI is one of the most used libraries, which provides various classes and methods to read/write data from various formats of Excel files(xls, xlsx etc). Subsequently, in this article, we will understand the details of Apache POI and how we can use the same to read/write data from Excel files, by covering the details under the following topics:

  • What is Apache POI?
  • How to manage Excel workbooks?
  • How to manage Excel sheets?
  • Also, how to manage Excel rows?
  • _How to manage Excel cells? _
  • How to read data from Excel in Selenium tests using Apache POI?
  • Additionally, how to read a specific cell value?
  • How to read the entire Excel sheet?
  • How to write data in Excel in Selenium tests using Apache POI?
  • _Moreover, how to write to a new cell in an existing row? _
  • And, how to write to a new cell in a new row?

What is Apache POI?

_Apache POI, where POI stands for _(Poor Obfuscation Implementation) _ is an _API that offers a collection of Java libraries that helps us to read, write, and manipulate different Microsoft files such as excel sheets, power-point, and word files.

How to manage Excel workbooks pragmatically?

Apache POI provides various interfaces and classes that help us to work with Excel. It provides a “_Workbook _” interface to maintain Excel Workbooks. There are certain classes that implement this interface and we use these classes to create, modify, read, and write data in Excel files. The two mainly used classes for managing Excel Workbooks are:

  • HSSFWorkbook-_ These class methods are used to read/write data to Microsoft Excel file in **.xls_**_ format. It is compatible with MS-Office versions 97–2003._
  • XSSFWorkbook-_ These class methods are used to read-write data to Microsoft Excel in **.xls__ or _.xlsx**_ format. It is compatible with MS-Office versions 2007 or later._

How to manage Excel sheets programmatically?

There is another interface, “_Sheet _“, which we use to create a sheet in the Workbook. There are two classes that used to work with sheets, same as we have for Workbook Interface:

  • _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., ie, the new format of Excel

How to manage Excel rows pragmatically?

The_ Row _interface provides us with the ability to work with rows in the Excel sheet. Below two classes implement this interface:

  • HSSFRow_ – This represents a row in the HSSFSheet._
  • XSSFRow_ – This represents a row in the XSSFSheet._

_How to manage Excel cells? _

The_ Cell _interface helps us in accessing the cells of a particular row. There are two classes that implement this interface and we can use for reading/writing data into the cell:

  • HSSFCell_ – We use it to work with cells of HSSFRow._
  • XSSFCell_ – We use it to work with cells of XSSFRow._

Now that we have gone through the details of the Apache POI library, let’s try to use it to read and write into Excel Files using Selenium WebDriver.

selenium-webdriver apache poi

What is Geek Coin

What is GeekCash, Geek Token

Best Visual Studio Code Themes of 2021

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Selenium WebDriver With Python 3.x - Novice To Ninja

Selenium WebDriver With Python 3.x - Novice To Ninja

Features of Selenium WebDriver - Benefits of Selenium WebDriver

Learn the various features of selenium webdriver that make it so useful. These are the benefits of selenium webdriver and reason to learn selenium testing.

How To Get Attribute Value In Selenium WebDriver

How To Get Attribute Value In Selenium WebDriver. While you are automating your test cases with Selenium automation, here is how to start implementing Selenium getAttribute() method to get the best results.

Selenium Tool Suite - Components and Features

Learn about selenium tool suite - See its components like Selenium IDE, Selenium Remote control, webdriver & selenium grid with their features & limitations

Selenium Online Training- Selenium Course with live Project| OnlineITGuru

Software company need expert with selenium certification. Learn best Java concepts in selenium training and become a testing engineer.