viktor.external.spreadsheet

DirectInputCell

class viktor.external.spreadsheet.DirectInputCell(sheet_name, column, row, value)

Bases: _DirectCellBaseClass

Class for defining a direct cell in which a value must be inserted

If a rectangular block of data has to be inserted, use InputCellRange for more efficiency.

Parameters:

• sheet_name (str) – name of the sheet in which the cell is present.

• column (str) – target column.

• row (int) – target row.

• value (Union[bool, int, str, float]) – value to be placed in the cell.

serialize(convert_value_to_str=False)

Parameters:

convert_value_to_str (bool) – convert value to str. Necessary when DirectInputCell is used in coupled Excel program (only old workers).

Return type:

dict

DirectOutputCell

class viktor.external.spreadsheet.DirectOutputCell(sheet_name, column, row)

Bases: _DirectCellBaseClass

Class for defining a direct cell of which an output is desired.

Example usage:

direct_cell = DirectOutputCell('sheet_name', 'G', 3)
excel = Excel(template, direct_output_cells=[direct_cell])
excel.execute()
my_desired_value = direct_cell.result

equals(direct_cell_result)

Return type:

bool

property result: Any

serialize()

Return type:

dict

InputCellRange

class viktor.external.spreadsheet.InputCellRange(sheet_name, left_column, top_row, data)

Convenience object to define a range of cells in row- and/or column direction.

For single cells, use DirectInputCell

Example:

data = [
    [1, 2, 3],
    [4, 5, 6],
    ['a', 'b', 'c'],
]
cell_range = InputCellRange('Sheet1', left_column='B', top_row=3, data=data)

This produces the following sheet:

	A	B	C	D	E
1
2
3		1	2	3
4		4	5	6
5		a	b	c
6

Parameters:

• sheet_name (str) – name of the sheet in which the data is inserted

• left_column (str) – column letter of the top left target of the data

• top_row (int) – row number of the top left target of the data

• data (List[List[Union[float, str]]]) – content which is filled in the cell range. the nested list structure should be rectangular (each list should have the same length) and not empty.

serialize()

Return type:

dict

NamedInputCell

class viktor.external.spreadsheet.NamedInputCell(name, value)

Bases: _NamedCellBaseClass

Class for defining a named cell in which a value must be inserted.

Parameters:

• name (str) – name of the cell.

• value (Union[bool, int, str, float]) – value to be placed in the cell.

serialize(convert_value_to_str=False)

Parameters:

convert_value_to_str (bool) – convert value to str. Necessary when NamedInputCell is used in coupled Excel program (only old workers).

Return type:

dict

serialize_for_fill_spreadsheet()

Return type:

dict

NamedOutputCell

class viktor.external.spreadsheet.NamedOutputCell(name)

Bases: _NamedCellBaseClass

Class for defining named cells of which the output is desired. Example usage:

named_cell = NamedOutputCell('my_name')
excel = Excel(template, named_output_cells=[named_cell])
excel.execute()
my_desired_value = named_cell.result

equals(named_cell_result)

Return type:

bool

property result: Any

Property that returns the result of this cell. May be called after Excel has been executed.

Returns:

the result of this cell after excel execution

serialize()

Return type:

dict

SpreadsheetCalculation

class viktor.external.spreadsheet.SpreadsheetCalculation(file, inputs)

Using a spreadsheet for calculations, inserting inputs and reading outputs. This spreadsheet should not contain macros. See the excel module for spreadsheet calculations with macros.

Example usage:

inputs = [
   SpreadsheetCalculationInput('x', 1),
   SpreadsheetCalculationInput('y', 2),
]

spreadsheet = SpreadsheetCalculation(spreadsheet, inputs)
result = spreadsheet.evaluate(include_filled_file=False)
values = result.values

Parameters:

file (Union[BytesIO, File]) – spreadsheet file

evaluate(include_filled_file=False)

This function enters the values provided into the input tab of the sheet. The sheet evaluates the input and returns a dictionary containing key value pairs of the result parameters

Note

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

Parameters:

include_filled_file (bool) – when True, the SpreadsheetResult will contain the filled in spreadsheet.

Return type:

SpreadsheetResult

property file: File

New in v14.14.0

Returns a File object of the to-be-calculated spreadsheet.

classmethod from_path(file_path, inputs)

Parameters:

• file_path (Union[str, bytes, PathLike]) – Complete path including extension

• inputs (List[SpreadsheetCalculationInput])

Return type:

SpreadsheetCalculation

property result: SpreadsheetResult

SpreadsheetCalculationInput

class viktor.external.spreadsheet.SpreadsheetCalculationInput(name, value)

Bases: NamedInputCell

This class is subclassed from NamedInputCell because the same functionality is needed.

Parameters:

• name (str) – name of the cell.

• value (Union[bool, int, str, float]) – value to be placed in the cell.

SpreadsheetResult

class viktor.external.spreadsheet.SpreadsheetResult(*, values=None, file=None)

Wrapper around results obtained from spreadsheet services.

Warning

Do not instantiate this class directly, it is created by the spreadsheet service.

property file: File

New in v14.14.0

Returns a File object of the resulting filled-in spreadsheet.

property file_content: bytes

get_value(name)

Return type:

Any

property values: dict

SpreadsheetTemplate

class viktor.external.spreadsheet.SpreadsheetTemplate(file, input_cells)

Note

Prefer to use the function render_spreadsheet() instead.

Fill spreadsheet with values/text. This can be done both with direct cells (e.g. A2), or named cells.

Example usage:

cells = [
   DirectInputCell('sheet1', 'A', 1, 5),
   NamedInputCell('named_cell_1', 'text_to_be_placed'),
]

template = SpreadsheetTemplate(template, cells)

result = template.render()
filled_template = result.file_content

Parameters:

• file (BytesIO) – BytesIO object of the spreadsheet

• input_cells (List[Union[DirectInputCell, NamedInputCell, InputCellRange]]) – The cells to fill the file with.

classmethod from_path(file_path, input_cells)

Parameters:

• file_path (Union[str, bytes, PathLike]) – Complete path including extension

• input_cells (List[Union[DirectInputCell, NamedInputCell, InputCellRange]]) – The cells to fill the file with

Return type:

SpreadsheetTemplate

render()

This function renders the SpreadsheetTemplate with cells. It returns a SpreadsheetResult object of the filled template.

Return type:

SpreadsheetResult

Returns:

a SpreadsheetResult object containing the filled template

property result: SpreadsheetResult

render_spreadsheet

viktor.external.spreadsheet.render_spreadsheet(template, cells)

Fill spreadsheet with values/text. This can be done both with direct cells (e.g. A2), or named cells.

Example usage:

cells = [
   DirectInputCell('sheet1', 'A', 1, 5),
   NamedInputCell('named_cell_1', 'text_to_be_placed'),
]

template_path = Path(__file__).parent / 'my' / 'relative' / 'path' / 'template.xlsx'
with open(template_path, 'rb') as template:
    filled_spreadsheet = render_spreadsheet(template, cells)

Parameters:

• template (BinaryIO) – spreadsheet template file

• cells (List[Union[DirectInputCell, NamedInputCell, InputCellRange]]) – cells to fill the template with

Return type:

File

Returns:

File object containing the rendered spreadsheet

stringify_value

viktor.external.spreadsheet.stringify_value(value)

Function that will create the correct string representation of python data types, in order for ‘value’ to be inserted in Excel correctly.

Parameters:

value (Union[bool, int, str, float]) – the value to be stringified.

Return type:

str

Returns:

a string representation of value.