5 Simple Ways to Extract Excel Data in Java

Getting Started with Excel Data Extraction in Java

Extracting data from Excel files is a common task in software development, especially when dealing with data analysis, reporting, or any business intelligence related work. Java, with its robust ecosystem of libraries, offers multiple ways to handle Excel files efficiently. Here, we'll explore five simple methods to extract Excel data using Java, focusing on accessibility, performance, and ease of implementation.

1. Using Apache POI

Apache POI is one of the most widely used libraries for working with Microsoft Office file formats in Java. It provides a comprehensive API for reading and writing Excel files in both .xls (HSSF - Horrible SpreadSheet Format) and .xlsx (XSSF - XML Spreadsheet Format) formats.

• Setup: Add the Apache POI dependencies to your project, which can be done through Maven or by manually adding the .jar files.
• Code Example:

  import org.apache.poi.ss.usermodel.*;
  
  public class ExcelReader {
      public static void main(String[] args) throws Exception {
          Workbook workbook = WorkbookFactory.create(new File("example.xlsx"));
          Sheet sheet = workbook.getSheetAt(0);
          Iterator rowIterator = sheet.iterator();
  
          while (rowIterator.hasNext()) {
              Row row = rowIterator.next();
              Iterator cellIterator = row.cellIterator();
  
              while (cellIterator.hasNext()) {
                  Cell cell = cellIterator.next();
                  switch (cell.getCellType()) {
                      case STRING: System.out.print(cell.getStringCellValue() + "\t");
                          break;
                      case NUMERIC: System.out.print(cell.getNumericCellValue() + "\t");
                          break;
                      // Handle other cell types...
                  }
              }
              System.out.println();
          }
          workbook.close();
      }
  }
      

2. JExcelApi

JExcelApi is an alternative library for handling Excel files. It’s known for its simplicity and was initially designed to work with the older .xls format but has since been updated to support .xlsx with some limitations.

• Setup: Similar to Apache POI, include JExcelApi in your project.
• Code Example:

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

3. OpenCSV with Excel Output

While primarily designed for handling CSV files, OpenCSV can be used with additional libraries or processes to extract data from Excel and convert it into CSV format for easy parsing.

• Setup: Add OpenCSV dependency.
• Code Example: (Assuming conversion to CSV beforehand)

  import com.opencsv.*;
  
  public class CSVReaderExample {
      public static void main(String[] args) {
          try (CSVReader reader = new CSVReader(new FileReader("converted.csv"))) {
              String[] line;
              while ((line = reader.readNext()) != null) {
                  for (String value : line) {
                      System.out.print(value + "\t");
                  }
                  System.out.println();
              }
          }
      }
  }
      

4. Using Tika

Apache Tika can extract content from various document formats, including Excel, without requiring specific knowledge of the file structure.

• Setup: Add Apache Tika and necessary parsers to your project.
• Code Example:

  import org.apache.tika.*;
  import org.apache.tika.exception.TikaException;
  import org.xml.sax.*;
  
  public class TikaExample {
      public static void main(String[] args) throws Exception, TikaException {
          BodyContentHandler handler = new BodyContentHandler();
          Metadata metadata = new Metadata();
          File excelFile = new File("example.xlsx");
          FileInputStream inputstream = new FileInputStream(excelFile);
          ParseContext pcontext = new ParseContext();
  
          // Parsing the document using Tika
          AutoDetectParser parser = new AutoDetectParser();
          parser.parse(inputstream, handler, metadata, pcontext);
  
          System.out.println("Contents of the document: " + handler.toString());
          System.out.println("Metadata of the document:");
          String[] metadataNames = metadata.names();
          for(String name : metadataNames) {
              System.out.println(name + ": " + metadata.get(name));
          }
      }
  }
      

❗ Note: Tika can be particularly useful for extracting text from documents when the structure isn't critical for processing.

5. Converting to XML with Excel

Using Excel’s built-in XML features, you can save your spreadsheets in XML format, which Java can easily parse.

• Setup: No external library is necessary; Java has built-in XML parsing capabilities.
• Steps:
  1. In Excel, save your file as an XML Spreadsheet 2003 (.xml).
  2. Use Java to parse this XML file.
• Code Example:

  import javax.xml.parsers.*;
  import org.w3c.dom.*;
  
  public class XMLExcelReader {
      public static void main(String[] args) throws Exception {
          DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
          DocumentBuilder builder = factory.newDocumentBuilder();
          Document doc = builder.parse("example.xml");
  
          doc.getDocumentElement().normalize();
          NodeList nList = doc.getElementsByTagName("Row");
          for (int temp = 0; temp < nList.getLength(); temp++) {
              Node node = nList.item(temp);
              if (node.getNodeType() == Node.ELEMENT_NODE) {
                  Element eElement = (Element) node;
                  NodeList cells = eElement.getElementsByTagName("Cell");
                  for (int j = 0; j < cells.getLength(); j++) {
                      System.out.print(getTagValue("Data", (Element)cells.item(j)) + "\t");
                  }
                  System.out.println();
              }
          }
      }
  
      private static String getTagValue(String sTag, Element eElement) {
          NodeList nlList = eElement.getElementsByTagName(sTag).item(0).getChildNodes();
          Node nValue = (Node) nlList.item(0);
          return nValue.getNodeValue();
      }
  }
      

Summing up, there are numerous ways to extract data from Excel in Java, each with its own advantages:

• Apache POI for comprehensive Excel manipulation.
• JExcelApi for simplicity and speed.
• OpenCSV for quick CSV parsing post-conversion.
• Apache Tika for document content extraction with less focus on structure.
• Converting to XML provides an alternative, sometimes simpler approach if the file is not too complex.

These methods highlight the flexibility Java offers when dealing with Excel data, making it an excellent choice for data extraction and manipulation in various application contexts.

What are the differences between Apache POI and JExcelApi?

+

Apache POI supports both .xls and .xlsx formats with extensive features for reading, writing, and formatting cells, while JExcelApi, initially for .xls, now offers limited support for .xlsx with simpler functionality.

Can I use Java to extract data from encrypted Excel files?

+

Yes, libraries like Apache POI can handle encrypted Excel files, provided you know the password. However, the process might involve extra steps to decrypt the file first.

Why would someone choose to convert Excel to XML?

+

Converting Excel to XML can be beneficial for web applications or when you need to pass data in a universally parsable format. It also simplifies data handling when the focus is on content rather than complex spreadsheet features.