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, 2019 in Selenium by Tarun
17,141 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, 2019 by Abha
• 28,140 points

Related Questions In Selenium

+2 votes
3 answers

How can we handle authentication popup in Selenium WebDriver using Java

1) By passing user credentials in URL. String ...READ MORE

answered Aug 31, 2020 in Selenium by Sri
• 3,190 points
28,318 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,910 points
3,455 views
0 votes
1 answer

Can anyone explain how an AJAX call can be handled in Selenium webdriver?

Hello Megha, to demonstrate how AJAX calls are ...READ MORE

answered May 9, 2019 in Selenium by Pratibha
• 3,690 points
1,171 views
0 votes
2 answers

How can we use JavaScript Executor to click and enter data to a web element in Selenium?

WebElement element = driver.findElement(By.id("abcd")); // Let the ...READ MORE

answered Mar 22, 2020 in Selenium by Lakshmi Sarvepalli
3,133 views
0 votes
2 answers

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,500 points
12,576 views
0 votes
2 answers

Problem while using InternetExplorerDriver in Selenium WebDriver

enable trusted connection  in internet explorer by ...READ MORE

answered Aug 31, 2020 in Selenium by Sri
• 3,190 points
8,558 views
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,910 points
6,603 views
0 votes
2 answers

How to use such xpath to find web elements

xpath are two types. 1) Absolute XPath:    /html/b ...READ MORE

answered Sep 3, 2020 in Selenium by Sri
• 3,190 points
7,506 views
0 votes
1 answer
0 votes
1 answer

How can I read numeric data from an Excel sheet using Selenium Webdriver?

Hey Jignesh, for reading numeric data from ...READ MORE

answered Jul 17, 2019 in Selenium by Abha
• 28,140 points
7,673 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP