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.12')

  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.

Properties

Property Name Acceptable values Default Description

excel.preserve-cell-formatting

true false

false

If true original cells format will be preserved. For example cell’s date value 3/31/1994 will be represented in human-readable format 3/31/94, not 34424.0 (number of days after 01/01/1900), number value 1 will be represented as 1, not 1.0.

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}

Excel cell types

It is allowed to specify the type and format of cells while creating or adding content to Excel documents.

Type Usage example Description

String

String value

#{withCellType(String, String value)}

Default cells type. Used if the cell type is not specified explicitly

Formula

#{withCellType(Formula, DOLLAR(A2))}

#{withCellType(Formula, A2+A3)}

Excel formulas

Date

#{withCellType(Date, 03/31/2024)} - date with default date input format MM/dd/yyyy and default cell format m/d/yy. Output 3/31/24

#{withCellType(Date, 31-Mar-2024 input dd-MMM-yyyy)} - date with custom date input format dd-MMM-yyyy and default cell format m/d/yy. Output 3/31/24

#{withCellType(Date, 03/31/2024 format m.d.yyyy h:mm)} - date with default date input format MM/dd/yyyy and custom cell format m.d.yyyy h:mm. Output 3.31.2024 0:00

#{withCellType(Date, 2024/03/31 13:04 input yyyy/MM/dd HH:mm format m.d.yy h:mm)} - date with custom date input and cell formats. Output 3.31.24 13:04

Excel date.

Optional parameters:

input - the input date format based on the date/time format. Default value is MM/dd/yyyy

format - the output cell date format in Excel. Default value is m/d/yy

Boolean

#{withCellType(Boolean, false)}

Boolean cell type

Numeric

#{withCellType(Numeric, 128)} - output 128.0

#{withCellType(Numeric, 32768 format #,##0.00)} - the number with , thousands separator and two decimal places. Output 32,768.00

Number cell type

Optional parameters:

format - the current cell number format. Default value is #.0

Cells type for column

Cells type can be specified for all values in column using the next column name format: #{withColumnCellsType(<values-type>, <column-name>). The types specified in individual cells will have priority.

Example 6. Create Excel file with typed column
When I create temporary excel file with content:
|#{withColumnCellsType(date, multi-type column)}            |
|31-Mar-2024 input dd-MMM-yyyy                              |
|#{withCellType(Numeric, 100500 format #,##0.00)}           |
|#{withCellType(Boolean, true)}                             |
|2024/03/31 13:04 input yyyy/MM/dd HH:mm format m.d.yy h:mm |
and put path to scenario variable `path`

Data from output Excel file:

|multi-type column |
|3/31/24           |
|100,500.00        |
|TRUE              |
|3.31.24 13:04     |

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 7. Create temporary excel file
When I create temporary excel file with content:
|key1  |key2                                           |
|value1|#{withCellType(Numeric, 123.321 format #.000)} |
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 8. 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 table with data of different types to put into the sheet. Any valid ExamplesTable.

  • $path - The path to existing Excel file.

Example 9. 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 10. 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 11. 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     !                       !