How do you read data from Excel?

To read data from an Excel file in Selenium with Java, you can use the Apache POI library, which provides excellent support for handling Excel files in Java applications. Here’s a detailed guide with an example to help you get started:

Step-by-Step Guide to Reading Data from Excel in Selenium with Java

• Add Apache POI Dependencies First, include the Apache POI library in your project. If you’re using Maven, add the following dependencies to your pom.xml file:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>        

• Create an Excel File Prepare an Excel file (TestData.xlsx in this example) with the data you need. For instance, create a sheet called Sheet1 with the following structure:

Username --> Password

user1 --> pass1

user2 --> pass2

• Write the Code to Read Data from Excel

Below is a sample code snippet to read data from an Excel file using Apache POI:

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

import java.io.FileInputStream;
import java.io.IOException;

public class ExcelReader {

    public static void main(String[] args) throws IOException {
        // Path to the Excel file
        String filePath = "path/to/TestData.xlsx";
        FileInputStream file = new FileInputStream(filePath);

        // Create Workbook instance for XLSX file
        Workbook workbook = new XSSFWorkbook(file);
        // Get the sheet at index 0 (first sheet)
        Sheet sheet = workbook.getSheetAt(0);

        // Loop through all rows in the sheet
        for (int i = 1; i <= sheet.getLastRowNum(); i++) { // Assuming row 0 has headers
            Row row = sheet.getRow(i);
            // Get cell values for each column in the row
            String username = row.getCell(0).getStringCellValue();
            String password = row.getCell(1).getStringCellValue();

            // Output the values
            System.out.println("Username: " + username + ", Password: " + password);

            // Here you can use the values in your Selenium test scripts
            // Example: driver.findElement(By.id("username")).sendKeys(username);
            // driver.findElement(By.id("password")).sendKeys(password);
        }

        // Close workbook and file
        workbook.close();
        file.close();
    }
}        

In this code:

• We create a FileInputStream to read the Excel file.
• A Workbook instance is created using XSSFWorkbook for .xlsx files.
• We access the sheet by index and loop through each row to retrieve values from the cells.
• The data extracted can be used in your Selenium test case by interacting with web elements as shown in the comments.

Example Output

If your Excel file has two rows of data (excluding the header), the output should look like this:

Username: user1, Password: pass1
Username: user2, Password: pass2        

Additional Tips

• Make sure to close the Workbook and FileInputStream objects to avoid memory leaks.
• If you encounter numeric data, you might need to convert it to a String using .toString() or by formatting.

Our Services

If you’re interested in further advancing your automation testing skills or seeking career opportunities, we offer the following services through Your Corporate Life:

1. Job Update Service: Get daily job updates via WhatsApp for roles like Manual Testing, Automation Testing, ETL Testing, Developer, and DevOps with experience ranging from Fresher to 12 years.
2. Naukri Profile Optimization: Stand out to recruiters with a professionally optimized profile.
3. LinkedIn Profile Optimization: Enhance your LinkedIn profile to attract job opportunities.
4. Mock Interviews: Prepare for interviews with real-world questions and expert feedback.
5. Placement Assistance Service: Comprehensive support in securing your next job.
6. Resume & Cover Letter Writing: Professionally crafted to make a great first impression.

For more details or to avail of our services, connect with us on WhatsApp: https://wa.me/c/919156951085. Let us help you achieve your career goals!