Skip to content

Spreadsheet Unit Operation

Requirement

Excel is optional. With CalculationEngine at Automatic (the default) the unit operation recalculates the workbook in Excel through COM when Excel is installed, and with the internal engine (EPPlus) otherwise, which is always the case on Linux and macOS.

Requirement

The internal engine reads .xlsx and .xlsm workbooks and implements the common worksheet functions; it does not run macros, add-ins or iterative calculation. A cell that evaluates to an error stops the calculation with the cell and its formula in the message.

Represents an Excel/spreadsheet-based unit operation that reads input parameters from the flowsheet, writes them into a spreadsheet file (via Excel COM or GemBox), triggers a recalculation, and reads back the computed output parameters into the flowsheet.

DWSIM.UnitOperations.UnitOperations.ExcelUO
Assembly DWSIM.UnitOperations.dll · Object ← BaseClass ← UnitOpBaseClass ← ExcelUO

At a glance

Spreadsheet Unit Operation in the example flowsheet

Port Index Connected in the example
Inlet, material 0 Feed
Inlet, material 1
Inlet, material 2
Inlet, material 3
Inlet, energy 4 Duty
Outlet, material 0 Product
Outlet, material 1
Outlet, material 2
Outlet, material 3

Example

This code runs on every build of this site, and the output below is what it printed.

import os
import tempfile
import openpyxl

# The workbook needs an "Input" and an "Output" sheet. DWSIM writes the inlet streams
# into Input (T in row 6, P in row 7, H in row 8, molar flows from row 12, one column
# per port from B) and reads the outlet streams from the same cells of Output.
# Parameters go in columns G (name), H (value), I (unit) from row 5.
wb = openpyxl.Workbook()
inp = wb.active
inp.title = "Input"
out = wb.create_sheet("Output")
inp["G5"], inp["H5"], inp["I5"] = "DeltaT", 40.0, "K"
out["B6"] = "=Input!B6+Input!H5"             # outlet T = inlet T + DeltaT
out["B7"] = "=Input!B7"                      # same pressure
out["B12"], out["B13"] = "=Input!B12", "=Input!B13"   # same molar flows
out["G5"], out["H5"], out["I5"] = "Total flow", "=SUM(Input!B12:B13)", "mol/s"
path = os.path.join(tempfile.gettempdir(), "dwsim_spreadsheet_uo.xlsx")
wb.save(path)

fs = (Flowsheet.Create("SpreadsheetUOExample")
      .WithCompounds("N-hexane", "N-heptane")
      .WithPropertyPackage(PropertyPackages.PengRobinson))

feed = (fs.AddMaterialStream("Feed")
        .At(Q.Celsius(25.0), Q.Bar(2.0))
        .SetCompoundMassFlow("N-hexane", 0.9)        # kg/s
        .SetCompoundMassFlow("N-heptane", 0.6))
product = fs.AddMaterialStream("Product")
duty = fs.AddEnergyStream("Duty")

uo = (fs.AddUnitOperation(ObjectType.ExcelUO, "XL-1")
      .ConnectFeed(feed, 0)
      .ConnectProduct(product, 0)
      .ConnectEnergyFeed(duty, 4))           # energy inlet is port 4
x = uo.Object.__implementation__             # the ExcelUO behind the generic builder
x.Filename = path

fs.AutoLayout()
fs.Solve()                                   # runs Excel through COM automation

print(f"Outlet T          = {product.TemperatureK - 273.15:.2f} C")
print(f"Duty              = {x.DeltaQ:.2f} kW")
print(f"Energy stream     = {duty.Object.EnergyFlow:.2f} kW")
print(f"Total flow (sheet)= {float(x.OutputParams['Total flow'].Value):.4f} mol/s")

Output

Outlet T          = 65.00 C
Duty              = 135.84 kW
Energy stream     = 135.84 kW
Total flow (sheet)= 16.4317 mol/s

DWSIM 10.2.11.0, generated 2026-10-08.

Properties

IDs accepted by GetPropertyValue, SetPropertyValue, the sensitivity analysis, the optimizer, the Adjust block and dynamic events. Units are SI; pass another unit system to GetPropertyValue to get them converted.

ID Name Unit (SI) Input
Calc_dQ kW result
In_DeltaT yes
Out_Total flow result

Learn more

API members

Public members declared by this class. Inherited members are documented on the base classes.

Constructors

ExcelUO(): Initializes a new default instance of the ExcelUO class.

Initializes a new default instance of the ExcelUO class.

public ExcelUO()

Public Sub New()

ExcelUO(string, string): Initializes a new instance of the ExcelUO class with a name and description.

Initializes a new instance of the ExcelUO class with a name and description.

Parameter Type Description
name String The display name of the spreadsheet unit operation.
description String A brief description of the spreadsheet unit operation. 
public ExcelUO(string name, string description)

Public Sub New(name As String, description As String)

Properties

CalculationEngine: Gets or sets what recalculates the workbook.

Gets or sets what recalculates the workbook. Automatic (the default) uses Excel when it is installed and the internal engine otherwise.

public SpreadsheetCalculationEngine CalculationEngine { get; set; }

Public Property CalculationEngine As SpreadsheetCalculationEngine

DeltaQ: Gets or sets the calculated energy imbalance / heat duty (kW).

Gets or sets the calculated energy imbalance / heat duty (kW).

public double? DeltaQ { get; set; }

Public Property DeltaQ As Double?

EmbeddedFileName: Gets or sets the file name of the embedded spreadsheet.

Gets or sets the file name of the embedded spreadsheet.

public string EmbeddedFileName { get; set; }

Public Property EmbeddedFileName As String

FileIsEmbedded: Gets or sets whether the spreadsheet file is embedded within the simulation file.

Gets or sets whether the spreadsheet file is embedded within the simulation file.

public bool FileIsEmbedded { get; set; }

Public Property FileIsEmbedded As Boolean

Filename: Gets or sets the full file path to the external spreadsheet workbook.

Gets or sets the full file path to the external spreadsheet workbook.

public string Filename { get; set; }

Public Property Filename As String

HasPropertiesForDynamicMode: Gets a value indicating this unit operation has no dedicated dynamic-mode properties.

Gets a value indicating this unit operation has no dedicated dynamic-mode properties.

public override bool HasPropertiesForDynamicMode { get; }

Public Overrides ReadOnly Property HasPropertiesForDynamicMode As Boolean

InputParams: Gets or sets the dictionary of input parameters sent from the flowsheet to the spreadsheet.

Gets or sets the dictionary of input parameters sent from the flowsheet to the spreadsheet.

public Dictionary<string, ExcelParameter> InputParams { get; set; }

Public Property InputParams As Dictionary(Of String, ExcelParameter)

MobileCompatible: Gets a value indicating whether this unit operation is compatible with mobile interfaces.

Gets a value indicating whether this unit operation is compatible with mobile interfaces.

public override bool MobileCompatible { get; }

Public Overrides ReadOnly Property MobileCompatible As Boolean

ObjectClass: Gets or sets the simulation object class category (UserModels).

Gets or sets the simulation object class category (UserModels).

public override SimulationObjectClass ObjectClass { get; set; }

Public Overrides Property ObjectClass As SimulationObjectClass

OutputParams: Gets or sets the dictionary of output parameters read back from the spreadsheet to the flowsheet.

Gets or sets the dictionary of output parameters read back from the spreadsheet to the flowsheet.

public Dictionary<string, ExcelParameter> OutputParams { get; set; }

Public Property OutputParams As Dictionary(Of String, ExcelParameter)

SupportsDynamicMode: Gets a value indicating whether this unit operation supports dynamic simulation mode.

Gets a value indicating whether this unit operation supports dynamic simulation mode.

public override bool SupportsDynamicMode { get; }

Public Overrides ReadOnly Property SupportsDynamicMode As Boolean

Methods

Calculate(object): Performs the spreadsheet-based calculation: writes input parameters to the workbook, triggers recalculation, and...

Performs the spreadsheet-based calculation: writes input parameters to the workbook, triggers recalculation, and reads back output parameters into the flowsheet.

Parameter Type Description
args Object Optional calculation arguments (not used). 
public override void Calculate(object args = null)

Public Overrides Sub Calculate(args As Object = Nothing)

CloneXML(): Creates a deep copy of this spreadsheet UO via XML serialization.

Creates a deep copy of this spreadsheet UO via XML serialization.

public override object CloneXML()

Public Overrides Function CloneXML() As Object

CloseEditForm(): Closes and disposes the editing form.

Closes and disposes the editing form.

public override void CloseEditForm()

Public Overrides Sub CloseEditForm()

DeCalculate(): Clears all calculated results.

Clears all calculated results.

public override void DeCalculate()

Public Overrides Sub DeCalculate()

DisplayEditForm(): Opens or activates the editing form.

Opens or activates the editing form.

public override void DisplayEditForm()

Public Overrides Sub DisplayEditForm()

GetDisplayDescription(): Returns the localised display description.

Returns the localised display description.

public override string GetDisplayDescription()

Public Overrides Function GetDisplayDescription() As String

GetDisplayName(): Returns the localised display name.

Returns the localised display name.

public override string GetDisplayName()

Public Overrides Function GetDisplayName() As String

GetIconBitmapBytes(): Returns the icon bitmap as a byte array.

Returns the icon bitmap as a byte array.

public override byte[] GetIconBitmapBytes()

Public Overrides Function GetIconBitmapBytes() As Byte()

GetProperties(PropertyType): Returns an array of property identifiers for the specified property type.

Returns an array of property identifiers for the specified property type.

Parameter Type Description
proptype PropertyType 
public override string[] GetProperties(PropertyType proptype)

Public Overrides Function GetProperties(proptype As PropertyType) As String()

GetPropertyUnit(string, IUnitsOfMeasure): Returns the unit string for the specified property.

Returns the unit string for the specified property.

Parameter Type Description
prop String
su IUnitsOfMeasure 
public override string GetPropertyUnit(string prop, IUnitsOfMeasure su = null)

Public Overrides Function GetPropertyUnit(prop As String, su As IUnitsOfMeasure = Nothing) As String

GetPropertyValue(string, IUnitsOfMeasure): Returns the value of the specified property.

Returns the value of the specified property.

Parameter Type Description
prop String
su IUnitsOfMeasure 
public override object GetPropertyValue(string prop, IUnitsOfMeasure su = null)

Public Overrides Function GetPropertyValue(prop As String, su As IUnitsOfMeasure = Nothing) As Object

GetReport(IUnitsOfMeasure, CultureInfo, string): Generates a plain-text report of the spreadsheet results.

Generates a plain-text report of the spreadsheet results.

Parameter Type Description
su IUnitsOfMeasure
ci CultureInfo
numberformat String 
public override string GetReport(IUnitsOfMeasure su, CultureInfo ci, string numberformat)

Public Overrides Function GetReport(su As IUnitsOfMeasure, ci As CultureInfo, numberformat As String) As String

ReadExcelParams(): Reads input and output parameter definitions from the embedded Excel worksheet.

Reads input and output parameter definitions from the embedded Excel worksheet.

public void ReadExcelParams()

Public Sub ReadExcelParams()

RunDynamicModel(): Executes a single dynamic model step by delegating to the steady-state Calculate routine.

Executes a single dynamic model step by delegating to the steady-state Calculate routine.

public override void RunDynamicModel()

Public Overrides Sub RunDynamicModel()

SetPropertyValue(string, object, IUnitsOfMeasure): Sets the value of the specified property.

Sets the value of the specified property.

Parameter Type Description
prop String
propval Object
su IUnitsOfMeasure 
public override bool SetPropertyValue(string prop, object propval, IUnitsOfMeasure su = null)

Public Overrides Function SetPropertyValue(prop As String, propval As Object, su As IUnitsOfMeasure = Nothing) As Boolean

UpdateEditForm(): Refreshes the editing form with updated data.

Refreshes the editing form with updated data.

public override void UpdateEditForm()

Public Overrides Sub UpdateEditForm()

Fields

f: The classic (WinForms) editor window open for this unit operation, if any.

The classic (WinForms) editor window open for this unit operation, if any. Not saved with the flowsheet.

public object f

Public f As Object

ParamsLoaded: Indicates whether the spreadsheet parameters have been successfully loaded from the file.

Indicates whether the spreadsheet parameters have been successfully loaded from the file.

public bool ParamsLoaded

Public ParamsLoaded As Boolean