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.