5 Ways to Extract Excel Data Using Java

The manipulation of Excel data can enhance productivity, automate routine tasks, and boost data analysis efficiency within an organization. Java, with its robust libraries and tools, provides a rich ecosystem to facilitate the extraction of data from Excel spreadsheets. Here are five methods to harness Java for the purpose:

1. Using Apache POI

Apache POI is the go-to library for Excel file manipulation in Java. It offers functionality for both Excel 2003 format (.xls) and the newer 2007+ formats (.xlsx). Here’s how to get started:

• Include Dependencies: Add the POI libraries to your project's dependencies.
• Read Data: Use POI to open a workbook and iterate through its rows and columns to extract data.
• Example Code:

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

public class ExcelReader {
    public static void main(String[] args) {
        try (FileInputStream fis = new FileInputStream("data.xlsx");
             Workbook workbook = new XSSFWorkbook(fis)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    // Handle different cell types
                    switch (cell.getCellType()) {
                        case STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                        case NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        // Handle other types
                    }
                }
                System.out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

💡 Note: POI handles .xlsx and .xls formats differently, so choose the correct class (XSSF or HSSF) based on your file type.

2. JExcelApi

JExcelApi is another Java library designed specifically for Excel operations. Although it lacks support for newer Excel formats, it’s straightforward for .xls files:

• Include Dependency: Add JExcelApi to your project.
• Access Data: Use it to open an Excel file and read cell values.

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import java.io.IOException;

public class JExcelReader {
    public static void main(String[] args) {
        try {
            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++) {
                    System.out.print(sheet.getCell(j, i).getContents() + "\t");
                }
                System.out.println();
            }
        } catch (IOException | BiffException e) {
            e.printStackTrace();
        }
    }
}

💡 Note: JExcelApi does not support .xlsx files, limiting its use to older Excel formats.

3. Using Eclipse SWT (Standard Widget Toolkit)

Eclipse SWT provides components that can be used for reading and writing Excel files. While primarily designed for GUI development, it includes:

• Dependencies: Include SWT libraries in your project.
• Reading Data: Use the `Workbook` and `Sheet` classes for direct access to Excel file contents.

import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.program.Program;

public class SwtExcelReader {
    public static void main(String[] args) {
        Display display = new Display();
        Shell shell = new Shell(display);
        
        // You might want to use Program to launch Excel and then control it
        // For simplicity, let's assume reading using some third-party Excel API
    }
}

💡 Note: SWT is powerful but more suited for building interfaces, and you might need an Excel API alongside for data manipulation.

4. JDBC-ODBC Bridge

This method involves connecting to an Excel file through a JDBC driver that translates SQL queries into ODBC calls, allowing database-like operations on Excel data:

• Configure ODBC: Set up an ODBC Data Source in Windows.
• JDBC Connection: Establish a JDBC connection to this source to execute SQL queries on Excel data.

import java.sql.*;

public class JdbcOdbcExcelReader {
    public static void main(String[] args) {
        String url = "jdbc:odbc:ExcelData";
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT * FROM [Sheet1$]");
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(rs.getString(i) + "\t");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

💡 Note: This approach is Windows-specific and might not work seamlessly in a cross-platform environment.

5. JXL

While similar to JExcelApi, JXL is worth mentioning for its simplicity in dealing with .xls files. Here’s a basic implementation:

• Library Inclusion: Add JXL to your project.
• Read Data: Use JXL to navigate through rows and columns.

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

public class JxlReader {
    public static void main(String[] args) {
        try {
            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++) {
                    System.out.print(sheet.getCell(j, i).getContents() + "\t");
                }
                System.out.println();
            }
        } catch (IOException | BiffException e) {
            e.printStackTrace();
        }
    }
}

💡 Note: Like JExcelApi, JXL lacks .xlsx support.

To sum it all up, extracting data from Excel files in Java can be accomplished through various methods, each with its advantages and limitations. From the robust POI library, suitable for all Excel versions, to the simpler JXL for older formats, the choice of method depends on your specific needs such as file format support, platform compatibility, and the complexity of data manipulation required. The key is to choose the tool that best fits your project’s context and constraints, ensuring efficient and error-free data handling.

What is the difference between POI and JExcelApi?

+

Apache POI supports both .xls (97-2003) and .xlsx (2007+) file formats while providing extensive data handling capabilities. JExcelApi, on the other hand, is only for .xls files and is simpler to use for basic operations.

Can I use Java to write data back into Excel files?

+

Yes, all the libraries mentioned (POI, JExcelApi, JXL) can write back to Excel files, allowing you to create, modify, or update data in spreadsheets.

Which method is best for large Excel files?

+

For large files, using libraries like Apache POI, which supports streaming operations, might be more efficient in terms of memory usage.