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.