When the Analysis Server starts an ExcelWrapper component, it loads the specified workbook into Excel. After loading the file, the Analysis Server calls the PHXStart macro, if it exists within the workbook. The component's properties are initialized using the cell values from the workbook. When the component is executed, the values for all input properties are transferred to the cells within the workbook, the PHXExecute macro is called (if it exists), and the values for all output cells are extracted from the workbook. Lastly, when the component is ended, the PHXEnd macro is called (if it exists).
When the Analysis Server encounters an ExcelWrapper file in its analyses path or in a public_aserver directory, it will automatically create an ExcelWrapper component using the information contained within this file. NOTE: Microsoft Excel must be installed on the same machine that hosts the Analysis Server.
ExcelWrapper components can take advantage of any user-defined macros within the workbooks that they wrap. In addition to custom macros, three macros exist with special meaning to the ExcelWrapper components. These macros are all optional, but provide special functionality that may be of use for some components.
| Macro Name | Macro Description |
|---|---|
| PHXStart | Called when the ExcelWrapper component is started as an Analysis Server object. Use this macro to perform any initialization of the workbook |
| PHXExecute | Called when the user executes the component. |
| PHXEnd | Called when the object is terminated. Use this macro to perform any necessary clean-up operations. |
Any line that begins with the pound (#) character within the ExcelWrapper file is treated as a comment line. It is common to start the wrapper with a series of comment lines that describe both the wrapper and the Excel workbook that performs the analysis. This is referred to as the header section. The remainder of the ExcelWrapper file is comprised of commands used to specify the name of the Excel workbook that performs the analysis, define the component properties, and map their values to cell values within the workbook.
Each ExcelWrapper file should contain a header section, which is comprised of comment lines that provide additional information about the file and the Excel workbook that performs the analysis. The ExcelWrapper utility recognizes several tags within these comment lines. Any information entered beyond a tag (on the same line) can be extracted by the server and displayed by an Analysis Server client issuing the describe command.
| @author | Specifies the author of the ExcelWrapper file. Usually, also the author of the Excel workbook. |
| @version | Specifies the version of the ExcelWrapper file. |
| @description | Specifies a short description of the ExcelWrapper component. |
| @helpURL | Specifies a URL where users may look to find additional information about the ExcelWrapper component. |
| @keywords | Specifies keywords that can be used to help locate and identify the ExcelWrapper component. |
file=<name>The file command is used to specify the name of the workbook (.xls file). The path of the workbook is declared relative to the directory that holds the ExcelWrapper file. An absolute path may also be used. For each ExcelWrapper file there should be one and only one file statement.
cell: <cell> <name> <type>
[description="string"]
[units=value]
[upperBound=value] [lowerBound=value]
[enumValues="val1,val2,...valN"]
[enumAliases="val1,val2,...valN"]
[ignoreExcelFormat=true]
range: <range> <name> <type>
[description="string"]
[units=value]
[upperBound=value] [lowerBound=value]
[enumValues="val1,val2,...valN"]
[enumAliases="val1,val2,...valN"]
[numDimensions=1|2]
[ignoreExcelFormat=true]
setActiveSheet <name>
visible=<true|false|method>
checkInputsAfterRun=<true|false>
excelShare=<true|false|auto>
prompt=<msg> promptVar=true|false
method=<methodName> [macro]
The cell and range commands specify variables and arrays that exist in the workbook. These commands have the following arguments and options:
| Name | Description |
|---|---|
| cell | The cell argument provides the location of the variable within the Excel workbook. |
| name | The name is a string that specifies what the value will be called. |
| type | The type of variable can be "input" or "output". |
| description | This argument allows the user to write a description for the variable. |
| units | This argument allows the user to write a units string for the variable. Users can query the unit field in the Analysis Server. |
| upperBound | This argument allows the user to set an upper bound for the variable. |
| lowerBound | This argument allows the user to set a lower bound for the variable. |
| enumValues | This option allows the user to create an enumeration for any property. The user provides the enumValues flag followed by a string of the enumerated values in double quotes. |
| enumAliases | This option allows alternative values to be used for enumerated values. For example, if the enumValues are set to "1" and "0", the enumAliases can be set to "true" and "false" to provide more user friendly values for the user. Values are specified as a comma-separated list. |
| numDimensions | This option allows you to specify that an array should be viewed as 1 or 2 dimensions. If 1 is specified, the array will be linearized by reading left to right first, then top to bottom. This option may be abbreviated as "ndims". Default is 1. |
| ignoreExcelFormat |
This option allows you to force
the wrapper to not read the Excel format for a cell. By default,
Analysis Server will pass the current formatting of the cell to the
client so that the client can display the contents of the cell
correctly. Set this option to "true" to prevent this from
happening. |
The setActiveSheet command is used to specify which sheet in the workbook is used for identifying cells or ranges. This command may be issued any number of times and applies to cell and range statements that follow the command.
Setting the visible command to true brings Excel to the foreground when the component is started on the Analysis Server. The default is false. If the value is set method, two methods will be created for the component: showExcel and hideExcel. In ModelCenter, these methods will show up as item in the pop up menu when you right-click on an ExcelWrapper component. Selecting the items will cause Excel to be made visible or will cause it to be hidden. The method value is new as of build 221.
The excelShare command default is false. When true, the Analysis Server will try to share a single session of Excel for all instances of ExcelWrapper that have the statement excelShare=true. The statement excelShare=auto is equivalent to excelShare=true. The use of excelShare=true is generally unreliable and, therefore, NOT RECOMMENDED. Excel was not designed to be run in an automated, multiple-process fashion and strange behavior can result from doing so. The share feature is provided as a convenience to those who fully understand this risk.
In the case when excelShare=true, to avoid name conflicts in Excel, the Excel file wrapped by the ExcelWrapper is copied to a temporary, unique name before being loaded into Excel. When excelShare=false, a private copy of Excel is opened for each instance of the ExcelWrapper. This consumes more resources but, as noted above, is much more robust. If sharing fails, an attempt is made to load a private copy of Excel.
The prompt statement will cause a dialog with the specified message to be displayed. While the prompt is displayed, Excel will automatically be made visible. The user may then manually modify values in Excel. After the user is finished editing values, the prompt dialog can be closed. Excel will automatically be hidden again and the Analysis Server will continue. This feature is designed to assist "user in the loop" type problems. This statement is new as of build 219.
As of build 237, the promptVar option allows prompting to be controlled by the user at runtime. If true, a boolean variable named "promptUser" will be created. When this variable is set to true, the user will be prompted when the wrapper is run. When set to false, the wrapper runs without prompting the user.
The method statement allows user specified macros to be assigned to component methods. These methods are displayed in ModelCenter as items in a the pop up menu when you right-click on an ExcelWrapper. the methodName is the name that will be displayed in ModelCenter. The macro is the name of the macro in Excel. If not specified, the macro is assumed to be the same as the methodName. Multiple methods may be declared. This statement is new as of build 221.
As of v2.2.1 the checkInputsAfterRun command (can be abbreviated checkInputs) allows you to have ExcelWrapper check for input cells changing their values during a run. This is particularly useful if you are using the prompt statement to solve "user in the loop" problems where users may manually change linked input values. Note that in order for this to be useful, you will probably need to make your client program also check the inputs after running. In ModelCenter, you can right click on a component and choose "Properties..." to set this option.
The sample presented is for a workbook that calculates the stiffness of a spring. The ExcelWrapper component that wraps the workbook is given below.
# # @author: Phoenix Integration # @description: calculates stiffness of a spring # @version: DEMO # # spreadsheet file=spring.xls # variables setActiveSheet "spring" # cell name type cell: c4 wireDiameter input units="inches" lowerBound=0.05 cell: c5 startingCoilDiameter input cell: c6 endingCoilDiameter input cell: c7 modulus input cell: c8 activeCoils input description="number of coils in the spring" cell: f4 springRate output
See also Analysis Server