Read Excel File in Java with Apache POI

Excel files are widely used to maintain large datasets and for statistical data analysis. Reading Excel files in Java applications is a widely used feature for accessing and parsing data. If you are a Java application developer who intends to provide the functionality of reading Excel files in Java, Apache POI for Java should be your obvious choice.

In our previous article for Creating Excel Files in Java using Apache POI, we showed the basics of creating an Excel workbook. In this article, we’ll be exploring how to read Excel files in Java using the Apache POI library. So, let’s get started by working with the API.

How to Read Excel Files in Java?

Apache POI is a Java library for working with Excel XLS/XLSX files in Java applications. Its XSSF component is specifically meant for working with OOXML files using the POI API. It lets you open an XLSX file in Java, traverse it rows/columns, select a Cell, and read its value.

POI APIs for Reading Excel Files

Apache POI API lets you read Excel files in Java using the XSSF component. The steps involved in reading the XLSX file in Java using Apache POI are as follows.

1. Open the workbook using the API
2. Get to the desired sheet
3. Increment row number
4. Iterate over all cells in a row
5. Repeat steps 3 and 4 until all data is read

The Apache POI API calls used in this process are as follows.

Read Excel File in Java – Example

The following code sample is used to read XLSX files in Java with Apache POI API.

try
        {
            FileInputStream file = new FileInputStream("ExcelWorkbook.xlsx");
 
            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook wb = new XSSFWorkbook(file);
 
            //Get first/desired sheet from the workbook
            XSSFSheet ws = wb.getSheetAt(0);
 
            //Iterate through each rows one by one
            Iterator<Row> rowIterator = ws.iterator();
            while (rowIterator.hasNext()) 
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                 
                while (cellIterator.hasNext()) 
                {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) 
                    {
                        case NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                    }
                }
                System.out.println("Reading File Completed.");
            }
            file.close();
        } 
        catch (Exception ex) 
        {
            ex.printStackTrace();
        }

Explanation of the Java Example for Reading Excel Files

The above Java code uses Apache POI to read Excel files. It reads the Excel workbook using the instance of XSSFWorkbook class. After that, the required worksheet is accessed using the getSheetAt method of the instance. Once the sheet is accessible this way, the data in each cell is accessed by iterating over all the cells. Since each cell style can be different e.g. Date, Numeric, String, etc. the type of cell needs to be checked first before getting its contents.

Code Output

Here’s a sample output for the given program assuming that the ExcelWorkbook.xlsx file contains a simple spreadsheet like this

Sample Output

Name    Age	Salary  Reading File Completed.
John	25.0	5000.0	Reading File Completed.
Mary	30.0	6000.0	Reading File Completed.
Alice	28.0	5500.0	Reading File Completed.

Conclusion

Apache POI is a powerful Java API for working with Excel files from within your Java application. As shown in this article, you can read and access the contents of an Excel workbook using the API. In our follow-up series of blogs, we’ll further explore Apache POI API  for working with Excel workbooks.