Automating calculations in Excel through a web app

by VIKTOR

Excel is a handy tool when it comes to performing calculations. However, when multiple people work with Excel, often several versions of a file get created, which can become quite confusing. Which data is the latest version? Which information is correct?

How to create successful application that ensure adoption

Build successful applications

Learn how you (developer, engineer, end-user, domain expert, project manager, etc.) can contribute to the creation of apps that provide real value to your work.

Python and Excel in VIKTOR

With VIKTOR, it is possible to create a web app with Python that integrates with any desired software, just like Excel. This web app functions as a single point of truth with a central database that always contains the latest version of a project (with version history) that all stakeholders can access.

We have written open-source code as an example of such a VIKTOR application for the automated calculation of a simply supported beam under load with Excel.

Using the functionality

There are two ways in which you can use the functionality:

With VIKTOR, out-of-the-box, using our free version.
Without VIKTOR, in that case you integrate the open-source code with your own Python code

For a full tutorial on this web app click here.

This is a snippet of the functionality’s code from the VIKTOR GitHub repository:

1def get_evaluated_spreadsheet(self, params): 
2  inputs = [ 
3     SpreadsheetCalculationInput('L', params['general']['beam']['length']), 
4     SpreadsheetCalculationInput('W', params['general']['beam']['width']), 
5     SpreadsheetCalculationInput('H', params['general']['beam']['height']),
6     SpreadsheetCalculationInput('E', params['general']['beam']['E']),
7     SpreadsheetCalculationInput('aw', params['general']['beam']['aw']), 
8     SpreadsheetCalculationInput('wa', params['general']['beam']['wa']),
9  ] 
10  sheet_path = Path(__file__).parent / 'beam_calculation.xls'
11  sheet = SpreadsheetCalculation.from_path(sheet_path, inputs=inputs) 
12  result = sheet.evaluate(include_filled_file=True) 
13  
14  return result

Here you can see it is very easy to send input to your Excel sheet. It is required to have a tab in Excel called ‘viktor-input-sheet'. To get results, you use the evaluate() method. Here, VIKTOR retrieves output from the ‘viktor-output-sheet' Excel tab.

The documentation for the SpreadsheetCalculation class can be found here

In the video, you can see how a simply supported beam under load is calculated through an integration with a spreadsheet in a VIKTOR application.

Calculating a beam in 3 steps

As you can see in the video, the process of automatically calculating a simply supported beam under load consists of 3 steps.

Insert general information. Provide information about the beam (length, the width, height, and modulus of elasticity) and loads (starting point and distributed load amplitude).
Generate results. View the maximum deflection and maximum bending stress, a schematic visualization, and a 3D visualization of the beam and loads.

schematic visualization of a beam in Excel