Why & When you require to read Excel file using Apache POI?

Apache POI – Either you are a Developer or Automation Engineer, you must come across the requirement where you have to read data from Excel. It can be about reading your application report, business report and verify with Database or it may be for reading test data from the excel sheet. Also, Selenium Automation Data-Driven Framework majorly depends on Excel data and here good excel reader code can make your task easier.

In this tutorial, you will be learning “How to read excel file using Apache POI in Java & Selenium?” and we will proceed with step by step process along with the detailed description, snapshot, and our own excel reader code. After completing this tutorial you will be able to read the excel sheet using java program and add can further use the code in your automation utility/project.

Brief description about Excel workbook:

Excel workbook: Excel Workbook is a Microsoft Office product which is a collection of sheets and each sheet stores data in tabular format. Refer below for the sample workbook image:

What is Apache POI?

Apache POI: Apache POI is the wonderful java API that is widely used for reading and writing data in Microsoft Office Products like Microsoft Word, PowerPoint, and Excel. This tutorial is specifically based on Excel Reader.

Types of Excel Sheet: Microsoft excel sheets are categorized into two categories .xls format (files part of Microsoft 97-2003 ) and .xlsx files which are part of Microsoft 2007+. During code writing, you have to choose a suitable library as per your need. Refer below for popular libraries:

Pre requisite:

1. Selenium or Java Project set up on Machine.
2. Good to have a basic idea of the Excel sheet.

Now here are steps you have to follow for complete excel reader code:

Note: This blog is written for .xls file so we used HSSF library if you want to write for .xlsx file then use XSSF library.

Add Apache POI Maven Depedency:

• Step 1:

  Add Apache POI Maven dependency in  your POM.xml

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>

If you are using java project you have to go to Apache Maven website, download POI Jar, and add it in your project.

Website link : https://poi.apache.org/download.html

Download .zip file if you are using windows and download .gz file in case of Linux user.

• Unzip the downloaded zip file and add below jar files in your project and build.

List of Jars required for excel reader:

1. poi-3.10-FINAL.jar
2. poi-ooxml-3.10-FINAL.jar
3. commons-codec-1.5.jar
4. poi-ooxml-schemas-3.10-FINAL.jar
5. xml-apis-1.0.b2.jar
6. stax-api-1.0.1.jar
7. xmlbeans-2.3.0.jar
8. dom4j-1.6.1.jar

• Step 2

  : Go to Project and create ExcelReader class in the desired package.

• Step 3

  : Decide class level variables and make constructors.- Class level constructor used while object creation. Generally, we pass the value as an argument at the time of object creation. In this tutorial, we took FilePath and sheet index as a constructor and we will pass at the time of object creation.

private String filePath;
private int sheetIndex;
ExcelReader(String filePath, int sheetIndex) {
    this.filePath = filePath;
    this.sheetIndex = sheetIndex;
}

• Step 4:

i. To read the excel sheet first we have to load excel sheet using FileInputStream

FileInputStream fis = new FileInputStream(filePath);

ii. Then we have to create a workbook object and pass the input stream as an input parameter.

HSSFWorkbook workbook = new HSSFWorkbook(fis);

iii. Now access desired sheet using sheet index. To access sheet use method get SheetAt method.

HSSFSheet sheet = workbook.getSheetAt(sheetIndex);

Here is the complete method code: This method return desired sheet on the basis of sheet index.

• Step 5

  : Method to get the total number of Rows: TotalRowCount method return a total number of rows of excel sheet.

i. Make a sheet object which is to be read. (Using Step 3 method)
HSSFSheet sheet = get sheet();

ii. getLastRowNum() method provided by POI to get last row number from sheet.
int rowCount = sheet.getLastRowNum();

• Step 6: Method to get Total Number of column count:

i. Make sheet object and use getSheet() method.
HSSFSheet sheet = getSheet();

ii. Make Row object by getRow(int rowIndex)
Row row = sheet.getRow(0);

iii. Now use getLastCellNum() on row object it will return last column number ie total column number.
int lastColumnNum = row.getLastCellNum();

• Step 7

  : Method to get sheet count.

i. First load workbook using FileInputStream

ii. Create workbook object

iii. And use method getNumberOfSheets() on workbook object. It returns the count of the total number of sheets.

• Step 8

  : Method to get sheet Name.

1. First load workbook using FileInputStream
2. Create a workbook object
3. And use method getSheetName(int sheetIndex) on workbook object. It returns the name of the sheet name.

• Step 9

  : Now write Method to getCellValue(int rowNum, int ColumnName). This method return cell value as String for a given row and column id.

• Step 10:

  Write a method to read cell numeric, Boolean, or string. This method read any cell type data. Pass get Cell object as a parameter and it will return String of any type of Cell Data.

public String getCellValueAsString(Cell cell) {
    String cellValue = null;
    switch (cell.getCellType()) {
        case NUMERIC:
            cellValue = String.valueOf(cell.getNumericCellValue());
            break;
        case STRING:
            cellValue = cell.getStringCellValue();
            break;
        case BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        case FORMULA:
           cellValue= cell.getCellFormula();
        case BLANK:
            cellValue="BLANK";
        default:
            cellValue ="DEFAULT";
    }
    return cellValue;
}

Complete Code of Excel Reader: (Step 1 to Step 10)

package stepDef;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;
public class ExcelReader {
    private String filePath;
    private int sheetIndex;
    ExcelReader(String filePath, int sheetIndex) {
        this.filePath = filePath;
        this.sheetIndex = sheetIndex;
    }
    private HSSFSheet getSheet() throws IOException {
        FileInputStream fis = new FileInputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        return sheet;
    }
    public int totalRowCount() throws IOException {
        HSSFSheet sheet = getSheet();
        int rowCount = sheet.getLastRowNum();
        return rowCount;
    }
    public String getCellValue(int rowNum, int columnNum) throws IOException {
        HSSFSheet sheet = getSheet();
        Row row = sheet.getRow(rowNum);
        Cell cell = row.getCell(columnNum);
        String value = getCellValueAsString(cell);
        return value;
    }
    public String getSheetName(int index) throws IOException {
        FileInputStream file = new FileInputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        String sheet = workbook.getSheetName(index);
        return sheet;
    }
    public int getSheetCount() throws IOException {
        FileInputStream file = new FileInputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        int sheetCount = workbook.getNumberOfSheets();
        return sheetCount;
    }
    public int totolColumnCount() throws IOException {
        HSSFSheet sheet = getSheet();
        Row row = sheet.getRow(0);
        int lastColumnNum = row.getLastCellNum();
        return lastColumnNum;
    }
      public String getCellValueAsString(Cell cell) {
        String cellValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
               cellValue= cell.getCellFormula();
            case BLANK:
                cellValue="BLANK";
            default:
                cellValue ="DEFAULT";
        }
        return cellValue;
    }
}

• Step 11

  : Prepare test data in the .xls file

• Step 12

  . Create another class with the main method to test ExcelReader class method. Refer below code for

package stepDef;
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.Row;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Map;
public class ExcelReaderTest {
public static void main(String[] args) throws IOException {
// Create Object of ExcelReader class and pass workbook path as first argument 
//and sheet index as second argument
ExcelReader ex = new ExcelReader(" \\userData.xls", 0);
//use Excel Reader class  method totalRowCount() method to get total row count.
System.out.println("get Total row count: "+ex.totalRowCount());
//use  Excel Reader class method getSheetName(sheet Index) to get sheet Name        System.out.println("Sheet Name: "+ex.getSheetName(1));
//use  Excel Reader class method getSheetCount() to get total sheet count.
System.out.println("Sheet Count: "+ex.getSheetCount());
//use  Excel Reader class method totolColumnCount() to get total Column count   
System.out.println("Total Column Count: "+ex.totolColumnCount());
// Code to iterate over each cell and read data  
int rowNum = ex.totalRowCount();
int columnCount = ex.totolColumnCount();
for(int i=0; i<rowNum;i++){
for(int j=0; j<columnCount;j++){
 System.out.println("Row "+i+ " Column "+j+" value: "+ex.getCellValue(i,j));
 }
 }
 }
 }

Step 13: Run above test method and verify the result: Refer below for console output

Congratulations you completed code for one most used utility class ExcelReader. Now it will help you to read all the excel data. Feel free to contact us if you face any issue or share your feedback on info@thoughtcoders.com.