5 Ways to Fetch Excel Data in Java

Working with Excel files is an integral part of many business processes. Whether you need to automate reports, perform bulk data operations, or simply migrate data from Excel to a database, Java provides a variety of methods to fetch data from Excel spreadsheets. Here are five effective ways to extract data from Excel files using Java, covering different tools and libraries to ensure you can choose the method best suited for your project needs.

Method 1: Using Apache POI

Apache POI is one of the most popular libraries for handling Microsoft Office documents, including Excel files in Java. Here’s how you can use it:

• Add Dependencies: First, include the POI library in your project by adding these dependencies to your pom.xml or building script:

  
      org.apache.poi
      poi
      5.2.0
  
  
      org.apache.poi
      poi-ooxml
      5.2.0
  
  
  

• Read Excel File:

  
  import org.apache.poi.ss.usermodel.*;
  import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  
  try (Workbook workbook = WorkbookFactory.create(new File("path/to/your/excelFile.xlsx"))) {
      Sheet sheet = workbook.getSheetAt(0);
      for (Row row : sheet) {
          for (Cell cell : row) {
              System.out.println(cell.toString());
          }
      }
  } catch (IOException e) {
      e.printStackTrace();
  }
  
  

📌 Note: Apache POI provides robust support for various Excel formats (.xls, .xlsx), making it versatile for different file types.

Method 2: Using JXL

The JExcelApi (JXL) library is another option for reading Excel files, particularly older formats like .xls.

• Add Dependencies: Add JXL to your project dependencies:

  
      net.sourceforge.jexcelapi
      jxl
      2.6.12
  
  
  

• Read Excel File:

  import jxl.*;
  import jxl.read.biff.BiffException;
  import java.io.IOException;
  
  try {
      Workbook workbook = Workbook.getWorkbook(new File("path/to/your/excelFile.xls"));
      Sheet sheet = workbook.getSheet(0);
      for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {
          for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {
              Cell cell = sheet.getCell(colIndex, rowIndex);
              System.out.println(cell.getContents());
          }
      }
  } catch (BiffException | IOException e) {
      e.printStackTrace();
  }
  
  

Method 3: Using OpenXML SDK

The Microsoft OpenXML SDK can read .xlsx files directly, bypassing the need for additional parsing libraries:

• Add Dependencies:

  
      org.apache.xmlbeans
      xmlbeans
      5.0.3
  
  
      com.microsoft.office">
      DocumentFormat.OpenXml
      2.19.0
  
  
  

• Read Excel File:

  
  import java.io.*;
  import org.apache.poi.xssf.usermodel.*;
  import com.microsoft.schemas.spreadsheetml.x2006.main.*;
  
  public static void readXLSX(String filePath) throws Exception {
      try (SpreadsheetDocument document = SpreadsheetDocument.open(new File(filePath))) {
          WorkbookPart workbookPart = document.getWorkbookPart();
          Sheet sheet = workbookPart.getSheetAt(0);
          for (Row row : sheet) {
              for (Cell cell : row) {
                  System.out.println(cell.getStringValue());
              }
          }
      }
  }
  
  

📍 Note: While OpenXML provides direct access to Excel file contents, it's specifically tailored for newer Excel formats (.xlsx, .xlsm).

Method 4: Using CSV

If you can convert your Excel to CSV, Java’s standard libraries can handle CSV parsing quite effectively:

• Simple CSV Reading:

  import java.io.*;
  import java.util.*;
  
  public void readCSV(String filePath) throws IOException {
      try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
          String line;
          while ((line = br.readLine()) != null) {
              String[] values = line.split(",");
              for (String value : values) {
                  System.out.println(value);
              }
          }
      }
  }
  
  

Method 5: Using Stream API

Java 8 introduced the Stream API, which can be paired with libraries like Apache POI for a more functional approach to reading Excel files:

• Stream API with POI:

  import org.apache.poi.ss.usermodel.*;
  import java.io.*;
  import java.util.stream.*;
  
  public static void streamReadExcel(String filePath) throws IOException {
      try (Workbook workbook = WorkbookFactory.create(new File(filePath))) {
          Sheet sheet = workbook.getSheetAt(0);
          IntStream.range(0, sheet.getLastRowNum() + 1).mapToObj(sheet::getRow).flatMap(row -> 
              IntStream.range(0, row.getLastCellNum()).mapToObj(row::getCell))
              .forEach(cell -> System.out.println(cell.toString()));
      }
  }
  
  

🔍 Note: This method leverages Java's functional programming capabilities, making the code more readable and potentially more efficient.

In wrapping up this comprehensive look at fetching Excel data in Java, it’s clear that there are multiple effective methods tailored to different needs:

• Apache POI offers full-fledged support for Excel file operations, making it a robust choice for various Excel formats.
• JXL serves well for older Excel formats, though it’s less maintained compared to POI.
• OpenXML SDK is ideal for direct manipulation of newer Excel files, providing fine-grained control over content.
• CSV conversion simplifies data access through Java’s standard libraries, reducing the dependency on external libraries.
• Java 8 Streams with POI combine modern Java constructs with established libraries, offering a clean and efficient way to process Excel data.

Each method has its own merits and can be chosen based on project requirements, the version of Excel files you deal with, and your familiarity with Java libraries. Remember, the best approach often depends on the specific context of your data handling tasks.

Which library should I use for .xls files?

+

For .xls files, JXL or Apache POI are both good choices. However, Apache POI is more actively maintained and supports newer file formats as well.

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

+

Apache POI and OpenXML SDK can handle password-protected files, though POI might need additional configuration or libraries for decryption.

What are the advantages of using the Stream API?

+

Stream API offers functional programming benefits like readability, ease of parallel processing, and the ability to easily manipulate and filter data without mutable state.