Excel

VIKTOR provides an integration with Excel to be able to use an Excel sheet with macros within your app. If you want to use an Excel sheet without macros as a calculation tool, you can also consider using SpreadsheetCalculation.

VIKTOR's Excel integration requires a specific Excel worker which can be installed using these instructions.

Instances of NamedInputCell (cells which can be found using a certain key) and DirectInputCell (cell which can be found by a direct address {sheet, column, row}) can be used to fill the template. Similarly, instances of NamedOutputCell and DirectOutputCell can be used to specify which cells should be returned.

Below example assumes the template is available. The sheet can be filled with predefined values:

import viktor as vkt


def run_excel():
    named_input_cells = [
        vkt.excel.NamedInputCell('named_cell_1', 'text_to_be_placed'),
        vkt.excel.NamedInputCell('named_cell_2', 5),
    ]
    macros = [vkt.excel.Macro('caculate_mean'), vkt.excel.Macro('calculate_area')]
    area_cell = vkt.excel.DirectOutputCell('sheet1', 'B', 9)
    direct_output_cells = [
        area_cell
    ]

    excel = vkt.excel.Excel(template=my_template, named_input_cells=named_input_cells,
                            macros=macros, direct_output_cells=direct_output_cells)

    # The timeout in excel.execute() is the time in seconds the whole cycle of
    # sending the job, executing the job, and receiving the job result may take.
    excel.execute(timeout=120)
    return area_cell.result  # or excel.get_direct_cell_result('sheet1', 'B', 9)

The macros are run in the order in which they are present in the list. Thus, in above example 'calculate_mean' is run first and 'calculate_area' second.

Testing

New in v13.5.0

mock_Excel decorator for easier testing of Excel

Excel.execute needs to be mocked within the context of (automated) testing.

The viktor.testing module provides the mock_Excel decorator that facilitate mocking of workers:

import unittest

from viktor import File
from viktor.testing import mock_Excel

from app.my_entity_type.controller import MyEntityTypeController

class TestMyEntityTypeController(unittest.TestCase):

    @mock_Excel(
        get_named_cell_result={
            'cell_1': 1,  # <name>: <value>
            'cell_2': 2, 
            ...
        },
        get_direct_cell_result={
            ('sheet_1', 'A', 1): 1,  # (<sheet>, <col>, <row>): <value>
            ('sheet_1', 'C', 1): 3, 
            ...
        },
        get_filled_template=File.from_path('test_file.xlsx'),
    )
    def test_excel_analysis(self):
        MyEntityTypeController().excel_analysis()

For the decorator's input parameters the following holds:

• If a Sequence type is provided, the next entry is returned for each corresponding method call. When a call is performed on a depleted iterable, an Exception is raised.
• If a single object is provided, the object is returned each time the corresponding method is called (endlessly).
• If None is provided (default), a default File/BytesIO object (with empty content) is returned each time the corresponding method is called (endlessly).