5 Ways to Extract Excel Data in Java

<p>Extracting data from an Excel file in Java is a common task for developers working on data analysis, reporting, or applications requiring manipulation of spreadsheet information. Java's ecosystem provides several libraries that make this task relatively straightforward. This post will explore five different methods to achieve this goal, ensuring you can choose the one best suited for your project needs.</p>

<h2>1. Using Apache POI</h2>
<p>Apache POI is one of the most popular libraries for dealing with Excel files in Java:</p>
<ul>
    <li><strong>Download the Library:</strong> First, you need to include POI in your project. You can do this via Maven, Gradle, or by downloading the jars.</li>
    <li><strong>Code Implementation:</strong></li>
</ul>

```java
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;

public class ApachePOIDemo {
    public static void main(String[] args) {
        try {
            FileInputStream excelFile = new FileInputStream(new File("example.xlsx"));
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();

            while (iterator.hasNext()) {
                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();
                
                while (cellIterator.hasNext()) {
                    Cell currentCell = cellIterator.next();
                    if (currentCell.getCellType() == CellType.STRING) {
                        System.out.print(currentCell.getStringCellValue() + " ");
                    } else if (currentCell.getCellType() == CellType.NUMERIC) {
                        System.out.print(currentCell.getNumericCellValue() + " ");
                    }
                }
                System.out.println();
            }
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

💡 Note: Apache POI can handle both .xlsx (XSSF) and .xls (HSSF) formats, making it versatile for different Excel versions.

2. JExcelApi

If you prefer a library that focuses solely on Excel files:

• Features: Lightweight, does not require external dependencies.
• Code Example:

import jxl.*;
import java.io.File;
import java.util.Arrays;

public class JExcelDemo {
    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++) {
                    Cell cell = sheet.getCell(col, row);
                    System.out.print(cell.getContents() + " ");
                }
                System.out.println();
            }
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3. JExcel

For a newer alternative with a more modern API:

• Advantages: Streamlined API, designed to work with newer Java versions.
• Sample Code:

import io.github.vietgo.project04.jexcel.*;
import java.io.File;

public class JExcelReader {
    public static void main(String[] args) {
        try {
            JExcelReader reader = new JExcelReader("example.xlsx");
            Worksheet sheet = reader.getWorksheet(0);
            
            for (Row row : sheet) {
                for (Cell cell : row) {
                    System.out.print(cell.getValue() + " ");
                }
                System.out.println();
            }
            reader.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

4. OpenCSV for CSV

Although Excel is the focus, Excel files can be saved as CSV for easier manipulation:

• Advantage: CSV files are simpler to read/write compared to binary Excel formats.
• Code Snippet:

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

public class OpenCSVDemo {
    public static void main(String[] args) {
        try {
            CSVReader reader = new CSVReader(new FileReader("example.csv"));
            String[] line;
            while ((line = reader.readNext()) != null) {
                System.out.println(Arrays.toString(line));
            }
            reader.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

📝 Note: Ensure your Excel file is saved in a format that can be easily read by the library you choose.

5. JDBC-ODBC Bridge

For integration with databases, the JDBC-ODBC bridge can be used:

• Requirement: Microsoft’s Office is installed with the ODBC driver.
• Example:

import java.sql.*;

public class JDBCODBCDemo {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            // Load the JDBC-ODBC bridge driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            // Setup the URL to connect to Excel file
            String dbUrl = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:/path/to/example.xlsx;READONLY=true;";
            conn = DriverManager.getConnection(dbUrl, "", "");

            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM [Sheet1$]");
            ResultSetMetaData metaData = rs.getMetaData();
            int columnsNumber = metaData.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("");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) conn.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

In summary, these five methods offer different approaches to extracting data from Excel files in Java. Apache POI stands out for its robust feature set and compatibility with both old and new Excel formats. JExcelApi provides a simple solution for reading .xls files. The modern JExcel library offers a new take on Excel manipulation. OpenCSV is ideal for those dealing with CSV versions of Excel files, and the JDBC-ODBC bridge allows for integration with database systems. Your choice would depend on your project's requirements, performance needs, and familiarity with the libraries.

Which library is best for dealing with large Excel files?

+

Apache POI is often recommended for handling large Excel files due to its efficient streaming capabilities which allow reading large spreadsheets without loading the entire file into memory.

Can I write back to Excel files with these libraries?

+

Yes, Apache POI and JExcelApi both support writing to Excel files as well. They provide methods to modify existing sheets or create new ones within an Excel document.

Is it necessary to know Microsoft Excel to use these libraries?

+

No, while understanding Excel can be helpful, these libraries provide APIs to interact with Excel files programmatically, abstracting away many Excel-specific concepts.

Are these libraries compatible with all versions of Java?

+

Apache POI is compatible with Java 8 or later. JExcelApi and OpenCSV are compatible with older versions of Java, but might require slight modifications for newer versions. Always check the library documentation for specific Java version compatibility.