Excel Data read by Selenium java

 Code to read data from Excel 

package mit;

import java.io.File;

import java.io.FileInputStream;

import java.util.Arrays;

import java.util.Iterator;

import org.apache.poi.ss.usermodel.DataFormatter;

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

    public static void main(String[] args) throws Exception {

        File file = new File("Untitled spreadsheet (1) (1).xlsx");

        FileInputStream fin = new FileInputStream(file);

        Workbook wb = new XSSFWorkbook(fin);

        

        Sheet s1 = wb.getSheet("Sheet1");

        

        int r1=s1.getPhysicalNumberOfRows();

        int c1=s1.getRow(0).getPhysicalNumberOfCells();

        

String[][] data =new String[r1-1][c1];

   //     String s = r.getCell(1).toString();

    //    System.out.println(s);

        

        for (int i = 0; i < r1-1; i++)

        

        {

for (int j = 0; j < c1; j++)

{

DataFormatter df=new DataFormatter();

data[i][j]= df.formatCellValue(s1.getRow(i).getCell(j));

}

}

    

        // Clean up

       

        fin.close();

        

       for (String[]dataAStr:data)

        {

System.out.println(Arrays.toString(dataAStr));

}

    }

}

Package and Imports

package mit;

• Declares the package name mit. It organizes classes into namespaces.

import java.io.File;
import java.io.FileInputStream;
import java.util.Arrays;
import java.util.Iterator;

• File: For representing file and directory pathnames.

• FileInputStream: For reading raw bytes from a file.

• Arrays: For utility functions like toString() to print array contents.

• Iterator: Though imported, it is not used in this program.

import org.apache.poi.ss.usermodel.DataFormatter;
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;

• These classes are part of Apache POI for working with Excel files (.xlsx).

  • Workbook: Represents the whole Excel file.

  • Sheet: Represents a single worksheet.

  • Row: Represents a row in a sheet.

  • DataFormatter: Converts cell values to readable Strings.

  • XSSFWorkbook: Used specifically for .xlsx files.

---

Class Definition

public class TestExcelData {

• Defines a public class named TestExcelData.

---

Main Method

public static void main(String[] args) throws Exception {

• Entry point of the program.

• throws Exception: Declares that the method may throw exceptions (e.g., IOException or FileNotFoundException).

---

Step-by-step Operations

File file = new File("Untitled spreadsheet (1) (1).xlsx");

• Creates a File object pointing to the Excel file. Make sure the path is correct.

FileInputStream fin = new FileInputStream(file);

• Opens the file for reading its contents.

Workbook wb = new XSSFWorkbook(fin);

• Loads the Excel workbook using Apache POI. This supports .xlsx files.

Sheet s1 = wb.getSheet("Sheet1");

• Accesses the sheet named "Sheet1" from the workbook.

int r1 = s1.getPhysicalNumberOfRows();

• Gets the number of rows that contain data (non-empty rows).

int c1 = s1.getRow(0).getPhysicalNumberOfCells();

• Gets the number of cells (columns) in the first row (assumed to be the header).

String[][] data = new String[r1 - 1][c1];

• Initializes a 2D array to store the Excel data (excluding the header row).

---

Reading the Excel Data

for (int i = 0; i < r1 - 1; i++) {
    for (int j = 0; j < c1; j++) {
        DataFormatter df = new DataFormatter();
        data[i][j] = df.formatCellValue(s1.getRow(i).getCell(j));
    }
}

• Outer loop (i): Iterates over rows from 0 to r1-2 (excluding header row at r1-1).

• Inner loop (j): Iterates over each cell in a row.

• DataFormatter: Converts cell to a formatted string regardless of cell type (numeric, string, date).

• Stores the formatted value in the data array.

Note: Since r1-1 is used in the array, it's implied that the header is included but not stored.

---

Closing Resources

fin.close();

• Closes the file input stream.

---

Printing the Data

for (String[] dataAStr : data) {
    System.out.println(Arrays.toString(dataAStr));
}

• Iterates through the data array.

• Prints each row as a string using Arrays.toString().

---

🔚 Summary:

• This program reads an Excel .xlsx file using Apache POI.

• It stores data (excluding the header) in a 2D String array.

• Uses DataFormatter to ensure proper formatting of cell values.

• Then it prints the extracted data to the console.

To read data from an Excel file in Selenium with Java,

 How read Data from Excel for selenium java 

To read data from an Excel file in Selenium with Java, the most commonly used library is Apache POI. Here's a basic step-by-step guide to help you get started:

---

✅ 1. Add Apache POI Dependency

If you're using Maven, add these dependencies in your pom.xml:

<dependencies>
    <!-- Apache POI for Excel -->
    <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>
</dependencies>

---

✅ 2. Create a Utility Class to Read Excel

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

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

public class ExcelUtils {

    public static String getCellData(String filePath, String sheetName, int rowNum, int cellNum) {
        String value = "";
        try {
            FileInputStream fis = new FileInputStream(new File(filePath));
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheet(sheetName);
            Row row = sheet.getRow(rowNum);
            Cell cell = row.getCell(cellNum);

            value = cell.toString();  // You can handle different types here if needed

            workbook.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return value;
    }
}

---

✅ 3. Use ExcelUtils in Your Selenium Test

public class LoginTest {
    public static void main(String[] args) {
        String filePath = "C:\\TestData\\data.xlsx";
        String username = ExcelUtils.getCellData(filePath, "Sheet1", 0, 0);
        String password = ExcelUtils.getCellData(filePath, "Sheet1", 0, 1);

        System.out.println("Username: " + username);
        System.out.println("Password: " + password);

        // WebDriver code
        // driver.findElement(By.id("username")).sendKeys(username);
        // driver.findElement(By.id("password")).sendKeys(password);
    }
}

---

🔄 Tips:

• .xlsx files are handled by XSSFWorkbook, .xls by HSSFWorkbook.

• Add null checks if your Excel might have empty cells.

• You can enhance this utility to read full rows, columns, or convert to 2D array.

---

Let me know if you want to read all data as a table or use DataProvider with TestNG too.

Selenium WebDriver Basic Methods You Should Know

 

🔍 Selenium WebDriver Basic Methods You Should Know

Now that your setup is ready, let’s explore some basic Selenium WebDriver methods in Java that you’ll use all the time. These methods help you interact with web elements, navigate between pages, and control the browser like a pro.

🚗 1. get(String url)

Opens a web page in the browser.

driver.get("https://www.google.com");

---

📄 2. getTitle()

Returns the title of the current web page.

String title = driver.getTitle();
System.out.println("Page title: " + title);

---

🔗 3. getCurrentUrl()

Gets the current page URL.

String currentUrl = driver.getCurrentUrl();

---

🧱 4. findElement(By locator)

Finds a single web element using a locator (like ID, name, XPath, etc.).

WebElement searchBox = driver.findElement(By.name("q"));
searchBox.sendKeys("Selenium WebDriver");

---

🧮 5. findElements(By locator)

Finds multiple elements that match the locator and returns them as a list.

List<WebElement> links = driver.findElements(By.tagName("a"));
System.out.println("Total links: " + links.size());

---

🎯 6. click()

Clicks on a button, link, checkbox, etc.

driver.findElement(By.id("submitBtn")).click();

---

🖊 7. sendKeys(String text)

Enters text into an input field or textarea.

driver.findElement(By.name("username")).sendKeys("myUser123");

---

↩ 8. navigate().back(), navigate().forward(), navigate().refresh()

Used for browser navigation, like the back and forward buttons.

driver.navigate().back();    // Go back to the previous page
driver.navigate().forward(); // Move forward in browser history
driver.navigate().refresh(); // Refresh the current page

---

📷 9. getPageSource()

Returns the full HTML source of the current page.

String source = driver.getPageSource();

---

🛑 10. quit() vs. close()

• quit() closes all browser windows and ends the WebDriver session.

• close() closes only the current browser window.

driver.close(); // Just the active tab
driver.quit();  // Entire browser session

---

🧠 Quick Tip: Locators You’ll Use Often

By.id("id")
By.name("name")
By.className("className")
By.tagName("tagName")
By.linkText("full link text")
By.partialLinkText("partial text")
By.cssSelector("css selector")
By.xpath("xpath expression")

---

✅ Practice Makes Perfect

Using these basic methods, you can:

• Open a browser

• Search on Google

• Click links

• Fill login forms

• Navigate back and forth

I

How to Install Selenium WebDriver with Java – Step-by-Step Guide

🛠 How to Install Selenium WebDriver with Java – Step-by-Step Guide

Before you can start writing Selenium scripts in Java, you need to set up your system with the right tools. Don’t worry—I'll guide you through it like a friend helping you install a game 😄

✅ Step 1: Install Java (JDK)

Selenium uses Java to run your automation scripts, so you’ll need the Java Development Kit (JDK).

🔗 Download Java JDK

• Download the latest version (LTS recommended).

• Install it and set the environment variable JAVA_HOME if it doesn’t happen automatically.

• Verify by running this in your terminal/command prompt:

  java -version
  

---

✅ Step 2: Install an IDE (Eclipse or IntelliJ)

You’ll need an IDE (Integrated Development Environment) to write and run your Java code easily.

• Eclipse IDE for Java Developers – lightweight and popular. 🔗 Download Eclipse

• IntelliJ IDEA – modern and feature-rich. 🔗 Download IntelliJ

---

✅ Step 3: Download the Selenium WebDriver Java Library

You have two easy ways to include Selenium in your project:

💡 Option 1: Using Maven (Recommended)

If you're using Maven (common in most Java projects), just add this to your pom.xml:

<dependencies>
    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-java</artifactId>
        <version>4.20.0</version>
    </dependency>
</dependencies>

Maven will download everything for you!

💡 Option 2: Manual Download (If you're not using Maven)

• Download the latest Selenium Java client from: 🔗 https://www.selenium.dev/downloads/

• Unzip it, and add all .jar files to your project’s build path.

---

✅ Step 4: Download ChromeDriver

Selenium needs a driver to control the browser. For Chrome, you need ChromeDriver.

• Check your Chrome version (go to Chrome → Help → About Google Chrome).

• Download the matching ChromeDriver from:
  🔗 https://sites.google.com/chromium.org/driver/

• Extract it and place it somewhere on your system (like C:/webdriver/ or /usr/local/bin/).

• In your Java code, point to it like this:

System.setProperty("webdriver.chrome.driver", "path/to/chromedriver");

---

✅ Step 5: You're Ready!

Now open your IDE, create a new Java project, and try this quick test script:

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;

public class Test {
    public static void main(String[] args) {
        System.setProperty("webdriver.chrome.driver", "path/to/chromedriver");

        WebDriver driver = new ChromeDriver();
        driver.get("https://www.google.com");

        System.out.println("Title: " + driver.getTitle());

        driver.quit();
    }
}

If the browser opens and you see Google, congratulations—you’ve successfully set up Selenium with Java!

---

🔄 Summary

Tool	Why You Need It
Java JDK	To write and run Java code
Eclipse/IntelliJ	To develop in Java with ease
Selenium Java Library	To control the browser
ChromeDriver	To connect Selenium with Chrome browser

Introduction to Selenium WebDriver with Java: Automate

🚀 Introduction to Selenium WebDriver with Java: Automate Your Browser Like a Boss!

Ever dreamed of having your browser run on autopilot? Like automatically opening websites, clicking buttons, filling out forms, or testing your web app while you sit back and relax?

Meet Selenium WebDriver—your browser’s best friend and your personal web automation assistant.

🌐 What is Selenium WebDriver?

Selenium WebDriver is a free, open-source tool that lets you control web browsers using code. It simulates how a real user interacts with a website—like clicking, typing, scrolling, and even submitting forms.

In short:

You write Java code → Selenium controls the browser → Tasks get done automatically.

Whether you're into testing, scraping, or just learning how websites work, Selenium is a must-have tool in your toolkit.

💡 Why Use Selenium with Java?

• ✅ Java is one of the most popular languages in the world—perfect for large-scale testing.

• ✅ Strong community support and rich documentation.

• ✅ Works seamlessly with testing frameworks like JUnit and TestNG.

• ✅ Great for building scalable test automation frameworks.

🔧 What Can You Do with Selenium?

With just a few lines of Java code, you can:

• Launch any website

• Click on buttons and links

• Fill out login forms

• Take screenshots

• Run automated test scripts across multiple browsers

👨‍💻 Let’s See It in Action: A Simple Example in Java

Here’s a quick demo to search something on Google using Selenium and Java:

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;

public class GoogleSearchAutomation {
    public static void main(String[] args) {
        // Set the path to your ChromeDriver
        System.setProperty("webdriver.chrome.driver", "path/to/chromedriver");

        // Launch Chrome browser
        WebDriver driver = new ChromeDriver();

        // Open Google
        driver.get("https://www.google.com");

        // Find the search box and type a query
        WebElement searchBox = driver.findElement(By.name("q"));
        searchBox.sendKeys("Selenium WebDriver with Java");
        searchBox.submit();

        // Wait and close the browser (optional)
        try {
            Thread.sleep(3000); // Just to see the result
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        driver.quit();
    }
}

That’s it! You just made your browser open Google, type a search query, and hit Enter—all from Java code.

🚀 What's Next?

In the next parts of this series, we’ll dive into:

• How to set up Selenium in your Java project

• Working with different web elements (buttons, dropdowns, alerts, etc.)

• Running tests across multiple browsers

• Building your first testing framework using TestNG or JUnit

---

Selenium is more than just automation—it's a superpower for web testers and developers. Once you get started, you'll wonder how you ever lived without it.

Ready to dive deeper? Let’s automate the web—one line of Java code at a time.