Tutorial - Spreadsheet Calculator
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
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:
- Create, install and start an empty app
- The Excel spreadsheet
- Adding inputs and views
- Downloading the spreadsheet
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:
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
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 platform...
INFO : Connection is established: https://cloud.viktor.ai <---- here you can see your app
INFO : The connection can be closed using Ctrl+C
INFO : App is ready
- 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 useviktor-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.
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
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 ourParameterization
. - 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
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