This guide is an informative piece to help you if you have questions about the Speadsheet calculation functionality. If you would like a more instructive lesson, take a look at the Spreadsheet calculator tutorial we have available.
Services need to be mocked within the context of (automated) testing.
SpreadsheetCalculation object can be used to
utilize a spreadsheet without macros as an external calculator. If you want to use an Excel sheet with macros
as a calculation tool, you can use VIKTOR's Excel integration.
Calculation sheet set-up
Add two sheets inside the Excel calculation document named "viktor-input-sheet" and "viktor-output-sheet".caution
Note that these names must match in order for VIKTOR to find the correct sheet!
In the input and output sheets specify the following four columns:
- A1 = Parameters,
- B1 = Unit,
- C1 = Comment,
- D1 = Values
In "viktor-input-sheet": add input identifiers in the "Parameters" column and optionally specify default values in the "Values" column
- Link to these values as a source for the calculation in the calculation sheet
- In "viktor-output-sheet": add output identifiers in the "Parameters" column
- In "viktor-output-sheet": point to the relevant cell(s) in the calculation sheet
calculation sheet example
Implementation in app
Evaluate the spreadsheet and obtain a SpreadsheetResult with a dictionary of the
output_keyparameters with corresponding values
from viktor.external.spreadsheet import SpreadsheetCalculation, SpreadsheetCalculationInput
def calculation_result(a, b):
input_list = [
spreadsheet = File.from_path('calculation_sheet.xlsx')
sheet = SpreadsheetCalculation(spreadsheet, input_list)
result = sheet.evaluate()
calculation_values = calculation_result(3, 6)
Using lists as input or output
To pass a list of values as input for the calculation spreadsheet, one can pass a (e.g. semicolon) separated string
SpreadsheetCalculationInput and write code logic in the spreadsheet to 'parse'
and use the values.
It is also possible to store a range of cells to a list as output. Simply link the cells in the columns behind "Values" to the desired cells using an array formula.
output list example
In the example
output_list2 will return the same range of results.
Currently a list can have a maximum of 47 values. A temporary workaround to make use of longer lists is by using a
TEXTJOIN function in the calculation sheet. This function returns a joined string of the results separated by a user
specified separator (e.g. ";"). In the client code, this list can then be converted back to a list of the results by