5 Ways to Read Excel Files in Java Code

Excel files are a common format for data storage and analysis, especially in business and scientific environments. However, when you need to manipulate, analyze, or integrate Excel data into Java applications, the process can seem daunting at first. Fortunately, Java provides several libraries and methods to handle Excel files effectively. This blog post will explore five different methods to read Excel files in Java code, each with its own strengths and use cases.

JExcelAPI

JExcelAPI is one of the earliest Java libraries for handling Excel files. Here’s how you can use it to read an Excel file:

• Download and include the JExcelAPI library in your project.
• Use the following code snippet to open and read an Excel file:

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

// Create a workbook from an Excel file
Workbook workbook = Workbook.getWorkbook(new File("path/to/excel/file.xls"));
Sheet sheet = workbook.getSheet(0); // First sheet

for (int i = 0; i < sheet.getRows(); i++) {
    for (int j = 0; j < sheet.getColumns(); j++) {
        Cell cell = sheet.getCell(j, i);
        System.out.println(cell.getContents());
    }
}

🔎 Note: JExcelAPI supports only the older .xls format and not the newer .xlsx format.

Apache POI

Apache POI is probably the most popular choice for working with Excel files in Java. It supports both .xls and .xlsx formats:

• Download Apache POI from their official site and add it to your project.
• Here’s how to read an Excel file using POI:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;

// Load the workbook
try (FileInputStream file = new FileInputStream(new File("path/to/excel/file.xlsx"))) {
    Workbook workbook = new XSSFWorkbook(file);
    Sheet sheet = workbook.getSheetAt(0);
    Iterator rowIterator = sheet.iterator();

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellType()) {
                case STRING: System.out.print(cell.getStringCellValue() + "\t");
                             break;
                case NUMERIC: System.out.print(cell.getNumericCellValue() + "\t");
                              break;
                // handle other cell types...
            }
        }
        System.out.println();
    }
} catch (IOException e) {
    e.printStackTrace();
}

Java API for Microsoft Office (Jxls)

Jxls is another library but is more focused on generating Excel reports, though it can also read Excel files:

import org.jxls.util.JxlsHelper;

JxlsHelper.getInstance().processTemplateAtCell(
    "path/to/excel/file.xlsx", 0, 0, "path/to/output/file.xlsx");

This method creates an output file from the input, copying all content, which means it's primarily for transformations.

📌 Note: Jxls is more suited for creating Excel files but can read existing ones for purposes like data transformation.

OpenXLS

OpenXLS is another alternative, known for its simplicity and compatibility with both .xls and .xlsx files:

import org.openxls.ExtenXLS.*;
import java.io.File;

Workbook wb = WorkbookFactory.create(new File("path/to/excel/file.xls"));
Sheet sheet = wb.getSheet(0);
WorkCell[] cells = sheet.getRows();

for (WorkCell cell : cells) {
    System.out.println(cell.getValue());
}

JasperReports

While primarily known for reporting, JasperReports can also interact with Excel files through its JRXlsxDataSource:

import net.sf.jasperreports.engine.data.JRXlsxDataSource;
import net.sf.jasperreports.engine.JREmptyDataSource;
import net.sf.jasperreports.engine.JasperRunManager;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperFillManager;

// Create a data source from an Excel file
JRXlsxDataSource ds = new JRXlsxDataSource("path/to/excel/file.xlsx");
ds.setUseFirstRowAsHeader(true);
ds.next();

// Then you can fill a JasperReport with this data source

Each of these methods has its use cases:

• JExcelAPI for older .xls files.
• Apache POI for broadest support and advanced features.
• Jxls for report generation and transformation.
• OpenXLS for simplicity and ease of use.
• JasperReports if you're already using it for reporting.

Integrating these methods into your Java application allows for a seamless integration of Excel data into your Java workflow. Whether you're importing data for analysis, creating reports, or just moving information from Excel to your system, these libraries provide the necessary tools to accomplish your tasks efficiently.

The variety of libraries highlights Java's versatility in handling different formats and applications. When choosing, consider your project's specific needs, such as format support, performance requirements, or whether you need additional features like data transformation or report generation. Each option comes with its nuances, so selecting the right one can significantly impact how effectively you manage Excel files within your Java application.

Can Apache POI handle older .xls Excel formats?

+

Yes, Apache POI can handle both .xls (HSSF) and .xlsx (XSSF) formats.

What if my Excel file contains formulas?

+

Libraries like Apache POI can evaluate formulas, returning their calculated values.

Is it possible to read only specific sheets from an Excel file?

+

Yes, all libraries allow you to specify which sheet to read by providing an index or name.

Do these methods support password-protected Excel files?

+

Some libraries like Apache POI have support for decrypting and reading password-protected files.

Can I use these libraries with Android development?

+

While possible, libraries like Apache POI can be heavy for mobile devices, and optimizations or alternatives might be necessary for efficient use in Android apps.