How to Read Excel File in Java

  2. HowTo
  3. Java Howtos
  4. How to Read Excel File in Java
Haider Ali Feb 02, 2024
Java Java Excel
  1. Read an Excel File in Java
  2. Overview of APACHE POI Library
How to Read Excel File in Java

This article educates the necessary information to read an excel file in the Java program. What software to use? What steps to follow, and last but not least, how to apply the libraries so the user can read the excel file. So, let us begin.

Read an Excel File in Java

In Java, reading an excel file is not easy as the other file formats, like Microsoft Word. However, it can be easily understood why, as excel sheets contain cells.

JDK (Java Development Kit) does not allow the user to handle files such as Microsoft Excel and Microsoft Word. Furthermore, it does not provide the user with the required API, so we have no choice but to rely on third-party libraries to do our tasks.

To read an excel file in Java, we would have to learn about the library APACHE POI because it can work for both .xls and .xlsx forms.

Overview of APACHE POI Library

The library, APACHE POI, also known as Poor Obfuscation Implementation, provides the user with two implementations. One is HSSF, and the other is XSSF.

  1. HSSF - The HSSF, Horrible Spread Sheet Format, Implementation denotes the user with an API that works for earlier versions of Microsoft Excel, more specifically Excel 2003 and earlier.
  2. XSSF - XSSF, XML Spread Sheet Format, Implementation denotes the user with an API that works for later versions of Microsoft Excel. In this case, it is for Excel 2007+ versions. We can also use this implementation to work with the .xlsx format.

Let’s look into its interfaces and classes to understand its work further.

Interfaces and Classes

Note that all of the below-mentioned interfaces work for both HSSF and XSSF.

  1. workbook - The workbook is an interface that represents Excel Workbook. It has two classes, HSSFWorkbook and XSSFWorkbook.

  2. sheet - As we know, the central structure of a workbook is known as a worksheet. So a sheet is an interface that represents Excel Worksheet.

    It is an extension to the library java.lang.Iterable. The two classes for this interface are HSSFSheet and XSSFSheet.

  3. row - It is an interface that represents the row of an Excel Sheet. This interface is also an extension of the library java.lang.Iterable. It has two classes, HSSFRow and XSSFRow.

  4. cell - It is an interface that represents the cells in a row of an Excel Sheet. It also has two classes called HSSFCell and XSSFCell.

Let us look into the steps to read an excel file now that we know what the library APACHE POI contains and what interfaces and classes we can use.

Steps to Read an Excel File in Java

These steps are straightforward to follow.

  • Create a lib folder

    Create a folder named lib in the Java project that the user will use to open and read the Excel file.

  • Download jar files

    This step requires the user to download some jar files in the lib folder created in the previous step. You need to download commons-collections4-4.1.jar, poi-3.17.jar, poi-ooxml-3.17.jar, poi-ooxml-schemas-3.17.jar and xmlbeans-2.6.0.jar

  • Create path to add jar files

    In this step, we have to create the path to add the jar files we downloaded in the previous step. To do that, right-click on a Java project we created in step 1.

    Then we will build our path for the jar files by clicking on the build path option. Finally, we will click on Apply and close after adding the external’ jar’ files.

  • Create a Java Class

    Create a Java class file in this step, and then we can call on our desired excel file to read the data. Here is an example of a class file that reads an Excel file.

    The XSSF will be used in this example as it uses Excel 2007+ versions.

    import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Example {
  public static void main(String[] args) {
    try {
      File ExcelFile = new File("D:\\your_filename.xlsx");
      // obtaining bytes from the file
      FileInputStream FileStream = new FileInputStream(ExcelFile);
      // using the Workbook interface
      XSSFWorkbook ExcelWorkbook = new XSSFWorkbook(FileStream);

      // using the sheet class to get the object
      XSSFSheet ExcelSheet = ExcelWorkbook.getSheetAt(0);
      // iterating over excel file, rows.
      Iterator<Row> IterateRows = ExcelSheet.iterator();
      while (IterateRows.hasNext()) {
        Row ExcelRow = IterateRows.next();

        // iterating over the column
        Iterator<Cell> IterateCells = ExcelRow.cellIterator();

        while (IterateCells.hasNext()) {
          Cell ExcelCell = IterateCells.next();

          switch (ExcelCell.getCellType()) {
            case Cell.CELL_TYPE_STRING: // represents string cell type
              System.out.print(ExcelCell.getStringCellValue() + "\t");
              break;
            case Cell.CELL_TYPE_NUMERIC: // represents number cell type
              System.out.print(ExcelCell.getNumericCellValue() + "\t");
              break;
            default:
          }
        }
        System.out.println("");
      }
    } catch (Exception exp) {
      exp.printStackTrace();
    }
  }
}

Using this example code, we can easily understand how to use the library APACHE POI. The output of this example code will be the excel file data that the user will provide.

Author: Haider Ali
Haider Ali avatar Haider Ali avatar

Haider specializes in technical writing. He has a solid background in computer science that allows him to create engaging, original, and compelling technical tutorials. In his free time, he enjoys adding new skills to his repertoire and watching Netflix.

LinkedIn

Related Article - Java Excel