viktor.external.spreadsheet

SpreadsheetError

exception viktor.external.spreadsheet.SpreadsheetError¶

Bases: Exception

Custom error to be thrown when there is an error with a spreadsheet service

NamedInputCell

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

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

SpreadsheetCalculationInput

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

Bases: viktor.external.spreadsheet.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.

NamedOutputCell

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

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

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

property result: Any¶

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

Return type

Any

Returns

the result of this cell after excel execution

equals(named_cell_result)¶

Return type

bool

serialize()¶

Return type

dict

DirectInputCell

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

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)¶

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

Example usage:

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

property result: Any¶

Return type

Any

equals(direct_cell_result)¶

Return type

bool

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

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.

SpreadsheetResult

class viktor.external.spreadsheet.SpreadsheetResult(*, values=None, file_content=None)¶

Wrapper around results obtained from spreadsheet services

property values: dict¶

Return type

dict

property file_content: bytes¶

Return type

bytes

get_value(name)¶

Return type

Any

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:

file = BytesIO(b'file')

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

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

Parameters

file (BytesIO) – BytesIO object of the spreadsheet

classmethod from_path(file_path, inputs)¶

Parameters

• inputs (List[SpreadsheetCalculationInput]) –

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

Return type

SpreadsheetCalculation

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 result: viktor.external.spreadsheet.SpreadsheetResult¶

Return type

SpreadsheetResult

SpreadsheetTemplate

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

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

Example usage:

file = BytesIO(b'my_template')

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

template = SpreadsheetTemplate(file, 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 BytesIO object of the filled template.

Return type

SpreadsheetResult

Returns

a BytesIO object containing the bytes of the filled template

property result: viktor.external.spreadsheet.SpreadsheetResult¶

Return type

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