java read from excel file

HaC 2025

3 min read

·

19-10-2024

51

0

Share

Reading Excel Files in Java: A Comprehensive Guide

Excel files are ubiquitous in various data-driven applications. Java developers often need to access and process information stored in these spreadsheets. This article provides a comprehensive guide to reading Excel files in Java, leveraging common libraries and best practices.

Understanding the Libraries

Several libraries empower Java developers to interact with Excel files. Among the most popular are:

• Apache POI: A widely used library that offers a robust and feature-rich approach. https://poi.apache.org/
• JExcelApi: A lightweight alternative, particularly suitable for simpler scenarios. http://jexcelapi.sourceforge.net/

Choosing the Right Library

The choice between Apache POI and JExcelApi depends on your specific needs:

• Apache POI:
  • Pros: Comprehensive support for various Excel file formats (XLS, XLSX), advanced features like formulas and charts, and active community support.
  • Cons: Can be slightly more complex to learn and use due to its extensive capabilities.
• JExcelApi:
  • Pros: Easy to learn and implement, suitable for basic data reading tasks.
  • Cons: Limited support for newer Excel file formats (XLSX) and fewer features compared to POI.

Reading Excel Files with Apache POI

1. Add the Apache POI Dependency:

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

2. Code Example:

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

import java.io.FileInputStream;
import java.io.IOException;

public class ReadExcelFile {

    public static void main(String[] args) throws IOException {
        String excelFilePath = "path/to/your/excel_file.xlsx"; // Replace with your file path

        // Create Workbook object from the file
        Workbook workbook = new XSSFWorkbook(new FileInputStream(excelFilePath)); 

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

        // Iterate through rows
        for (Row row : sheet) {
            // Iterate through cells in each row
            for (Cell cell : row) {
                // Get the cell value
                String cellValue = "";
                switch (cell.getCellType()) {
                    case STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        cellValue = String.valueOf(cell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    // Handle other cell types as needed
                }
                System.out.print(cellValue + "\t"); 
            }
            System.out.println();
        }

        workbook.close();
    }
}

Explanation:

• Dependencies: This code snippet utilizes the poi and poi-ooxml dependencies for reading XLSX files.
• Workbook: The XSSFWorkbook object represents the entire Excel file.
• Sheet: Each Excel file can contain multiple sheets, and you can access them by index using workbook.getSheetAt(index).
• Row and Cell Iteration: The code iterates through each row and each cell in the row, extracting the cell value.
• Cell Type Handling: The example handles different cell types (string, numeric, boolean) and converts them to strings.
• Output: The cell values are printed to the console.

Reading Excel Files with JExcelApi

1. Add the JExcelApi Dependency:

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

2. Code Example:

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.io.IOException;

public class ReadExcelFileJExcelApi {

    public static void main(String[] args) throws IOException {
        String excelFilePath = "path/to/your/excel_file.xls"; // Replace with your file path

        // Create Workbook object from the file
        Workbook workbook = Workbook.getWorkbook(new File(excelFilePath));

        // Get the first sheet
        Sheet sheet = workbook.getSheet(0);

        // Iterate through rows
        for (int row = 0; row < sheet.getRows(); row++) {
            // Iterate through cells in each row
            for (int col = 0; col < sheet.getColumns(); col++) {
                Cell cell = sheet.getCell(col, row);
                String cellValue = cell.getContents();
                System.out.print(cellValue + "\t"); 
            }
            System.out.println();
        }

        workbook.close();
    }
}

Explanation:

• Dependencies: This code utilizes the jxl dependency for reading XLS files.
• Workbook: The Workbook object represents the entire Excel file.
• Sheet: Each Excel file can contain multiple sheets, and you can access them by name using workbook.getSheet(sheetName).
• Row and Cell Iteration: The code uses loops to iterate through each row and cell, retrieving their values.
• Cell Value Retrieval: The cell.getContents() method retrieves the cell value as a string.
• Output: The cell values are printed to the console.

Advanced Scenarios

• Handling Formulas: Apache POI provides methods to evaluate formulas directly. You can also read formula strings and process them separately.
• Data Validation: Apache POI supports reading data validation rules applied to cells.
• Conditional Formatting: Apache POI allows you to read and apply conditional formatting rules.
• Data Visualization: Both Apache POI and JExcelApi provide capabilities for reading and manipulating charts and graphs.

Choosing the right approach for your specific needs will depend on the complexity of your Excel file and the features you require. Remember to use the appropriate library and its specific methods to achieve your desired outcome.