Reading and Writing Data to Excel File in Java using Apache POI

Java does not provide built-in APIs to handle Microsoft Excel files. To perform operations such as creating, reading, or updating Excel sheets, we use the Apache POI library.

Apache POI is an open-source Java library developed by the Apache Software Foundation. It allows Java programs to read, write, and manipulate Microsoft Office documents such as Excel, Word, and PowerPoint.

It supports:

• HSSF (Horrible Spreadsheet Format): for .xls (Excel 97–2003)
• XSSF (XML Spreadsheet Format): for .xlsx (Excel 2007 and later)

Adding Apache POI to Your Project

To use Apache POI, you need to add the following dependencies to your project.

For Maven:

<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
</dependencies>

For Gradle:

implementation 'org.apache.poi:poi:5.2.5'
implementation 'org.apache.poi:poi-ooxml:5.2.5'

Writing Data to Excel File 

Steps:

1. Create a workbook (XSSFWorkbook for .xlsx files)
2. Create a sheet inside the workbook
3. Create rows and cells inside the sheet
4. Fill the cells with data
5. Write the workbook to an output stream
6. Close the workbook

Example:

Output:

• XSSFWorkbook: represents an Excel workbook in .xlsx format
• createSheet(): creates a new sheet
• createRow() and createCell(): create rows and cells dynamically
• setCellValue(): assigns values to cells
• Finally, the workbook is written to the disk using FileOutputStream

Reading Data from Excel File

Steps:

1. Create a FileInputStream from the Excel file
2. Create a workbook instance (XSSFWorkbook)
3. Get the desired sheet
4. Iterate through rows and cells
5. Retrieve values based on cell type
6. Close the workbook

Example:

• The program reads the file StudentData.xlsx.
• It retrieves each row and cell, printing data to the console.
• getCellType() ensures correct handling of string and numeric cells.

Reading Excel File from a Specific Path

You can read Excel files stored at any path using an absolute file location.

WorkbookFactory.create() can open both .xls and .xlsx files, making it more flexible than using specific XSSFWorkbook or HSSFWorkbook.

Appending Data to an Existing Excel File

This example shows how to add new data to an already existing sheet.