Skip to content

How to...

This section will answer the most common questions related to working with scripts in MIKE Workbench.

For more information about using the API of MIKE OPERATIONS, see the MIKE OPERATIONS SDK section in DHI Developers.

Access data in managers

It is possible to create scripts, which utilize many of the functionalities available in MIKE Workbench. For example, time series can be retrieved from the database and processed. They can also be manipulated using the tools available in the tool explorer and printed as tables or graphs or stored in the database.

In the following, a small script is created, which reads a time series from the database, changes the data in the series and saves it back into the database with a new name.

Create a new storage and insert a simple script template

Write a code line to get hold of Time series Manager Module in the code named mgr.

The line shall contain the following information:

  • The app object is a built-in representation of the IApplication interface, which is available for scripts.
  • The Modules object represent all the modules that are available.
  • The Get(string) gets a specific module with the name specified in the string
def ChangeTimeSeries():
    """
    <Script>
    <Author>admin</Author>
    <Description>Please enter script description here</Description>
    </Script>
    """
    tsMgr = app.Modules.Get('Time series Manager')

Write a code line to get hold of a specific time series which will be called dataseries

The line shall contain the following information:

  • Reference to the Time series Manager specified in the line above and named mgr.
  • The TimeSeriesList represent a specific property of the Time series Manager through which methods exist to get data from the database.
  • The Fetch(string) gets a specific time series from the path with the name specified in the string. In the example it is a series names Rainfall 1 located in the root group.
def ChangeTimeSeries():
    """
    <Script>
    <Author>admin</Author>
    <Description>Please enter script description here</Description>
    </Script>
    """
    tsMgr = app.Modules.Get('Time series Manager')
    dataSeries = tsMgr.TimeSeriesList.Fetch('Rainfall 1')

Write a code line to get the specific data from the dataseries

The line shall contain the following information:

  • Reference to the time series specified in the line above and named dataseries
  • The GetAll() gets all the data contained in the time series.
def ChangeTimeSeries():
    """
    <Script>
    <Author>admin</Author>
    <Description>Please enter script description here</Description>
    </Script>
    """
    tsMgr = app.Modules.Get('Time series Manager')
    dataseries = tsMgr.TimeSeriesList.Fetch('Rainfall 1')
    data = dataseries.GetAll()

Write code lines to add 6 to each data value in the time series.

def ChangeTimeSeries():
    """
    <Script>
    <Author>admin</Author>
    <Description>Please enter script description here</Description>
    </Script>
    """
    tsMgr = app.Modules.Get('Time series Manager')
    dataseries = tsMgr.TimeSeriesList.Fetch('Rainfall 1')
    data = dataseries.GetAll()

    for valuePair in data:
        if valuePair.YValue is not None:
            valuePair.YValue = valuePair.YValue + 6

Write code lines to store the time series into the database under a new name.

The lines shall contain the following commands:

  • Set modified time series values back to time series object.
  • Set the Id to the time series.
  • Assign a new name to the time series
  • Add the modified time series to the database.
  • Print Done when the script has been executed.
import clr
import System

def ChangeTimeSeries():
    """
    <Script>
    <Author>admin</Author>
    <Description>Please enter script description here</Description>
    </Script>
    """
    tsMgr = app.Modules.Get('Time series Manager')
    dataseries = tsMgr.TimeSeriesList.Fetch('Rainfall 1')
    data = dataseries.GetAll()

    for valuePair in data:
        if valuePair.YValue is not None:
            valuePair.YValue = valuePair.YValue + 6

    dataseries.SetData(data)
    dataseries.Id = System.Guid.NewGuid()
    dataseries.Name = dataseries.Name + ' Plus 6'
    tsMgr.TimeSeriesList.Add(dataseries)
    print('Done')

Save and execute the script

After saving, the script can be executed from the script explorer.

The new time series appears now in the time series explorer.

Use tools in scripts

In order to save on development time, it is possible to use existing tools in the script manager.

The example below shows how to run the timeseries average tool and work with the output.

Identify the tool to use

Investigate the inputs

Drag and drop the script in the target storage

Work with the outputs

One important reason to a tool in a script is to extent its functionality. This requires the ability to work with its output(s). All tools have a property called OutputItems holding the results from the tools. This property is unique to each tool, in the case of the Average tool, the output is a so called TableContainer.

For example, it is possible to interrogate the results, compute a new value and export it in file as we can see below.

    f.open('c:\\DHI\\output.txt', 'a')

    for i in tool.OutputItems:
        if i.TabularData.Rows.Count>0 and i.TabularData.Columns.Count>1:
            result.append(i.TabularData.Rows[0][1])
            value = value + 1.3
            result.append(value)
            f.write(str(value))

    return result

Embed script in other script

Finally, the tool should be embedded in a new script to be used in the script manager.

def MyScript():
    """
    <Script>
    <Author>admin</Author>
    <Description>Please enter script description here</Description>
    </Script>
    """

    tsMgr = app.Modules.Get('Time series Manager')
    ts = tsMgr.TimeSeriesList.Fetch('/Input/Calculated/MyTimeSeries')
    TimeWeighedAverageValueTool(ts, True)

Call one script from another storage

Scripts stored in a given storage can be called directly. As we can see in the image below, the main script (i.e. TestCalls) can directly use HelloWorld.

def TestCalls():
    """
    <Script>
    <Author>admin</Author>
    <Description>This script calls the HelloWorld script.</Description>
    </Script>
    """

    HelloWorld()


def HelloWorld():
    print("Hello World")

Sometimes, it is important to organize scripts in different storages. For example, this can be useful if several scripts with the same name need to be create. In order to be able to call scripts from one storage to another one, it is required to reference the storage using the import keyword and provide the full name of the script to use, as we can see below.

import Storage2

def TestCalls():
    """
    <Script>
    <Author>admin</Author>
    <Description>This script calls the HelloWorld script.</Description>
    </Script>
    """

    Storage2.HelloWorld()

User can navigate backward and forward using several ways:

  • Ctrl+- or Ctrl+Shift+- short keys.
  • Backward and Forward toolbar button.

Using Python Packages

MIKE Workbench includes some standard libraries and packages which can be imported into a script when using the Python version installed with MIKE OPERATIONS.

  • IronPython scripts will always use Python packages installed with MIKE OPERATIONS
  • CPYTHON scripts will use Python packages/modules installed with the activated Python version (refer to the section about Using Pip to install Python modules).

The packages and libs are located in the following sub-directories under the MIKE OPERATIONS installation folder:

  • python-pkgs
  • python-stdlib

It is possible to add other packages in other folders and use them in Script Manager code. The Script Manager searches paths in this way:

  • The temporary folder of the MIKE Workbench script storages involved in the script execution (they are exported to disk when script is run)
  • The python-stdlib folder
  • The python-pkgs folder
  • Folders mentioned in environment variable PYTHONPATH

So, creating this environment variable as a semi-colon separated list of your normal python package and lib folders prior to starting MIKE Workbench, packages placed in the folder, available to scripts in MIKE Workbench as well.

Note that the environment variable and the packages need to exist on the executing machine, i.e. on the server when running on that machine.

Use third party packages

CPython (Using pip)

When using CPython scripts in MIKE Workbench, it is possible to import Python modules using pip install.

It is important that modules are installed in the active Python installation.

The following sample shows how to import the numpy module.

  1. Open a cmd prompt and run cd /d C:\Users\[user]\AppData\Local\Programs\Python\Python37\Scripts, find your Python installation folder.

  2. Run the command pip install --target="[<site-packages folder\]" numpy.
    The site-packages folder should be the folder of the active Python installation.
    E.g. pip install --target=" C:\Program Files (x86)\DHI\MIKE OPERATIONS\2024\bin\python-stdlib\python\site-packages" numpy

  3. Open MIKE Workbench and create a storage. Remember to change the script type in the property grid to CPython.

  4. The script below will import the numpy module.

import numpy as np

def PrintArray():
    """
    <Script>
    <Author>admin</Author>
    <Description>Numpy sample script</Description>
    </Script>
    """

    a = np.array([1,2,3,4,5]) 
    np.savetxt(r"c:\Temp\array.txt", a)

After running, the file array.txt can be found in the c:\temp\ folder containing this array.

CPython (Using Python Package Wizzard)

MIKE OPERATIONS 2025.2

CPython package installation and update was streamlined with MIKE OPERATIONS 2025.2 when a Python Package Wizzard was created.

IronPython

Third party package compatible with IronPython can be imported in the script manager. The package should be copied in MIKE OPERATIONS's installation folder \python-stdlib\site-packages.

Then it can be directly used in a script using the 'import' command.

Active CPython installation

MIKE OPERATIONS is finding the Python installation to use in the following way:

  1. Specify a custom Python DLL path (from MIKE OPERATIONS 2024.1)
    A custom Python installation can be specified on the script module, to be shared by all script providers containing CPython scripts.
    Specifying the custom Python DLL path, will override searching for other Python installation (point 2-4 below).
    In the script explorer, specify the full path to the Python DLL to use including the file name.
    Python installation are usually found in %LOCALAPPDATA%\Programs\Python.

  2. PYTHONPATH env. variable
    Python installations specified in the PYTHONPATH environment variable will override any other Python installation.

    a. In case FEFLOW is installed, the FEFLOW Python installation will be used (Python 3.11). FEFLOW includes the 'ifm' library for working with FEFLOW models. Python modules of the MIKE OPERATIONS installation will be used.

    b. Else the first Python installation (containing a python[version].dll) will be used, using Python modules of the Python installation.

  3. Use the activated Python version
    The activated Python version is found using the python --version from a command prompt.

    a. If the activated version is found in the registered Python Paths (where python), this version is used.

    b. Search for the active Python version in the Windows: - Program Files - Program Files (x86) - User programs (default Python installation path)

  4. Python installation of MIKE OPERATIONS
    If no Python installation was found, the Python installation of MIKE OPERATIONS will be used.
    In that case, MIKE OPERATIONS will use the Python dll found in
    [MIKE OPERATIONS installation bin folder]\bin\
    e.g.: "C:\Program Files (x86)\DHI\MIKE OPERATIONS\2025\bin\python3.dll"

Note

Note that on Linux, MIKE OPERATIONS will use the latest version of Python installed in the '/usr/lib' folder.
The MIKE OPERATIONS backend can run on Linux using Microsoft .NET 6.0 and later, e.g. using by container images. Please refer to DHIDevelopers for more information about using MIKE OPERATIONS in containers.

Tip

MIKE OPERATIONS must be restarted after changing the Python DLL Path, in case the script engine has already been initialized with the previous settings.

MIKE OPERATIONS 2025.2

This has changed with MIKE OPERATIONS 2025.2.
The Python DLL used now follows the following logic:
- CPython DLL Path = empty: MO Python is used
- CPython DLL Path = path to a python.dll: another version is used
- CPython DLL Path = PYTHONPATH : Environment Variable is used

Walk-Through: Get the Values of a Time Series

This section describes how to retrieve the values of a time series using the script.

Step 1 - Create a new script storage

In order to write a script, a new script storage to store customised script functions must be created. This process to create a new storage is as follows:

  • Select the storage view.
  • Right-click on the root (the Database node) in the storage explorer. The script manager's context menu is shown in Figure 4.

"Add storage" menu

  • Click Add storage menu to create a new storage.
  • Right-click on the newly create storage. Context menu is shown in Figure 5 below.

Menu strip for storage

  • Click Rename to rename the storage, then, for example, set the name to GetTimeseriesValues.

Step 2 - Edit the script storage

After adding a new storage or clicking the Edit storage menu as depicted in Figure 5, the following editor (Figure 6) will be shown as a data view in MIKE Workbench. The tools in the editor will be discussed later in the Hints and best practices section of this document.

Script editor

Step 3 - Write the script function

The following listing shows how to retrieve values from a time series.

import clr
import System

                                                                # (1)
def GetTimeSeriesValue(path):                       
    """
    <Script>                                                    # (2)
    <Author>admin</Author>
    <Description>Get the values of a time series.</Description>
    <Parameters>
    <Parameter name="path" type="string">Path of the time series.</Parameter>
    </Parameters>
    </Script>
    """
    tsMgr = app.Modules.Get('Time series Manager')              # (3)
    ts = tsMgr.TimeSeriesList.Fetch(path)                       # (4)
    if ts is not None:
        list = ts.FetchAll()                                    # (5)
        for item in list:
            print(item.XValue)
            print(item.YValue)                                  # (6)

Script to get values of a time series

Note the following:

  1. Define a script function named GetTimeSeriesValue with one input parameter which is the path of a time series.
  2. Add comments in the script. You do not need to add these comments manually. There are tools and menus in the editor to insert the Script without arguments and Script with arguments which will insert the comments in a newly created script function. You just need to update them. The Hints and best practices section in this document will describe in detail how to simplify the editing of a script.
  3. Get the time series manager module: The app object represents the built-in IApplication interface which is available for scripts. The method to access the module in a script function is the same as in standard dotnet programming language. Please refer to document /2/ in the Reference documents table.
  4. Use the Fetch(string path) method to retrieve the TimeSeriesList from the database.
  5. Use FetchAll() method to get all value pairs of the time series.
  6. Loop over the data list and print the date times and values.

Create a New Time Series

This section describes how to create a new time series, set its data values and save it to the database using script. As described in the Walk-Through: Get the values of a time series section, you first need to create a new storage which can be named CreateTimeSeries and then open the script editor to write the script functions.

The following listing demonstrates the script function to create a new time series and save it to the database.

import clr
clr.AddReference('DHI.Solutions.Generic')                       # (1)
from System import DateTime                                     # (2)
from DHI.Solutions.Generic import DataSeriesAxisType            # (3)
from DHI.Solutions.Generic import DataSeriesValueType

                                                                # (4)
def CreateTimeSeries(path):                                 
    """
    <Script>                                        
    <Author>admin</Author>
    <Description>Create a new time series.</Description>
    <Parameters>
    <Parameter name="path" type="string">Full path of the time series to create.</Parameter>
    </Parameters>
    </Script>
    """

    tsMgr = app.Modules.Get('Time series Manager')              # (5)
    ts = tsMgr.TimeSeriesList.CreateNew(path)                   # (6)
    valuePairList = []
    initialDate = DateTime(2024, 11, 9, 0 ,0, 0)
    for i in range(1, 24):
        valuePair = ts.CreateNew()
        valuePair.XValue = initialDate.AddHours(i)
        valuePair.YValue = 2.0 + i * 0.1
        valuePairList.Add(valuePair)                            # (7)
    ts.SetData(valuePairList)                                   # (8)
    ts.XAxisType = DataSeriesAxisType.Equidistant_Calendar      # (9)
    ts.SetXAxisUnit('second', False)
    ts.YAxisVariable = 'Precipitation Rate'
    ts.SetYAxisUnit('mm/h', False)
    ts.ValueType = DataSeriesValueType.Mean_Step_Accumulated
    tsMgr.TimeSeriesList.Add(ts)                                # (10)
Script to create a new time series

Note the following:

  1. Add reference to the DHI.Solutions.Generic assembly.
  2. Import the DateTime class from System namespace which is defined in the mscorlib assembly.
  3. Import the DataSeriesAxisType, DataSeriesValueType and IDataSeries classes from the DHI.Solutions.Generic module.
  4. Define a script function named CreateTimeSeries with one input parameter which is the path of the new time series.
  5. Get the time series module.
  6. Create a new time series using the CreateNew(string path) method.
  7. Create a list of IDataSeriesValuePair.
  8. Set the above newly created data to the new time series.
  9. Set the properties of the newly created time series.
  10. Save the newly created time series to the database via Add(IDataSeries entity) method in the TimeSeriesList class.

Update an Existing Time Series

This section describes how to update an existing time series and save the changes to the database using script. The first and second steps here are the same as the first and second steps in the Walk-Through: Get the values of a time series section. The newly created storage can be named UpdateTimeSeries.

The following listing shows how to update a time series using scripts.

import clr
clr.AddReference('DHI.Solutions.Generic')
from System import DateTime
from DHI.Solutions.Generic import DataSeriesValueType

                                                                # (1)
def UpdateTimeSeries(path):                                 
    """
    <Script>                                        
    <Author>admin</Author>
    <Description>Update an existing time series.</Description>
    <Parameters>
    <Parameter name="path" type="string">Full path of the time series to update.</Parameter>
    </Parameters>
    </Script>
    """

    tsMgr = app.Modules.Get('Time series Manager')              # (2)
    ts = tsMgr.TimeSeriesList.Fetch(path)                       # (3)
    valuePairList = ts.FetchAll()                               # (4)
    if valuePairList.Count > 0:
        valuePairList[0].YValue = 3.0                           # (5)
        ts.Update(valuePairList[0])                             # (6)
    ts.ValueType = DataSeriesValueType.Step_Accumulated         # (7)
    tsMgr.TimeSeriesList.Update(ts)                             # (8)
Script to update an existing time series

Note the following:

  1. Define a script function named UpdateTimeSeries with one input parameter which is the path of an existing time series.
  2. Get the time series module.
  3. Get the time series from the database using Fetch(string path) method of the TimeSeriesList class.
  4. Get all of the data of the above time series from database.
  5. Change the value of the first value pair if the count of data list is greater than zero.
  6. Use Update(IDataSeriesValuePair dataSeriesValuePair) to update the above change of data to the database.
  7. Set a new value to the ValueType of the existing time series.
  8. Save the above type change to the database via Update(IDataSeries entity) method of the TimeSeriesList class.

Export a Time Series Using the TimeseriesExportTool

This section describes how to export a time series using the TimeseriesExportTool defined in the time series manager via script.

The first and second steps here are same as the first and second steps in the Walk-Through: Get the values of a time series part. The name of the newly created storage can be ExportTS.

The following listing demonstrates the definition of the script function used by the TimeseriesExportTool to export a time series.

import clr
clr.AddReference('DHI.Solutions.TimeseriesManager.Tools.TimeseriesExportTool') # (1)
from DHI.Solutions.TimeseriesManager.Tools.TimeseriesExportTool import *     # (2)

def ExportTS(path):
    """
    <Script>                                        
    <Author>admin</Author>
    <Description>Export a time series.</Description>
    <Parameters>
    <Parameter name="path" type="string">Full path of the time series to export.</Parameter>
    </Parameters>
    </Script>
    """

    tsMgr = app.Modules.Get('Time series Manager')                           # (3)
    ts = tsMgr.TimeSeriesList.Fetch(path)                                    # (4)

    toFileTool = app.Tools.CreateNew('To file')                              # (5)
    toFileTool.InputItems.Add(ts)                                            # (6)
    toFileTool.ExportFormat = ExportFormatOptions.dfs0                       # (7)
    toFileTool.DirectoryStructure = DirectoryStructureOptions.NoSubfolders   # (8)
    toFileTool.ExportDirectory = "c:/Temp/"                                  # (9)
    toFileTool.Execute()                                                     # (10)
Script to export a time series

Note the following:

  1. Add reference to the DHI.Solutions.TimeseriesManager.Tools.TimeseriesExportTool assembly.
  2. Import all the class or variable definitions in the above assembly.
  3. Get the time series module.
  4. Get the time series from the database using Fetch(string path) method.
  5. Create a TimeseriesExportTool by using CreateNew(string toolName) method of the Tools class.
  6. Add the fetched time series to the input items of the export tool.
  7. Set the ExportFormat property of the export tool. It defines the format of the exported file. Use dfs0 as an example.
  8. Set the DirectoryStructure to make sure that the time series is exported to the path specified (by default, time series are exported into folders reflecting the group structure in the time series explorer).
  9. Set the ExportDirectory property of the export tool. It defines the directory of the exported file.
  10. Execute the export tool. A dfs0 file will be created in the specified export directory.

Execute a Scenario

This section describes how to execute a scenario via script. You will also need to create a new storage and open the storage editor first as described in the Walk-Through: Get the values of a time series Section. The new storage can be named ExecuteScenario.

After following the above steps, write the script as in the following listing, to implement the function of executing a scenario.

import clr

def ExecuteScenario(path):
    """
    <Script>                                        
    <Author>admin</Author>
    <Description>Run existing scenario.</Description>
    <Parameters>
    <Parameter name="path" type="string">Full path of an existing scenario to run.</Parameter>
    </Parameters>
    </Script>
    """

    sceMgr = app.Modules.Get('Scenario Manager')                    # (1)
    scenario = sceMgr.ScenarioList.Fetch(path)                      # (2)
    if scenario is not None:
        simulation = None                                           # (3)
        sceMgr.RunScenario(scenario, True, False, simulation)       # (4)
Script to execute a scenario

Note the following:

  1. Get the module of scenario manager.
  2. Get the scenario from the database using the Fetch(string path) method.
  3. Define a simulation variable.
  4. Execute the scenario via the RunScenario(IScenario scenario, bool runModelGUI, bool approveSimulation, ref ISimulation simulation) method defined in the scenario module class.

Get a Simulation of an Executed Scenario

This section describes how to get a simulation of an executed scenario via script. The first and second steps are the same as the first and second steps in the Walk-Through: Get the values of a time series section. The new storage can be named GetSimulation.

The following listing shows the script function to get the latest simulation of a scenario.

import clr

def GetSimulation(path):
    """
    <Script>                                        
    <Author>admin</Author>
    <Description>Get the latest simulation executed on a scenario.</Description>
    <Parameters>
    <Parameter name="path" type="string">Full path of an existing scenario containing simulations.</Parameter>
    </Parameters>
    <ReturnValue type="ISimulation">The latest simulation.</ReturnValue>
    </Script>
    """

    sceMgr = app.Modules.Get('Scenario Manager')                            # (1)
    childSimulations = sceMgr.SimulationList.FetchChildren(path)            # (2)
    simulation = None                                                       # (3)
    if childSimulations is not None:                                
        runtime = [item.TimeOfSimulationRun for item in childSimulations]   # (4)
        simulation = find(
            lambda item:item.TimeOfSimulationRun == max(runtime),
            childSimulations)                                               # (5)
    return simulation                                                       # (6)

def find(f, collection):                                                    # (7)
    for item in collection:
        if f(item):
            return item

Script to get the latest simulation of an executed scenario

Note the following:

  1. Get the scenario manager module.
  2. Get all simulations of a scenario specified by the input scenario path from the database by the FetchChildren(string groupPath) method of the SimulationList class.
  3. Define a simulation variable.
  4. Get the run time list of the above child simulations.
  5. Get the latest simulation of the specified scenario by calling find function defined in the same storage.
  6. Return the result simulation.
  7. Define a function named find in the same storage. This function will be used to retrieve an item from a collection, matching it up according to a search criterion. As the function does not have the formatted comments, it will not be treated as a script entity in the script manager.

Get Attributes from a Feature Class

This section describes how to retrieve attributes from a feature class using script. Firstly, you would need to create a new storage and open the storage editor which has been introduced in the Walk-Through: Get the values of a time series section. The storage can be named as GetFeatureAttributes. After that, write the script functions in the editor to get a feature class' attributes, similar to those shown in the following listing. Take the Countries feature class as an example.

import clr
clr.AddReference('DHI.Solutions.GISManager.Interfaces')                     # (1)
from DHI.Solutions.GISManager.Interfaces import *                           # (2)

def GetFeatureAttributes(path, attributeName):
    """
    <Script>                                        
    <Author>admin</Author>
    <Description>Get the attribute values of an specified attribute of a feature class.</Description>
    <Parameters>
    <Parameter name="path" type="string">Full path of an existing feature class.</Parameter>
    <Parameter name="attributeName" type="string">Name of the attribute to get values for.</Parameter>
    </Parameters>
    <ReturnValue type="[]">The list of attribute values.</ReturnValue>
    </Script>
    """

    gisMgr = app.Modules.Get('GIS Manager')                                 # (3)
    featureClass = gisMgr.FeatureClassList.Fetch(path)                      # (4)
    attributeList = featureClass.AttributeList                              # (5)
    attribute = find(lambda item:item.Name == attributeName, attributeList) # (6)
    if attribute is not None:
        featureList = featureClass.FetchAll()                               # (7)
        attributeValueList = [item[attribute] for item in featureList]      # (8)
        return attributeValueList                                           # (9)

def find(f, collection):                                                    # (10)
    for item in collection:
        if f(item):
            return item
Script to get the name attribute of Countries feature class.

Note the following:

  1. Add reference to the DHI.Solutions.GISManager.Interfaces assembly.
  2. Import all classes and variables defined in the above assembly.
  3. Get the module of the GIS manager.
  4. Get the Countries feature class from the database using the Fetch(string path) method.
  5. Get the attribute list of the above feature class.
  6. Get the name attribute from the attribute list by calling the find function defined in the same storage.
  7. Get all features of the Countries feature class from the database via the FetchAll() method of the IFeatureClass class.
  8. Get the list of name values by using this [IFeatureClassAttribute attribute] property of the IFeature class.
  9. Return the result value list of name attribute.
  10. Define a function named find to return the item, matching it up according to search criteria. If nothing is found, it will return None. It is not a script entity as it does not have formatted comments.

Get and Set a Cell Value in a Spreadsheet

This section describes how to get or set a cell's value in a spread sheet using script. The first and second steps here are the same as the first and second steps in the Walk-Through: Get the values of a timeseries section. The name of the newly created storage can be GetSetCellValue.

After adding the storage and opening the editor, write the script code, as indicated in the following listing, to the editor to get a cell value from a specified spread sheet or to set a cell value in a specified spread sheet.

def GetCellValue(path, sheetName, cellReference):
    """
    <Script>                                        
    <Author>admin</Author>
    <Description>Get the cell value of a spreadsheet.</Description>
    <Parameters>
    <Parameter name="path" type="string">Full path of an existing spreadsheet.</Parameter>
    <Parameter name="sheetName" type="string">The sheet name.</Parameter>
    <Parameter name="cellReference" type="string">The cell reference (e.g. "B3").</Parameter>
    </Parameters>
    <ReturnValue type="object">The cell's value.</ReturnValue>
    </Script>
    """

    sprMgr = app.Modules.Get('Spreadsheet Manager')                         # (1)
    spreadsheet = sprMgr.OpenSpreadsheet(path)                              # (2)
    value = None
    try:                                                                    # (3)
        value = sprMgr.GetCellValue(spreadsheet, sheetName, cellReference)  # (4)
    finally:
        sprMgr.CloseSpreadsheet(spreadsheet)                                # (5)
    return value                                                            # (6)

def SetCellValue(path, sheetName, cellReference, value):
    """
    <Script>                                        
    <Author>admin</Author>
    <Description>Set the value of a cell in a spreadsheet.</Description>
    <Parameters>
    <Parameter name="path" type="string">Full path of an existing spreadsheet.</Parameter>
    <Parameter name="sheetName" type="string">The sheet name.</Parameter>
    <Parameter name="cellReference" type="string">The cell reference (e.g. "B3").</Parameter>
    <Parameter name="value" type="string">The value to set.</Parameter>
    </Parameters>
    </Script>
    """

    sprMgr = app.Modules.Get('Spreadsheet Manager')
    spreadsheet = sprMgr.OpenSpreadsheet(path)
    try:
        sprMgr.SetCellValue(spreadsheet, sheetName, cellReference, value)   # (7)
        sprMgr.SaveSpreadsheet(spreadsheet)                                 # (8)
    finally:
        sprMgr.CloseSpreadsheet(spreadsheet)
Script to get/set a cell value

Note the following:

  1. Get the module of spreadsheet manager.
  2. Open the spreadsheet using the OpenSpreadsheet(string path) method in the spreadsheet manager module.
  3. Do the spreadsheet operations in a try/finally catch block to make sure that the spreadsheet is closed.
  4. Get the cell value using the GetCellValue(ISpreadsheet document, string sheetName, string reference) method in the module.
  5. Close the spreadsheet in the finnaly block using CloseSpreadsheet(ISpreadsheet document) method in the module.
  6. Return the cell value.
  7. Set the cell value using the SetCellValue(ISpreadsheet document, string sheetName, string reference, object value) method in the module.
  8. Save the change using the SaveSpreadsheet(ISpreadsheet document) method.

Make a Spreadsheet Event Handler Function

This section describes how to make a spread sheet event handler script function. The spread sheet event handler is used to implement the functionality of executing a script by clicking a push button in the spread sheet. An event handler function should have two parameters. The first being EventSender which is a type of ISpreadsheetScriptEventSender and second parameter is Control which is a type of ISpreadsheetControl. These two parameters are required when defining an event handler script. The Hints and best practices section later in this document, will discuss these two parameters in more detail.

In order to make a spread sheet event handler function, you first need to create a new storage and then open the storage editor as described in the Walk-Through: Get the values of a time series section and name the storage NameSelectedEvent.

The following listing demonstrates what a spread sheet event handler function looks like.

from System.Collections.Generic import List                             # (1)

                                                                        # (2)
def SpreadsheetEventHandler(eventSender, control):
    """
    <Script>
      <Author>admin</Author>
      <Description>Populate a dropdownlist control in a spreadsheet with a name list.</Description>
      <Parameters>
        <Parameter name="eventSender" type="ISpreadsheetScriptEventSender">script event sender</Parameter>
        <Parameter name="control" type="ISpreadsheetControl">spreadsheet control</Parameter>
      </Parameters>
    </Script>
    """

    dropDownListName = eventSender.GetCellValue('Sheet1', 'A1')         # (3)
    nameList = List[str](['Jerry', 'Mary', 'Mike', 'Linda'])            # (4)
    eventSender.Controls[dropDownListName].Items = nameList.ToArray()   # (5)
    eventSender.Controls[dropDownListName].SelectedText = nameList[1]   # (6)
    eventSender.SetCellValue('Sheet1',                                  # (7)
                            'B1',
                            eventSender.Controls[dropDownListName].SelectedText)
Script of spread sheet event handler

The script in the above listing tries to populate a combo box in a spreadsheet.

Note the following:

  1. Import List from the System.Collections.Generic module.
  2. Define an event handler script named NameSelectedEvent which has two parameters.
  3. Get the combo box name using the GetCellValue(string sheetName, string reference) method of EventSender.
  4. Define a name list which contains four items.
  5. Populate the specified combo box using the above defined name list. Use the Controls property of the eventSender to get the combo box.
  6. Set the second item as the selected one in the combo box.
  7. Set the text of the selected item to a specified cell using the SetCellValue(string sheetName, string reference, object value) method of eventSender. The following figure shows a spread sheet that uses the above eventhandler.

Spreadsheet using the populating drop down list event handler

Note the following:

  1. Right-click on this button and the following context menu is shown:

Menu strip of control in a spread sheet

By clicking the Properties menu, the relevant properties for this button will show in the property grid.

Button properties

By setting the path of the event handler script to the Script property and clicking this button, the event handler script will be executed.

  1. This is the dropdown list control. Its properties can be set just like the button. Set its name as NameDropDown.
  2. The value of cell A1 is the name of the above dropdown list. It is used in the above event handler example.