5 Ways to Read Excel Sheets in Java: Quick Tutorial

Whether you're dealing with data processing, report generation, or simply managing large datasets, working with Excel files in Java is a common requirement for many developers. Excel files are widely used in businesses for their versatility in storing, organizing, and manipulating data. In this blog post, we will explore five different methods to read Excel sheets in Java. Each method has its own advantages and use cases, allowing developers to choose the most suitable approach depending on their project needs.

1. Using Apache POI

Apache POI is a powerful library for working with Microsoft Office files in Java. It’s particularly renowned for its Excel handling capabilities. Here’s how you can use Apache POI to read an Excel file:

• Download the Apache POI libraries from the official website.
• Include the necessary JAR files in your project’s classpath.
• Write Java code to interact with Excel files.

Here's a sample code snippet:

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 org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ReadExcel {
    public static void main(String[] args) {
        try {
            FileInputStream excelFile = new FileInputStream(new File("example.xlsx"));
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    System.out.print(cell.toString() + " ");
                }
                System.out.println();
            }
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

📌 Note: Apache POI supports both .xls and .xlsx formats, making it versatile for different versions of Excel.

2. JExcelApi

JExcelApi is another library for reading Excel files, primarily supporting older .xls files. Here’s how you can implement reading with JExcelApi:

• Include JExcelApi in your project dependencies.
• Use JExcelApi classes to read the Excel sheet.

Example:

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

public class ReadExcelWithJExcelApi {
    public static void main(String[] args) {
        try {
            Workbook workbook = Workbook.getWorkbook(new File("example.xls"));
            Sheet sheet = workbook.getSheet(0);
            for (int row = 0; row < sheet.getRows(); row++) {
                for (int col = 0; col < sheet.getColumns(); col++) {
                    System.out.print(sheet.getCell(col, row).getContents() + " ");
                }
                System.out.println();
            }
            workbook.close();
        } catch (BiffException | IOException e) {
            e.printStackTrace();
        }
    }
}

3. JXL (Java Excel API)

JXL, similar to JExcelApi, is used for .xls files. It provides simpler API but is less versatile compared to POI.

• Add JXL to your project.
• Read from an Excel file using JXL.

Here's a brief example:

import jxl.Workbook;
import jxl.Sheet;
import jxl.Cell;
import java.io.File;

public class ReadWithJXL {
    public static void main(String[] args) throws Exception {
        Workbook workbook = Workbook.getWorkbook(new File("example.xls"));
        Sheet sheet = workbook.getSheet(0);
        for (int i = 0; i < sheet.getRows(); i++) {
            Cell[] row = sheet.getRow(i);
            for (Cell cell : row) {
                System.out.print(cell.getContents() + " ");
            }
            System.out.println();
        }
        workbook.close();
    }
}

4. Using OpenCSV for CSV Files

Although not specific to Excel, CSV files are commonly exported from Excel. Here’s how you can read these files:

• Include OpenCSV in your project.
• Read CSV as if it were an Excel sheet.

Example:

import com.opencsv.CSVReader;
import java.io.FileReader;
import java.io.IOException;

public class ReadCSV {
    public static void main(String[] args) throws IOException {
        try (CSVReader reader = new CSVReader(new FileReader("example.csv"))) {
            String[] line;
            while ((line = reader.readNext()) != null) {
                for (String cell : line) {
                    System.out.print(cell + " ");
                }
                System.out.println();
            }
        }
    }
}

5. Using JavaFX’s javafx.scene.control.TableView

JavaFX provides components that can read and display Excel-like data directly:

• Integrate JavaFX into your project.
• Use TableView to visualize data from a CSV or potentially an Excel file converted to CSV.

Here's how you might do it:

import javafx.application.Application;
import javafx.scene.Scene;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.stage.Stage;

public class ExcelViewer extends Application {

    @Override
    public void start(Stage primaryStage) {
        TableView table = new TableView();
        // Assume we have a CSV file converted to objects, ColumnA and ColumnB
        TableColumn<Person, String> columnA = new TableColumn<>("ColumnA");
        columnA.setCellValueFactory(new PropertyValueFactory<>("columnA"));
        TableColumn<Person, String> columnB = new TableColumn<>("ColumnB");
        columnB.setCellValueFactory(new PropertyValueFactory<>("columnB"));

        table.getColumns().addAll(columnA, columnB);
        // Populate the table with data

        Scene scene = new Scene(new Group(table), 300, 200);
        primaryStage.setScene(scene);
        primaryStage.show();
    }

    public static void main(String[] args) {
        launch(args);
    }
}

To summarize, when it comes to reading Excel files in Java, developers have multiple options each with unique strengths:

• Apache POI: Ideal for comprehensive Excel file manipulation.
• JExcelApi: Best for older .xls formats with simpler operations.
• JXL: A straightforward approach for reading .xls files.
• OpenCSV: Useful for CSV files which can be generated from Excel.
• JavaFX: Provides a graphical interface for viewing Excel-like data in a user-friendly manner.

Each method caters to different needs, from handling complex spreadsheets to quick and simple data extraction. Knowing when to apply each method can streamline your workflow and ensure that your application interacts with Excel data efficiently and effectively. Whether you're dealing with legacy files or need to incorporate Excel reading into a graphical user interface, these methods offer the flexibility Java developers require.

What is the difference between .xls and .xlsx file formats?

+

The .xls format is used in Excel 97-2003 files and is based on Binary Interchange File Format (BIFF). Conversely, .xlsx is the default file format for Excel 2007 and later, utilizing Office Open XML (OOXML), an XML-based file format.

Can I read password-protected Excel files with these libraries?

+

Yes, with Apache POI, you can read password-protected Excel files by using POI’s BiffViewer class or related tools. JExcelApi and JXL do not support this feature out of the box.

Are these libraries free to use?

+

Apache POI, JExcelApi, and OpenCSV are free and open-source. JXL is also free but requires a small attribution fee for commercial use.