How to read data from excel file using Selenium WebDriver

Before we dive into the implementation in selenium, first we have look at the data source we are going to use. As usual I would prefer excel is best option to read and write data using selenium WebDriver. Many of the folk and depend on the requirement use sql server or any database but that little different way to implementation of reading and writting data from both.

There are lots of different approaches possible, and I am aware that the solution presented here can possible be enhanced further and extended as well.

Above is the data source from where data is being used for data driven using Selenium WebDriver.

For this purpose, I use the Apache POI library, which allows you to read, create and edit Microsoft Office-documents using Java. The library, as well as its JavaDoc,can be found at http://poi.apache.org. The classes and methods we are going to use to read data from our Excel sheet are located in the org.apache.poi.hssf.usermodelpackage.

Code:

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

/**
 * Created by Gaurav on 5/26/2014.
 */
public class demoReadFromExcel {


        public static void main(String[] args) {
            try {
                FileInputStream file = new FileInputStream(new File("D:\\data1.xls"));
                HSSFWorkbook workbook = new HSSFWorkbook(file);
                HSSFSheet sheet1 = workbook.getSheet("sheet1");
                String heading = sheet1.getRow(0).getCell(0).getStringCellValue();
                String searchText1 = sheet1.getRow(1).getCell(0).getStringCellValue();
                String searchText2 = sheet1.getRow(2).getCell(0).getStringCellValue();
                String searchText3 = sheet1.getRow(3).getCell(0).getStringCellValue();

                String heading1 = sheet1.getRow(0).getCell(1).getStringCellValue();
                String searchText4 = sheet1.getRow(1).getCell(1).getStringCellValue();
                String searchText5 = sheet1.getRow(2).getCell(1).getStringCellValue();
                String searchText6 = sheet1.getRow(3).getCell(1).getStringCellValue();


                String heading2 = sheet1.getRow(0).getCell(2).getStringCellValue();
                String searchText7 = sheet1.getRow(1).getCell(2).getStringCellValue();
                String searchText8 = sheet1.getRow(2).getCell(2).getStringCellValue();
                String searchText9 = sheet1.getRow(3).getCell(2).getStringCellValue();


                System.out.println("Heading is:" +heading+ " " +heading1+ " " +heading2);
                System.out.println("Search Text 1 is:" +searchText1+ " " +searchText4+ " " +searchText7);
                System.out.println("Search Text 2 is:" +searchText2+ " " +searchText5+ " " +searchText8);
                System.out.println("Search Text 3 is:" +searchText3+ " " +searchText6+ " " +searchText9);

                file.close();
            }
            catch (FileNotFoundException fnfe) {
                fnfe.printStackTrace();
            } catch (IOException ioe) {
                ioe.printStackTrace();
            }
        }
    }