10j. Advanced WebDriver – Reading data from excel using POI

Hiya friends! Today, let us delve into excel sheets and understand how to read data from them. As part of automation and building an automation framework, we tend to store data in excel sheets in a predefined format (usually following a template). The data we store is mostly test data, different test URLs, release specific parameters etc. In any such case, knowing how to deal with an excel sheet in our code becomes a lot significant.

This is going to be another pure java article. So, it is time that you grab a cup of coffee (Java)!! We will be using POI jar to achieve this.

Step 1:

As always, our first step is to download the required POI JAR. Head over to, Apache POI and download the binary distribution of the latest stable version at that point (3.17 is the latest stable release at the time of writing this article). Clicking on the binary version for the zip file redirected to the actual download page.

POI download link

Step 2:

Add these JARs to our project build path. Make sure to select all the JARs under “poi-x.xx”, “ooxml-lib” and “lib” folders. I have placed these in our GitHub repo as well, along with all other code files dealt as part of this post.

We have seen this adding JARs to build path procedure numerous times before and hence I am not re-iterating it (Refer to Step 3 of this article for detailed explanation).

Step 3:

Create a new class, “ExcelOperationsUsingPOI.java”. In this class, let us have a method to read a particular sheet of the excel file from a specific location.

  • Create an object of the File class by passing the complete file path of the excel file you wish to open –  File file = new File(filePath+"\\"+fileName);
  • Next step is to create a FileInputStream object to obtain input bytes of the excel file –  FileInputStream inputStream = new FileInputStream(file);
  • Create a Workbook object –  Workbook myWorkbook = null;
  • Excel files can have two extensions most of the time. Either “.xls” or “.xlsx”. Find the extension by splitting the file name using substring method and create Workbook object accordingly.
//indexOf gives the index of . in the file name
//substring method splits the string starting from index of . to the end
String fileExtensionName = fileName.substring(fileName.indexOf("."));
       
//Check condition if the file is xlsx file
if(fileExtensionName.equals(".xlsx")){
//If it is xlsx file then create object of XSSFWorkbook class
myWorkbook = new XSSFWorkbook(inputStream);
}

//Check condition if the file is xls file
else if(fileExtensionName.equals(".xls")){
//If it is xls file then create object of HSSFWorkbook class
  myWorkbook = new HSSFWorkbook(inputStream);
}
  • With the passed in exact sheet name, that particular sheet can be read –  Sheet mySheet = myWorkbook.getSheet(sheetName);

Now it is very easy to work with rows and columns whose intersection will give us the cell content we wish to read.

Let us now see the code implementing the whole functionality discussed so far,

ExcelOperationsUsingPOI.java

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 ExcelOperationsUsingPOI {
   public static void readExcel(String filePath,String fileName,String sheetName) throws IOException{

        //Create a object of File class to open xlsx file
        File file = new File(filePath+"\\"+fileName);

        //Create an object of FileInputStream class to read excel file
        FileInputStream inputStream = new FileInputStream(file);

        Workbook myWorkbook = null;

        //Find the file extension by spliting file name in substring and getting only extension name
        //indexOf gives the index of . in the file name
        //substring method splits the string starting from index of . to the end
        String fileExtensionName = fileName.substring(fileName.indexOf("."));
       
        //Check condition if the file is xlsx file
        if(fileExtensionName.equals(".xlsx")){
        //If it is xlsx file then create object of XSSFWorkbook class
        	myWorkbook = new XSSFWorkbook(inputStream);
        }

        //Check condition if the file is xls file
        else if(fileExtensionName.equals(".xls")){
            //If it is xls file then create object of HSSFWorkbook class
        	myWorkbook = new HSSFWorkbook(inputStream);
        }

        //Read sheet inside the workbook by its name
        Sheet mySheet = myWorkbook.getSheet(sheetName);

        //Find number of rows in excel file
        int rowCount = mySheet.getLastRowNum()- mySheet.getFirstRowNum();
        
        //Create a loop over all the rows of excel file to read it
        for (int i = 0; i < rowCount+1; i++) {
            Row row = mySheet.getRow(i);
            //Create a loop to print cell values in a row
            for (int j = 0; j < row.getLastCellNum(); j++) {
                //Print excel data in console
                System.out.print(row.getCell(j).getStringCellValue()+"|| ");
            }
            System.out.println();
        }		    
   }
}

ReadExcelData.java

Used to call the readExcel method and pass required arguments.

import java.io.IOException;
import com.blog.utility.ExcelOperationsUsingPOI;

public class ReadExcelData {

  public static void main(String[] args) {
    try {
      ExcelOperationsUsingPOI.readExcel("E:\\Selenium", "ReadUsingPOI.xlsx", "Demographics");
    } catch (IOException e) {
      e.printStackTrace();
    } 
  }
}

Comments make the code self-explanatory. The excel sheet under consideration has data as shown below,

Excel Sheet

Accessing this information with our code prints all the values separated by pipes to console as expected.

excel console output

Give me a shout-out in the comments section if you would like code snippets to retrieve,

  1. Zero-based row and column indices for a given entry
  2. Value using given zero-based row and column indices
  3. All the row values in a list, based on a given entry
  4. All the column values in a list, based on a given entry

Experiment with these features and let me know if you have a bumpy ride.

Have a nice day!

<< 10i. Advanced WebDriver – Using property files

8 Comments 10j. Advanced WebDriver – Reading data from excel using POI

    1. Chandana Chaitanya

      Hi Suraj,

      The data provider is a feature that comes with TestNG. We are covering JUnit4 as part of this tutorial series. Unfortunately, in JUnit, we do not have data providers.
      But, to answer your question, you can read the data from excel using the same POI. Use @DataProvider annotation and give it a name. Return a String array from a method with all the read data. Use the data provider in your test method by providing the name as, @Test(dataProvider=”name_of_the_data_provider”) and pass the individual data to the test method as a String argument and use it in your method as required. Hope that helps.

      Reply

Leave A Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.