Working with Excel sheet using POI apache

October 2, 2016

In this tutorial, we will learn how to integrate Microsoft Excel sheet with Selenium WebDriver. Selenium does not provide an inbuilt feature to work with excel. So, we will take help of some 3rd party tool like POI from Apache.

Apache POI is an open source library provided by Apache Software Foundation to read and write Excel files.

Essential classes and Interface in Apache POI API:

There are two formats of Excel files – .xls and .xlsx format.

To read .xls format POI has HSSF implementation and for .xlsx format POI has XSSF implementation.

important classes in POI apache

Workbook – It’s an interface implement by HSSFWorkbook and XSSFWorkbook to work with xls and xlsx format workbook respectively.

Sheet – It’s an interface implemented by HSSFSheet and XSSFSheet to work with sheets of xls and xlsx format workbook respectively.

Row – It’s an interface implemented by HSSFRow and XSSFRow, which represent a ROW of a sheet of xls and xlsx format respectively.

Cell – It’s an interface implemented by HSSFCell and XSSFCell, which represent a CELL in a row of a sheet of xls and xlsx format respectively.

Now let us list down all the methods required to work with an Excel:

  • Creating a workbook.
  • Opening a Workbook.
  • Creating a Sheet.
  • Saving a Workbook.
  • Saving a Workbook with a new name.
  • Closing a Workbook.
  • Getting Row number from a Sheet.
  • Getting Cell number from a Row of a Sheet.
  • Reading Data from a Cell.
  • Writing Data into a Cell.

Now let us learn all these methods one-by-one. These are most frequently used methods while working with an excel sheet. You can directly use these methods in your project. There may be few changes required to cover up your requirement.

Creating a Workbook:

In the below method we will learn how to create a new workbook using POI Apache.

Opening a Workbook:

In this method, we will learn how to open a workbook using POI API’s.

Saving a Workbook:

In this method, we will learn how to save a workbook using POI files

Saving a Workbook with new name:

In this method, we will learn how to save a workbook with a new name using POI fiiles.

Closing a Workbook:

In this method, we will learn how to close a workbook.

Create a Sheet:

In this method, we will learn how to create a sheet in a workbook.

Getting number of Row from a Sheet:

In this method, we will learn how to get number of rows which has data from a sheet.

Get a Cell Count from a Row in a Sheet:

In this method, we will learn how to get cell number of a row from a sheet.

Reading data from a Cell of a sheet:

In this method, we will learn how to read data from a cell of a sheet.

Writing Data in a Cell of a Sheet:

In this method, we will learn how to write in a cell of a sheet.

Now let us test the above code with a scenario:

Scenario:

  • Create a new workbook with name say testdata.xlsx
  • Open the workbook
  • Create a new sheet with name say TestData
  • Write some data in the sheet.
  • Save the workbook.

Here, is the complete code:

PS: For any questions, queries and feedback, feel free to write us at saurabh@qatechhub.com or support@qatechhub.com. Happy Learning 🙂

Saurabh

About the Author

Saurabh

Follow Saurabh:

Leave a Comment: