Simcenter Testing Solutions Simcenter Testlab Automation: Excel Export

Direct YouTube link: https://youtu.be/2yHSHfBq0IY
 

 

This article documents a Simcenter Testlab Automation example program for reading the properties of a data block from a Simcenter Testlab project file.

Figure 1:  Path and data block used in example program and project.
 

The example project is attached at the end of this article.  The example program illustrated uses Visual Basic.

Program Overview

When a button on the Visual Basic form is clicked, the following subprogram is run.

First, it links to Simcenter Testlab by instantiating the LMSTestLabAutomation.Application object named “TL.”  If no instance of Simcenter Testlab is running, it starts the Desktop workbook and opens the given file; otherwise, it connects to the running instance and opens the file in that workbook.
Once the project is open, it accesses the database of the currently open project using the ActiveBook.Database property on the Application object.

Once the database is accessed, a data block is retrieved from the database as an IBlock2.  The parameter of the GetItem(dataPath As String) method is an absolute path through the project’s directory, and if this path does not exist, an error is raised.

The properties of the data block are stored in a LMSTestLabAutomation.AttributeMap object, which acts similar to a directory; each item in the AttributeMap can be a system object such as a string or integer, a ComObject such as an Enumerate or IScalarVariable, or another IData structure whose AttributeMap holds more properties, similar to a subfolder in a directory.

In this example, some of the properties are accessed by directly using the KeyNames to navigate through the AttributeMap “directory.”
The particular property attributes browsed for are the “Average type,” which is an Enumerate, and the value of the “Section width,” which is a double with a quantity unit – in this case, the quantity is “RatioPercentage” with a unit of “%.” as shown in Figure 2.

First, the program accesses the LMSTestLabAutomation.Enumerate in the “Average type” property.  To determine what the value of this property is, the LocalValue As String method is called, which returns the string name assigned to the Enumerate’s current value. 

The program then accesses the “Section width” item of the block’s property AttributeMap.  This is an IData in which more properties are nested, and it also has an AttributeMap which can be browsed deeper into.  The “Section width” AttributeMap contains an item called “Double,” which holds the MKS value of the section width.  However, section width is usually expressed as a percentage (here, 20%), while the MKS value is written as a proportion (0.2).  Conversion from a proportion to a percentage is a simple multiplication by 100, but not all unit conversions are so easy, and hardcoding a conversion can lead to errors if data of an unexpected unit is used in a program.  So, a property containing the value’s quantity type must be found.  Still within the AttributeMap of the “Section width” item, there is another item with the KeyName “QTS.”  This is another IData with an AttributeMap of its own, and inside its AttributeMap is an item called “TUUSIQuantity.”  This item is a string containing many pieces of information; here, we just need its first portion, which is the name of a quantity type enclosed in quotation marks.  After isolating the substring between the quotation marks, this can be passed as a parameter to the LMSTestLabAutomation.Application’s UnitSystem property to get the corresponding IQuantity.

An LMSTestLabAutomation.IQuantity object stores the type of units associated with a number, such as ForceOverAcceleration or RatioPercentage.  The UnitSystem on an Application object can compare the quantity type with its corresponding default units as set in the “Simcenter Configuration and Unit System”  tool in order to determine conversions from MKS values to the units and the string text associated with the units, such as “N/g” for ForceOverAcceleration or “%” for RatioPercentage.

The CreateQuantity(QuantityName As String) As LMSTestLabAutomation.IQuantity method on the UnitSystem takes the name of a quantity type, i.e. ForceOverAcceleration, as its parameter and returns the matching IQuantity.  If no such quantity exists, the method raises an error.  The substring acquired above from the “TUUSIQuantity” item is a valid input for this parameter; likewise, the QuantityType property of any IQuantity object returns a string that can be used for this parameter to create the same type of IQuantity.

The MKSToUserValue(Quantity As LMSTestLabAutomation.IQuantity, ac_MKSValue As Double) As Double method of the UnitSystem returns the input value converted to the default units for the given IQuantity; in the case of this example, a RatioPercentage quantity and the double 0.2 are passed as parameters into this method, which returns 20.0, or the proportion 0.2 expressed as a percentage.

The Label(Quantity As LMSTestLabAutomation.IQuantity) As String method of the UnitSystem returns the string representations of the particular units set as the defaults for the IQuantity’s quantity type, i.e. “Pa/N” for a PressureOverForce IQuantity. An example of this output is shown in Figure 3.
 

After accessing the specific properties above, the program begins to recursively browse the AttributeMap of properties, recording both the KeyName and the value in Visual Basic form’s ListBox.  Items in the AttributeMap that have the value “Nothing” are skipped; Component Object Model (COM Objects) are checked for whether they are Enumerates and, if so, the string name (LocalValue) of the current value is recorded, and if not, the item’s AttributeMap is sent back into the subprogram to browse further; other objects, i.e. String, Double, or Boolean, are converted to strings.

The third subprogram, PrintValues(ByVal myBlock As LMSTestLabAutomation.IBlock2), requires a reference to Microsoft Excel to be added to the project.  The program iterates through the x and y values of the data block and prints the values to an Excel document.
The XValues([ac_nIndex As Integer = 0]) As Array property of the LMSTestLabAutomation.IBlock2 object returns a one-dimensional array of the x-values of the data block; the optional zero-based index parameter allows a particular x-axis to be selected if more than one is present.
The YValues As Array property of the LMSTestLabAutomation.IBlock2 object returns a two-dimensional array of the complex values.  When retrieving values from this array, the first dimension represents the zero-based index of the x-value in the XValues array, while the second dimension holds the real part at index 0 and the imaginary part at index 1.  So, YValues(4, 0) retrieves the real part of the y-value at x = XValues(4), while YValues(0, 1) retrieves the imaginary part of the y-value at the first x-value (not necessarily x = 0).

Program Code

The Visual Basic code of the example is provided below; Visual Basic keywords are in dark blue, object classes/types are in light blue, string values are in red, and comments are in green.


Public Class Form1
    Private TL As LMSTestLabAutomation.IApplication

    Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
        ' Examples: used project file and path to data in project file
        Const filePath As String = "D:\\LMSData\\example.lms"
        Const dataPath As String = "ordertracking/Run 1/Fixed sampling/Runup/Sections/Frequencies/Frequency 25.00 Hz Point5"

        ' Declarations of the used objects
        Dim database As LMSTestLabAutomation.IDatabase
        Dim myBlock As LMSTestLabAutomation.IBlock2
        Dim myProperties As LMSTestLabAutomation.AttributeMap

        ' Open Simcenter Testlab to the Desktop Workbook, if not already opened
        TL = New LMSTestLabAutomation.Application()
        If (TL.Name = "") Then
            TL.Init("-w DesktopStandard " + filePath)
        Else
        'TL.OpenProject(filePath)
        End If

        ' Store database
        database = TL.ActiveBook.Database()

        ' Retrieve block
        myBlock = database.GetItem(dataPath)

        ' Retrieve the attributes
        myProperties = myBlock.Properties

        Try
            ' Get an example value from the properties of this block: "Average type": Enum
            Dim averageType As LMSTestLabAutomation.IData = myProperties("Average type")
            Dim averageTypeEnum As LMSTestLabAutomation.Enumerate = averageType

            ' Get an example value from the properties of this block: "Section width": Object
            ' Retrieve the value, which can be found in the Attribute with name 'Double'
            Dim sectionWidth As LMSTestLabAutomation.IData = myProperties("Section width")
            Dim value As Double = sectionWidth.AttributeMap("Double")

            ' Retrieve the quantity associated with this number:
            ' this is in a string it AttributeMap["QTS"]["TUUSIQuantity"]

            ' This information can be found be using the 'PrintAttributeMap' function, lower in this file!
            ' This function prints an overview of all names and values of an AttributeMap and their 
            ' child - parent relations.
            Dim qts As LMSTestLabAutomation.IData = sectionWidth.AttributeMap("QTS")
            Dim quantityName As String = qts.AttributeMap("TUUSIQuantity")
            ' TUUSIQuantity is a long string or data with the quantity type
            ' enclosed in quotation marks at the beginning of the string
            quantityName = quantityName.Substring(1)
            quantityName = quantityName.Substring(0, quantityName.IndexOf(ControlChars.Quote))
            Dim quantity As LMSTestLabAutomation.IQuantity
            quantity = TL.UnitSystem.CreateQuantity(quantityName)

            ' Convert the value from MKS to the user unit
            value = TL.UnitSystem.MKSToUserValue(quantity, value)

            ' Get the units of the quantity
            quantityName = TL.UnitSystem.Label(quantity)

            ' Print the result
            resultList.Items.Add("Retrieve a specific value:")
            resultList.Items.Add("Average Type (Enum): " & averageTypeEnum.LocalValue)
            resultList.Items.Add("Section width (Object): " & value & " " & quantityName)
            resultList.Items.Add("")
        Catch ex As Exception
            ' This block does not have those Attribute names
        End Try


        ' Example to loop through all the items in an attribute map
        resultList.Items.Add("Retrieve all values:")
        PrintAttributeMap(myProperties, 0)

        ' Example to iterate through all data points in the block
        PrintValues(myBlock)
    End Sub

    ' A function to print all names and values of an AttributeMap, indented so 
    ' it is clear what the child - parent relations are.
    Public Sub PrintAttributeMap(ByVal map As LMSTestLabAutomation.AttributeMap, ByVal level As Integer)
        ' Get the names of the properties
        Dim names As LMSTestLabAutomation.AttributeMap = map.KeyNames
        Dim i As Integer

        ' Loop all the names
        For i = 0 To names.Count - 1
            ' If the type is a ComObject, create this object
            ' Else display its value
            If (IsNothing(map(names(i)))) Then
                ' If object is Nothing, do Nothing
            ElseIf (map(names(i)).GetType.FullName <> "System.String" And map(names(i)).GetType().Name = "__ComObject") Then
                ' Print indentation for overview purposes
                Dim j As Integer
                Dim r As String = ""
                For j = 0 To level - 1
                    r += "  "
                Next

                ' If the ComObject is an Enumerate Object, Create it and display its readable value
                ' else, repeat this recursive function to read all child AttributeMaps
                If ((map(names(i))).IsSubTypeOf("LmsHq::DataModelI::Base::IEnumerate") = 1) Then
                    Dim myEnum As LMSTestLabAutomation.Enumerate = map(names(i))
                    resultList.Items.Add(r & names(i) + " : " + myEnum.LocalValue)
                Else
                    Dim dat As LMSTestLabAutomation.IData = map(names(i))
                    resultList.Items.Add(r & names(i) + ": ")
                    PrintAttributeMap(dat.AttributeMap, level + 1)

                End If
            Else
                ' Print indentation for overview purposes
                Dim j As Integer
                Dim r As String = ""
                For j = 0 To level - 1
                    r += "  "
                Next
                Dim value As Object = map(names(i))

                If (names(i) = "Contents") Then
                    value = StripQuotesFromContents(value)
                End If
                resultList.Items.Add(r + names(i) + " : " + value.ToString())
            End If
        Next
    End Sub


    Private Function StripQuotesFromContents(ByVal original As String) As String
        ' All Attributes with the name 'Contents' have quotes around
        ' the values. This function simply removes the '-symbols
        ' to give a better display of the value
        Return original.Substring(1, original.Length - 2)
    End Function

    ' A function to print all x, real y, and imaginary y values
    ' to an Excel spreadsheet
    Public Sub PrintValues(ByVal myBlock As LMSTestLabAutomation.IBlock2)
        Dim ExcelDoc As Microsoft.Office.Interop.Excel.Application
        Dim ExcelBook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExcelSheet As Microsoft.Office.Interop.Excel.Worksheet

        ' Connect to Microsoft Excel
        ExcelDoc = New Microsoft.Office.Interop.Excel.Application
        ' Open a workbook
        ExcelBook = ExcelDoc.Workbooks.Add
        ' Access the first sheet (base 1 indexed)
        ExcelSheet = ExcelBook.Sheets(1)

        ' Make the Excel application visible
        ExcelDoc.Visible = True

        ' Create column headers
        ExcelSheet.Cells(1, 1) = "X"
        ExcelSheet.Cells(1, 2) = "Y (real)"
        ExcelSheet.Cells(1, 3) = "Y (imag)"

        ' Access the x values of the block (1D Array)
        Dim XValues As Array = myBlock.XValues
        ' Access the y values of the block (2D Array - complex values)
        Dim YValues As Array = myBlock.YValues

        ' Iterate through each point in the block
        For i As Integer = 0 To XValues.GetUpperBound(0) - 1
            ' Write the x value
            ExcelSheet.Cells(i + 2, 1) = XValues(i)
            ' Write the real part of the y value
            ExcelSheet.Cells(i + 2, 2) = YValues(i, 0)
            ' Write the imaginary part of the y value
            ExcelSheet.Cells(i + 2, 3) = YValues(i, 1)
        Next

    End Sub
End Class


Questions?  Check out the resources below (including example.lms project attached at upper right of article) or contact Siemens Support Center.

Simcenter Testlab Automation resources:

• Index of Testing Knowledge Articles
• What is Simcenter Testlab Automation?
• Simcenter Testlab Automation Forum
• Simcenter Testlab Automation: Application Objects
• Simcenter Testlab Automation: Parameters
• Simcenter Testlab Automation: Display Example Program
• Simcenter Testlab Automation: Cursor Example Program
• Simcenter Testlab Automation: Measurement Example Program