Introduction to Muk3D Simulation’s Excel interface
Overview
Muk3D Simulation is a set of tools to allow Muk3D to be tightly coupled to models in Excel and GoldSim. It is designed to simplify the running of either a range of options, sensitivity analysis, or modelling sequences.
This article discusses the Excel interface in Muk3D Simulation with a simple sequence example.
The Excel integration is done through a Python script file. A shell script is created that defines inputs and outputs from/to Excel. The user then creates code within the script that tells Muk3D what to do when information is received.
To follow along with this tutorial, download the Excel Simulation project and set the working directory to /base/Example_1
This example requires Muk3D v2019.4.1 or higher to run.
Setting up an Excel Model
The Excel model script is created using the command:
Simulation/Define new Excel model
The first tab to populate is the General tab.
| Field | Value | Description |
|---|---|---|
| Model name | Pond | The name of the Excel model to create. A file called Pond.ExcelModel will be created in the working directory. |
| Sheet name | Muk3D_Interface | The name of the tab to create in the Excel workbook that will function as the input/output point. |
| Settings sheet name | Muk3D_Settings | The name of the tab to create in the Excel workbook where some static settings are stored. |
| Data is in | rows | The orientation of the data. In this example, data for each timestep will in rows. |
The next tab to populate are the User inputs. This is data that the user is prompted for at the start of a simulation run.
| Field | Value | Description |
|---|---|---|
| Datatype | file | Add a file field to the dialog presented at the start of a simulation run. |
| Name | Base grid | The label for the file field. This will also be the dictionary key to access the value later in the script. |
The next tab is the Spreadsheet inputs. These are the rows/columns of data to import from Excel.
| Field | Value | Description |
|---|---|---|
| Datatype | float | Import the first column of date as a floating point number. |
| Name | Pond elevation | The label for the data. |
The next tab is the Spreadsheet outputs. These are the rows/columns of data to export back to Excel.
| Field | Value | Description |
|---|---|---|
| Name (row 1) | Pond_volume | Creates a field for pond volume at each timestep. |
| Name (row 2) | Surface area | Creates a field for pond surface area at each timestep. |
In this example, no Spreadsheet settings are entered, so this tab can remain blank. Hit Ok to generate the Excel script.
Overview of the ExcelModel
Open Pond.ExcelModel in your text editor. In Notepad++, you can set the language to Python so syntax highlighting is enabled.
The ExcelModel is a subclass of a class called ExcelCommand which is built into Muk3D. It provides some methods that help with getting names of inputs and outputs, user input data, and an execute method. The execute method is overridden by the user to tell Muk3D what to do when it gets data from Excel.
Adding the interface to a spreadsheet
The next step is to add the Excel interface to a new or existing spreadsheet. This is done with the command:
Simulation/Setup Excel model
| Field | Value | Description |
|---|---|---|
| Sheet to use | <create new…> | For this example a new Excel workbook will be created. |
| New Excel workbook name | Excel_model_pond | The name of the workbook to create. |
| Excel model | Pond.excelmodel | The name of the ExcelModel to link to the spreadsheet. |
| Generate input/output tab | True | Creates the input/output tab if True. |
| Generate settings tab | True | Creates the settings tab if True. |
Hit OK and the Excel workbook will be created. Looking at the Excel Workbook, there will be a tab called Muk3D_Interface. It will show the Input and Output data columns.
Testing the interface
Now that the interface is in the spreadsheet, we want to test it. We can do this by putting some test data in the spreadsheet, printing values in the ExcelModel, and returning some dummy values.
Open Pond.ExcelModel in a text editor. In the execute method we will print the pond_elevation value using the ExcelCommands write method, and then return two values – the current row (accessed as Inputs[‘Row_index’]) and the input pond elevation x 2.
In the Excel worksheet, enter some dummy values, as shown below.
The final step is to run the interface. This is done using the command:
Simulation/
Once it runs, you will see the pond elevation data printed to the output window for each timestep, and then in the Excel worksheet, you should see the Row_index value, and the surface area (pond value x 2).
Recording a macro
Now that we have a working interface, we can start to do something more useful. In this example we are going to calculate pond volumes and surface areas for different pond elevations. To do this, we need to populate the execute method with code that calculates the pond volume and surface area for a given pond elevation. The easiest way to do this is to record a macro of us creating a pond surface and then we can edit it to fit in our macro.
To record a macro, run the command Scripts/Record macro. Do the following:
- Load grid+dam.mgrid from the base directory.
- Create a pond using Structures/Pond/Create a pond.
- Select the pond layer and unload it using View/Unload current layer.
- Select the pond_outline layer and unload it using View/Unload current layer.
That’s all we need to do, so stop macro recording with the Stop button on the macro toolbar or Scripts/End macro recording.
When a command runs in a macro, it returns a value(s). This is stored in the result variable after the command is executed. To find out what the is in the return value, we can just print it.
Run the script and we can see that the result is a Python dictionary (denoted by the curly braces) with values for the volume and surface_area.
We can now copy our macro into our ExcelModel. The first step is to copy the import of set_active_layer into the imports section at the top of the ExcelModel.
Next we load our base grid in the setup method.
Finally we copy the command to create the pond surface and unload the pond layers into the execute method.
Save the script and run again. You should see the pond created 4 times.
Allowing the user to select the grid
Currently the grid is hard-wired into the script. Instead of this, we want to use the grid the user selects. The value that the user enters into the dialog at the commencement of the modelling run is stored in the UserParams argument. UserParams is a dictionary and the grid is stored using the key ‘Base grid‘.
We also want to rename the grid when its brought in to simplify its access later on. In the Open geometry command, there is a keyword argument called layer_name that if not left as None, will be the name of the layer in the scene manager. By renaming it to something of our choosing, its easier to access later on in the Create pond surface function.
In the execute method, the source grid for the Create pond surface function is changed to be grid, to match the layer name above.
Using the pond elevation from Excel
The next step is to use the actual pond elevation from the Excel spreadsheet. At the start of the execute method, there is a variable created called pond_elevation that has the pond elevation from the Excel spreadsheet for each iteration. In the Create pond surface function, the pond_elevation keyword argument is assigned the variable pond_elevation.
Go back into Excel and change the dummy values in the Pond elevation column to be the values shown below.
Returning the pond volume and area
The final step is to return the pond volume and surface areas to Excel. In the result variable from the Create pond surface function call, there is a dictionary with the volume and surface area. Here, we’ve created 2 variables for surf_area and pond_vol and assigned the returned values. At the bottom of the script, the Outputs for Pond volume and Surface area are assigned the respective variables.
 | If we tried to assign the values from the Create pond surface result variable at the bottom of the script where the Outputs are assigned, the result variable would have been over-written with the results from the other commands executed. |
Running the Excel model again, we should see actual pond volumes and surface areas populated in the Excel spreadsheet.
Rerunning specific rows
Since the modelling here is a set of independent runs (i.e. consecutive runs don’t rely on the output of the previous run) its possible to just run specific rows in the spreadsheet. When running the Excel model, the start row and optionally an end row can be specified. This means that you can rerun specific lines in the spreadsheet without having to run all rows again.
If the model was a sequential one, for example, tailings staging, it would be up to the user to ensure that the model starts with the correct state.