Cucumber Data Tables - Excel Data to Data Table

CsvConfiguration.java

package io.testing.tables.csvtable;

import java.util.Objects;

public class CsvConfiguration {

	private final String fileName;

	public String getFileName() {
		return fileName;
	}

	public String getFileLocation() {
		return fileLocation;
	}

	public int getIndex() {
		return index;
	}

	private final String fileLocation;
	private int index = -1;

	private CsvConfiguration(String fileName, String fileLocation, int index) {
		this.fileName = fileName;
		this.fileLocation = fileLocation;
		this.index = index;
	}

	public static class CsvConfigurationBuilder {
		private String fileName;
		private String fileLocation;
		private int index = -1;

		public CsvConfigurationBuilder setFileName(String fileName) {
			this.fileName = fileName;
			return this;

		}

		public CsvConfigurationBuilder setFileLocation(String fileLocation) {
			this.fileLocation = fileLocation;
			return this;

		}

		public CsvConfigurationBuilder setIndex(int index) {
			this.index = index;
			return this;

		}

		public CsvConfiguration build() {
			Objects.requireNonNull(fileName);
			Objects.requireNonNull(fileLocation);
			return new CsvConfiguration(fileName, fileLocation, index);
		}

	}

}

CsvDataReader.java

package io.testing.tables.csvtable;

import java.io.File;
import java.io.FileReader;
import java.io.Reader;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;

import io.cucumber.core.logging.Logger;
import io.cucumber.core.logging.LoggerFactory;
import io.testing.tables.datatables.IDataReader;

public class CsvDataReader implements IDataReader {

	private final CsvConfiguration csvConfig;
	private Logger logger = LoggerFactory.getLogger(CsvDataReader.class);

	public CsvDataReader(CsvConfiguration csvConfig) {
		this.csvConfig = csvConfig;
	}

	@Override
	public List<Map<String, String>> getAllRows() {
		List<Map<String, String>> data = new ArrayList<Map<String, String>>();

		try (Reader in = new FileReader(new File(csvConfig.getFileLocation()))) {
			CSVFormat format = getCsvFormat();
			Iterable<CSVRecord> csvRecords = format.parse(in);

			csvRecords.forEach((csvRecord) -> {

				Map<String, String> csvRecordMap = new HashedMap<String, String>();
				csvRecordMap.put(CsvHeaders.first_name.name(), csvRecord.get(CsvHeaders.first_name.name()));
				csvRecordMap.put(CsvHeaders.last_name.name(), csvRecord.get(CsvHeaders.last_name.name()));
				csvRecordMap.put(CsvHeaders.email.name(), csvRecord.get(CsvHeaders.email.name()));
				csvRecordMap.put(CsvHeaders.gender.name(), csvRecord.get(CsvHeaders.gender.name()));
				csvRecordMap.put(CsvHeaders.city.name(), csvRecord.get(CsvHeaders.city.name()));

				data.add(csvRecordMap);

			});

		} catch (Exception e) {
			logger.error(e, () -> {
				return String.format("Not able to read the CSV %s from location %s", csvConfig.getFileName(),
						csvConfig.getFileLocation());
			});
			return Collections.emptyList();
		}

		return Collections.unmodifiableList(data);
	}

	private CSVFormat getCsvFormat() {
		return CSVFormat.EXCEL.builder().setSkipHeaderRecord(true).setHeader(CsvHeaders.class).build();
	}

	@Override
	public Map<String, String> getASingleRow() {
		Map<String, String> csvRecordMap = new LinkedHashMap<String, String>();

		try (Reader in = new FileReader(new File(csvConfig.getFileLocation()))) {
			CSVFormat format = getCsvFormat();
			Iterable<CSVRecord> csvRecords = format.parse(in);

			forEachWithIndex(csvRecords, (csvRecord) -> {
				csvRecordMap.put(CsvHeaders.first_name.name(), csvRecord.get(CsvHeaders.first_name.name()));
				csvRecordMap.put(CsvHeaders.last_name.name(), csvRecord.get(CsvHeaders.last_name.name()));
				csvRecordMap.put(CsvHeaders.email.name(), csvRecord.get(CsvHeaders.email.name()));
				csvRecordMap.put(CsvHeaders.gender.name(), csvRecord.get(CsvHeaders.gender.name()));
				csvRecordMap.put(CsvHeaders.city.name(), csvRecord.get(CsvHeaders.city.name()));

			});

		} catch (Exception e) {
			logger.error(e, () -> {
				return String.format("Not able to read the CSV %s from location %s", csvConfig.getFileName(),
						csvConfig.getFileLocation());
			});
			return Collections.emptyMap();
		}
		return Collections.unmodifiableMap(csvRecordMap);
	}

	private void forEachWithIndex(Iterable<CSVRecord> csvRecords, Consumer<CSVRecord> consumer) {
		int i = 1;
		for (CSVRecord record : csvRecords) {
			if (i >= csvConfig.getIndex()) {
				consumer.accept(record);
				return;
			}
			i++;
		}
	}

}

CsvHeaders.java

package io.testing.tables.csvtable;

public enum CsvHeaders {
	first_name, last_name, email, gender, city
}

DataTables.feature

Feature: Read the data from external file system and transform it

  Scenario: Convert the data table to the user define type
    Given The excel file name and location is given as
      | Excel             | Location                         | Sheet | Index |
      | MockTestData.xlsx | C:\\Data\\log\\MockTestData.xlsx | data  |     3 |
      

ExcelConfiguration.java

package io.testing.tables.datatables;

import java.util.Objects;

public class ExcelConfiguration {

	private final String fileName;

	public String getFileName() {
		return fileName;
	}

	public String getFileLocation() {
		return fileLocation;
	}

	public String getSheetName() {
		return sheetName;
	}

	public int getIndex() {
		return index;
	}

	private final String fileLocation;
	private final String sheetName;
	private int index = -1;

	private ExcelConfiguration(String fileName, String fileLocation, String sheetName, int index) {

		this.fileName = fileName;
		this.fileLocation = fileLocation;
		this.sheetName = sheetName;
		this.index = index;
	}

	public static class ExcelConfigurationBuilder {
		private String fileName;
		private String fileLocation;
		private String sheetName;
		private int index = -1;

		public ExcelConfigurationBuilder setFileName(String fileName) {
			this.fileName = fileName;
			return this;

		}

		public ExcelConfigurationBuilder setFileLocation(String fileLocation) {
			this.fileLocation = fileLocation;
			return this;

		}

		public ExcelConfigurationBuilder setSheetName(String sheetName) {
			this.sheetName = sheetName;
			return this;

		}

		public ExcelConfigurationBuilder setIndex(int index) {
			this.index = index;
			return this;

		}

		public ExcelConfiguration build() {
			Objects.requireNonNull(fileName);
			Objects.requireNonNull(fileLocation);
			Objects.requireNonNull(sheetName);
			return new ExcelConfiguration(fileName, fileLocation, sheetName, index);
		}

	}

}

ExcelDataReader.java

package io.testing.tables.datatables;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.function.BiConsumer;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import io.cucumber.core.logging.Logger;
import io.cucumber.core.logging.LoggerFactory;

public class ExcelDataReader implements IDataReader {
	private final ExcelConfiguration config;
	private Logger logger = LoggerFactory.getLogger(ExcelDataReader.class);

	public ExcelDataReader(ExcelConfiguration config) {
		this.config = config;
	}
	// 1. To get the instance of work book

	private XSSFWorkbook getWorkBook() throws InvalidFormatException, IOException {
		return new XSSFWorkbook(new File(config.getFileLocation()));
	}

	// 2. Get the sheet using the work book object

	private XSSFSheet getSheet(XSSFWorkbook workBook) {
		return workBook.getSheet(config.getSheetName());
	}

	// 3. To get the header from the excel file

	private List<String> getHeaders(XSSFSheet sheet) {
		List<String> headers = new ArrayList<String>();
		XSSFRow row = sheet.getRow(0);
		row.forEach((cell) -> {
			headers.add(cell.getStringCellValue());
		});
		return Collections.unmodifiableList(headers);
	}

	/**
	 * 
	 * every row --> Map(header, column value)
	 * first_name	last_name	email	gender	city
	 * Shurlocke	Chapleo	[email protected]	Male	Watuweri --> Map(first_name=Shurlocke, last_name=Chapleo, [email protected]...)
	 * Hali	Allery	[email protected]	Female	Kokemäki --> Map(first_name=Hali, last_name=Allery, [email protected]...)
	 * 
	 * List<Map<String, String>> data
	 */
	@Override
	public List<Map<String, String>> getAllRows() {
		List<Map<String, String>> data = new ArrayList<Map<String, String>>();

		try (XSSFWorkbook workBook = getWorkBook()) {
			XSSFSheet sheet = getSheet(workBook);
			data = getData(sheet);
		} catch (Exception e) {
			logger.error(e, () -> {
				return String.format("Not able to read the excel %s from location %s", config.getFileName(),
						config.getFileLocation());
			});
			return Collections.emptyList();
		}
		return Collections.unmodifiableList(data);

	}

	private List<Map<String, String>> getData(XSSFSheet sheet) {
		List<Map<String, String>> data = new ArrayList<Map<String, String>>();
		List<String> headers = getHeaders(sheet);

		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			Map<String, String> rowMap = new HashedMap<String, String>();
			XSSFRow row = sheet.getRow(i);
			forEachWithCounter(row, (index, cell) -> {
				rowMap.put(headers.get(index), cell.getStringCellValue());
			});
			data.add(rowMap);
		}
		return Collections.unmodifiableList(data);
	}

	private Map<String, String> getData(XSSFSheet sheet, int rowIndex) {
		List<String> headers = getHeaders(sheet);
		Map<String, String> rowMap = new HashedMap<String, String>();

		XSSFRow row = sheet.getRow(rowIndex);
		forEachWithCounter(row, (index, cell) -> {
			rowMap.put(headers.get(index), cell.getStringCellValue());
		});

		forEachWithCounter(row, (i,j) -> {
			
		});
		
		return Collections.unmodifiableMap(rowMap);
	}

	@Override
	public Map<String, String> getASingleRow() {
		Map<String, String> data = new HashedMap<String, String>();

		try (XSSFWorkbook workBook = getWorkBook()) {
			XSSFSheet sheet = getSheet(workBook);
			data = getData(sheet, config.getIndex());
		} catch (Exception e) {
			logger.error(e, () -> {
				return String.format("Not able to read the excel %s from location %s", config.getFileName(),
						config.getFileLocation());
			});
			return Collections.emptyMap();
		}
		return Collections.unmodifiableMap(data);
	}

	private void forEachWithCounter(Iterable<Cell> source, BiConsumer<Integer, Cell> biConsumer) {
		int i = 0;
		for (Cell cell : source) {
			biConsumer.accept(i, cell);
			i++;
		}
	}

}

IDataReader.java

package io.testing.tables.datatables;

import java.util.List;
import java.util.Map;

public interface IDataReader {
	
	/**
	 * To get all the rows from the excel
	 * @return
	 */
	public List<Map<String, String>> getAllRows();
	
	
	/**
	 * To get a single row from the excel
	 * @return
	 */
	public Map<String, String> getASingleRow();
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://maven.apache.org/POM/4.0.0"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>io.testing.tables</groupId>
	<artifactId>datatables</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>11</maven.compiler.source>
		<maven.compiler.target>11</maven.compiler.target>
	</properties>

	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>io.cucumber</groupId>
				<artifactId>cucumber-bom</artifactId>
				<version>7.3.4</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
			<dependency>
				<groupId>org.junit</groupId>
				<artifactId>junit-bom</artifactId>
				<version>5.8.2</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>

	<dependencies>
		<dependency>
			<groupId>io.cucumber</groupId>
			<artifactId>cucumber-java</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>io.cucumber</groupId>
			<artifactId>cucumber-junit-platform-engine</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.junit.platform</groupId>
			<artifactId>junit-platform-suite</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.junit.jupiter</groupId>
			<artifactId>junit-jupiter</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.testng/testng -->
		<dependency>
			<groupId>org.testng</groupId>
			<artifactId>testng</artifactId>
			<version>7.6.0</version>
			<scope>test</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/io.cucumber/cucumber-testng -->
		<dependency>
			<groupId>io.cucumber</groupId>
			<artifactId>cucumber-testng</artifactId>

		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>5.2.2</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.2</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-core</artifactId>
			<version>2.17.2</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-csv</artifactId>
			<version>1.9.0</version>
		</dependency>



	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.10.1</version>
				<configuration>
					<encoding>UTF-8</encoding>

				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-surefire-plugin</artifactId>
				<version>3.0.0-M6</version>
			</plugin>
		</plugins>
	</build>
</project>

StepDefinitions.java

package io.testing.tables.datatables;

import java.util.Map;

import io.cucumber.java.DataTableType;
import io.cucumber.java.en.Given;

public class StepDefinitions {
	
	@Given("The excel file name and location is given as")
	public void the_excel_file_name_and_location_is_given_as(IDataReader dataTable) {
	    // Write code here that turns the phrase above into concrete actions
	    // For automatic transformation, change DataTable to one of
	    // E, List<E>, List<List<E>>, List<Map<K,V>>, Map<K,V> or
	    // Map<K, List<V>>. E,K,V must be a String, Integer, Float,
	    // Double, Byte, Short, Long, BigInteger or BigDecimal.
	    //
	    // For other transformations you can register a DataTableType.
	   System.out.println(dataTable.getAllRows());
	   
	   
	}

	// 1. create a another method
	// 2. Parameter to the method will be a map object
	// 3. IdataReader will be return type
	// 4. @DataTableType
	
	@DataTableType
	public IDataReader excelToDataTable(Map<String, String> entry) { // [Excel= <fileName>, Location=<FileLocation> ..]
		ExcelConfiguration config = new ExcelConfiguration.ExcelConfigurationBuilder()
				.setFileName(entry.get("Excel"))
				.setFileLocation(entry.get("Location"))
				.setSheetName(entry.get("Sheet"))
				.setIndex(Integer.valueOf(entry.getOrDefault("Index", "0")))
				.build();
		return new ExcelDataReader(config);
				
	}
	
	/**
	 * --------- Working with CSV File ----------------------
	 * @DataTableType public IDataReader excelToDataTable(Map<String, String> entry)
	 *                { CsvConfiguration config = new
	 *                CsvConfiguration.CsvConfigurationBuilder()
	 *                .setFileName(entry.get("Excel"))
	 *                .setFileLocation(entry.get("Location"))
	 *                .setIndex(Integer.valueOf(entry.getOrDefault("Index", "0")))
	 *                .build(); return new CsvDataReader(config);
	 * 
	 *                }
	 * 
	 */

}

TestDataTableRunner.java

package io.testing.tables.datatables;

import io.cucumber.testng.AbstractTestNGCucumberTests;
import io.cucumber.testng.CucumberOptions;


@CucumberOptions(
		dryRun = false,
		monochrome = true,
		features = {"src/test/resources/io/testing/tables/datatables/DataTables.feature"},
		plugin = { "pretty"},
		glue = {"classpath:io.testing.tables.datatables"}
		)
public class TestDataTableRunner extends AbstractTestNGCucumberTests {

}