Selenium Framework[Test Data Driven Framework] - Key methods in reading and writing Excel using POI

     Test data is very important in software automation. Because automation testers will be updating test data frequently. So, it must be handy for them. This type of framework provides the ability to store the test data in a separate file. Because of this testers can able to run scripts with multiple sets of test data. To access the external file java had some type of file reading libraries and we will use Apache POI in this tutorial.

Before starting with framework designing check some basics about selenium

Steps to download and configure Apache POI

1. Navigate to this page https://poi.apache.org/
2. Click download in the latest version of the JAR
3. Download zip file in Binary Distribution
4. Extract that in any location of your local
5. In eclipse open new Java project
6. Right-click on the project and navigate to the path Build Path -> Configure build path
7. In the Libraries tab, click Add External Libraries
8. Select all available jar files from the POI zip file
9. Click Apply and close

Overview of Excel sheet structure

Workbook

    This is the whole Excel file and it may contain more than 1 sheet in it. Apache POI had an interface called "Workbook"  to do all required actions on the workbook. This interface provides methods to create, edit, read, and write any sheets in the workbook. There are 2 classes in this method that provides all required methods for the file of type .xls(MS office version 97-2003) and .xls/.xlsx(MS office version 2007 or later). 

• Class HSSFWorkbook - .xls(MS office version 97-2003)
• Class XSSFWorkbook - .xls/.xlsx(MS office version 2007 or later)

Sheet

    This is present inside the workbook and the sheet contains rows and cells. As like the work book "Sheet" interface contains different classes for different versions 

• Class HSSFSheet - .xls(MS office version 97-2003)
• Class XSSFSheet - .xls/.xlsx(MS office version 2007 or later)

Rows and Cells

    "Row" and "Cell" interface provides classes to read and write rows and specific cells in the sheet.

• Class HSSFRow - Row in HSSF sheet
• Class XSSFRow - Row in XSSF sheet
• Class HSSFCell -  Cell in HSSF sheet
• Class XSSFCell - Cell in HSSF sheet

Let us try to understand the process using the below example

Workbook with sheet name as UserDetails

Having 2 columns  Email and Phone number

Below are the steps to fetch and modify fields in the workbook

1. Pass the Excel file to the File class

        File file = new File("F:\\TestData.xls");

2. Pass the file object reference to FileInputStream class

        FileInputStream inStream = new FileInputStream(file);

3. Pass the FileInputStream object reference to the respective workbook class. 

        HSSFWorkbook workbook = new HSSFWorkbook(inStream);

4. Then we need to create sheet object reference in the workbook using method getSheet(String sheet name) or getSheetAt(int index)

        HSSFSheet sheet1 = workbook.getSheet("UserDetails");

                                            or

        HSSFSheet sheet1 = workbook.getAt(1);

5. Create row object reference using method getRow(int Index)

        HSSFRow row1 = sheet1.getRow(1);

6. Create cell object reference using getCell(int Index)

        HSSFCell cell1 = sheet1.getRow(1).getCell(1); 

7. Get the required value or index from the cell using method getStringCellValue() or getNumericCellValue()

        String value = cell1.getStringCellValue();

        int numericValue = cell1.getNumericCellValue();

Sample Program

package TestCases;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FileRead{

public static void main(String args[]) throws InterruptedException, IOException {

 

    File file =    new File("F:\\MyProgress.xlsx");

    FileInputStream inputStream = new FileInputStream(file);

    XSSFWorkbook wb=new XSSFWorkbook(inputStream);

    XSSFSheet sheet=wb.getSheet("UserDetails");

    XSSFRow row2=sheet.getRow(1);

    XSSFCell cell=row2.getCell(1);

    double value= cell.getNumericCellValue();

    

    System.out.println("Value is :"+ value);

}

}

Note: index value for row and cell starts with 0

To write any value in the excel sheet use setCellValue() method

    XSSFCell cell = row2.getCell(2);

    cell.setCellValue("Pass");

    String value = cell.getStringCellValue();

createRow() and createCell()

    These methods are used to create a new row or cell in the sheet. 

                    XSSFRow addRow  = sheet.createRow(2);

                    addRow.createCell(0).setCellValue("aaaaa@gmail.com");

Let us discuss framework creation in next article