How to Read Excel File in Java (Tutorial)

What is Excel?

Microsoft Excel Workbook is a spreadsheet-based software that allows its users to organize and store data in an efficient way. It stores and organizes data in a tabular form i.e. using rows and columns. The cell in an excel spreadsheet is the box that holds data and can be manipulated with different styles, formatting, and formulas.

How to Read Excel Files in Java?

Reading an Excel file can be complicated at times. Reading Excel files in Java is also a little different to reading Word files in Java because of Excel's cells. The JDK does not provide a direct API to read or write Microsoft Excel documents. Instead, we have to rely on the third-party library Apache POI.

Apache POI Library

POI stands for “Poor Obfuscation Implementation.” Apache POI is an open-source Java library designed for reading and writing Microsoft documents. It offers a way to create and manipulate various file formats based on Microsoft Office. Using Apache POI, one should be able to perform create, modify and display/read operations on a range of Microsoft Office file formats.

Download and Install The Apache POI library

To begin, we'll download the latest version of the POI JARs. Navigate to http://poi.apache.org/download.html and download the latest ZIP file, which will contain the Java API to read an Excel file in Java.

Download Apache POI JAR Files

When you download the ZIP file, you need to unzip it and add the following JAR files to the classpath of your project. This is explained in Reading from an Excel File below.

Add the Apache POI Jar Files to the Java classpath

Note: Also copy the files from lib and ooxml-lib along with the other files.

Classes and Interfaces in POI

The following are the XLS and XLSX file format supported classes that can be used:

Apache POI Classes

The following is a list of different Java interfaces and classes in POI for reading XLS and XLSX files in Java:

The Workbook interface is implemented by the HSSFWorkbook and XSSFWorkbook classes.

• HSSFWorkbook: This is a class representation of the XLS file.
• XSSFWorkbook: This is a class representation of the XLSX file.

The Sheet interface is implemented by the HSSFSheet and XSSFSheet classes.

• HSSFSheet: This is a class representing a sheet in an XLS file.
• XSSFSheet: This is a class representing a sheet in an XLSX file.

The Row interface is implemented by the HSSFRow and XSSFRow classes.

• HSSFRow: This is a class representing a row in the sheet of an XLS file.
• XSSFRow: This is a class representing a row in the sheet of an XLSX file.

The Cell interface is implemented by the HSSFCell and XSSFCell classes.

• HSSFCell: This is a class representing a cell in a row of an XLS file.
• XSSFCell: This is a class representing a cell in a row of an XLSX file.

Reading from an Excel File

For our example, we will read the following Excel file in Java:

The Excel File that will be Read

Steps to Read Excel File in Java

1. Create a Java project using any Java IDE. We'll be using Netbeans for this project.
2. Next, create a lib folder in the project.
3. Then, add the downloaded JAR files to the lib folder created in the previous step.
4. Set the Class-Path by right-clicking the Project folder > Build Path > Add External JARs files > select all the above JAR files > Apply and close.
5. Now, let's create a class file with the name ReadExcelFileDemo.
6. Create an Excel file with the name "studentdata.xls" and write the above data or any data into it.

Below is the code sample for reading Excel files in Java:

import java.io.File;  
import java.io.FileInputStream;  
import java.io.IOException;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.FormulaEvaluator;  
import org.apache.poi.ss.usermodel.Row;  

public class ReadExcelFileDemo  {  
    public static void main(String args []) throws IOException  {  
        //obtaining input bytes from a file  
        FileInputStream fis=new FileInputStream(new File("C:\\demo\\studentdata.xls"));  
        //creating workbook instance that refers to .xls file  
        HSSFWorkbook wb=new HSSFWorkbook(fis);   
        //creating a Sheet object to retrieve the object  
        HSSFSheet sheet=wb.getSheetAt(0);  
        //evaluating cell type   
        FormulaEvaluator formulaEvaluator=wb.getCreationHelper().createFormulaEvaluator();  
        for(Row row: sheet) {  
            for(Cell cell: row) {
                switch(formulaEvaluator.evaluateInCell(cell).getCellType()) {  
                    case Cell.CELL_TYPE_NUMERIC: //field that represents numeric cell type  
                        //getting the value of the cell as a number  
                        System.out.print(cell.getNumericCellValue()+ "\t\t");   
                        break;  
                    case Cell.CELL_TYPE_STRING: //field that represents string cell type  
                        //getting the value of the cell as a string  
                        System.out.print(cell.getStringCellValue()+ "\t\t");  
                        break;
                }
            }
            System.out.println();  
        }
    }
}

import java.io.File;  
import java.io.FileInputStream;  
import java.io.IOException;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.FormulaEvaluator;  
import org.apache.poi.ss.usermodel.Row;  

public class ReadExcelFileDemo  {  
    public static void main(String args []) throws IOException  {  
        //obtaining input bytes from a file  
        FileInputStream fis=new FileInputStream(new File("C:\\demo\\studentdata.xls"));  
        //creating workbook instance that refers to .xls file  
        HSSFWorkbook wb=new HSSFWorkbook(fis);   
        //creating a Sheet object to retrieve the object  
        HSSFSheet sheet=wb.getSheetAt(0);  
        //evaluating cell type   
        FormulaEvaluator formulaEvaluator=wb.getCreationHelper().createFormulaEvaluator();  
        for(Row row: sheet) {  
            for(Cell cell: row) {
                switch(formulaEvaluator.evaluateInCell(cell).getCellType()) {  
                    case Cell.CELL_TYPE_NUMERIC: //field that represents numeric cell type  
                        //getting the value of the cell as a number  
                        System.out.print(cell.getNumericCellValue()+ "\t\t");   
                        break;  
                    case Cell.CELL_TYPE_STRING: //field that represents string cell type  
                        //getting the value of the cell as a string  
                        System.out.print(cell.getStringCellValue()+ "\t\t");  
                        break;
                }
            }
            System.out.println();  
        }
    }
}

Output:

IdNames
1Zeeshan
2Shoaib
3Umar
4Rizwan
5Ahsan

The steps are the same for reading xlsx files, except for the below two main points:

• Firstly, change the file format to .xlsx. The same file "studentdata" can be used, but with the .xlsx extension.
• Secondly, there is a difference in how we import Java Apache POI. The code goes as follows:

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 XLSXReaderExample  {

    public static void main(String [] args) {  
        try {  
            File file = new File("C:\\demo\\studentdata.xlsx");  
            FileInputStream fis = new FileInputStream(file);  
            //creating Workbook instance that refers to .xlsx file  
            XSSFWorkbook wb = new XSSFWorkbook(fis);   
            XSSFSheet sheet = wb.getSheetAt(0); 
            Iterator<Row> itr = sheet.iterator();
            while (itr.hasNext()) {  
                Row row = itr.next();  
                Iterator<Cell> cellIterator = row.cellIterator();   //iterating over each column  
                while (cellIterator.hasNext()) {  
                    Cell cell = cellIterator.next();  
                    switch (cell.getCellType()) {  
                        case Cell.CELL_TYPE_NUMERIC: //field that represents numeric cell type  
                            //getting the value of the cell as a number  
                            System.out.print(cell.getNumericCellValue() + "\t\t");   
                            break;
                        case Cell.CELL_TYPE_STRING: //field that represents string cell type  
                            //getting the value of the cell as a string  
                            System.out.print(cell.getStringCellValue() + "\t\t");  
                            break;
                    }
                }
                System.out.println("");  
            }  
        } catch(Exception e) {  
            e.printStackTrace();  
        }
    }
}

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 XLSXReaderExample  {

    public static void main(String [] args) {  
        try {  
            File file = new File("C:\\demo\\studentdata.xlsx");  
            FileInputStream fis = new FileInputStream(file);  
            //creating Workbook instance that refers to .xlsx file  
            XSSFWorkbook wb = new XSSFWorkbook(fis);   
            XSSFSheet sheet = wb.getSheetAt(0); 
            Iterator<Row> itr = sheet.iterator();
            while (itr.hasNext()) {  
                Row row = itr.next();  
                Iterator<Cell> cellIterator = row.cellIterator();   //iterating over each column  
                while (cellIterator.hasNext()) {  
                    Cell cell = cellIterator.next();  
                    switch (cell.getCellType()) {  
                        case Cell.CELL_TYPE_NUMERIC: //field that represents numeric cell type  
                            //getting the value of the cell as a number  
                            System.out.print(cell.getNumericCellValue() + "\t\t");   
                            break;
                        case Cell.CELL_TYPE_STRING: //field that represents string cell type  
                            //getting the value of the cell as a string  
                            System.out.print(cell.getStringCellValue() + "\t\t");  
                            break;
                    }
                }
                System.out.println("");  
            }  
        } catch(Exception e) {  
            e.printStackTrace();  
        }
    }
}

Output:

IdNames
1Zeeshan
2Shoaib
3Umar
4Rizwan
5Ahsan

The IronXL C# Library

IronXL is a standalone.NET library that facilitates reading and editing Microsoft Excel documents with C#. It neither requires that Microsoft Excel be installed, nor does it depend on Interop.

Using IronXL, developers can perform all Excel-related calculations effortlessly by simply writing a few lines of code and with fast performance. This could be for tasks such as adding two cells, calculating the grand total of a column, adding an entire column to an Excel table, adding an entire row to an Excel table, single and multi-row column summations, or many other tasks made easy through IronXL's helpful features.

IronXL completely supports the .NET Framework, .NET Core, Mobile, Xamarin, Azure Linux, and MacOS.

IronXL Feature Set

• Load, read and edit data from XLS/XLSX/CSV/TSV.
• Saving and exporting to XLS/XLSX/CSV/TSV/JSON.
• System.Data Objects — work with Excel Spreadsheets as System.Data.DataSet and System.Data.DataTable objects.
• Formulas — works with Excel formulas.
• Ranges — ["A1:B10"] syntax is easy to use.
• Sorting — sort rows, columns, and ranges.
• Styling — visual styles, font and font size, background pattern, border, alignment, and number formats.

Below is a code example of reading an Excel file in C# using IronXL:

using IronXL;
using System.Linq;

//Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();

//Select cells easily in Excel notation and return the calculated value
int cellValue = sheet ["A2"].IntValue;

// Read from Ranges of cells elegantly.
foreach (var cell in sheet ["A2:A10"]) {
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

using IronXL;
using System.Linq;

//Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.WorkSheets.First();

//Select cells easily in Excel notation and return the calculated value
int cellValue = sheet ["A2"].IntValue;

// Read from Ranges of cells elegantly.
foreach (var cell in sheet ["A2:A10"]) {
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

Imports IronXL
Imports System.Linq

'Supported spreadsheet formats for reading include: XLSX, XLS, CSV and TSV
Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.WorkSheets.First()

'Select cells easily in Excel notation and return the calculated value
Private cellValue As Integer = sheet ("A2").IntValue

' Read from Ranges of cells elegantly.
For Each cell In sheet ("A2:A10")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell

Working with IronXL makes a developer's job much easier. Its simple and easy-to-use code makes software less prone to bugs while working with Excel files.

Download IronXL and use it with your projects today.