TestNG CSV Excel DataProvider

INSTALLATION

Maven

<dependency>
  <groupId>com.github.hemanthsridhar</groupId>
  <artifactId>testng-ext-dataprovider</artifactId>
  <version>14.3.0</version>
</dependency>

Gradle

implementation 'com.github.hemanthsridhar:testng-ext-dataprovider:14.3.0'

CSV

Assuming our CSV is like below with name random_comma_seperated_value.csv

param1,param2,param3
hi,bye,hello
bye,hi,hello
hello,hi,bye

DataProvider definition

@DataProvider(parallel=true)
public Object[][] csvDataRead() throws Exception {
  String path = "src/test/resources/random_comma_seperated_value.csv";
  ExtUtils ext = new CSVUtils(path);
/*
If there are column names in the first row, 
ExtUtils ext = new CSVUtils(path, true);
*/
  return ext.parseData();
}

XLSX

Excel workbook with default sheet name (Sheet1)

@DataProvider
public Object[][] excelSheetDataRead() throws Exception {
    ExtUtils ext = new ExcelUtils("src/test/resources/workbookName.xlsx");
    return ext.parseData();
}

Excel workbook with custom sheet name

@DataProvider
public Object[][] excelSheetDataRead() throws Exception {
    ExtUtils ext = new ExcelUtils("src/test/resources/workbookName.xlsx", "sheetName");
    return ext.parseData();
}

XLS

Excel workbook with default sheet name (Sheet1)

@DataProvider
public Object[][] excelSheetDataRead() throws Exception {
    ExtUtils ext = new ExcelUtils("src/test/resources/workbookName.xls");
    return ext.parseData();
}

Excel workbook with custom sheet name

@DataProvider
public Object[][] excelSheetDataRead() throws Exception {
    ExtUtils ext = new ExcelUtils("src/test/resources/workbookName.xls","custom_sheet_name");
    return ext.parseData();
}

READING INDIVIDUAL DATA

XLSX

1.Using Column name and row number from default sheet (Sheet1)

String path = "src/test/resources/workbook.xlsx";
ExtUtils ext = new ExcelUtils(path);
ext.parseData();
ext.readCell("columnName", 2);

2.Using Column number and row number from default sheet (Sheet1)

String path = "src/test/resources/workbook.xlsx";
ExtUtils ext = new ExcelUtils(path);
ext.parseData();
ext.readCell(3, 2);

3.Using Column name and row number from custom sheet name

String path = "src/test/resources/workbook.xlsx";
ExtUtils ext = new ExcelUtils(path,"sheet_name");
ext.parseData();
ext.readCell("columnName", 2);

4.Using Column number and row number from custom sheet name

String path = "src/test/resources/workbook.xlsx";
ExtUtils ext = new ExcelUtils(path,"sheet_name");
ext.parseData();
ext.readCell("columnName", 2);

CSV

1.Using Column name and row number

String path = "src/test/resources/sample.csv";
ExtUtils ext = new CSVUtils(path,true);
ext.parseData();
ext.readCell("columnName", 2);

2.Using Column number and row number

String path = "src/test/resources/sample.csv";
ExtUtils ext = new CSVUtils(path);
ext.parseData();
ext.readCell(2, 2);

Generating Random Data from CSV and Excel workbooks

This library internally uses org.apache.commons:commons-lang3

We can generate random data as below by adding the exact text in either the workbook or csv.

randomAlphabetic(10)
randomAlphabetic(10)
randomAlphaNumeric(5)
randomNumeric(5)
randomAscii(5)
randomGraph(5)
random(5)
randomAlphabetic(1:5)
randomAlphaNumeric(1:50)
randomAlphaNumeric(25)