TestNG CSV Excel DataProvider
2 min readMay 22, 2020
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)