How to Read & Write Data Using Apache POI - JAVA

Apache POI is a Java API to read and write Documents in .xls and .xlsx formats. It contains classes and interfaces. An Apache POI library provides two implementations to read an excel file:

• Horrible Spreadsheet Format (HSSF) Implementation: It’s an API that works with Excel 2003 or earlier versions.
• XML Spreadsheet Format (XSSF) Implementation: It’s an API that works with Excel 2007 or later versions.

Interfaces and Classes in Apache POI

Interfaces

• Workbook: It represents an Excel Workbook. This is an interface implemented by HSSFWorkbook and XSSFWorkbook.
• Sheet: This is an interface that represents an Excel worksheet. A sheet is a structure of a workbook, which represents the grid of cells. A Sheet interface extends java.lang.Iterable.
• Row: It is an interface that represents the row of the spreadsheet. The Row interface extends java.lang.Iterable

There are two classes: HSSFRow and XSSFRow.

• Cell: It is an interface. It is the representation of the cell in a row of a spreadsheet. HSSFCell and XSSFCell implement a Cell interface.

Classes

XLS Classes

• HSSFWorkbook: It’s a class that represents the XLS file.
• HSSFSheet: It’s a class that represents the sheet in an XLS file.
• HSSFRow: It’s a class that represents a row in the sheet of the XLS file.
• HSSFCell: It’s a class that represents a cell in a row of XLS files.

XLSX Classes

• XSSFWorkbook: It’s a class that represents the XLSX file.
• XSSFSheet: It’s a class that represents the sheet in an XLSX file.
• XSSFRow: It’s a class that represents a row in the sheet of the XLSX file.
• XSSFCell: It’s a class that represents a cell in a row of an XLSX file.

Steps to Read Data From Excel file

Step 1: Create a simple Java project in the eclipse.

Step 2: Now, create a library folder in the project.

Step 3: Download and add the following jar files in the library folder:

• commons-collections4–4.1.jar
• poi-3.17.jar
• poi-OOXML-3.17.jar
• poi-OOXML-schemas-3.17.jar
• xmlbeans-2.6.0.jar

Step 4: Set the Class-Path:

Right-click on the project ->Build Path ->Add External JARs -> select all the jar files -> Apply and close.

Step 5: Now create a class file with the name ReadFile and write the following code in the file.

Step 6: Create an excel file with the name “student.xls” and input some data in it.

Step 7: Save and run the program.

Read Also:- How to Process Java Script Executor in Selenium Test Automation

Example of Reading Excel File

Read Also:- How to Import Excel Data to SQLite DB Using Java

Writing Data In Excel File

Originally published at https://www.devstringx.com on Dec 21, 2021.