5 Proven Methods to Extract Excel Data with Java

Excel data extraction is a common need in enterprise environments, where vast amounts of information often reside in Excel spreadsheets. Java, a language known for its platform independence and robust capabilities in handling file manipulation, provides developers with tools to effectively interact with Excel files. In this post, we will delve into five proven methods to extract Excel data using Java, catering to various use cases from simple data retrieval to complex data manipulation.

Method 1: Using Apache POI

Apache POI is perhaps the most widely used library for working with Microsoft Office documents, especially Excel files. Here’s how you can use it to extract data:

• Setup: Add the Apache POI dependencies to your project via Maven or Gradle.
• Code: Use the following snippet to open and read an Excel file:

  import org.apache.poi.ss.usermodel.*;
  import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  
  import java.io.FileInputStream;
  import java.io.IOException;
  
  public class ExcelExtractor {
      public static void main(String[] args) throws IOException {
          FileInputStream excelFile = new FileInputStream(“path/to/your.xlsx”);
          Workbook workbook = new XSSFWorkbook(excelFile);
          Sheet datatypeSheet = workbook.getSheetAt(0);
          for (Row row : datatypeSheet) {
              for (Cell cell : row) {
                  CellType type = cell.getCellType();
                  switch (type) {
                      case STRING:
                          System.out.println(cell.getStringCellValue());
                          break;
                      case NUMERIC:
                          System.out.println(cell.getNumericCellValue());
                          break;
                      case BOOLEAN:
                          System.out.println(cell.getBooleanCellValue());
                          break;
                      case BLANK:
                          System.out.println(“Blank Cell”);
                          break;
                      default:
                          System.out.println(“Unknown Cell Type: ” + type);
                  }
              }
          }
          excelFile.close();
      }
  }

Method 2: Java Excel API

The Java Excel API, another library, allows for reading and writing to Excel files:

• Setup: Download and add the library to your project’s classpath.
• Code:

  import jxl.*;
  
  public class ExtractDataJXL {
      public static void main(String args[]) throws Exception {
          Workbook workbook = Workbook.getWorkbook(new File(“path/to/your.xls”));
          Sheet sheet = workbook.getSheet(0);
          for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {
              for (int col = 0; col < sheet.getColumns(); col++) {
                  Cell cell = sheet.getCell(col, rowIndex);
                  System.out.println(cell.getContents());
              }
          }
          workbook.close();
      }
  }

Method 3: Using JExcel

JExcel offers a simpler interface for Excel operations:

• Setup: Include JExcel in your project build path.
• Code:

  import com.jexcel.*;
  
  public class JExcelExtractor {
      public static void main(String[] args) {
          try {
              Workbook workbook = Workbook.getWorkbook(new File(“path/to/your.xlsx”));
              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.println(cell.getContents());
                  }
              }
              workbook.close();
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  }

Method 4: Direct JDBC-ODBC Bridge

If Excel files are configured as a database through an ODBC driver, Java can use JDBC-ODBC:

• Setup: Configure an ODBC connection to your Excel file.
• Code:

  import java.sql.*;
  
  public class JDBCODBC {
      public static void main(String[] args) {
          try {
              Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
              String connectionString = “jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx)};DBQ=path/to/your.xls;”;
              Connection con = DriverManager.getConnection(connectionString);
              Statement stmt = con.createStatement();
              ResultSet rs = stmt.executeQuery(“SELECT * FROM [Sheet1$]”);
              while (rs.next()) {
                  System.out.println(rs.getString(“Column1”) + “ - ” + rs.getString(“Column2”));
              }
              rs.close();
              stmt.close();
              con.close();
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
  }

Method 5: Using CSV for Simple Extraction

If your Excel data can be converted to CSV, Java’s built-in CSV reading can be leveraged:

• Setup: Use a tool or Excel itself to convert your .xlsx file to .csv.
• Code:

  import com.opencsv.CSVReader;
  
  public class CSVEextractor {
      public static void main(String[] args) throws Exception {
          String csvFile = “path/to/your.csv”;
          try (CSVReader reader = new CSVReader(new FileReader(csvFile), ‘,’, ‘“’, 1)) {
              String[] line;
              while ((line = reader.readNext()) != null) {
                  System.out.println(“Line ” + line[0] + “ - ” + line[1]);
              }
          }
      }
  }

💡 Note: Each method above has its strengths. Apache POI and JExcel are great for complex data operations and formatting, while JDBC-ODBC works well if your Excel data needs to be queried like a database. CSV extraction is straightforward but requires a conversion step.

Summing up, extracting data from Excel files using Java can be done in numerous ways, each with its own set of benefits depending on the complexity of the task at hand. Whether you're dealing with simple data exports, complex data manipulation, or integrating Excel files into a larger database system, Java provides robust libraries and methodologies to handle these operations efficiently.

What is the best method for extracting large datasets from Excel files?

+

For large datasets, consider using Apache POI or JDBC-ODBC. POI handles both .xls and .xlsx files with excellent performance, while JDBC-ODBC can treat the Excel file as a database, allowing for complex queries.

Is it possible to extract data from an Excel file without any external libraries?

+

No, Java itself does not provide built-in capabilities to read Excel files directly. External libraries like Apache POI or JDBC-ODBC are necessary for this task.

What are the limitations of using CSV extraction?

+

CSV extraction is limited by the loss of Excel’s formatting and can struggle with complex Excel features like charts, macros, or formulas. Also, manual conversion is required, which might not preserve data accurately if not done carefully.