5 Ways to Read Excel Data in Java Easily

Excel is one of the most widely used formats for storing and manipulating data, particularly in business and data analysis contexts. Java, being a versatile language, offers multiple ways to interact with Excel files. Here's how you can easily read Excel data in Java:

Using Apache POI

Apache POI is an open-source library provided by the Apache Software Foundation to work with Microsoft Office formats like Excel.

• POI-OOXML: This part of Apache POI can handle modern formats like .xlsx (Excel 2007 and later).
• POI-HSSF: Used for .xls files from Excel 97-2003.

To start using Apache POI:

1. Download the POI libraries from the Apache POI website or add the dependencies in your Maven/Gradle project.
2. Import the necessary packages in your Java file:

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

Here's a basic example to read an Excel file using Apache POI:

public class ReadExcelPOI {
    public static void main(String[] args) {
        try {
            // Load the workbook
            Workbook workbook = WorkbookFactory.create(new File("example.xlsx"));
            // Get the first sheet
            Sheet sheet = workbook.getSheetAt(0);
            // Iterate through rows
            Iterator rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    // Process cell data
                    switch (cell.getCellType()) {
                        case STRING:
                            System.out.print(cell.getStringCellValue() + " | ");
                            break;
                        case NUMERIC:
                            System.out.print(cell.getNumericCellValue() + " | ");
                            break;
                        // ... Handle other cell types
                    }
                }
                System.out.println();
            }
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

🚨 Note: Be sure to handle exceptions as IO operations can throw IOException.

Using JXL (JExcelApi)

JXL or JExcelApi is another library for handling Excel files (.xls files only) with a simpler API than Apache POI.

1. Download the JXL jar from the official repository.
2. Import the JXL package:

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

Here's a simple example:

public class ReadExcelJXL {
    public static void main(String[] args) {
        try {
            Workbook workbook = Workbook.getWorkbook(new File("example.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();
            }
        } catch (BiffException | IOException e) {
            e.printStackTrace();
        }
    }
}

🚨 Note: JXL supports only .xls files, not .xlsx, which might limit its use for newer Excel files.

Using OpenCSV with Excel

While primarily a CSV tool, OpenCSV can be combined with Excel's "Save As CSV" feature to read Excel data:

1. Save the Excel file as CSV.
2. Use OpenCSV to parse the CSV file:

import com.opencsv.CSVReader;

public class ReadExcelOpenCSV {
    public static void main(String[] args) {
        try {
            CSVReader reader = new CSVReader(new FileReader("example.csv"));
            String[] nextLine;
            while ((nextLine = reader.readNext()) != null) {
                // nextLine[] is an array of values from the line
                for (String line : nextLine) {
                    System.out.print(line + " | ");
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Using Java SQL Connection with HXTT Excel

HXTT Excel JDBC driver allows you to connect to Excel files as if they were databases:

1. Download the HXTT JDBC driver for Excel.
2. Connect to the Excel file:

import java.sql.*;
public class ReadExcelHXTT {
    public static void main(String[] args) {
        try {
            Class.forName("com.hxtt.sql.excel.ExcelDriver");
            Connection conn = DriverManager.getConnection("jdbc:excel:///C:/path/to/yourfile.xlsx");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM [Sheet1$]");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnsNumber; i++) {
                    if (i > 1) System.out.print(",  ");
                    String columnValue = rs.getString(i);
                    System.out.print(columnValue + " ");
                }
                System.out.println("");
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Using UNO Interface (Apache OpenOffice or LibreOffice)

The UNO API allows Java programs to control OpenOffice or LibreOffice to read Excel files. This method requires:

• OpenOffice/LibreOffice installed on the system.
• The UNO library added to the classpath.

Here's an example:

import com.sun.star.uno.UnoRuntime;
import com.sun.star.lang.Locale;
import com.sun.star.sheet.XSpreadsheet;
import com.sun.star.sheet.XSpreadsheetDocument;

public class ReadExcelUNO {
    public static void main(String[] args) throws Exception {
        // Start OpenOffice in headless mode
        XComponentLoader loader = Bootstrap.bootstrap();
        XComponent xComp = loader.loadComponentFromURL(
            "private:factory/scalc", "_blank", 0, null);

        XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(
            XSpreadsheetDocument.class, xComp);

        XSpreadsheet xSpreadsheet = (XSpreadsheet) xSpreadsheetDocument.getSheets().getByIndex(0);
        // Access cells, etc.
    }
}

This method offers a wide range of features as it uses the Office suite's full capability through the UNO interface.

🚨 Note: Ensure OpenOffice or LibreOffice is installed and configured properly for this method to work.

When summarizing, each approach has its own set of advantages:

• Apache POI: Highly versatile, supports both .xls and .xlsx files with rich formatting capabilities.
• JXL: A simpler API but limited to .xls files.
• OpenCSV with CSV export: Good for quick, tabular data, but not for complex Excel functionalities.
• HXTT JDBC: Offers SQL query capabilities directly on Excel sheets.
• UNO: Provides access to Office's extensive feature set for complex Excel operations.

When choosing a method, consider your project's needs, the complexity of Excel files you're working with, and the resources available for integration and maintenance. Each approach brings different levels of complexity, setup requirements, and performance considerations, ensuring that there's a solution suitable for everyone's Excel reading needs in Java.

Which method is best for reading Excel files with complex formatting?

+

Apache POI would be the best choice for handling complex formatting in Excel files due to its rich set of features.

Can these libraries read password-protected Excel files?

+

Apache POI and HXTT JDBC support reading password-protected Excel files, whereas others might require additional steps or external libraries.

Is JXL suitable for reading large Excel files?

+

JXL is better suited for smaller files due to its limitations in handling newer Excel formats and large datasets.

How can I read Excel files without additional libraries?

+

Using UNO with OpenOffice/LibreOffice allows you to read Excel files without needing to manually handle the file format intricacies, though you’ll still need the Office suite installed.

Are there any performance considerations to keep in mind?

+

Performance can vary greatly depending on file size and complexity. Apache POI and HXTT JDBC tend to be memory-intensive for large files, while JXL and CSV methods are generally lighter. Choosing the right library and optimizing your code is crucial for performance.