Topic:
Using WebService API in Conjunction with EXCEL and Python
Notes:
Spreadsheet programs like MS EXCEL are very popular with engineers because they allow you to simply automatize your calculations and quickly output the results. Therefore, combining MS EXCEL used as a graphical interface with Dlubal's WebService API is an obvious choice. By using the free xlwings library for Python, you can control EXCEL, and read and write values. The functionality is described in the following, using an example.
Description:
Preparation
The following is required for the example and for programming:
- RFEM 6
- EXCEL
- Python
- Dlubal RFEM_Python_Client
- Python library xlwings
- An editor for writing the program
Assuming that RFEM 6, EXCEL, and Python are already installed, you should first install the RFEM_Python_Client libraries. We offer various learning videos and help pages describing the installation. The following links are particularly important for installation. They show you the installation as well as the first steps in programming:
- https://dlubal-software.github.io/.github/guide/
- https://www.dlubal.com/en-US/support-and-learning/learning/videos/003477
- https://www.dlubal.com/en-us/support-and-learning/learning/videos/003478
- https://www.dlubal.com/en-us/support-and-learning/learning/videos/003479
Program editors are also discussed in these videos. For the present example, the editor used is irrelevant. Then, xlwings must be installed; you can find corresponding information on the library website:
The complete code, for example, is located in the RFEM_Python_Client in the following path:
- RFEM_Python_Client/tree/main/Examples/SteelDesign/
The SteelDesignExcel.py file is the Python program (script) and the SteelDesignExcel.xlsm file is the associated EXCEL file.
Example of Programming
The xlwings library offers several options for combining Python and EXCEL. On one hand, it is possible to control EXCEL out of a Python program (better known as a Python script) and also to start a Python script out of EXCEL. When starting out of EXCEL, you can either use the built-in plug-in or create a sub-routine which then starts the Python script. Details are shown here:
In our example, the Python script is started via the built-in plug-in (see image). There are two requirements. The Python script must have the same name as the EXCEL file, and it must include a main function because this function is then executed via the plug-in.
Here is the first part of the main function:
- code.de#
...
- Open the excel sheet
wb = xw.Book.caller()
- Read inputs
inputSheet = wb.sheets('Inputs')
frame_number = 6
width = 10
frame_length = 4
console_height = 3
column_height = 4
gable_height = 2
- Geometric Inputs
frame_number = int(inputSheet["G6"].value) # number of frames
width = inputSheet["G7"].value # Width of Frame
frame_length = inputSheet["G8"].value # Frame Length
console_height = inputSheet["G9"].value # Height of Console
column_height = inputSheet["G10"].value # Height of Column
gable_height = inputSheet["G11"].value # Height of Gable
...
- /#
If xlwings is used directly out of a Python script (not via EXCEL), a file must be opened first:
- code.de#
wb = xw.Book('userSheet.xlsm')
- /#
If xlwings is used out of EXCEL, the connection is established using the following command:
- code.de#
wb = xw.Book.caller()
- /#
The wb variable includes the complete workbook, from which individual worksheets can be extracted using the sheets() method. In the example, the "Inputs" worksheet is opened, and individual cells are accessed by means of square brackets. The value of a cell can be read out or modified by the 'value' property. This implementation can be found at the end of the example:
- code.de#
...
- Writing Results to Output Sheets
nodaldeformation["A2"].value = node_number
nodaldeformation["B2"].value = nodeSupportType
nodaldeformation["C2"].value = nodeDisp_abs
nodaldeformation["D2"].value = nodeDisp_x
nodaldeformation["E2"].value = nodeDisp_y
nodaldeformation["F2"].value = nodeDisp_z
nodaldeformation["G2"].value = nodeRotation_x
nodaldeformation["H2"].value = nodeRotation_y
nodaldeformation["I2"].value = nodeRotation_z
...
- /#
In the present case, load combination 7 is read out. Image 2 shows the deformations of the members in EXCEL and, for comparison, the results from RFEM 6 in Image 3.
Summary
In the example...