Skip to main content
Version: 12.11.0

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
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
classmethod from_path(file_path, input_cells)
Parameters
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
Return type

File

Returns

File object containing the rendered spreadsheet