5 Ways to Fetch Excel Data with Java Code

In today's data-driven world, the ability to import and manipulate data from spreadsheets is invaluable. One of the most common tasks in data handling is fetching data from Excel files. Here are five efficient ways to read Excel data using Java, catering to different libraries and scenarios:

1. Using Apache POI

Apache POI is one of the most widely used libraries for working with Microsoft Office documents in Java.

• Setup: Add the Apache POI dependencies to your project.
• Code Example:

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

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

public class ExcelReaderUsingPOI {
    public static void main(String[] args) throws IOException {
        FileInputStream fis = new FileInputStream(new File(“data.xlsx”));
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            for (Cell cell : row) {
                System.out.print(cell.toString() + “\t”);
            }
            System.out.println();
        }
        workbook.close();
        fis.close();
    }
}

💡 Note: Apache POI supports both XLS and XLSX formats, making it versatile for older and newer Excel files.

2. JExcelApi

JExcelApi, or Java Excel API, is another library for reading, writing, and modifying Excel spreadsheets.

• Setup: Include JExcelApi in your project libraries.
• Code Example:

import jxl.*;

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

public class ExcelReaderUsingJExcelApi {
    public static void main(String[] args) throws IOException, BiffException {
        Workbook workbook = Workbook.getWorkbook(new File(“data.xls”));
        Sheet sheet = workbook.getSheet(0);
        for (int i = 0; i < sheet.getRows(); i++) {
            for (int j = 0; j < sheet.getColumns(); j++) {
                Cell cell = sheet.getCell(j, i);
                System.out.print(cell.getContents() + “\t”);
            }
            System.out.println();
        }
        workbook.close();
    }
}

💡 Note: JExcelApi only works with Excel XLS files (not XLSX), which means it’s better suited for legacy applications.

3. Using OpenCSV

Although primarily designed for CSV files, OpenCSV can read Excel data by converting the file into CSV first.

• Setup: Include OpenCSV and a library like Apache POI for Excel file conversion.
• Code Example:

import com.opencsv.CSVReader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.*;

public class ExcelReaderUsingOpenCSV {
    public static void main(String[] args) throws Exception {
        // Convert Excel to CSV
        Workbook workbook = new HSSFWorkbook(new FileInputStream(“data.xls”));
        Sheet sheet = workbook.getSheetAt(0);
        try (PrintWriter csvWriter = new PrintWriter(new FileWriter(“temp.csv”))) {
            for (Row row : sheet) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    csvWriter.write((cell == null ? “” : cell.toString()) + (j < row.getLastCellNum() - 1 ? “,” : “”));
                }
                csvWriter.println();
            }
        }
        workbook.close();

    // Read CSV
    CSVReader reader = new CSVReader(new FileReader("temp.csv"));
    String[] nextLine;
    while ((nextLine = reader.readNext()) != null) {
        System.out.println(Arrays.toString(nextLine));
    }
    reader.close();
}


}

💡 Note: Using this method requires more steps but can be efficient for large datasets when memory is a constraint.

4. With JXLS

JXLS is specialized for Excel reporting but can also read Excel files.

• Setup: Add JXLS dependencies to your project.
• Code Example:

import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReadStatus;
import org.jxls.reader.XLSReader;

import java.io.FileInputStream;
import java.io.InputStream;

public class ExcelReaderUsingJXLS {
    public static void main(String[] args) throws Exception {
        InputStream inputXML = new FileInputStream(“input-config.xml”);
        InputStream inputXLS = new FileInputStream(“data.xlsx”);

    XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
    XLSReadStatus readStatus = mainReader.read(inputXLS);

    System.out.println("Rows Read: " + readStatus.getReadRowList().size());
    inputXLS.close();
    inputXML.close();
}


}

💡 Note: JXLS requires an XML configuration file to define how data should be read, making it complex but also very precise.

5. Using JReadExcel

JReadExcel is a straightforward library for reading Excel files in Java.

• Setup: Add JReadExcel to your project’s classpath.
• Code Example:

import jreadeexcel.ExcelFile;
import jreadeexcel.Sheet;

import java.io.File;

public class ExcelReaderUsingJReadExcel {
    public static void main(String[] args) throws Exception {
        ExcelFile excelFile = new ExcelFile(new File(“data.xlsx”));
        Sheet sheet = excelFile.openSheet(0);
        for (String[] row : sheet.getRowData()) {
            System.out.println(Arrays.toString(row));
        }
        excelFile.close();
    }
}

Which method is the fastest for reading Excel data in Java?

+

Apache POI is generally considered the fastest due to its native support for Excel formats and extensive optimization for performance.

Can I use these methods to write data to Excel as well?

+

Yes, libraries like Apache POI, JExcelApi, and JXLS support writing as well as reading Excel files.

What are the memory implications when handling large Excel files?

+

Large Excel files can lead to memory issues. Libraries like Apache POI offer event-based reading for processing files without loading them entirely into memory.