Tutorial - Spreadsheet Calculator

info

Level: Beginners
Time: 20 min

Prerequisites:

You have an account and completed the installation process. No account? Get one here
You have some experience with reading Python code

Not a reader? feel free to follow this tutorial as a video

Introduction

Welcome to the spreadsheet calculations tutorial! In most branches of engineering, spreadsheets are the golden standard for sharing results and knowledge in a clear, well organised way. In this tutorial you will learn how to implement an existing calculation from an excel sheet into a VIKTOR web application.

For this, the example we will use is a simply supported beam under load and all the calculations will be performed in the spreadsheet. In this tutorial we will cover:

By the end of this tutorial, you will have created a VIKTOR application that generates calculations from a spreadsheet and that will allow the user to download the results to share with colleagues. You can test the final result below:

info

You can now also use the 'Create new app - Upload an Excel sheet' option in the App Store to instantly generate the boilerplate code for your particular excel spreadsheet. However, we recommend that you follow this tutorial to get an understanding of the fundamentals of developing a VIKTOR app.

1. Create, install and start an empty app

Need help?

Are you encountering an error? Take a look at the complete app code.

Also know that you can always ask for help at our Community Forum, where our developers are ready to help with any question related to the installation, coding and more.

Let's create, install and start an empty app. This will be the starting point for the rest of the tutorial. But before we start, make sure to shut down any app that is running (like the demo app) by closing the command-line shell (for example Powershell) or end the process using Ctrl + C.

Follow these steps to create, install and start an empty app:

Go to the App store in your VIKTOR environment to create a new app. After clicking 'Create app' choose the option 'Create blank app' and enter a name, 'spreadsheet-tutorial', and description of your choice. Submit the form by clicking 'Create and setup'.

Select 'Editor' as app type and click 'Next'.
Now follow the instructions to run the quickstart command to download the empty app template. After entering the command click 'I have run the command' to continue. The CLI will ask you to select your code editor of choice. Use the arrows and press enter to select a code editor. The app will now open in your code editor of choice.

If all went well, your empty app is installed and connected to your development workspace. Do not close the terminal as this will break the connection with your app. The terminal in your code editor should show something like this:

INFO    : Connecting to cloud.viktor.ai...
INFO    : Connection is established:
INFO    : 
INFO    :     https://cloud.viktor.ai/workspaces/XXX/app  <--- navigate here to find your app
INFO    : 
INFO    : The connection can be closed using Ctrl+C
INFO    : App is ready

Re-starting your app

You only need create an app template and install it once for each new app you want to make.
The app will update automatically once you start adding code in app.py, as long as you don't close the terminal or your code editor.
Did you close your code editor? Use viktor-cli start to start the app again. No need to install, clear, etc.

Did you encounter any errors?

Always make sure to check the spelling of everything you placed in the command-line, a small mistake and the command you are trying to run may not be recognised!
If you are encountering:
```
ERROR:
  Exiting because of an error: no requirements.txt file
PS C:\Users\<username>\viktor-apps>
```
Then you are not in the correct folder! check the command-line and navigate to the spreadsheet-tutorial folder.

If you are encountering:

Error: App definition is not compatible with the data currently stored in the database. Use the command 'viktor-cli clear' to clear the database.
PS C:\Users\<username>\viktor-apps\spreadsheet-tutorial>

That means you have not cleared the database yet! Use the viktor-cli clear to clear and then you can use viktor-cli start to start the app. No need to install it again!

Not seeing any of these errors? Head over to our community! There is a good chance another developer encountered it and solved it too!

2. The Excel spreadsheet

As mentioned previously, this example will be of a simply supported beam under load. If you would like to use or modify the example spreadsheet used for this tutorial, you can download it here [1]. You will need to add your spreadsheet to the root directory/folder, in this case the one we made when we made the empty app named 'spreadsheet-tutorial'.

[1] source: https://stuff.mit.edu (04-05-2020)

The app should now have the following folder structure:

spreadsheet-tutorial
├── tests
├── app.py
├── beam_calculation.xls
├── CHANGELOG.md
├── README.md
├── requirements.txt
└── viktor.config.toml

To be able to use a spreadsheet as a calculation tool within a VIKTOR app, a few modifications must be performed to the spreadsheet file (more elaboration on this can be found here). Open beam_calculation.xls and add two sheets: viktor-input-sheet and viktor-output-sheet. These sheets contain the input and output values respectively. In the viktor-input-sheet, add the following data:

The values from viktor-input-sheet should now be used in the Analysis sheet. For example, cell C22 below refers to the "L" parameter defined in the viktor-input-sheet sheet:

In the viktor-output-sheet the desired output parameters are defined, with their values taken from the Analysis sheet:

You should now be able to change the values on the viktor-input-sheet, resulting in changed values in the viktor-output-sheet. If you got lost along the way, or want to take the easy route, download the fully adjusted spreadsheet here.

Coupling the spreadsheet

The spreadsheet is now ready to be used within the app. VIKTOR provides a helper class specifically for this task: SpreadsheetCalculation. The first thing that needs to be done is to make sure that we can get to the spreadsheet. For this you may add the function fill_spreadsheet as described below. The app.py file should now look like this:

import viktor as vkt
from pathlib import Path


SPREADSHEET_PATH = Path(__file__).parent / "beam_calculation.xls"


def fill_spreadsheet(params) -> vkt.spreadsheet.SpreadsheetCalculation:
    inputs = [
        vkt.spreadsheet.SpreadsheetCalculationInput('L', params.beam.length),
        vkt.spreadsheet.SpreadsheetCalculationInput('W', params.beam.width),
        vkt.spreadsheet.SpreadsheetCalculationInput('H', params.beam.height),
        vkt.spreadsheet.SpreadsheetCalculationInput('E', params.beam.E),
        vkt.spreadsheet.SpreadsheetCalculationInput('aw', params.loads.aw),
        vkt.spreadsheet.SpreadsheetCalculationInput('wa', params.loads.wa),
        vkt.spreadsheet.SpreadsheetCalculationInput('wL', params.loads.wL)
    ]
    return vkt.spreadsheet.SpreadsheetCalculation.from_path(SPREADSHEET_PATH, inputs=inputs)


class Parametrization(vkt.Parametrization):
    pass


class Controller(vkt.Controller):
    parametrization = Parametrization

Great! Now that we are ready to process the excel sheet, let's start with providing the user with the possibility to enter inputs for that sheet.

3. Adding inputs and views

Our workspace is still a bit empty, lets change that! First, lets add some inputs to our parametrization so that our user can make changes to the calculations. To reduce the clutter in our app, lets make a Section for this and fill it with the parameters of the beam. The end result can be downloaded here. Additionally, we will also add an Image to our parametrization that will allow the user to check the locations and orientations of the parameters. For this we need to make two more modifications to our app.

First, add a new folder called 'assets' to our 'spreadsheet-tutorial' app folder. You need to add the beam schematic from the top of the Analysis sheet to this folder (can also be downloaded here: beam_schematic.png. The resulting folder structure would look like this:

spreadsheet-tutorial
├── tests
├── assets
│   └── beam_schematic.png
├── app.py
├── beam_calculation.xls
├── CHANGELOG.md
├── README.md
├── requirements.txt
└── viktor.config.toml

In the viktor.config.toml file remove the hashtag in order to add: assets_path = "assets" to the config. The full config file should now look like this:

# For all available configuration settings, please go to:
#   https://docs.viktor.ai/docs/create-apps/references/viktor-config-toml

app_type = 'editor'
assets_path = 'assets'
# packages = []
python_version = '3.13'
# welcome_text = 'welcome.md'
registered_name = 'spreadsheet-tutorial'

Update the Parametrization to show the required input fields:

...

class Parametrization(vkt.Parametrization):
    beam = vkt.Section('Beam')
    beam.schematic = vkt.Image(path="beam_schematic.png")
    beam.length = vkt.NumberField('Length (L)', suffix='mm', default=80, max=100)
    beam.width = vkt.NumberField('Width (W)', suffix='mm', default=10)
    beam.height = vkt.NumberField('Height (H)', suffix='mm', default=10)
    beam.E = vkt.NumberField('Modulus of Elasticity (E)', default=200000, suffix='N/mm2')

This will allow you to change the parameters of the beam which are used in our spreadsheet calculations. But you may also want to change the way we load the beam. Lets also add some input fields for that in a new Section.

...

class Parametrization(vkt.Parametrization):
    beam = vkt.Section('Beam')
    beam.schematic = vkt.Image(path="beam_schematic.png")
    beam.length = vkt.NumberField('Length (L)', suffix='mm', default=80, max=100)
    beam.width = vkt.NumberField('Width (W)', suffix='mm', default=10)
    beam.height = vkt.NumberField('Height (H)', suffix='mm', default=10)
    beam.E = vkt.NumberField('Modulus of Elasticity (E)', default=200000, suffix='N/mm2')
    
    loads = vkt.Section('Loads')
    loads.aw = vkt.NumberField('Starting point of load (aw)', suffix='mm', default=9)
    loads.lb = vkt.LineBreak()
    loads.wa = vkt.NumberField('Distributed load amplitude (wa)', suffix='N/mm', flex=40, default=5)
    loads.wL = vkt.NumberField('Distributed load amplitude (wL)', suffix='N/mm', flex=40, default=5)

Showing results and a schematic

If all went well, you should now be able to see all the inputs for the beam and loads in your workspace. Now let's display some results and a handy schematic so that the user knows what all the inputs mean and which way we have defined as positive and negative (coordinate system). For the results, let's add a TableView to our controller. You will need to use the method we made earlier to grab the results from the spreadsheet.

...

class Controller(vkt.Controller):
    parametrization = Parametrization

    @vkt.TableView("Outputs")
    def spreadsheet_calculation(self, params, **kwargs) -> vkt.TableResult:
        sheet = fill_spreadsheet(params)
        result = sheet.evaluate()

        column_headers = [vkt.TableHeader("Value", num_decimals=2), "Unit", "Description"]
        row_headers = [
            "maximum_deflection",
            "maximum_bending_stress",
        ]
        data = [
            [result.values["maximum_deflection"], "microns", ""],
            [result.values["maximum_bending_stress"], "N/mm2", "Maximum bending stress along the beam"],
        ]

        return vkt.TableResult(data, row_headers=row_headers, column_headers=column_headers)

After reloading the app, you should now be able to see the maximum deflection and maximum bending stress in the workspace.

tip

If you get stuck, you can always try to have a quick peek at the complete code at the end of this tutorial

Beautiful! If all went well you should now be able to use the schematic as a reference to define your loads and dimensions of the beam and see the maximum stress and deflection in the DataView.

Adding plots for analysis

To perform some analysis, lets add a plot to show the curvature along the beam. All the data points for this are already in the excel file so you will only need to extract and plot this data. The data we are using for this example is the deflection data but you can use any other data that the sheet provides as the pandas dataframe will have all the data. We can then make a figure using the plotly.express package and display this figure in our application.

First, let's add the plotly, pandas and openpyxl packages to our app by updating the requirements.txt to:

viktor==...
plotly
pandas
openpyxl

Second, let's add the imports and the PlotlyView to the controller:

import viktor as vkt
import pandas as pd
import plotly.express as px
from pathlib import Path

...

class Controller(vkt.Controller):
    ...

    @vkt.PlotlyView('Beam Curvature')
    def beam_curvature(self, params, **kwargs):
        sheet = fill_spreadsheet(params)
        result = sheet.evaluate(include_filled_file=True)
        evaluated_file = vkt.File.from_data(result.file_content)
        with evaluated_file.open_binary() as fp:
            data_df = pd.read_excel(fp, sheet_name='Data')
        deflection_data = data_df['Deflection (microns)'].head(params.beam.length+1)
        fig = px.line(deflection_data, title='Beam deflection', labels={'value': 'Deflection (microns)', 'index': 'Length (mm)'})
        return vkt.PlotlyResult(fig.to_json())

4. Download the spreadsheet

Now that we have all the functionality for our spreadsheet calculations, we can download the result as a spreadsheet to share with our colleagues in our next meeting.

...

class Parametrization(vkt.Parametrization):
    ...

    calculation_sheet = vkt.Section('Download')
    calculation_sheet.button = vkt.DownloadButton('Download', 'download_spreadsheet')

Then, to our controller class we need to add the method download_spreadsheet to download the spreadsheet.

...

class Controller(vkt.Controller):
    ...

    def download_spreadsheet(self, params, **kwargs):
        sheet = fill_spreadsheet(params)
        result = sheet.evaluate(include_filled_file=True)
        return vkt.DownloadResult(result.file_content, 'evaluated_beam.xlsx')

Give it a quick test to make sure you made no mistakes and everything works. Now admire the beautiful app you have made!

5. All code together

Congratulations!!!

This is concludes the tutorial, you have now built a fully functional app to perform spreadsheet calculations. Enjoy!!!

Complete app.py code

import viktor as vkt
import pandas as pd
import plotly.express as px
from pathlib import Path

SPREADSHEET_PATH = Path(__file__).parent / "beam_calculation.xls"


def fill_spreadsheet(params) -> vkt.spreadsheet.SpreadsheetCalculation:
    inputs = [
        vkt.spreadsheet.SpreadsheetCalculationInput('L', params.beam.length),
        vkt.spreadsheet.SpreadsheetCalculationInput('W', params.beam.width),
        vkt.spreadsheet.SpreadsheetCalculationInput('H', params.beam.height),
        vkt.spreadsheet.SpreadsheetCalculationInput('E', params.beam.E),
        vkt.spreadsheet.SpreadsheetCalculationInput('aw', params.loads.aw),
        vkt.spreadsheet.SpreadsheetCalculationInput('wa', params.loads.wa),
        vkt.spreadsheet.SpreadsheetCalculationInput('wL', params.loads.wL)
    ]
    return vkt.spreadsheet.SpreadsheetCalculation.from_path(SPREADSHEET_PATH, inputs=inputs)


class Parametrization(vkt.Parametrization):
    beam = vkt.Section('Beam')
    beam.schematic = vkt.Image(path="beam_schematic.png")
    beam.length = vkt.NumberField('Length (L)', suffix='mm', default=80, max=100)
    beam.width = vkt.NumberField('Width (W)', suffix='mm', default=10)
    beam.height = vkt.NumberField('Height (H)', suffix='mm', default=10)
    beam.E = vkt.NumberField('Modulus of Elasticity (E)', default=200000, suffix='N/mm2')
    
    loads = vkt.Section('Loads')
    loads.aw = vkt.NumberField('Starting point of load (aw)', suffix='mm', default=9)
    loads.lb = vkt.LineBreak()
    loads.wa = vkt.NumberField('Distributed load amplitude (wa)', suffix='N/mm', flex=40, default=5)
    loads.wL = vkt.NumberField('Distributed load amplitude (wL)', suffix='N/mm', flex=40, default=5)

    calculation_sheet = vkt.Section('Download')
    calculation_sheet.button = vkt.DownloadButton('Download', 'download_spreadsheet')


class Controller(vkt.Controller):
    parametrization = Parametrization
    
    @vkt.TableView("Outputs")
    def spreadsheet_calculation(self, params, **kwargs) -> vkt.TableResult:
        sheet = fill_spreadsheet(params)
        result = sheet.evaluate()

        column_headers = [vkt.TableHeader("Value", num_decimals=2), "Unit", "Description"]
        row_headers = [
            "maximum_deflection",
            "maximum_bending_stress",
        ]
        data = [
            [result.values["maximum_deflection"], "microns", ""],
            [result.values["maximum_bending_stress"], "", "Maximum bending stress along the beam"],
        ]
        return vkt.TableResult(data, row_headers=row_headers, column_headers=column_headers)
    
    @vkt.PlotlyView('Beam Curvature')
    def beam_curvature(self, params, **kwargs):
        sheet = fill_spreadsheet(params)
        result = sheet.evaluate(include_filled_file=True)
        evaluated_file = vkt.File.from_data(result.file_content)
        with evaluated_file.open_binary() as fp:
            data_df = pd.read_excel(fp, sheet_name='Data')
        deflection_data = data_df['Deflection (microns)'].head(params.beam.length+1)
        fig = px.line(deflection_data, title='Beam deflection', labels={'value': 'Deflection (microns)', 'index':'Length (mm)'})
        return vkt.PlotlyResult(fig.to_json())
    
    def download_spreadsheet(self, params, **kwargs):
        sheet = fill_spreadsheet(params)
        result = sheet.evaluate(include_filled_file=True)
        return vkt.DownloadResult(result.file_content, 'evaluated_beam.xlsx')

The full app-repository can be downloaded here.

Want to learn how VIKTOR works?

If you are interested in how VIKTOR works behind the scenes, for example how it processes your input, expand the tabs below!

How does it work?

How does the Parametrization work?

In the Parameterization class you can add input fields that allow the user to provide input to your app, and there are more than 20 different input fields you can use, including numbers, text, colors, images and files.

Inside the Parametrization class, you can also format the layout of your app by adding sections, tabs, steps and pages.

To show your Parametrization in the app, we need to add the line parametrization = Parametrization inside the Controller class, because it is the controller that determines what is shown and not.

How does the Parametrization get saved?

So you may be wondering, how do you get the information from the parametrization to my controller? Well, we do this automatically for you. The values of all parameters are stored in a single variable called params , which is accessible inside the Controller class.

These variables are stored in a Munch; this is similar to a dictionary, but work with point denotation.

Example:

Let's say we have a variable called height as a NumberField in our Parameterization.
To use it in a method in the Controller, define it as: def my_method(self, params, **kwargs)
You can now make calculations inside that method using our height parameter as params.height!

How does the Controller work?

The Controller class is the place where you add everything you want to calculate and show.

As explained in this tutorial, we show results in a View and we always add views in our controller. You can even add several views in a single app by adding them to the controller class... and yes, we have many Views, for showing graphs, maps, 3D models, reports, images and more.

In the Controller, you also do or call your calculation. Remember that the user input given in the parametrization, is accessible inside the Controller class in the variable The params.

What's next

Very impressive! You have now learned the basics of working with external files and programs. In this tutorial, we have only scratched the surface of what you can do these integrations. So don’t stop your journey there!

If you like an extra challenge, here are some ideas:

Convert your excel sheet to a pdf and show it in a PDFView
Try to adjust the geometry to show the deformation of the beam
Or even try to re-write the logic of your Excel file to a python script to use directly in the app code!

If you enjoyed this spreadsheet tutorial and want to explore some of the possibilities with Excel and VIKTOR, check out some of our other tutorials

More about excel sheets

You can find more information about how to use excel sheets in the documents and spreadsheets guide. Also check more about documents and spreadsheets in general

Introduction​

1. Create, install and start an empty app​

2. The Excel spreadsheet​

Coupling the spreadsheet​

3. Adding inputs and views​

Showing results and a schematic​

Adding plots for analysis​

4. Download the spreadsheet​

5. All code together​

Want to learn how VIKTOR works?​

How does it work?​

What's next​