Spreadsheet Module¶

The Spreadsheet Manager offers Excel compatible functionality within MIKE Workbench. It is possible to organize spreadsheet workbooks consisting of several spreadsheets into a logical folders structure with the database and import and export spreadsheets from/to Excel files. The spreadsheet is compatible with Excel with respect to built-in functions and charting functionality and each sheet can contain data and graphics as well as link to data in the MIKE Workbench database such as time series.

C#IronPythonCPython

// Get the spreadsheet module.
var module = application.Modules.Get("Spreadsheet Manager") as DHI.Solutions.SpreadsheetManager.Interfaces.ISpreadsheetModule;

// Get a spreadsheet.
var spreadsheet = module.SpreadsheetList.Fetch("/ImportTsConfiguration");

try
{
    // Open the spreadsheet before getting or setting cells.
    module.OpenSpreadsheet(spreadsheet);

    // Set a cell value and save the spreadsheet (row, column).
    module.SetCellValue(spreadsheet, "Sheet1", 0, 0, "test1234");

    // Before MIKE OPERATIONS 2024.3, Make sure to save the spreadsheets into the same format as stored, in case controls has been added to the spreadsheet.
    var workbook = spreadsheet.Workbook as SpreadsheetGear.IWorkbook;
    module.SaveSpreadsheet(spreadsheet, (DHI.Solutions.SpreadsheetManager.Interfaces.SpreadsheetFormat)workbook.FileFormat);

    // Get a cell value (row, column)
    var cellVal = module.GetCellValue(spreadsheet, "Sheet1", 0, 0);
finally
{
    // To make sure that the spreadsheet is closed, it is important to keep code working on spreadsheets in a try/catch/finally block.
    // Close the spreadsheet.
    module.CloseSpreadsheet(spreadsheet);
}

# Get the spreadsheet module.
module = app.Modules.Get("Spreadsheet Manager")

# Get a spreadsheet.
spreadsheet = module.SpreadsheetList.Fetch("/ImportTsConfiguration")

try:
    # Open the spreadsheet before getting or setting cells.
    module.OpenSpreadsheet(spreadsheet)

    # Set a cell value and save the spreadsheet (row, column).
    module.SetCellValue(spreadsheet, "Sheet1", 0, 0, "test1234")
    # Before MIKE OPERATIONS 2024.3, Make sure to save the spreadsheets into the same format as stored, in case controls has been added to the spreadsheet.
    spreadsheetFormat = DHI.Solutions.SpreadsheetManager.Interfaces.SpreadsheetFormat(spreadsheet.Workbook.FileFormat)
    module.SaveSpreadsheet(spreadsheet, spreadsheetFormat)

    # Get a cell value (row, column)
    cellVal = module.GetCellValue(spreadsheet, "Sheet1", 0, 0)
finally:
    # To make sure that the spreadsheet is closed, it is important to keep code working on spreadsheets in a try/except/finally block.
    # Close the spreadsheet.
    module.CloseSpreadsheet(spreadsheet)

# Get the spreadsheet module.
module = DHI.Solutions.SpreadsheetManager.Interfaces.ISpreadsheetModule(app.Modules.Get("Spreadsheet Manager"))

# Get a spreadsheet.
spreadsheet = module.SpreadsheetList.Fetch("/ImportTsConfiguration")

try:
    # Open the spreadsheet before getting or setting cells.
    module.OpenSpreadsheet(spreadsheet)

    # Set a cell value and save the spreadsheet (row, column).
    module.SetCellValue(spreadsheet, "Sheet1", 0, 0, "test1234")
    # Before MIKE OPERATIONS 2024.3, Make sure to save the spreadsheets into the same format as stored, in case controls has been added to the spreadsheet.
    spreadsheetFormat = DHI.Solutions.SpreadsheetManager.Interfaces.SpreadsheetFormat(spreadsheet.Workbook.FileFormat)
    module.SaveSpreadsheet(spreadsheet, spreadsheetFormat)

    # Get a cell value (row, column)
    cellVal = module.GetCellValue(spreadsheet, "Sheet1", 0, 0)
finally:
    # To make sure that the spreadsheet is closed, it is important to keep code working on spreadsheets in a try/except/finally block.
    # Close the spreadsheet.
    module.CloseSpreadsheet(spreadsheet)

The C# sample below loops all rows of a spreadsheet.

C#IronPythonCPython

var spreadsheetModule = application.Modules.Get("Spreadsheet Manager") as DHI.Solutions.SpreadsheetManager.Interfaces.ISpreadsheetModule;
var spreadsheet = spreadsheetModule.SpreadsheetList.Fetch("/ImportTsConfiguration");

try
{
    // Open the spreadsheet before getting or setting cells.
    spreadsheetModule.OpenSpreadsheet(spreadsheet);

    // Loop all rows until the value in the first column is empty.
    for (int rowNo = 1; rowNo <= 1000; rowNo++)
    {
        var column1Value = spreadsheetModule.GetCellValue(spreadsheet, "Sheet1", rowNo, 0);
        var column2Value = spreadsheetModule.GetCellValue(spreadsheet, "Sheet1", rowNo, 1);

        if (column1Value != null)
        {
            break;
        }
    }
}
finally
{
    // Close the spreadsheet.
    spreadsheetModule.CloseSpreadsheet(spreadsheet);
}

spreadsheetModule = app.Modules.Get("Spreadsheet Manager")
spreadsheet = spreadsheetModule.SpreadsheetList.Fetch("/ImportTsConfiguration")

try:
    # Open the spreadsheet before getting or setting cells.
    spreadsheetModule.OpenSpreadsheet(spreadsheet)

    # Loop all rows until the value in the first column is empty.
    for rowNo in range(1, 1000):
        column1Value = spreadsheetModule.GetCellValue(spreadsheet, "Sheet1", rowNo, 0)
        column2Value = spreadsheetModule.GetCellValue(spreadsheet, "Sheet1", rowNo, 1)

        if column1Value != null:
            break
finally:
    # Close the spreadsheet.
    spreadsheetModule.CloseSpreadsheet(spreadsheet)

spreadsheetModule = DHI.Solutions.SpreadsheetManager.Interfaces.ISpreadsheetModule(app.Modules.Get("Spreadsheet Manager"))
spreadsheet = spreadsheetModule.SpreadsheetList.Fetch("/ImportTsConfiguration")

try:
    # Open the spreadsheet before getting or setting cells.
    spreadsheetModule.OpenSpreadsheet(spreadsheet)

    # Loop all rows until the value in the first column is empty.
    for rowNo in range(1, 1000):
        column1Value = spreadsheetModule.GetCellValue(spreadsheet, "Sheet1", rowNo, 0)
        column2Value = spreadsheetModule.GetCellValue(spreadsheet, "Sheet1", rowNo, 1)

        if column1Value != null:
            break
finally:
    # Close the spreadsheet.
    spreadsheetModule.CloseSpreadsheet(spreadsheet)

Methods¶

The spreadsheet module object contains the following properties commonly used. Most methods has overloads conatining additional paramaters Refer to the MIKE Workbench API Help chm file for more information.

Methods	Description
ISpreadsheet OpenSpreadsheet(string path)	Opens a spreadsheet with the specified path.
ISpreadsheet CopyDocument(string path, string groupPath, bool replace)
object GetCellValue(ISpreadsheet document, string sheetName, int row, int column)	Get a cell value.
object GetRangeValue(ISpreadsheet document, string sheetName, int row1, int column1, int row2, int column2);
void SetCellValue(ISpreadsheet document, string sheetName, int row, int column, object value)	Sets a cell value
void CloseSpreadsheet(ISpreadsheet document)	Close a spreadsheet.
void AddWorksheet(ISpreadsheet document, string sheetName)	Add a worksheet to the spreadsheet.
void RemoveWorksheet(ISpreadsheet document, string sheetName)	Remove worksheet.
void RenameWorksheet(ISpreadsheet document, string sheetName, string newSheetName)	Rename worksheet.
ISpreadsheet SaveSpreadsheet(ISpreadsheet document, ISpreadsheet anotherSpreadSheet = null)	Save a spreadsheet to the database

Module Providers¶

The following providers are included in the spreadsheet module.

Provider	Description
SpreadsheetList	Manage spreadsheets.
SpreadsheetGroupList	Manage spreadsheet groups
SpreadsheetFunctionList	Manage spreadsheet functions.
SpreadsheetFeatureAssociationList	Manage spreadsheet associations to features of a feature class.

Read more about using module providers here

Using Queries¶

Query Properties¶

Spreadsheets supports using the following query properties.

Spreadsheet¶

Query properties supported when querying on spreadsheets in the SpreadsheetList provider.

Query Property	Type	Database Column
Data	Guid	data
GroupId	Guid	group_id
Id	Guid	id
IsPublic	bool	is_public
ModifiedTime	DateTime	modified_time
Name	string	name
Version	Guid	version

Tools¶

When using the API for running tools, every tool are following the concept show below.

C#IronPythonCPython

// Get the tool from the tool name.
var tool = application.Tools.CreateNew("Export Spreadsheet") as DHI.Solutions.SpreadsheetManager.UI.Tools.ExportSpreadsheet.IExportSpreadsheet;

// Add input items to the tool.
tool.InputItems.Add(spreadsheet);

// Set the properties of the tool.
tool.FilePath = @"c:\export.xlsx";

// Execute the tool.
tool.Execute();

// Get the output of the tool (if any).
var output = tool.OutputItems[0];

# Get the tool from the tool name.
var tool = app.Tools.CreateNew("Export Spreadsheet")

# Add input items to the tool.
tool.InputItems.Add(spreadsheet);

# Set the properties of the tool.
tool.FilePath = "c:/export.xlsx";

# Execute the tool.
tool.Execute()

# Get the output of the tool (if any).
output = tool.OutputItems[0]

# Get the tool from the tool name.
var tool = DHI.Solutions.SpreadsheetManager.UI.Tools.ExportSpreadsheet.IExportSpreadsheet(app.Tools.CreateNew("Export Spreadsheet"))

# Add input items to the tool.
tool.InputItems.Add(spreadsheet);

# Set the properties of the tool.
tool.FilePath = "c:/export.xlsx";

# Execute the tool.
tool.Execute()

# Get the output of the tool (if any).
output = tool.OutputItems[0]

Export Spreadsheet¶

Tool for exporting spreadsheets into external files.

Tool Info	Export Spreadsheet
NuGet Package	`DHI.MikeOperations.SpreadsheetManager.Tools.ExportSpreadsheet`
API Reference	`DHI.Solutions.SpreadsheetManager.UI.Tools.ExportSpreadsheet.IExportSpreadsheet`
Input Items	A single spreadsheet
Output Items	No output items

Tool Properties

FilePath: Gets or sets the file path fo the spreadsheet to exmport

Code Sample

C#IronPythonCPython

// Get the tool.
var tool = application.Tools.CreateNew("Export Spreadsheet") as DHI.Solutions.SpreadsheetManager.UI.Tools.ExportSpreadsheet.IExportSpreadsheet;

# Get the tool.
tool = app.Tools.CreateNew("Export Spreadsheet")

# Get the tool.
tool = DHI.Solutions.SpreadsheetManager.UI.Tools.ExportSpreadsheet.IExportSpreadsheet(app.Tools.CreateNew("Export Spreadsheet"))

Import Spreadsheet¶

Tool for importing spreadsheets from disk to MIKE Workbench.

Tool Info	Import Spreadsheet
NuGet Package	`DHI.MikeOperations.SpreadsheetManager.Tools.ImportSpreadsheet`
API Reference	`DHI.Solutions.SpreadsheetManager.UI.Tools.ImportSpreadsheet.IImportSpreadsheet`
Input Items	A single spreadsheet group
Output Items	A spreadsheet

Tool Properties

FilePath: Gets or sets the file path for the spreadsheet to import
Group: Gets or sets the group fo the spreadsheet to import
FileName: Gets or sets the file name of the spreadsheet to import

Code Sample

C#IronPythonCPython

// Get the tool.
var tool = application.Tools.CreateNew("Import Spreadsheet") as DHI.Solutions.SpreadsheetManager.UI.Tools.ImportSpreadsheet.IImportSpreadsheet;

# Get the tool.
tool = app.Tools.CreateNew("Import Spreadsheet")

# Get the tool.
tool = DHI.Solutions.SpreadsheetManager.UI.Tools.ImportSpreadsheet.IImportSpreadsheet(app.Tools.CreateNew("Import Spreadsheet"))

Spreadsheet query¶

QueryToolTool class

Tool Info	Spreadsheet query
NuGet Package	`DHI.MikeOperations.SpreadsheetManager.Tools.Query`
API Reference	`DHI.Solutions.SpreadsheetManager.Tools.QueryTool.QueryTool`
Input Items	No input items required
Output Items	No output items

Tool Properties

SpreadsheetName: Gets or sets the speadsheet for searching entities.
WithinGroup: Gets or sets the value of the group.
Metadata: Gets or sets the metadata for searching entities.

Code Sample

C#IronPython

// Get the tool.
var tool = application.Tools.CreateNew("Spreadsheet query") as DHI.Solutions.SpreadsheetManager.Tools.QueryTool.QueryTool;

# Get the tool.
tool = app.Tools.CreateNew("Spreadsheet query")