Microsoft Office Excel Analysis

Microsoft Office Excel Analysis
This container holds information to expose data from an instance of Microsoft Excel as Workbench parameters.

Methods

GetExcelSetup

Get the DataReference of the MSExcelSetup entity. There is only one MSExcelSetup entity per Analysis container. An exception is thrown if the entity is not found.

Return The DataReference of the MSExcelSetup entity.
Type DataReference

Example

The following example shows how the user can get a setup entity to set a different NamedRangeKey value.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    setup1.NamedRangeKey = "MyPrefix"

Data Entities

MSExcelFile

This entity represents an Excel file added to the Analysis container in order to expose data as Workbench parameters and perform calculations based on parameters. It is created by the MSExcelSetup.AddFile method which copies and registers the original user's file into the project files.

Properties

DisplayText

The general property that defines the user-visible name of an entity. This property is defined for all data entities but is used only in those entities that present a label in the user interface.

Type string
Read Only No

ErrorMessage

Error message if the calculation failed.

Type string
Read Only No

FileName

Name of the file.

Type string
Read Only No

MacroName

Name of the macro used to calculate the workbook.

Type string
Read Only No

OriginalFilePath

Original Path of the file.

Type string
Read Only No

State

Calculation state of the file.

Type Status
Read Only No

UseMacro

Set to True if a Visual Basic macro is used to calculate the workbook.

Type bool
Read Only No

Methods

GetRange

Gets the DataReference of an MSExcelRange entity from its name. An exception is thrown if the entity is not found.

Return The DataRefence of the MSExcelRange entity.
Type DataReference

Required Arguments

Name The Name of the MSExcelRange to retrieve.
Type string

Example

The following example shows how the user can get an MSExcelRange named "WB_Thickness".

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file1 = setup1.GetFile()
    range1 = file1.GetRange(Name="WB_Thickness")
    write range1.CellRange
    write range1.Value

GetRanges

Gets the list of all MSExcelRange entities.

Return The DataReferenceSet containing all the MSExcelRange entities associated with the MSExcelFile.
Type DataReferenceSet

Example

The following example shows how the user can get all the MSExcelRange entities of an MSExcelFile.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file1 = setup1.GetFile()
    ranges = file1.GetRanges()

OpenInApplication

No details are provided for this entry.

PublishParameter

Publishes an MSExcelRange as a Parameter in the Workbench project. The IsOutput argument allows to publish the range as an input or an output parameter. The method returns the created Parameter entity.

Return DataReference of the created parameter.
Type DataReference

Required Arguments

ExcelRange The MSExcelRange to be published as a parameter.
Type DataReference

Optional Arguments

IsOutput    True to specify that the range is published as an output parameter, or false for input parameter.
    Type     bool
    Default Value    False

Example

The following example shows how to publish ranges as input or output parameters.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file = setup1.AddFile(FilePath="C:\Test\ProcessCalculation.xlsx")
    ValvePosition = file.GetRange(Name="WB_Valve_Position")
    T1 = file.GetRange(Name="WB_T1")
    input1 = file.PublishParameter( ValvePosition )
    output1 = file.PublishParameter( T1 )

Reload

Reloads an MSExcelFile to synchronize the data between the Microsoft Office Excel application and Workbench. For instance, it is necessary to call this method when the ExcelSetup.NamedRangeKey has been modified, in order to filter the named ranges based on the new NamedRangeKey's value. It is also necessary to reload the file if the workbook has been edited outside of Workbench, for instance to change a formula. The method recreates MSExcelRange and Parameter entities as required. The existing results in Workbench are invalidated.

Example

The following example shows how to reload a file.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file = setup1.GetFile()
    file.Reload()

UnpublishParameter

Unpublishes an MSExcelRange as a Parameter from the Workbench project, which means deleting the Parameter entity that was created in association to this MSExcelRange.

Required Arguments

ExcelRange The MSExcelRange to unpublish.
Type DataReference

Example

The following example shows how to publish and unpublish ranges as input or output parameters.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file = setup1.AddFile(FilePath="C:\Test\ProcessCalculation.xlsx")
    ValvePosition = file.GetRange(Name="WB_Valve_Position")
    input1 = file.PublishParameter( ValvePosition )
    T1 = file.GetRange(Name="WB_T1")
    output1 = file.PublishParameter( T1 )
    file.UnpublishParameter(ValvePosition)
    file.UnpublishParameter(T1)

MSExcelRange

This entity represents an Excel named range that matches the prefix string define by the Parameter Key. The MSExcelRange entities are created automatically when an Excel file is added, or reloaded, by filtering all the named ranges found in the Excel workbook with the Parameter Key. The MSExcelRange is not published as a parameter by default: use the MSExcelFile.PublishParameter method to expose the range as an input or output parameter in the Workbench project. The named ranges can contain a single cell for the value, or two cells for the value and the unit string.

Properties

CellRange

The coordinates defining the Excel range in the workbook.

Type string
Read Only No

DisplayText

The general property that defines the user-visible name of an entity. This property is defined for all data entities but is used only in those entities that present a label in the user interface.

Type string
Read Only No

Value

The current value of the range.

Type Quantity
Read Only No

ValueQuantityName

The quantity name.

Type string
Read Only No

Methods

GetParameter

Gets the Parameter entity associated with a published MSExcelRange entity. If the MSExcelRange is not published as a parameter, the method returns null.

Return The Parameter associated with the MSExcelRange, or null if it is not published.
Type DataReference

Example

The following example shows how the user can get the parameter associated with a range.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file1 = setup1.GetFile()
    range1 = file1.GetRange(Name="WB_Thickness")
    parameter1 = range1.GetParameter()
    write parameter1.Name

MSExcelSetup

This entity holds properties to setup the data exchange with the Microsoft Office Excel application and information about the state of the connection with the instance of Microsoft Office Excel. There is one unique MSExcelSetup entity instance per Analysis container.

Properties

DisplayText

The general property that defines the user-visible name of an entity. This property is defined for all data entities but is used only in those entities that present a label in the user interface.

Type string
Read Only No

ExcelStatus

Status of the connection with the instance of the Microsoft Office Excel application.

Type ExcelConnectionState
Read Only Yes

ExcelVersion

Version number of the Microsoft Office Excel instance connected with Workbench.

Type string
Read Only Yes

NamedRangeKey

The Named Ranges Key is a prefix string used to filter the named ranges found in the Excel workbook. The default value is set using the value of the related user preference. It is possible to use an empty string in order to retrieve all named ranges.

Type string
Read Only No

UnitSystemName

The name of the unit system used for the Analysis container.

Type string
Read Only No

Methods

AddFile

Adds a file to an MSExcelSetup entity by providing its FilePath. The method copies and registers the file into the Workbench project and returns an MSExcelFile entity if successful. The method also creates an MSExcelRange entity for each named range matching the ExcelSetup.NamedRangeKey prefix string ("" by default). If the file does not exist, is not an Excel file or cannot be registered into the project, the method throws an exception. If a file was already added to the MSExcelSetup, the method throws an exception as well because only one file can be handled in each Analysis container. To use another file, the user has to Reset the data container first.

Return The DataReference of the added file.
Type DataReference

Required Arguments

FilePath The Path of the original Microsoft Office Excel file to add.
Type string

Example

The following example shows how to add a file to the MSExcelSetup entity.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file = setup1.AddFile(FilePath="C:\Test\ProcessCalculation.xlsx")
    write file.FileName
    ranges = file.GetRanges()

DeleteFile

Deletes a file from an MSExcelSetup entity and from the Workbench project file management. All the MSExcelRange entities and associated Parameter entities are deleted as well.

Required Arguments

File The DataReference of the MSExcelFile entity to be deleted.
Type DataReference

Example

The following example shows how to delete a file from an MSExcelSetup entity.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file = setup1.GetFile()
    setup1.DeleteFile(file)

GetFile

Gets the MSExcelFile entity associated to the MSExcelSetup entity. If the MSExcelSetup entity has no file, the method returns null. If the optional argument Name is specified but does not correspond to a file associated to the MSExcelSetup, the method throws an exception.

Return The DataReference of the retrieved file entity.
Type DataReference

Optional Arguments

Name Name of the MSExcelFile entity to retrieve.
Type string

Example

The following example shows how the user can get a file entity from a setup entity to retrieve one of its properties.

    system1 = GetSystem(Name="XLS")
    analysis1 = system1.GetContainer(ComponentName="Analysis")
    setup1 = analysis1.GetExcelSetup()
    file1 = setup1.GetFile()
    write file1.Name