Excel Plugin

The plugin provides the ability to work with Excel documents.

Installation

  1. Copy the below line to dependencies section of the project build.gradle file

    Please make sure to use the same version for all VIVIDUS dependencies.
    Example 1. build.gradle
    implementation(group: 'org.vividus', name: 'vividus-plugin-excel', version: '0.6.11')

  2. If the project was imported to the IDE before adding new dependency, re-generate the configuration files for the used IDE and then refresh the project in the used IDE.

Table Transformers

FROM_EXCEL

FROM_EXCEL transformer generates table from the given Excel file.

Parameter Default Description

column

the resulting column name in the generated table. If it’s missing in properties then the first row is used for the column headings.

path

the Excel resource name or the Excel file path

sheet

the sheet name from the excel file

range

the semicolon-separated collection of Excel ranges from the specified excel sheet. The table header is always taken from the first range and the number of columns in subsequent ranges must correspond to the header.

addresses

the semicolon-separated collection of the cell addresses from the specified excel sheet

increment

1

the increment used to traverse the specified range

joinValues

false

defines whether cell values should be joined

lineBreakReplacement

empty string

value to substitute line breaks in cell values
The following data types can be used in cells: NUMERIC (whole and fractional numbers), BOOLEAN, FORMULA, STRING. Other types are converted to empty string.
range and addresses are competing parameters and only one can be specified in the transformer declaration at the same time.
Example 2. Usage example
Examples:
{transformer=FROM_EXCEL, path=/my-excel-file.xlsx, sheet=Data, range=A2:A7, lineBreakReplacement= , increment=2, column=test}
Example 3. Usage example
Examples:
{transformer=FROM_EXCEL, path=/my-excel-file.xlsx, sheet=Data, addresses=A2;B4;A7, column=test, joinValues=true}
Example 4. Usage FROM_EXCEL transformer with full-table range
Examples:
{transformer=FROM_EXCEL, path=/my-excel-file.xlsx, sheet=Data, range=A2:B4}
Example 5. Usage FROM_EXCEL with a header not from the first line
Examples:
{transformer=FROM_EXCEL, path=/my-excel-file.xlsx, sheet=Data, range=A2:B2;A1:B1;A3:B4}

Steps

Create excel file containing sheet with specified content

Create temporary excel file with specified content and save the path to the variable

When I create temporary excel file with content:$content and put path to $scopes variable `$variableName`
Example 6. Create temporary excel file
When I create temporary excel file with content:
|key1  |key2  |
|value1|value2|
and put path to scenario variable `path`

Create excel file containing sheet with specified name and content

Create temporary excel file containing sheet with specified name and content and save the file’s path to the variable

When I create temporary excel file containing sheet with name `$sheetName` and content:$content and put its path to $scopes variable `$variableName`
Example 7. Create temporary excel file
When I create temporary excel file containing sheet with name `movies` and content:
|name                           |year|
|14 Peaks: Nothing Is Impossible|2021|
|Free Solo                      |2018|
|Grizzly Man                    |2005|
and put its path to scenario variable `movies-path`

Add sheet to existing excel file

Add new sheet with the specified name and content to the existing excel file.

When I add sheet with name `$sheetName` and content:$content to excel file at path `$path`

  • $sheetName - Then name of the sheet.

  • $content - The data to put into the sheet. Any valid ExamplesTable.

  • $path - The path to existing excel file.

Example 8. Add sheet with pets
When I add sheet with name `pets` and content:
|type|name      |
|duck|DuckDuckGo|
|fish|GlassFish |
|dog |Datadog   |
to excel file at path `/home/collection.xslx`

Excel validations

By sheet name

Checks that excel has a sheet with name name and specified records.

Then `$excelDocument` contains excel sheet with name `$name` and records:$records

  • $excelDocument - The excel document data to verify.

  • $name - The name of the sheet to verify.

  • $record - The ExampleTable that contains specified expected records in the sheet. Available columns:

    • cellsRange - The range of the cells to verify. (e.g. "B1:D8", "A1", "C1:C5")

    • valueRegex - The regular expression to match a value against.

Example 9. Validate excel sheet by name
When I execute HTTP GET request for resource with URL `https://github.com/vividus-framework/vividus/blob/master/vividus-plugin-excel/src/test/resources/TestTemplate.xlsx?raw=true`
Then response code is = `200`
Then `${response-as-bytes}` contains excel sheet with name `Mapping` and records:
{valueSeparator=!}
|cellsRange|valueRegex             |
!A4:B5     !(Product|Price)\d+\s*  !
!B3        !Price                  !
!C1:C5     !                       !

By sheet index

Checks that excel has a sheet with index index and specified records.

Then `$excelDocument` contains excel sheet with index `$index` and records:$records

  • $excelDocument - The excel document data to verify.

  • $index - The index of the sheet to verify.

  • $record - The ExampleTable that contains specified expected records in the sheet. Available columns:

    • cellsRange - The range of the cells to verify. (e.g. "B1:D8", "A1", "C1:C5")

    • valueRegex - The regular expression to match a value against.

Example 10. Validate excel sheet by index
When I execute HTTP GET request for resource with URL `https://github.com/vividus-framework/vividus/blob/master/vividus-plugin-excel/src/test/resources/TestTemplate.xlsx?raw=true`
Then response code is = `200`
Then `${response-as-bytes}` contains excel sheet with index `0` and records:
{valueSeparator=!}
|cellsRange|valueRegex             |
!A4:B5     !(Product|Price)\d+\s*  !
!B3        !Price                  !
!C1:C5     !                       !