How to Read Excel file in Java?
In today’s computer world, excel files are an integral part of every business ranging from small to large. The uses of excel files include data management, analysis, charting and graphing, etc. So when we come to the programming side, there will be a situation where we have to handle excel files. In java programming, there is no built-in feature to support excel files. Yet we can handle excel files easily by using the third-party API named Apache POI. In today’s article, we will look at how to read excel file in java using the Apache POI API.
Excel files:
We all know that excel files are spreadsheets that make data handling and manipulation easier.
Generally, there are two file formats available for excel files. They are
1. .xls
2. .xlsx.
The xls format is the older one for 2003 and older versions. And xlsx is a new format for excel 2007 and later.
Apache POI:
Generally, Apache POI (Poor Obfuscation Implementation) is an open-source library for handling Microsoft office related files. Java does not have the built-in feature to handle excel files. So, We use Apache POI, a third-party Java API with classes and interfaces to deal with reading and writing operations in excel files.
This API supports both .xls and .xlsx formats. To deal with the .xls format, we use HSSF(Horrible SpreadSheet Format) implementation. And to deal with the .xlsx format, we use XSSF(XML SpreadSheet Format) implementation.
Classes:
We all know that Excel files consist of Workbook, WorkSheets, cells, rows, and columns. The API comprises a variety of classes to incorporate the functionality that deals with excel files. There are separate classes available to deal with .xls and .xlsx formats.
HSSFWorkbook, HSSFSheet, HSSFRow, and HSSFCell are the classes that provide the functionality for handling workbooks, sheets, rows, and cells in xls format files, respectively.
Similarly, the XSSFWorkbook, XSSFSheet, XSSFRow, and XSSFCell are the classes that provide the functionality for handling workbooks, sheets, rows, and cells in xlsx format files, respectively.
Interface:
This API provides some common interfaces for all the classes discussed above. They are:
1. Workbook – Workbook is a common interface implemented by both HSSFWorkbook and XSSFWorkbook classes.
2. Sheet – The HSSFSheet and XSSFSheet classes implement this interface.
3. Row – Both HSSFRow and XSSFRow classes use this interface.
4. Cell – The HSSFCell and XSSFCell classes implement this interface.
Prerequisites for using ApachePOI to read excel file in java:
The requirements for using ApachePOI are:
1. An eclipse IDE
2. poi-bin-5.2.3-20220909.zip folder
Extract the zip file. There is a list of jar files required to use ApachePOI.
You can download the latest versions of the jar if needed.
3. An excel file in both formats.
a. In this article, we will use the excel files named FirstCode.xls and FirstCode.xlsx.
b. The content of the files are:
Now follow the steps given below to set up the environment.
1. Open the Eclipse IDE
2. Create a new java project.
3. Add all the downloaded jar files in the classpath, as shown below.
Right-click the project name→ Build Path → Configure Build Path → Click the libraries tab → Add External jars in Classpath → Select all the jars discussed above → Click Apply and close. If you are using maven, you should include the following dependencies in your pom.xml file.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.0</version>
</dependency>
Code to read the xls file in java:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Scanner;
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 ReadExcelFile {
public static void main(String args[])
{
try {
//Creating a file object and getting the excel file.
File file= new File("E:\\FirstCode.xls");
// Creating FileInputStream object to read the file.
FileInputStream fisObject=new FileInputStream(file);
//Creating Workbook instance for .xls file.
HSSFWorkbook workbook=new HSSFWorkbook(fisObject);
//Getting the sheet number from the user.
System.out.println("Enter the sheet number:");
Scanner scan = new Scanner(System.in);
int sheetnumber = scan.nextInt();
//Creating a Sheet object to get the required sheet in the file.
HSSFSheet sheet=workbook.getSheetAt(sheetnumber);
//Evaluating cell type
FormulaEvaluator formulaEvaluator=workbook.getCreationHelper().createFormulaEvaluator();
//Iterating the rows with, for each loop.
for(Row row: sheet)
{
//Iterating the cells with, for each loop.
for(Cell cell: row)
{
switch(formulaEvaluator.evaluateInCell(cell).getCellType())
{
//Numeric cell type.
case NUMERIC:
//Getting the cell value as a number and printing it.
System.out.print(cell.getNumericCellValue()+ "\t\t");
break;
//String cell type.
case STRING:
//Getting the cell value as a string and printing it.
System.out.print(cell.getStringCellValue()+ "\t\t");
break;
default:
break;
}
}
System.out.println();
}
workbook.close();
}
catch(FileNotFoundException exception) {
//If there is no excel file with the given name found in the specified location, it throws FileNotFoundException.
System.out.println("File Not Found.");
}
catch(IllegalArgumentException exception) {
//If the sheet is empty, it throws an IllegalArgumentException.
System.out.println("Sheet is empty");
}
catch(Exception exception) {
//To deal with other Exceptions.
exception.printStackTrace();
}
}
}
Output:
- When the excel file reading is successful, whole file will be displayed as output.
- When there is no excel file with the given name found in the specified location, the output will be:
File Not Found
- When there is no data in the given sheet index, the output will be:
Sheet is empty
Code to read the xlsx file in java:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Iterator;
import java.util.Scanner;
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 ReadExcelFile
{
public static void main(String[] args)
{
try
{
//Creating a file object and getting the excel file.
File file = new File("E:\\FirstCode.xlsx");
// Creating FileInputStream object to read the file.
FileInputStream fisObject = new FileInputStream(file);
//Creating Workbook instance for .xlsx file.
XSSFWorkbook workbook = new XSSFWorkbook(fisObject);
//Getting the sheet number from the user.
System.out.println("Enter the sheet number:");
Scanner scan = new Scanner(System.in);
int sheetnumber = scan.nextInt();
//Creating a Sheet object to get the required sheet in the file.
XSSFSheet sheet = workbook.getSheetAt(sheetnumber);
//Using an iterator to iterate the sheet object to get rows.
Iterator<Row> itr = sheet.iterator();
while (itr.hasNext())
{
// Getting the row values.
Row row = itr.next();
//Iterating each column in the row.
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
//Getting column values.
Cell cell = cellIterator.next();
//Getting cell type.
switch (cell.getCellType())
{
//String cell type.
case STRING:
//Getting the cell value as a string and printing it.
System.out.print(cell.getStringCellValue() + "\t\t\t");
break;
//Numeric cell type.
case NUMERIC:
//Getting the cell value as a number and printing it.
System.out.print(cell.getNumericCellValue() + "\t\t\t");
break;
default:
break;
}
}
System.out.println("");
}
workbook.close();
}
catch(FileNotFoundException exception)
{
//If there is no excel file with the given name found in the specified location, it throws FileNotFoundException.
System.out.println("File Not Found.");
}
catch(IllegalArgumentException exception)
{
//If the sheet is empty, it throws an IllegalArgumentException.
System.out.println("Sheet is empty");
}
catch(Exception exception)
{
//To deal with other Exceptions.
exception.printStackTrace();
}
}
}
Output:
- When the excel file reading is successful, the output will be the whole file.
- When there is no excel file with the given name found in the specified location, the output will be:
File Not Found
- When there is no data in the given sheet index, the output will be:
Sheet is Empty
Code to read a particular cell value in the xlsx file in java:
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Scanner;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile {
public static void main(String[] args)
{
//Creating an object for the ReadExcelFile class.
ReadExcelFile rc=new ReadExcelFile();
System.out.println("Enter the sheet, row, and column number of the cell:");
//Getting the input for sheet, row, and column number, from the user.
Scanner scan = new Scanner(System.in);
int sheetnumber = scan.nextInt();
int row = scan.nextInt();
int column = scan.nextInt();
//ReadCellData function call
String vOutput=rc.ReadCellData(row, column, sheetnumber);
//Checking whether the return value is null. The method returns null when the sheet or cell is empty.
if(vOutput!=null)
//Printing the cell value.
System.out.println(vOutput);
}
//Method for getting the cell value.
public String ReadCellData(int vRow, int vColumn, int vsheet)
{
//Variable to store the cell value.
String value=null;
//Initializing Workbook object as null.
Workbook workbook=null;
try
{
// Creating FileInputStream object to read the file.
FileInputStream fis=new FileInputStream("E:\\FirstCode.xlsx");
//Creating Workbook instance for .xls file.
workbook=new XSSFWorkbook(fis);
//Creating a Sheet object to get the required sheet in the file.
Sheet sheet=workbook.getSheetAt(vsheet);
// Getting the specified row.
Row row=sheet.getRow(vRow);
// Getting the specified column.
Cell cell=row.getCell(vColumn);
// Getting the specified cell value.
value=cell.getStringCellValue();
}
catch(FileNotFoundException exception)
{
//If there is no excel file with the given name found in the specified location, it throws FileNotFoundException.
System.out.println("File Not Found.");
}
catch(IOException exception)
{
System.out.println("Invalid inputs.");
}
catch(NullPointerException exception)
{
//If the cell is empty, it throws a NullPointerException.
System.out.println("No data in the specified cell");
}
catch(IllegalArgumentException exception)
{
//If the sheet is empty, it throws an IllegalArgumentException.
System.out.println("Empty sheet");
}
catch(Exception exception)
{
//To deal with other Exceptions.
exception.printStackTrace();
}
// Returns the corresponding cell value.
return value;
}
}
Output:
- When the excel file reading is successful, the output will be the desired value.
- When there is no excel file with the given name found in the specified location, the output will be:
File Not Found
- When there is no data in the given sheet index, the output will be:
Empty Sheet
- When there is no data in the given cell, the output will be:
No data in the specified cell
Code to read xls and xlsx files in java:
import java.io.File;
import java.io.FileNotFoundException;
import java.util.Scanner;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ReadExcelFile {
public static final String FILE_PATH = "E:\\FirstCode.xlsx";
public static void main(String[] args) {
try {
//Creating Workbook instance for excel file for both format .xls and .xlsx file.
Workbook workbook = WorkbookFactory.create(new File(FILE_PATH));
// Getting the number of sheets in the Workbook.
System.out.println("Workbook consists of " + workbook.getNumberOfSheets() + " Sheets : ");
System.out.println("Enter the sheet index number:");
//Getting the sheet number from the user.
Scanner scan = new Scanner(System.in);
int sheetnumber = scan.nextInt();
//Creating a Sheet object to get the required sheet in the file.
Sheet sheet = workbook.getSheetAt(sheetnumber);
// Create a DataFormatter to format each cell value as String.
DataFormatter dataFormatter = new DataFormatter();
//Using forEach loop with lambda(Java8).
sheet.forEach(row -> {
row.forEach(cell -> {
String cellValue = dataFormatter.formatCellValue(cell);
System.out.print(cellValue + "\t\t");
});
System.out.println();
});
// Closing the workbook object.
workbook.close();
}
catch(FileNotFoundException exception)
{
//If there is no excel file with the given name found in the specified location, it throws FileNotFoundException.
System.out.println("File Not Found.");
}
catch(IllegalArgumentException exception)
{
//If the sheet is empty, it throws an IllegalArgumentException.
System.out.println("Sheet is empty");
}
catch(Exception exception)
{
//To deal with other Exceptions.
exception.printStackTrace();
}
}
}
Output:
- When the excel file reading is successful, the output will be as desired.
- When there is no excel file with the given name found in the specified location, the output will be:
File not Found
- When there is no data in the given sheet index, the output will be:
Sheet is Empty
Additional tips:
If your excel file has more than one sheet, you can get the number of sheets and iterate over them. Refer to the code snippet for a better understanding.
Code snippet:
workbook.forEach(sheet -> {
System.out.println("=> " + sheet.getSheetName());
});
To iterate among the sheets or rows, or cells, you can use one of the following ways:
1. for each loop with a lambda expression.
2. for each loop
3. Iterator
I demonstrated each iterating way in the sample codes above for your reference.
Summary
There is another API for handling excel files named JXL or JEXCEL. But this API supports only xls files and does not support xlsx files. Its last update was in 2009. So everyone uses ApachePOI now. I hope you understand how to use ApachePOI to read excel files in java. Thank you for reading.