How to Read Data From Excel in Selenium? – 2 Easy Steps

Introduction

Excel sheets are a common repository for storing data. Selenium, a powerful automation tool primarily used for web testing, can also be employed to read data from Excel files. This enables testers and developers to automate the process of fetching data, saving time and reducing human errors.

In this guide, we will guide you on how to read data from Excel in Selenium step by step with explanations including best practices, errors & tips, and FAQs.

Important Factors We Will Use to Read Data From Excel in Selenium

We listed the important things to remember when using Selenium to read data from an Excel file. Now, let’s look at each of these things one by one:

1. java.io.File:
   The java.io.File class is used to represent file and directory pathnames. In the context of reading Excel files, you will use this class to create a reference to the Excel file you want to read.
2. FileInputStream:
   The FileInputStream class is used to read data from a file in bytes. It’s commonly used to create a stream between your Java program and the Excel file, allowing you to read its content.
3. Workbook:
   The Workbook interface is part of the Apache POI library (used for reading and writing Excel files). It represents the Excel workbook, which is the top-level container for all the sheets and data within the Excel file.
   • XSSFWorkbook[.xlsx]: This class is used for .xlsx files (Excel 2007 and later). It implements the Workbook interface and is specifically designed for the XML-based .xlsx format.
   • HSSFWorkbook[.xls]: This class is used for .xls files (Excel 97-2003). Similar to XSSFWorkbook, it implements the Workbook interface but is used for the older binary .xls format.
4. Sheet:
   The Sheet interface represents a sheet within an Excel workbook. A workbook can have multiple sheets, and each sheet contains rows and cells.
   • XSSFSheet[.xlsx]: This class represents a sheet in a .xlsx workbook. It’s used to interact with sheets when dealing with the XML-based format.
   • HSSFSheet[.xls]: This class represents a sheet in a .xls workbook. It’s used when working with the older binary format.
5. Row:
   The Row interface represents a row within an Excel sheet. It allows you to iterate through cells horizontally within a row.
   • XSSFRow[.xlsx]: This class represents a row in a .xlsx sheet. It’s used to interact with rows in the XML-based format.
   • HSSFRow[.xls]: This class represents a row in a .xls sheet. It’s used when working with the older binary format.
6. Cell:
   The Cell interface represents a single cell within an Excel sheet. It’s used to retrieve and manipulate data stored in individual cells.
   • XSSFCell[.xlsx]: This class represents a cell in a .xlsx sheet. It’s used to work with cells in the XML-based format.
   • HSSFCell[.xls]: This class represents a cell in a .xls sheet. It’s used when working with the older binary format.

When retrieving data from Excel using Selenium, you need to consider the file format (.xlsx or .xls), create a File and FileInputStream to access the file, use the appropriate Workbook class based on the format, access the desired Sheet, iterate through Row objects, and then retrieve data from individual Cell objects based on your needs.

Let’s find out how to read data from Excel in Selenium using the Java programming language.

How to Read Data From Excel in Selenium?

By following the below steps, we can read data from Excel easily:

1. Add Apache POI Dependencies
2. Read Data from Excel using Selenium

1. Add Apache POI Dependencies

To get information from Excel files (.xlsx or .xls) using Selenium, we use something called Apache POI.

To make Apache POI work, we have to do a few things in our project. If you are using a Maven project (a certain type of project), you need to add some extra pieces of code to a special file called pom.xml.

Here’s how you can do it:

1. Open the Maven Repository (a place where we can find extra tools for our project).
2. Look for something called Apache POI.
3. Find the part that says “poi-ooxml” – this is important.
4. You’ll see a piece of code that looks like the below dependency. You can just copy this code.
5. Open your pom.xml file in your project and paste the code there.
6. Save the file.

Doing all these steps will help you use Apache POI to get data from Excel files in your Selenium project.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>Code language: HTML, XML (xml)

2. Read Data from Excel using Selenium

If you want to use Selenium to read information from an Excel file, you can make use of Apache POI. This comes in handy when you have various details stored in your Excel file, such as information about your testing scripts and login credentials.

In simpler terms, Apache POI is like a helper that Selenium can use to understand and grab data from Excel files. This data could be things like what you are testing and the usernames and passwords you need to log in. It is a way to help your Selenium program understand the information stored in Excel sheets.

Let’s see the below program to know how to retrieve data from the Excel file:

/*
 * How to Read Data From Excel in Selenium using Apache POI in Java?
 */
package auto.java.apachepoi;

import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadTestData {
	void printData() {
		try {
			File file = new File("C:\\Satyajeet Nayak\\TestData.xlsx");

			FileInputStream fis = new FileInputStream(file);

			Workbook workbook = WorkbookFactory.create(fis);

			Sheet sheet = workbook.getSheet("Sheet1");

			for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
				for (int j = sheet.getRow(i).getFirstCellNum(); j < sheet.getRow(i).getLastCellNum(); j++) {
					System.out.print(sheet.getRow(i).getCell(j)+"\t");
				}
				System.out.println();
			}

			workbook.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		
		ReadTestData data = new ReadTestData();
		data.printData();

	}

}Code language: JavaScript (javascript)

Output

TestScript Name	UserName	Password	FirstName	LastName	
TC_001	Admin	Admin@1234	Satyajeet	Nayak	
TC_002	User1	User1@1234	Rohan	Parija	
TC_003	User2	User2@1234	Kierra	Gentry	
TC_004	User3	User3@1234	Pierre	Cox	Code language: CSS (css)

Explanation

Let’s break down each section step by step and try to understand how to read data from Excel in Selenium:

import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;Code language: CSS (css)

These lines are import statements, which bring in necessary classes from Java libraries (java.io) and Apache POI library to handle Excel files. File, FileInputStream, Sheet, and Workbook are classes used to work with files and Excel data.

public class ReadTestData {Code language: PHP (php)

This line declares the start of a Java class named ReadTestData. A class is a blueprint for creating objects that have specific behaviors and properties.

    void printData() {Code language: JavaScript (javascript)

This line defines a method named printData(). Methods are blocks of code that can be called to perform specific tasks.

        try {
            File file = new File("C:\\Satyajeet Nayak\\TestData.xlsx");Code language: JavaScript (javascript)

Here, an File object named file is created with the path to the Excel file to be read. This is where you would replace the file path with the actual path to your Excel file.

            FileInputStream fis = new FileInputStream(file);Code language: JavaScript (javascript)

A FileInputStream named fis is created to read the contents of the Excel file specified by the file object.

            Workbook workbook = WorkbookFactory.create(fis);

The WorkbookFactory class is used to create a Workbook object, representing the Excel workbook. The .create(fis) method initializes the workbook using the FileInputStream created earlier.

            Sheet sheet = workbook.getSheet("Sheet1");Code language: JavaScript (javascript)

A Sheet object named sheet is obtained from the workbook using the sheet name “Sheet 1”. You can replace “Sheet 1” with the actual name of the sheet you want to read.

            for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                for (int j = sheet.getRow(i).getFirstCellNum(); j < sheet.getRow(i).getLastCellNum(); j++) {
                    System.out.print(sheet.getRow(i).getCell(j)+"\t");
                }
                System.out.println();
            }Code language: PHP (php)

This section uses nested loops to iterate through each row and cell in the sheet. The outer loop (i) iterates over rows, and the inner loop (j) iterates over cells within the current row. It prints the cell values with a tab separator and a new line after each row.

            workbook.close();Code language: CSS (css)

This line closes the workbook to release system resources after the reading is done.

        } catch (Exception e) {
            e.printStackTrace();
        }
    }Code language: PHP (php)

This block is a try-catch construct. It captures any exceptions that might occur during the execution of the code within the try block. If an exception occurs, it prints the stack trace, which helps in identifying the cause of the error.

    public static void main(String[] args) {

        ReadTestData data = new ReadTestData();
        data.printData();

    }Code language: JavaScript (javascript)

This is the main method, which is the entry point of the Java program. An instance of the ReadTestData class is created, and the printData() method is called on that instance to initiate the reading and printing of Excel data.

Best Practices for Efficient Data Reading

• Always use descriptive variable names.
• Implement error handling to deal with unexpected situations.
• Consider using configuration files to manage input parameters.

Common Errors and Troubleshooting Tips

• Ensure the correct path to your Excel file is provided.
• Verify sheet names and data positions.
• Check for typos in your code.

Conclusion

Automating the process of reading data from Excel using Selenium can significantly improve your efficiency and accuracy. By following the steps outlined in this article “How to read data from Excel in Selenium?“, you’ll be well on your way to harnessing the power of automation for data extraction.

Frequently Asked Questions (FAQs)

Other Queries We Have Covered

• How to fetch data from Excel in Selenium
• Apache POI read Excel
• Get data from Excel in Selenium
• Excel reader in selenium
• Selenium code to read data from Excel using Apache POI

Recommended Articles

• How To Handle Multiple Windows In Selenium?
• How to check Selenium version?
• How To Handle CAPTCHA In Selenium?
• How to Achieve Synchronization in Selenium Webdriver?