How to Read Excel File in Java

In this section, we are going to learn how we can read data from an excel file.

In Java, reading excel file is not similar to read word file because of cells in excel file. JDK does not provide direct API to read or write Microsoft Excel or Word document. We have to rely on the third-party library that is Apache POI.

What is Apache POI?

Apache POI (Poor Obfuscation Implementation) is a Java API for reading and writing Microsoft Documents in both formats .xls and .xlsx. It contains classes and interfaces. The Apache POI library provides two implementations for reading excel files:

HSSF (Horrible SpreadSheet Format) Implementation: It denotes an API that is working with Excel 2003 or earlier versions.
XSSF (XML SpreadSheet Format) Implementation: It denotes an API that is working with Excel 2007 or later versions.

Interfaces and Classes in Apache POI

Interfaces

Workbook: It represents an Excel Workbook. It is an interface implement by HSSFWorkbook and XSSFWorkbook.
Sheet: It is an interface that represents an Excel worksheet. A sheet is a central structure of a workbook, which represents a grid of cells. The Sheet interface extends java.lang.Iterable.
Row: It is also an interface that represents the row of the spreadsheet. The Row interface extends java.lang.Iterable. There are two concrete classes: HSSFRow and XSSFRow.
Cell: It is an interface. It is a high-level representation of a cell in a row of the spreadsheet. HSSFCell and XSSFCell implement Cell interface.

Classes

XLS Classes

HSSFWorkbook: It is a class representing the XLS file.
HSSFSheet: It is a class representing the sheet in an XLS file.
HSSFRow: It is a class representing a row in the sheet of XLS file.
HSSFCell: It is a class representing a cell in a row of XLS file.

XLSX Classes

XSSFWorkbook: It is a class representing the XLSX file.
XSSFSheet: It is a class representing the sheet in an XLSX file.
XSSFRow: It is a class representing a row in the sheet of XLSX file.
XSSFCell: It is a class representing a cell in a row of XLSX file.

Steps to read data from XLS file

Step 1: Create a simple Java project in eclipse.

Step 2: Now, create a lib folder in the project.

Step 3: Download and add the following jar files in the lib folder:

commons-collections4-4.1.jar Click Here
poi-3.17.jar Click Here
poi-ooxml-3.17.jar Click Here
poi-ooxml-schemas-3.17.jar Click Here
xmlbeans-2.6.0.jar Click Here

Step 4: Set the Class Path:

Right-click on the project ->Build Path ->Add External JARs -> select all the above jar files -> Apply and close.

Step 5: Now create a class file with the name ReadExcelFileDemo and write the following code in the file.

Step 6: Create an excel file with the name “student.xls” and write some data into it.

Step 7: Save and run the program.

Example of reading excel file (.xls) file

  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\student.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)     //iteration over row using for each loop  {  for(Cell cell: row)    //iteration over cell using for each loop  {  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()+ “tt”);   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()+ “tt”);  break;  }  }  System.out.println();  }  }  }  

Output:

Name        Age        Height  Swarit      23.0        5"  Puneet      25.0        6'1"  Swastik     22.0        5'5"  Tejas       12.0        4'9"

Reading XLSX File

All steps will remain same except file format.

Table: employee.xslx

Example of read excel file (.xlsx)

In this example we use XSSFWorkbook class.

  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\employee.xlsx”);   //creating a new file instance  FileInputStream fis = new FileInputStream(file);   //obtaining bytes from the file  //creating Workbook instance that refers to .xlsx file  XSSFWorkbook wb = new XSSFWorkbook(fis);   XSSFSheet sheet = wb.getSheetAt(0);     //creating a Sheet object to retrieve object  Iterator itr = sheet.iterator();    //iterating over excel file  while (itr.hasNext())   {  Row row = itr.next();  Iterator cellIterator = row.cellIterator();   //iterating over each column  while (cellIterator.hasNext())   {  Cell cell = cellIterator.next();  switch (cell.getCellType())   {  case Cell.CELL_TYPE_STRING:    //field that represents string cell type  System.out.print(cell.getStringCellValue() + “ttt”);  break;  case Cell.CELL_TYPE_NUMERIC:    //field that represents number cell type  System.out.print(cell.getNumericCellValue() + “ttt”);  break;  default:  }  }  System.out.println(“”);  }  }  catch(Exception e)  {  e.printStackTrace();  }  }  }  

Output:

Employee ID   Employee Name    Salary     Designation          Department  1223.0         Harsh           20000.0    Marketing Manager    Marketing  3213.0         Vivek           15000.0    Financial Advisor    Finance  6542.0         Krishna         21000.0    HR Manager           HR  9213.0         Sarika          34000.0    Sales Manager       Sales

Reading a particular cell value from a excel file (.xlsx)

Table: EmployeeData.xlsx

Example

In the following example, we read the value of the 2^nd row and the 2^nd column. The row and column counting start from 0. So the program returns “Software Engineer.”

  //reading value of a particular cell  import java.io.FileInputStream;  import java.io.FileNotFoundException;  import java.io.IOException;  import org.apache.poi.ss.usermodel.Cell;  import org.apache.poi.ss.usermodel.*;  import org.apache.poi.ss.usermodel.Sheet;  import org.apache.poi.ss.usermodel.Workbook;  import org.apache.poi.xssf.usermodel.XSSFWorkbook;  public class ReadCellExample   {  public static void main(String[] args)   {  ReadCellExample rc=new ReadCellExample();//object of the class  //reading the value of 2nd row and 2nd column  String vOutput=rc.ReadCellData(2, 2);   System.out.println(vOutput);  }  //method defined for reading a cell  public String ReadCellData(int vRow, int vColumn)  {  String value=null;//variable for storing the cell value  Workbook wb=null;//initialize Workbook null  try  {  //reading data from a file in the form of bytes  FileInputStream fis=new FileInputStream(“C:\demo\EmployeeData.xlsx”);  //constructs an XSSFWorkbook object, by buffering the whole stream into the memory  wb=new XSSFWorkbook(fis);  }  catch(FileNotFoundException e)  {  e.printStackTrace();  }  catch(IOException e1)  {  e1.printStackTrace();  }  Sheet sheet=wb.getSheetAt(0);//getting the XSSFSheet object at given index  Row row=sheet.getRow(vRow);//returns the logical row  Cell cell=row.getCell(vColumn);//getting the cell representing the given column  value=cell.getStringCellValue();//getting cell value  return value;//returns the cell value  }  }  

Output:

Software Engineer

Next TopicJava Tutorial

How to Read Excel File in Java

How to Read Excel File in Java

What is Apache POI?

Interfaces and Classes in Apache POI

Interfaces

Classes

Steps to read data from XLS file

Reading XLSX File

Reading a particular cell value from a excel file (.xlsx)

How to Enable Java in Chrome

How to Verify Java Version

You may also like