Test Data Management — Excel, CSV, and JSON Readers

package com.testerrank.utils;

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

public class CsvUtils {

    /**
     * Reads a CSV file and returns data as Object[][].
     * First row is treated as header and skipped.
     */
    public static Object[][] readCsv(String filePath) {
        List<String[]> rows = new ArrayList<>();

        try (BufferedReader br = new BufferedReader(
                new FileReader(filePath))) {

            String line;
            boolean isHeader = true;
            while ((line = br.readLine()) != null) {
                if (isHeader) {
                    isHeader = false;
                    continue; // Skip header row
                }
                rows.add(line.split(",", -1)); // -1 keeps empty values
            }
        } catch (IOException e) {
            throw new RuntimeException(
                "Failed to read CSV: " + filePath, e);
        }

        // Convert List<String[]> to Object[][]
        Object[][] data = new Object[rows.size()][];
        for (int i = 0; i < rows.size(); i++) {
            data[i] = rows.get(i);
        }
        return data;
    }
}

package com.testerrank.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {

    /**
     * Reads data from a specific sheet in an Excel file.
     * First row is header (skipped).
     * Returns Object[][] for use with DataProvider.
     */
    public static Object[][] readExcel(String filePath,
                                        String sheetName) {
        List<Object[]> data = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                throw new RuntimeException(
                    "Sheet '" + sheetName + "' not found in "
                    + filePath);
            }

            int totalRows = sheet.getPhysicalNumberOfRows();
            int totalCols = sheet.getRow(0).getPhysicalNumberOfCells();

            // Start from row 1 (skip header)
            for (int r = 1; r < totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;

                Object[] rowData = new Object[totalCols];
                for (int c = 0; c < totalCols; c++) {
                    Cell cell = row.getCell(c);
                    rowData[c] = getCellValue(cell);
                }
                data.add(rowData);
            }
        } catch (IOException e) {
            throw new RuntimeException(
                "Failed to read Excel: " + filePath, e);
        }

        return data.toArray(new Object[0][]);
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) return "";

        if (cell.getCellType() == CellType.NUMERIC) {
            // Avoid scientific notation for large numbers
            double value = cell.getNumericCellValue();
            if (value == Math.floor(value)) {
                return String.valueOf((long) value);
            }
            return String.valueOf(value);
        }
        return cell.toString().trim();
    }
}

package com.testerrank.utils;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class JsonUtils {

    private static final Gson gson = new Gson();

    /**
     * Reads JSON array and returns Object[][] for DataProvider.
     * Each JSON object becomes one row.
     */
    public static Object[][] readJsonArray(String filePath,
                                            String... keys) {
        List<Object[]> data = new ArrayList<>();

        try (FileReader reader = new FileReader(filePath)) {
            JsonArray array = JsonParser.parseReader(reader)
                .getAsJsonArray();

            for (JsonElement element : array) {
                JsonObject obj = element.getAsJsonObject();
                Object[] row = new Object[keys.length];
                for (int i = 0; i < keys.length; i++) {
                    JsonElement val = obj.get(keys[i]);
                    row[i] = val != null ? val.getAsString() : "";
                }
                data.add(row);
            }
        } catch (IOException e) {
            throw new RuntimeException(
                "Failed to read JSON: " + filePath, e);
        }

        return data.toArray(new Object[0][]);
    }

    /**
     * Reads a single JSON object as a Map.
     * Useful for loading a specific test scenario.
     */
    public static Map<String, String> readJsonObject(
            String filePath) {
        try (FileReader reader = new FileReader(filePath)) {
            return gson.fromJson(reader, Map.class);
        } catch (IOException e) {
            throw new RuntimeException(
                "Failed to read JSON: " + filePath, e);
        }
    }
}

package com.testerrank.utils;

import org.testng.annotations.DataProvider;

/**
 * Centralized DataProviders — all test data sources in one class.
 * Test classes reference: dataProviderClass = DataProviders.class
 */
public class DataProviders {

    private static final String DATA_DIR =
        "src/test/resources/test-data/";

    @DataProvider(name = "loginData")
    public static Object[][] loginData() {
        return CsvUtils.readCsv(DATA_DIR + "login-data.csv");
    }

    @DataProvider(name = "transferData")
    public static Object[][] transferData() {
        return ExcelUtils.readExcel(
            DATA_DIR + "transfer-data.xlsx", "Transfers");
    }

    @DataProvider(name = "searchTerms")
    public static Object[][] searchTerms() {
        return CsvUtils.readCsv(DATA_DIR + "search-terms.csv");
    }

    @DataProvider(name = "insuranceQuotes")
    public static Object[][] insuranceQuotes() {
        return JsonUtils.readJsonArray(
            DATA_DIR + "insurance-quotes.json",
            "name", "age", "coverage", "expectedPremium");
    }
}

username,password,expectedResult
testuser,password123,success
wronguser,password123,error
testuser,wrongpass,error
,password123,error
testuser,,error
,,error
admin,admin123,success
locked_user,password123,error