5 Ways to Extract Excel Data with Java

In the era of data-driven decision-making, extracting and manipulating data from spreadsheets like Excel is a fundamental skill for developers and data analysts alike. Java, with its robust libraries and tools, offers several ways to work with Excel files seamlessly. In this blog post, we'll explore five effective methods to extract Excel data using Java. Whether you're managing large datasets or integrating with existing systems, these techniques will help you efficiently handle Excel files.

1. Using Apache POI

Apache POI is perhaps the most popular library for handling Microsoft Office document formats in Java. It provides excellent support for Excel files in both .xls and .xlsx formats.

Setting Up

• Add Apache POI dependencies to your project:

  org.apache.poi>
  poi-ooxml>
  5.0.0>

Reading Data

• Here’s a basic example on how to read data from an Excel file:

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

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

public class ExcelReadDemo {
    public static void main(String[] args) throws IOException {
        String filePath = “path/to/your/excel.xlsx”;
        FileInputStream file = new FileInputStream(filePath);

    // Creating Workbook instance that refers to .xlsx file
    Workbook workbook = WorkbookFactory.create(file);
    Sheet sheet = workbook.getSheetAt(0);

    for (Row row : sheet) {
        for (Cell cell : row) {
            //Print the value of the cell
            System.out.println(cell.toString());
        }
    }
    workbook.close();
    file.close();
}


}

📝 Note: Apache POI can be memory-intensive for large files. Consider using XSSF and SAX Event API for very large Excel files.

2. Using JExcelAPI

If your projects require working with .xls files only, JExcelAPI might be a more lightweight option compared to Apache POI.

Setting Up

• Add JExcelAPI to your project:

    net.sourceforge.jexcelapi>
    jxl>
    2.6.12>

Reading Data

• An example to read an Excel file with JExcelAPI:

import jxl.*;
import jxl.read.biff.BiffException;

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

public class JExcelApiDemo {
    public static void main(String[] args) {
        try {
            Workbook workbook = Workbook.getWorkbook(new File(“path/to/your/excel.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() + “ “);
                }
                System.out.println();
            }
            workbook.close();
        } catch (BiffException | IOException e) {
            e.printStackTrace();
        }
    }
}

3. Using OpenCSV

OpenCSV isn't explicitly designed for Excel, but when used with tools like Excel to CSV conversion, it can manage the data extraction process:

Setting Up

• Add OpenCSV to your project:

    com.opencsv>
    opencsv>
    5.3>

Reading Data

• Assuming you’ve converted your Excel file to CSV, here’s how to read it with OpenCSV:

import com.opencsv.CSVReader;

import java.io.FileReader;
import java.io.IOException;

public class OpenCSVDemo {
    public static void main(String[] args) {
        try {
            CSVReader reader = new CSVReader(new FileReader(“path/to/your/excel.csv”));
            String[] line;
            while ((line = reader.readNext()) != null) {
                // Print all the lines in the file
                for (String cell : line) {
                    System.out.print(cell + “ “);
                }
                System.out.println();
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

4. Using JXLS

JXLS focuses on transforming Excel templates, but it also has capabilities for reading data from Excel files.

Setting Up

• Add JXLS dependencies:

    org.jxls>
    jxls-poi>
    2.12.0>


    org.jxls>
    jxls-jexcel>
    2.12.0>

Reading Data

• Reading data with JXLS:

import org.jxls.reader.*;

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

public class JXLSReadDemo {
    public static void main(String[] args) throws IOException {
        // Define the Excel file to read
        FileInputStream inputFile = new FileInputStream(“path/to/your/excel.xls”);
        XLSReader reader = ReaderBuilder.buildFromXML();

    // Your reading logic here
}


}

5. Using JDBC-ODBC Bridge

The JDBC-ODBC Bridge approach might not be the most modern solution, but it's still useful for legacy systems or in environments where you have ODBC access to Excel.

Setting Up

• Ensure you have an ODBC driver for Excel installed on your machine.

Reading Data

• A simple example of connecting to Excel via JDBC-ODBC:

import java.sql.;
public class JDBCExtractDemo {
    public static void main(String[] args) {
        Connection con = null;
        try {
            // JDBC connection to Excel ODBC Data Source
            Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
            String excelDbPath = “path/to/your/excel.xls”;
            String conString = “jdbc:odbc:Driver={Microsoft Excel Driver (.xls, *.xlsx)};DBQ=” + excelDbPath + “;”;
            con = DriverManager.getConnection(conString, “”, “”);

        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM [Sheet1$]");
        // Extract your data here
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        if (con != null) try { con.close(); } catch(SQLException e) {}
    }
}


}

Each method has its strengths and use cases:

• Apache POI offers comprehensive support for Excel formats with a wide range of functionalities.
• JExcelAPI is lighter and suited for .xls files.
• OpenCSV provides a simple way to deal with CSV conversion from Excel.
• JXLS excels at transforming templates and can handle reading.
• JDBC-ODBC Bridge connects traditional database approaches with Excel.

The choice between these libraries often depends on project requirements, file format support, performance considerations, and existing system compatibility. For instance:

When dealing with large files, Apache POI's event model or JExcelAPI's cell-by-cell reading can be more memory-efficient. If your application involves template-based reporting, JXLS might be the better choice. For older systems or when connecting various databases, the JDBC-ODBC Bridge might still prove valuable.

In summary, Java offers a suite of powerful tools to interact with Excel files, each with its unique approach to data extraction. Whether you need comprehensive control over Excel documents, lightweight processing, template transformations, or straightforward database-like access, there's a library suited to your needs. By choosing the right tool for your specific task, you ensure efficient data handling and manipulation, which is pivotal in our data-centric world.