Read and Write Excel File in Java Using Apache POI Lib

Excel files provide a very convenient way to store data in a structured way and are widely used by both developers and automation testers to perform various operations on excel spreadsheets. 

In this article, I’ll show you how to read and write data in excel files in Java using the most preferred and widely used library called Apache POI. It provides an API for manipulating Microsoft Excel documents like .xls and .xlsx. 

If you are working with Selenium or LeanFT ( UFT Developer) you can utilize information written in this article to read input test data at runtime from Excel Sheet to parametrize input values in a test script and write required information back to an Excel Sheet.

Let’s get started!

Apache POI Dependencies

If you are not using either Maven or Gradle you can simply download the latest version of POI jars from http://poi.apache.org/download.html. Once you navigate to this page click on the link highlighted in the following image. Currently, the latest version of POI jars is 5.0.0. By the time you are reading this article, the latest version might have changed. Click on the link highlighted in the following image to download the jar files.

Apache POI Jar download
After downloading the zip file, extract the zip file and add all the jar files (including jar file inside lib and ooxml-lib folders) to the classpath of your project.

apache poi jar files
Maven: If you are using Maven in your Java project, you will have to add the following dependencies to your pom.xml file.

Gradle: If you are using the Gradle project, you can add the following dependencies to your build.gradle file.

We are using two different types of POI libraries. The first one is used to work with the old Microsoft’s binary file format for the excel having .xls extension.

The second one poi-ooxml is used to work with the newer XML-based file format having a .xlsx extension.

Read Excel file in Java Selenium and LeanFT

Sample Excel File to Read

We will read the following sample Excel file having different types of data that we come across frequently. Also, we will write code in such a way that will work with both types of Excel file formats – .xls and .xlsx.

Read and Write Excel File in Java

Apache POI Interfaces

Apache POI excel library has the following four key Interfaces:

  • Workbook: A workbook is the representation of a Spreadsheet. HSSFWorkbook and XSSFWorkbook classes implement the Workbook interface. 
  • Sheet: A workbook may contain one or more than one sheet. The above sample Excel File has two sheets – Customer and Statement. XSSFSheet and HSSFSheet classes implement the Sheet interface
  • Row: It represents a row or a record in the spreadsheet. XSSFRow and HSSFRow classes implement the Row interface.
  • Cell: A cell represents a cell in a row of the spreadsheet. XSSFCell and HSSFCell classes implement the Cell interface.Apache POI Library

Java Code to Read an Excel Sheet Using Apache POI

The following program would be able to read an excel file for both types of file formats – .xls and .xlsx.Also, the method written in this program can read excel sheets having the following types of values in a cell or CellTypes.When using the Apache POI library we have to be very specific about what kind of cell value we want to read. The following code will evaluate the cell type at runtime and read the cell with the appropriate method.

  • Text Value
  • Numeric value
  • Calander/Date value
  • Empty Cell value
  • Boolean (TRUE,False) value

The output of the Code:

Read Excel File in LeanFT Output

How to Read a Particular Cell value from an Excel file?

In practical scenarios, quite often we need to retrieve the value of a particular cell from an Excel file. The following program shows how to do it. The program reads the value of the second row and first column from the sample Excel sheet mentioned earlier in the Sample Excel file section.

The output of the Program:

The Cell value for the specified row and column is: John

How to Write Excel File in Java?

In order to write data into an Excel file using Apache POI we have to follow the following steps:

  1. Create a workbook
  2. Add a sheet in the workbook to write the data values
  3. Create a row in a sheet for every new record
  4. Add new a cell in the sheet to store every individual value for a row
  5. Repeat steps 3 and 4 to add more records

Java Code Example to Write Excel File Using Apache POI

The Output of the code:

Write excel file in Java using Apache POI
Java Code to Update an Excel Cell Value Using Apache POI?

The following java program shows how to update a value of a specific cell in java using Apache POI.

The output of the code:

Update cell value in excel using java apache poi

Conclusion

In this article, we learned how to read write and update excel file formats – .xls and .xlsx in java using the Apache POI library.Hope you will find this article helpful on Excel spreadsheet.Please don’t forget to share it and if you have any questions/queries, please do mention that in the comment box.

Recommended Posts

Leave a Reply