How can we read data from an excel sheet in Selenium webdriver?

0 votes
I want to read data from an excel sheet and don't know how can to do that in Selenium webdriver. Anyone please suggest.
May 8 in Selenium by Tarun
434 views

1 answer to this question.

0 votes

Hi Tarun, to read data from an excel sheet in selenium webdriver, you can use Apache POI. To get a better idea on how to read data from an excel sheet, checkout this piece of code:

package excelExportAndFileIO;

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 ReadExcelFileDemo {

    public void readExcel(String filePath,String fileName,String sheetName) throws IOException{

    //Create an 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 workbook = null;

    //Find the file extension by splitting file name in substring  and getting only extension name

    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

    workbook = 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 XSSFWorkbook class

        workbook = new HSSFWorkbook(inputStream);

    }

    //Read sheet inside the workbook by its name

    Sheet sheet = workbook.getSheet(sheetName);

    //Find number of rows in excel file

    int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();

    //Create a loop over all the rows of excel file to read it

    for (int i = 0; i < rowCount+1; i++) {

        Row row = sheet.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();
    } 

    }  

    //Main function is calling readExcel function to read data from excel file

    public static void main(String...strings) throws IOException{

    //Create an object of ReadExcelFileDemo class

    ReadExcelFileDemo objExcelFile = new ReadExcelFileDemo();

    //Prepare the path of excel file

    String filePath = System.getProperty("user.dir")+"\\src\\excelExportAndFileIO";

    //Call read file method of the class to read data

    objExcelFile.readExcel(filePath,"ExportExcel.xlsx","ExcelDemo");

    }

}
answered May 8 by Abha
• 23,030 points

Related Questions In Selenium

+2 votes
1 answer

How can we handle authentication popup in Selenium WebDriver using Java

The Alert Method, authenticateUsing() lets us skip ...READ MORE

answered Apr 18, 2018 in Selenium by king_kenny
• 3,650 points
6,648 views
0 votes
1 answer

Read data from excel sheet in python

Try to use xrld. A python module used ...READ MORE

answered Jun 28, 2018 in Selenium by Samarpit
• 5,130 points
723 views
0 votes
1 answer
0 votes
1 answer

Finding WebDriver element with Class Name in java

The better way to handle this element ...READ MORE

answered Apr 10, 2018 in Selenium by nsv999
• 5,090 points
597 views
0 votes
1 answer
0 votes
1 answer

Geo-location microphone camera pop up

To Allow or Block the notification, access using Selenium and you have to ...READ MORE

answered May 11, 2018 in Selenium by Samarpit
• 5,130 points
903 views
0 votes
1 answer

How to use such xpath to find web elements

It's much simpler to use: #MyDiv123 > div.super or ...READ MORE

answered Jun 14, 2018 in Selenium by Samarpit
• 5,130 points
587 views
0 votes
1 answer