This example is identical to the previous example but interacts with ANSYS AIM. As in the previous example, this example demonstrates the mechanics of interacting with Microsoft Excel from within a script and connecting ANSYS AIM operations to UI Control events issues by Microsoft Excel (or other similar interfaces). After you extract the ScriptingGuideExamples.zip file, you will find the files for this example in the AIM_Excel_Parameter_Scripting directory.
Of primary interest in this example is that user interaction with this project will be done via Microsoft Excel, where you will:
Set the input parameter values within an Excel Workbook.
Press an 'Update AIM' button in Excel.
See an 'Updating ...' message in Excel as the calculation proceeds, and the output parameter value updated within the Workbook when the calculation is complete.
When the script below is executed, it opens the necessary ANSYS AIM project and Excel Workbook, and then establishes an event connection so that an ANSYS AIM script function is executed at the press of the button within Excel.
Script The script for this example follows. Each line is numbered for reference in the discussion that follows.
1 SetScriptVersion(Version="16.0.260")
2 Reset()
3 # IronPython imports to enable Excel interop
4 import clr
5 import os
6 clr.AddReference("Microsoft.Office.Interop.Excel")
7 import Microsoft.Office.Interop.Excel as Excel
8
9 # Define working directory
10 workingDir = r"D:\ScriptingGuideExamples\AIM_Excel_Parameter_Scripting\"
11 def updateHandler():
12
13 # Update can take long, so disable the Excel warning -
14 # "Excel is waiting for another application to complete an OLE action"
15 ex.Application.DisplayAlerts = False
16
17 # Define key ranges in the Workbook
18 lengthCell = worksheet.Range["A3"]
19 loadCell = worksheet.Range["B3"]
20 defCell = worksheet.Range["C3"]
21
22 # Get the Workbench Parameters
23 lengthParam = Parameters.GetParameter(Name="P1")
24 loadParam = Parameters.GetParameter(Name="P2")
25 defParam = Parameters.GetParameter(Name="P3")
26
27 # Assign values to the input parameters
28 lengthParam.Expression = lengthCell.Value2.ToString() + " [cm]"
29 loadParam.Expression = loadCell.Value2.ToString() + " [N]"
30
31 # Mark the deformation parameter as updating in the workbook
32 defCell.Value2="Updating..."
33
34 # Run the project update
35 Update()
36
37 # Update the workbook value from the WB parameter
38 # Access the deformation value in "m"
39 defValue = defParam.Value.ConvertUnit("m")
40 defCell.Value2 = defValue.Value
41
42 # restore alert setting
43 ex.Application.DisplayAlerts = True
44
45 # Open the Workbench Project
46 if (os.path.exists(os.path.join(workingDir, "AIMExcelParameterScripting.wbpj"))):
47 Open(FilePath = os.path.join(workingDir, "AIMExcelParameterScripting.wbpj"))
48 else:
49 RunScript(FilePath = os.path.join(workingDir,"AIMExcelParameterScriptingProject.wbjn"))
50
51 # Open Excel and the workbook
52 ex = Excel.ApplicationClass()
53 ex.Visible = True
54 workbook = ex.Workbooks.Open(os.path.join(workingDir , "ParameterExample.xlsx"))
55 worksheet=workbook.ActiveSheet
56
57 #Apply the update handler to the workbook button
58 OLEbutton = worksheet.OLEObjects("CommandButton1")
59 commandButton = OLEbutton.Object
60 commandButton.CLICK += updateHandler
61
62Discussion This example demonstrates a number of the typical programming constructs necessary for ANSYS Workbench scripting to interact with the Common Language Runtime (CLR) API exposed by Microsoft Excel and similar applications.
The clr module is imported to enable IronPython to load and
interact with CLR modules from other applications. In this example we add a CLR
reference to the Microsoft Excel Interop assembly, and then import the module as the
Excel namespace. The 'os' module used in this script provides capabilities for
interacting with operating system services. Refer to the Python Standard Library
documentation for details on all available modules.
The working directory is set, so that the script can load the Project and Excel Workbook from the specified location. When completing this example, this line will need to be modified to reflect your working location.
An updateHandler() function is created that performs all the
necessary actions to interact with Excel and update the ANSYS Workbench project. This
function is connected to the CLICK event of the Excel button on
line 58 of this script.
Specific cells of interest in the Workbook are created as named references in the script to facilitate later use of these cells. In this instance, the cells that hold the values of the two input parameters and one output parameter are given named references.
References to the three parameters exposed by the Static Structural system are also assigned to variables for later use.
The Expression property defining the input parameters are set based on the values of the associated cells in the Excel Workbook. Note the 'Value2' property is used because it has simpler interaction when working with a single cell value.
The script sets the value of the output parameter cell in the workbook to 'Updating...' while calculation proceeds.
The Update() command is executed to update the project based
on the new input parameter values.
When the Update is complete, we update the output cell value in the Workbook with the parameter value in the Project.
Note the reference to defparam.Value. The
Value property of a parameter can have different types
(Numeric, String, Boolean, Quantity, etc.). In this instance, it is a Quantity, which
in turn has properties for its Value and
Unit. For consistency, we can access the value in a specific
unit system (independent of the Project unit system). So, in this case, we access the
result value in "m", by using ConvertUnit method.
The lines following the UpdateHandler definition are those first executed when the script executes. The first operation is to load the Workbench Project containing the parameterized Static Structural analysis.
Using the imported Excel namespace, we create an instance of the Excel application and make it visible.
We open the Excel workbook that contains the parameter table and 'Update' button, and get a reference to the primary (active) worksheet in the book.
We get a reference to the named OLE button (CommandButton1) that is present in the worksheet. This button was added to the worksheet by inserting the control within Excel, but no other macros or code associated with this button is required in the workbook.
The updateHandler() function is added as an event handler to
the CLICK event on the command button. Whenever the button is
clicked, the associated ANSYS Workbench script function is executed.