A Simple Excel Reader & Writer

We often have requirements where we need to perform validations on thousands of URLs (fetched from sitemap), so reading/writing data from/to Excel becomes paramount.

Here I will demonstrate a simple use case which will take us through the reading and writing data in an Excel sheet; something which is very essential in data-driven testing.

I have kept the scenario as simple as possible — an Excel sheet where I will have a list of URLs that my Selenium script will visit & populate the next column with the title of the page.

Step 1: Adding Maven dependencies, of course.

<dependencies>
    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-java</artifactId>
        <version>4.0.0</version>
    </dependency>
    <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>

Step 2: Some Excel functions!

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

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {
    public static List<String> readLinks(String filePath) throws IOException {
        List<String> links = new ArrayList<>();

        FileInputStream file = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);

        for (Row row : sheet) {
            Cell cell = row.getCell(0);
            if (cell != null) {
                links.add(cell.getStringCellValue());
            }
        }
        workbook.close();
        file.close();
        return links;
    }

    public static void writeTitles(String filePath, List<String> titles) throws IOException {
        FileInputStream file = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);
        int rowIndex = 0;

        for (String title : titles) {
            Row row = sheet.getRow(rowIndex++);
            if (row == null) {
                row = sheet.createRow(rowIndex - 1);
            }
            Cell cell = row.createCell(1);
            cell.setCellValue(title);
        }

        FileOutputStream outFile = new FileOutputStream(filePath);
        workbook.write(outFile);
        workbook.close();
        outFile.close();
        file.close();
    }
}

Step 3: Let Selenium do the magic!

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

import java.util.List;

public class SeleniumExample {
    public static void main(String[] args) throws IOException {

        List<String> links = ExcelUtils.readLinks("path/to/excel/file.xlsx");
        List<String> titles = new ArrayList<>();

        WebDriver driver = new ChromeDriver();
        for (String link : links) {
            driver.get(link);
            titles.add(driver.getTitle());
        }
        driver.quit();

        ExcelUtils.writeTitles("path/to/excel/file.xlsx", titles);
    }
}

I wanted to make slight change in the scenario. Over time I want to keep adding URLs but not everyone would I want to check every website. So how about adding another column that has a flag to decide whether to work on the URL? Something like

Steps 1 and 3 remain the same. Minor tweaks in the Excel handler as follows:

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

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {
    public static List<String> readLinks(String filePath) throws IOException {
        List<String> links = new ArrayList<>();

        FileInputStream file = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);

        for (Row row : sheet) {
            Cell linkCell = row.getCell(0);
            Cell statusCell = row.getCell(1);
            if (linkCell != null && statusCell != null) {
                String status = statusCell.getStringCellValue();
                if ("YES".equalsIgnoreCase(status)) {
                    links.add(linkCell.getStringCellValue());
                }
            }
        }
        workbook.close();
        file.close();
        return links;
    }

    public static void writeTitles(String filePath, List<String> titles) throws IOException {
        FileInputStream file = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);

        int rowIndex = 0;
        for (Row row : sheet) {
            Cell statusCell = row.getCell(1);
            if (statusCell != null) {
                String status = statusCell.getStringCellValue();
                if ("YES".equalsIgnoreCase(status)) {
                    Cell titleCell = row.createCell(2);
                    titleCell.setCellValue(titles.get(rowIndex++));
                }
            }
        }

        FileOutputStream outFile = new FileOutputStream(filePath);
        workbook.write(outFile);
        workbook.close();
        outFile.close();
        file.close();
    }
}