Apache POI with Selenium

What is Apache POI in Selenium?

Apache POI is a library that allows Java programs to read and write data in Microsoft Excel format. When combined with Selenium, it becomes a powerful tool for automating test cases that require reading data from Excel files. POI can also be used to interact with CSV files for data-driven testing.

Dependencies Needed for Apache POI

To use Apache POI in Selenium, you need to include the following dependencies in your Maven `pom.xml` file:

                <!-- Apache POI dependencies for Excel handling -->
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                    <version>5.0.0</version>
                </dependency>
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                    <version>5.0.0</version>
                </dependency>
                <dependency>
                    <groupId>org.apache.commons</groupId>
                    <artifactId>commons-collections4</artifactId>
                    <version>4.4</version>
                </dependency>
            

These dependencies allow you to work with `.xlsx` files (Excel) and provide additional utility for data extraction and formatting.

Data Providers with Excel and CSV Files

Using the `@DataProvider` annotation in TestNG, you can automate test cases that rely on external data sources such as Excel and CSV files. Below are examples of how to implement data-driven testing using both formats:

Reading from an Excel File

                import org.apache.poi.ss.usermodel.*;
                import org.apache.poi.xssf.usermodel.XSSFWorkbook;
                import java.io.File;
                import java.io.FileInputStream;
                import java.util.ArrayList;
                import java.util.Iterator;

                public class ExcelDataProvider {
                    public static Object[][] getExcelData(String filePath) {
                        Object[][] data = null;
                        try {
                            FileInputStream file = new FileInputStream(new File(filePath));
                            Workbook workbook = new XSSFWorkbook(file);
                            Sheet sheet = workbook.getSheetAt(0);
                            int rows = sheet.getPhysicalNumberOfRows();
                            int columns = sheet.getRow(0).getPhysicalNumberOfCells();
                            data = new Object[rows - 1][columns];
                            for (int i = 1; i < rows; i++) {
                                Row row = sheet.getRow(i);
                                for (int j = 0; j < columns; j++) {
                                    Cell cell = row.getCell(j);
                                    data[i - 1][j] = cell.toString();
                                }
                            }
                            workbook.close();
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        return data;
                    }
                }
            

Reading from a CSV File

                import java.io.BufferedReader;
                import java.io.FileReader;
                import java.io.IOException;
                import java.util.ArrayList;

                public class CSVDataProvider {
                    public static Object[][] getCSVData(String filePath) {
                        ArrayList<String[]> data = new ArrayList<>();
                        try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
                            String line;
                            while ((line = br.readLine()) != null) {
                                String[] values = line.split(",");
                                data.add(values);
                            }
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                        return data.toArray(new Object[0][]);
                    }
                }
            

Example: Automating Login with Excel Data

Here is an example of how to use Apache POI with Selenium to automate a login form, reading data from an Excel file using a `@DataProvider`.

                import org.apache.poi.ss.usermodel.*;
                import org.apache.poi.xssf.usermodel.XSSFWorkbook;
                import org.openqa.selenium.By;
                import org.openqa.selenium.WebDriver;
                import org.openqa.selenium.WebElement;
                import org.openqa.selenium.chrome.ChromeDriver;
                import org.testng.annotations.DataProvider;
                import org.testng.annotations.Test;

                public class LoginAutomationWithExcel {

                    @DataProvider(name = "loginData")
                    public Object[][] loginData() {
                        return ExcelDataProvider.getExcelData("./testdata/login.xlsx");
                    }

                    @Test(dataProvider = "loginData")
                    public void loginTest(String username, String password) {
                        System.setProperty("webdriver.chrome.driver", "./drivers/chromedriver.exe");
                        WebDriver driver = new ChromeDriver();
                        driver.get("https://shariqsp.com/login");

                        // Enter login credentials
                        WebElement userField = driver.findElement(By.id("username"));
                        WebElement passField = driver.findElement(By.id("password"));
                        WebElement loginButton = driver.findElement(By.id("loginButton"));

                        userField.sendKeys(username);
                        passField.sendKeys(password);
                        loginButton.click();

                        driver.quit();
                    }
                }