Working with spreadsheets¶

Opening a spreadsheet¶

Right-click a spreadsheet and select Open - or simply double-click the spreadsheet in the Explorer.

Lock/Unlock a Spreadsheet¶

Locking a spreadsheet, can make sure that other users will not be able to edit and save a spreadsheet while being edited by you.

Select Lock in the context menu to lock the spreadsheet.

When locking a spreadsheet, the spreadsheet will be automatically unlocked after 24 hours.

A lock icon will be shown indicating that the spreadsheet is locked.

If user tries to save a spreadsheet locked by another user (a user with another user name or from another computer), the user will get a warning that it is not possible to save the spreadsheet and that the spreadsheet should be saved into another spreadsheet.

Unlock the spreadsheet to allow other users to edit and save the spreadsheet.

An open spreadsheet can be manipulated through tools available from toolbar above the spreadsheet

The buttons and menus in the toolbar are:

Save - It saves the spreadsheet to the database
Cut/Copy/Paste - These standard actions work on selected items in the spreadsheet, e.g. text, formulas, cells, formats, plots.
Functions - This button is a quick access to the most common spreadsheet functions to insert into a cell.
The “Mode functions…” option opens a standard search dialog for available functions where they are grouped in different ways
Format - The Format button gives access to various options for formatting cells, rows, colums, sheet.
Cells... and “Conditions…” both give access to a general formatting dialog.
Chart - The chart button quickly generates a chart from the data in selected cells and the choice of Chart.
Once created the chart can be modified/customized via the Chart Explorer accessible via a right-click menu on the chart.
Controls - The controls button inserts controls into the spreadsheet.
Once a control has been inserted, right click the control to select it.
Right clicking a selected control, will show the context menu of the control.
To Database - The “To Database” button allows the values of a proper formatted range of cells to be stored in the database as a new time series.
The option enters a wizard for:
- Selecting the range of cells
- Specifying the group path and time series name in the database
- Specifying the time series type and unit

End with a confirmation page.

SpreadsheetGear menus¶

The special spreadsheet functionality is available through some right-click menus.

Cell or Range¶

Right-click a cell.

This will open a general menu for manipulation of the selected cell.

Choosing the Workbook Explorer... menu item (or double clicking the tab-section of the spreadsheet).
Choosing the Range Explorer... menu item will open a dialog for manipulating the details of the selected range of cells.

Chart¶

Right-click a chart

This will open a menu for manipulating the chart object.

Choosing the Chart Explorer - Opens a dialog giving access to properties of the chart details.

Spreadsheet Controls¶

Button Control¶

The button control is able to run a script when clicking the button.

Select the button by right clicking it and select Properties from the context menu.

In the properties window specify the full path of the script to execute.

Scripts to be executed should contain the following declaration.

def SpreadsheetEventHandler(eventSender, control):
    """
    <Script>
      <Author>admin</Author>
      <Description>Add a button to spreadsheet, then set value to the script path of this button </Description>
      <Parameters>
        <Parameter name="eventSender" type="ISpreadsheetScriptEventSender">script event sender</Parameter>
        <Parameter name="control" type="ISpreadsheetControl">spreadsheet control</Parameter>
      </Parameters>
    </Script>
    """

The code snippet above, can be added to a script using Insert snippet... from the context menu (right click the script canvas).

Select Spreadsheet - Creating an event handler to insert a snippet able to be called when clicking a button.

MIKE Workbench functions¶

In addition to the standard spreadsheet function (also known from Excel), such as SUM, AVG, COUNT, IF etc, the MIKE Workbench provides some functions to access the data in the MIKE Workbench database directly from the spreadsheet.

The functions are listed below with their name, arguments (mandatory and/or optional) and description of what they do.

Common to them all is that they are reached through type in the equal sig ('=') in a cell followed by the name. The list of functions show up filtered by the typed in text.

Selecting a function from the drop-down list with the mouse will start a tool-tip display of the arguments as they are entered.

Finalizing the function call with a parenthesis and pressing Enter evaluated the function

GetCellValueByRC¶

Syntax - GetCellValueByRC(path, sheet, row, col)
Gets the cell value in a sheet in the workbook by row and column number.
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- sheet: string, required – the name of the sheet within the workbook
- row: integer, required – the row number of the cell
- col: integer, required – the column number of the cell
Example - GetCellValueByRC(“/MyGroup/MyFirstSheet”, “Sheet1”, 2, 3)

GetCellValueByRef¶

Syntax - GetCellValueByRef (path, sheet, reference)
Gets the cell value in a sheet in the workbook by cell reference
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- sheet: string, required – the name of the sheet within the workbook
- reference: string, required – the celle reference
Example - GetCellValueByRC("/MyGroup/MyFirstSheet", "Sheet1", "C3")

GetRangeValueByRC¶

Syntax - GetRangeValueByRC(path, sheet, row1, col1, row2, col2)
Gets the cell value in a sheet in the workbook by row and column numbers
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- sheet: string, required – the name of the sheet within the workbook
- row1: integer, required – the row number of the upper left cell
- col1: integer, required – the column number of the upper left cell
- row2: integer, required – the row number of the lower right cell
- col2: integer, required – the column number of the lower right cell
Example - GetRangeValueByRC ("/MyGroup/MyFirstSheet", "Sheet1", 2, 3, 4.5)

GetRangeValueByRef¶

Syntax - GetRangeValueByRef (path, sheet, reference)
Gets the cell value in a sheet in the workbook by range reference
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- sheet: string, required – the name of the sheet within the workbook
- reference: string, required – the celle reference
Example - GetCellValueByRC("/MyGroup/MyFirstSheet", "Sheet1", "C3:F4")

GetTimeseries¶

Syntax - GetTimeseries (path, [starttime],[endtime])
Gets the cell value in a sheet in the workbook by range reference
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- starttime: string, optional – start date and time of interval to select, in format yyyy-MM-dd HH:mm:ss
- endtime: string, optional – end date and time of interval to select, in format yyyy-MM-dd HH:mm:ss
Example - GetTimeseries("/group/waterlevel")
GetTimeseries("/group/waterlevel", "2010-01-01", "2010-03:31 23:59:59")

GetTimeseriesQuantile¶

Syntax - GetTimeseriesQuantile (path, fraction)
Gets the time series quantile by path and fraction
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- fraction: float, required – number between 0 and 1
Example GetTimeseries("/group/waterlevel", 0.50)

MovingAverageTimeseries¶

Syntax - MovingAverageTimeseries(path, days, hours, minutes, seconds, averagingWindowPosition, interpolateAcrossGaps, maxNumberOfMissingValuesPerGap)
Gets a time series calculated as moving average of the source
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- days: integer, required – number of days to average
- hours: integer, optional – hours added to number of days to average
- minutes: integer, optional – minutes added to number of days to average
- seconds: integer, optional – seconds added to number of days to average
- averagingWindowPosition: integer, optional – start number of day to average
- interpolateAcrossGaps: boolean, optional– true/false
- maxNumberOfMissingValuesPerGap: integer, optional – number of values to accept as a gap
Example - ``

TimeseriesMaximumValue¶

Syntax - TimeseriesMaximumValue(path)
Gets the maximum value in a time series identified by by path
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
Example TimeseriesMaximumValue("/group/waterlevel")

TimeseriesMinimumValue¶

Syntax - TimeseriesMinimumValue(path)
Gets the minimum value in a time series identified by by path
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
Example TimeseriesMinimumValue("/group/waterlevel")

TimeseriesAnnualMaximum¶

Syntax - TimeseriesAnnualMaximum(path, startday, startmonth)
Gets the maximum value within a year value in a time series identified by by path. The year starts 1. January unless start day and start month is specified.
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- startday: integer, optional – day within a month. Default = 1
- startmonth: integer, optional – number of a month (1-12). Default = 1
Example TimeseriesMinimumValue("/group/waterlevel", 29, 2)

TimeseriesAnnualMinimum¶

Syntax - TimeseriesAnnualMinimum(path, startday, startmonth)
Gets the minimum value within a year value in a time series identified by by path. The year starts 1. January unless start day and start month is specified
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- startday: integer, optional – day within a month. Default = 1
- startmonth: integer, optional – number of a month (1-12). Default = 1
Example TimeseriesMinimumValue("/group/waterlevel", 29, 2)

TimeseriesExceedence¶

Syntax - TimeseriesExceedence (path, threshold)
Calculates the volume of a curve above the specified threshold
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
- threshold: float, required – threshold value to compare
Example TimeseriesExceedence ("/group/waterlevel", 154)

TimeWeighedAverageValue¶

Syntax - TimeWeighedAverageValue(path)
Calculates the time weighed average of a time series
Arguments
- path: string, required – the path in explorer to the spreadsheet starting with a /
Example TimeWeighedAverageValue ("/group/waterlevel")

MIKE Workbench scripts¶

Scripts build with the Script Manager will appear as functions accessible to the spreadsheet as the buil-in SpreadsheetGear functions and the MIKE Workbench functions.

A script defined as shown below in the Script Manager defined a function named MyFunction. It simply returns the value 23.5

In a spreadsheet the function appears when typing the equal sign = followed by the initial letters of the function name in a cell:

Selecting the function with the mouse

The function call is completed with the end-parenthesis (no arguments are required in the function call). Pressing Enter evaluates the function.

Saving spreadsheets¶

A modified spreadsheet is indicated by the asterix next to the title in the Tab. Closing the sheet by pressing the x (or right click on the tab choosing Close or pressing Ctrl+Shift+C) will pop-up in a confirmation dialog as this:

The same will happen if you close the MIKE Workbench entirely with open modified spreadsheets.

When saving a spreadsheet, if the used range has more than 256 columns and the sheet has form controls or cell comments, then below information dialog will show.

This is a limitation of SpreadsheetGear.

Exporting a spreadsheet¶

Spreadsheets can be exported into external files.

Right-click a spreadsheet in the explorer and select Export spreadsheet...

A Save As dialog is shown and a filename must be specified.

The spreadsheet can be saved in various formats which must be selected.