1. How to Read Excel Files in Java Using Apache POI: A Step-by-Step Guide

Apache POI : Introduction

Note the Java POI tool usage in opening Excel document data in Java. “How to Read Excel Files in Java Using Apache POI” tutorial will be your guide to how to process the Excel tables data in a step by step order, which will make work with and usage of Java for Excel tables an easy process. Obtained a firm grasp on Excel file processing in Apache POI for a Java project and with a lot of hard work made my Java project prototypes real things.

Overview of Apache POI:

Apache POI (Poor Obfuscation Implementation) is a commonly used open source Java library managed by The Apache Software Foundation. It grants compatibility for the reading and writing of Microsoft Office file formats, which comprise various documents, such as Excel spreadsheets (XLS, XLSX), Word document files (DOC, DOCX), and PowerPoint presentations (PPT, PPTX). Here’s an overview of its key features and components: Here’s an overview of its key features and components:

• Excel Support: Apache POI integrates the capability to create, modify and open Excel documents as well even with no specified purpose. It operates on both obsolete and the new Excel formats (XLS and XLSX) which rely upon the self regulating XML formats. Developers are in a position of selecting from cell data, working with formulas, determining the styles, managing format, and charts among other elements
• Word Support: Apache POI particularly gives APIs for dealing with word documents. This technology enables developers to write, revise, or extract documents from DOC and DOCX. This could be a case of performing operations on text, tables, images, styles and other document features.
• PowerPoint Support: Apache POI offers developers the possibility to modify PowerPoint presentations. Support for the creation of, editing as well as extracting content from PPT and PPTX presentations including different slide, shape, text, image and slide layout objects
• Integration with Java Applications: Apache POI has been designed so that it can be easily work with Java applications. Developers can use its APIs to execute different actions on Office documents within their Java based applications.

Setting up Apache POI:

Here is a step-by-step guide for How to Read Excel Files in Java Using Apache POI into your Spring Boot project:

• Add Apache POI Dependency: Start by adding the Apache POI dependencies to your pom.xml file if you’re using Maven or build.gradle if you’re using Gradle. Include the required dependencies for the modules you intend to use. For Excel manipulation, you would typically include dependencies for poi, poi-ooxml For Maven:

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

compile "org.apache.poi:poi:4.1.2"
compile "org.apache.poi:poi-ooxml:4.1.2"

• Create POJOs: Create Plain Old Java Objects (POJOs) to represent the data you want to read from or write to Excel files. here is a simple POJO (Plain Old Java Object) representing a Product with fields for productId, productName, and productPrice:

public class Product {

    private Long productId;
    private String productName;
    private double productPrice;

    // Constructors
    public Product() {
    }

    public Product(Long productId, String productName, double productPrice) {
        this.productId = productId;
        this.productName = productName;
        this.productPrice = productPrice;
    }

    // Getters and Setters
    public Long getProductId() {
        return productId;
    }

    public void setProductId(Long productId) {
        this.productId = productId;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public double getProductPrice() {
        return productPrice;
    }

    public void setProductPrice(double productPrice) {
        this.productPrice = productPrice;
    }

    // Override toString() method for better representation
    @Override
    public String toString() {
        return "Product{" +
                "productId=" + productId +
                ", productName='" + productName + '\'' +
                ", productPrice=" + productPrice +
                '}';
    }
}

How to Read Excel Files in Java Using Apache POI:

1. Create a Workbook: To Read Excel Files in Java Using Apache POI WorkbookFactory class to create a workbook instance from the Excel file. This class can handle both .xls (HSSF) and .xlsx (XSSF) formats.
2. Access the Desired Sheet: Once you have the workbook, you can access the specific sheet within it using the getSheetAt(int index) or getSheet(String name) method.
3. Iterate Through Rows and Cells: Iterate through the rows and cells of the sheet to read the data. Apache POI provides classes like Row and Cell to facilitate this.
4. Extract Cell Data: Use the appropriate methods of the Cell class (getStringCellValue(), getNumericCellValue(), etc.) to extract data from each cell based on its type.
5. Close the Workbook: It’s important to close the workbook and release any resources held by it once you’re done reading the data.

Sample EXCEL File: Refer below to read excel files in Java using Apache POI

| productId | productName  | productPrice |
|-----------|--------------|--------------|
| 1         | Laptop       | 999.99       |
| 2         | Smartphone   | 499.99       |
| 3         | Headphones   | 79.99        |
| 4         | Tablet       | 299.99       |

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelProductReader {

    public static List<Product> readProductsFromExcel(String filePath) throws IOException {
        List<Product> productList = new ArrayList<>();

        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = WorkbookFactory.create(fis);

        Sheet sheet = workbook.getSheetAt(0); // Assuming first sheet

        for (Row row : sheet) {
            // Skip header row, assuming it's the first row
            if (row.getRowNum() == 0) {
                continue;
            }

            Long productId = (long) row.getCell(0).getNumericCellValue(); // Assuming productId is numeric
            String productName = row.getCell(1).getStringCellValue();
            double productPrice = row.getCell(2).getNumericCellValue();

            Product product = new Product(productId, productName, productPrice);
            productList.add(product);
        }

        workbook.close();
        fis.close();

        return productList;
    }

    public static void main(String[] args) {
        String excelFilePath = "path/to/your/excel/file.xlsx";
        try {
            List<Product> products = readProductsFromExcel(excelFilePath);
            for (Product product : products) {
                System.out.println(product);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Code Overview:

1. Objective: Read product data from an Excel file and populate a list of Product objects.
2. Method readProductsFromExcel(String filePath):
   • Input: File path of the Excel file.
   • Output: List of Product objects.
   • Reads Excel file using Apache POI.
   • Iterates through each row in the first sheet, skipping the header row.
   • Extracts productId (assumed numeric), productName, and productPrice from each row.
   • Creates a Product object for each row and adds it to the list.
   • Closes the workbook and input stream.
   • Returns the populated list of Product objects.
3. Method main(String[] args):
   • Entry point of the program.
   • Defines the file path of the Excel file to read.
   • Calls readProductsFromExcel method within a try-catch block to handle IOExceptions.
   • Prints details of each Product object retrieved from the Excel file.
4. Exception Handling:
   • Handles IOExceptions that may occur during file processing.
   • If an IOException occurs, it prints the stack trace for debugging.
5. Imports:
   • Imports necessary classes from Apache POI and java.io package.
6. Class Definition:
   • Defines a class named ExcelProductReader.

Handling Different Data Types:

1. Strings:
   • Use the getStringCellValue() method of the Cell class to retrieve string values from cells.
   • Ensure to handle cases where cells may contain empty strings or null values.
2. Numbers:
   • Use the getNumericCellValue() method of the Cell class to retrieve numeric values from cells.
   • Be aware that numeric values can be integers or floating-point numbers.
3. Dates:
   • Use the getDateCellValue() or getLocalDateTimeCellValue() method of the Cell class to retrieve date values from cells.
   • Date values are often represented as numeric values (e.g., Excel stores dates as numeric values representing the number of days since a specific date).
4. Booleans:
   • Use the getBooleanCellValue() method of the Cell class to retrieve boolean values from cells.
   • Boolean values are typically used to represent true/false conditions.
5. Handling Cell Types:
   • To handle cell types in Apache POI using the getCellType() method:
   • Use getCellType() to determine the type of data in a cell.
   • Based on the cell type:
     • Use getStringCellValue(), getNumericCellValue(), getBooleanCellValue(), or other appropriate methods to retrieve the value.
     • Handle each type to avoid runtime exceptions or misinterpretation of data.

Conclusion:

Apache POI represents a Java Library for reading Excel file with great stability and rich features. It gives you access to API’s which were specially designed to manipulate and iterate through rows & cell data, elements with different data types, as well as to create a robust error handling. The Apache POI provides with a tool for interfacing with Excel file through which the data can be extracted and embedded into a Java application.

Related Post

Various Perfect possible combinations of try catch final
You can check other answer here