Skip to main content
Version: 13

viktor.external.excel

Macro​

class viktor.external.excel.Macro(command)

Class for defining an Excel macro.

Parameters

command (str) – the name of the Excel Macro command.

serialize()
Return type

dict

Excel​

class viktor.external.excel.Excel(template, named_input_cells=None, direct_input_cells=None, macros=None, named_output_cells=None, direct_output_cells=None, extension='.xlsm', typed_results=False)

Bases: ExternalProgram

Excel can be used to perform an analysis of an Excel sheet using a third-party worker. This Excel sheet may contain macros (i.e. .xlsm extension).

To start an analysis call the method execute(), with an appropriate timeout (in seconds). To retrieve the results call the method get_named_cell_result() or get_direct_cell_result(), after execute().

Example:

named_input_cells = [NamedInputCell('x', x)]
direct_output_cells = [DirectOutputCell('Sheet1', 'B', 3)]

excel_analysis = Excel(template, named_input_cells=named_input_cells,
    direct_output_cells=direct_output_cells, extension='.xlsx')
excel_analysis.execute(timeout=10)
result = excel.get_direct_cell_result('Sheet1', 'B', 3)
Parameters

  • template (Union[BytesIO, File]) – Excel template to be filled, executed and returned.

  • named_input_cells (Optional[List[NamedInputCell]]) – A list of named cells containing input values.

  • direct_input_cells (Optional[List[DirectInputCell]]) – A list of direct cells containing input values.

  • macros (Optional[List[Macro]]) – A list of macros to be executed. The order of the list is preserved. This means the first macro in the list will be executed first, the second macro wil be executed second, etc.

  • named_output_cells (Optional[List[NamedOutputCell]]) – A list of named cells of which the result after evaluation is desired.

  • direct_output_cells (Optional[List[DirectOutputCell]]) – A list of direct cells of which the result after evaluation is desired.

  • extension (str) – Extension of the file you want to evaluate: ‘.xlsm’ | ‘.xlsx’.

  • typed_results (bool) – Cell results are of the same type as spreadsheet, if False, all values are str

Exceptions which can be raised during calculation: - viktor.errors.ExecutionError: generic error. Error message provides more information

execute(timeout=30)

Run method to start an external Excel analysis using a VIKTOR worker.

Note

This method needs to be mocked in (automated) unit and integration tests.

Parameters

timeout (int) – Timeout period in seconds.

Raises
Return type

None

result_available()
Return type

bool

Returns

True if excel has returned a result. Warning! This does not necessarily have to be a successful result.

get_named_cell_result(name)

Function which may be called after excel.execute(). It can be used to fetch the result of a named cell.

Parameters

name (str) – Name of the named cell of which the result is desired.

Returns

The result contained in the named cell corresponding to name.

Return type

Check worker_version to know which type of result is expected:

  • worker_version < 1 returns the result with type str.

  • worker version >= 1 returns the result with type depending on cell type:

    • cell type integer / long returns integer

    • cell type single / double / currency / decimal returns float

    • cell type string returns string

    • cell type boolean returns boolean

    • cell type date returns RFC 3339 format string (e.g. “1998-02-23T00:00:00Z”)

get_direct_cell_result(sheet_name, column, row)

Function which may be called after excel.execute(). It can be used to fetch the result of a direct cell.

Parameters

  • sheet_name (str) – Name of the worksheet of the desired cell.

  • column (str) – Name of the column of the desired cell.

  • row (int) – Name of the row of the desired cell.

Returns

The result contained in the cell corresponding to (sheet_name, column, row).

Return type

Check worker_version to know which type of result is expected:

  • worker_version < 1 returns the result with type str.

  • worker version >= 1 returns the result with type depending on cell type:

    • cell type integer / long returns integer

    • cell type single / double / currency / decimal returns float

    • cell type string returns string

    • cell type boolean returns boolean

    • cell type date returns RFC 3339 format string (e.g. “1998-02-23T00:00:00Z”)

get_filled_template()

New in v13.5.0

Retrieve the filled-in template if available, otherwise raises a SpreadsheetError.

Return type

File

property filled_template: BytesIO

A BytesIO object which contains the filled template if available, otherwise raises a SpreadsheetError.

Return type

BytesIO

property success: Optional[bool]

True if excel has returned a successful result, False if excel has returned an unsuccessful result, None otherwise.

Return type

Optional[bool]

property error_message: Optional[str]

The error string containing information from the Excel worker when available, None otherwise.

Return type

Optional[str]