Excel Calculate

This AgileShape unleashes the full power of Excel Services as a calculation engine. It is the combination of the other two methods and permits both writing (temporarily or permanently) and reading from an Excel document in the same step. In this case, the Save To field is not a required parameter, but instead it is optional.

NOTE: Write is carried out first and then Read in executed.

The following is a use case:

Here is a simple InfoPath-based Process that uses the three methods of the AgilePart:

The example process is to demonstrate the different uses of the AgilePart. It is not necessary to use all the methods in the same process. Steps to set this up are as follows:

Create InfoPath file

Create the form:

Save form as ExcelServicesDemo.xsn

Prepare Excel Template, create an Excel document with the right structure:

Save the Excel document as ExcelServicesDemo.xlsx on the Desktop.

Create two SharePoint Doc Libraries to store the run-time Excel template and the resultant Excel file:

Call first one ExcelRuntimeTemplates:

Browse to the Excel file ExcelServicesDemo.xlsx on the Desktop and upload it to the document library. (Another method is to Publish the Excel from within Excel itself.)

Create second Doc library to store resultant Excel file:

Design the Process. Design a new InfoPath process based on ExcelServicesDemo.xsn form.

Copy the Excel Services.vss file to My Documents\\My Shapes directory.

Open the Excel Services stencil in Envision.

Drag an Excel Write shape on to the canvas.

Configure the shape as shown below:

In the example, the SharePoint URL is http://moss.litwareinc.com. You should substitute it with your own site URL.

Use the Browse button to select the Design-time Template Excel document from the Desktop. The named fields list box gets filled automatically.

Type the URL of a SharePoint Doc Library in the Save To field. In this example it is http://moss.litwareinc.com/ExcelServicesDemoLib If the document library does not exist, it will NOT be created.

Click the ellipsis button (shown in red above) and select the form field SaveWrite.

Add the extension ".xlsx":

Configure the mappings: Select named field ExcelField1 from the list and press the arrow button shown in red:

Do the same for the other named fields. Now select the first row of the mapping grid and press ellipsis button as shown in red below:

Select field1 from the schema dialog:

Repeat last two steps for all the other fields.

Manually change the XPath value for the Repeating Field Type 2 to: (XPATH)/my:myFields/my:RepeatingGroup[my:field5='Type 2']/my:field6

Press OK to finish configuring this Shape. Now to add a step in the process to read from an Excel document.

Drag an Excel Read shape on to the canvas:

Open the Configuration dialog and fill as shown:

NOTE: This time, it is the Excel Document field that is using paramaterized value coming from the form.

Now to add another step in the process to write and read from an Excel document:

Drag an Excel Calculate shape on to the canvas.

In the Configuration dialog, set fields as shown:

For the Read tab configure as shown:

For the last step, add Update InfoPath shape and configure as shown:

Publish Process and InfoPath to ExcelServicesDemo:

Initiate a Process.

Click New in the form Library ExcelServicesDemo.

Fill as shown and click Submit and Close:

The process runs automatically.

Revising the results of running the process. Open the form just submitted and should see this:

Click on the Open SharePoint Library link to open ExcelServicesDemoLib.

Open the Excel document created by first AgilePart to check it is correctly filled:

Check that the third AgilePart in the process has created an Excel file in C:\\SaveToDir\\CC.xlsx

Excel Calculate Configuration Window

Parent topic: SharePoint / Microsoft SPS

Properties for This AgilePart

Field Name	Definition
Configuration	Opens the Excel Calculate Configuration dialog box, where you can configure an Exel calculate action.
Mappings	In order to update the named fields in the Excel sheet, they need to be mapped to the process values here. The named fields appear in the list box on the right and can be mapped to, by selecting them first and then pressing the arrow button. Once a named field is in the mapping grid on the left, in order to map it to a process value, first select the row and then press the ellipsis button to select from the schema dialog for the process or type a custom attribute directly in the ProcessValue column. If the file in Design-time Template changes, then it is necessary to press the Refresh button to reload the named fields again. Note: The value of the named field in the ExcelField column can be evaluated from a string containing a custom attribute, for example $\{MyExcelField\} or field$\{year\}$\{month\} or a schema XPath. For the latter, the XPath itself is parameterizable! Note: The name of the custom attribute in the ProcessValue column can be drived from a string containing another custom attribute, for example $\{MyCustomAttribute\} or customattribute$\{year\}$\{month\} or a schema XPath. For the latter, the XPath itself is parameterizable! Note: You can also use advance XPath expressions such as filtering. For example (XPATH)/my:myFields/my:field2[.>32] returns all items that match the filter (field2 > 32). In addition, you can combine XPath expressions and custom attributes like this: (XPATH)/my:myFields/my:field2[.>$\{myCustomAttribute\}] Note: If you have an XPath expression that returns more than one value, there is a feature for mapping them with a named field that is defined as a one-dimensional range in the Excel document. The AgilePart will fill the range with returned values from the XPath expression, but if the range does not contain enough cells for the number of values, it will not fill past the range of cells (in order to protect rest of the worksheet) and the remaining values will be simply discarded.

Field Name

Definition

Configuration

Opens the Excel Calculate Configuration dialog box, where you can configure an Exel calculate action.

Mappings

In order to update the named fields in the Excel sheet, they need to be mapped to the process values here.

The named fields appear in the list box on the right and can be mapped to, by selecting them first and then pressing the arrow button.

Once a named field is in the mapping grid on the left, in order to map it to a process value, first select the row and then press the ellipsis button to select from the schema dialog for the process or type a custom attribute directly in the ProcessValue column.

If the file in Design-time Template changes, then it is necessary to press the Refresh button to reload the named fields again.

Note: The value of the named field in the ExcelField column can be evaluated from a string containing a custom attribute, for example $\{MyExcelField\} or field$\{year\}$\{month\} or a schema XPath. For the latter, the XPath itself is parameterizable!

Note: The name of the custom attribute in the ProcessValue column can be drived from a string containing another custom attribute, for example $\{MyCustomAttribute\} or customattribute$\{year\}$\{month\} or a schema XPath. For the latter, the XPath itself is parameterizable!

Note: You can also use advance XPath expressions such as filtering. For example (XPATH)/my:myFields/my:field2[.>32] returns all items that match the filter (field2 > 32). In addition, you can combine XPath expressions and custom attributes like this: (XPATH)/my:myFields/my:field2[.>$\{myCustomAttribute\}]

Note: If you have an XPath expression that returns more than one value, there is a feature for mapping them with a named field that is defined as a one-dimensional range in the Excel document. The AgilePart will fill the range with returned values from the XPath expression, but if the range does not contain enough cells for the number of values, it will not fill past the range of cells (in order to protect rest of the worksheet) and the remaining values will be simply discarded.

Common AgilePart Properties

Note: The following properties are always available for all instances of the AgilePart AgileShape. However, additional design-time properties will often be available for specific instances of the AgilePart AgileShape. The names, purposes, effect, and availability of these additional design-time properties will depend upon the specific .NET class associated with the AgilePart AgileShape instance

Field Name	Definition
(Name)	Read Only Default value: AgilePart.xxx, AgileWork The display name of the AgileShape.
(UID)	Read Only Default value: AgilePart.xxx or AgileWork The ID that uniquely identifies this AgileShape from all others within the same process template.
Description	Optional Note: This property is ignored at runtime. A description of the AgileShape. This property is primarily used as a means of recording additional notes, comments, and details about this AgileShape in order to make the process template more understandable to process modelers.
Debug	Default value: False This property enables you to monitor the performance of and troubleshoot a particular AgileShape. Setting this property to True adds information to the AgilePoint Server log regarding this shape – for example, data being passed to or from the shape, or database connection information. You can use this data to monitor the performance of particular AgileShapes. This property can be set to the following specific values: False - This value indicates that the AgileShape will not log additional progress and debugging messages to the AgilePoint Server log file at runtime. True - This value indicates that the AgileShape will log additional progress and debugging messages to the AgilePoint Server log file at runtime. These messages are often helpful in verifying that the AgileShape is functioning properly and/or with troubleshooting any problems or suspected problems with the AgileShape’s runtime behavior or configuration.
TimeSpan	Default value: 10 Minute(s) Specifies the maximum amount of time that should be allowed for the completion of the activity associated with this AgileShape before it is considered overdue.
BusinessTime	Default value: False Determines whether the time span represents normal time, or business hours only. This property can be set to the following specific values: True - When set to this value, the time span represents a span of business hours only. E.g. 5 Days of business time would normally be equivalent to 7 Days of normal time, and 8 hours of business time would normally be equivalent to 24 hours of normal time. As an example, if business hours are configured as 9am to 5pm and a Manual AgileShape activity with a TimeSpan of 6 Business Hours is entered at 4pm on Tuesday, then the activity must be completed by 2pm on Wednesday. False - When set to this value, the time span represents a normal (absolute) time span, and is unaffected by any business time configuration settings.
Length	Default value: 10 The number of time units represented by the time span.
Time Unit	Default value: Minute The type of time unit represented by the time span. This property can be set to the following specific values: Second Minute Hour Day Week Month
Activity Entry Email	Optional The name of an Email Template that should be used to generate an automatic email notification when this AgileShape is entered at runtime. If this property is left blank, then an email notification will not be sent automatically when the AgileShape is entered at runtime. If this property is not blank, then an email notification will be dynamically constructed and sent at runtime (when the AgileShape is entered) using the specified Email Template.
Activity Exit Email	Optional The name of an Email Template that should be used to generate an automatic email notification when this AgileShape is exited at runtime. If this property is left blank, then an email notification will not be sent automatically when the AgileShape is exited at runtime. If this property is not blank, then an email notification will be dynamically constructed and sent at runtime (when the AgileShape is exited) using the specified Email Template.
Audit Level	Default value: High This property determines the amount of process related data that is recorded to the workflow database. This property can be set at either the Process Template Properties layer or at the individual AgileShape layer. This property allows you to control the level of granularity in terms of the amount of process related data that is recorded to the workflow database. This property can be set to the following specific values: High - This value indicates that AgilePoint will record all data about the activity or process to the database. Low - This value indicates that AgilePoint will only allow a certain amount of data to be recorded to the database. This option provides a level of control over the granularity of AgilePoint process-related data that is stored in the AgilePoint workflow database, giving some flexibility in the audit trail records by filtering out unnecessary data. Audit level at Process Template level Manual activity (Generic manual activity and AgileWork) - After the process is completed (reaches \'91STOP' activity): If audit level is set to high all records will be kept at the movement of process completion. If audit level is set to low, the records for manual work items with status of 'Removed' and 'New' will be deleted from the database. After leaving the manual activity, the manual work items with status of 'Removed' will be deleted first, then after the whole process is completed, the manual work items with status of 'New' will be deleted. Also the records for unreached activity instances will be deleted from the database. For example, this is useful if you assign a group to this manual activity. Manual work items with status of 'Removed' and 'New' are not meaningful data after the process completed. Automatic activity - Not supported for now. No action will be taken. Audit level at AgileShape level - Manual activity (Generic manual activity and AgileWork) - If audit level is set to high, all records will be kept at the movement of leaving the activity. Note that one manual activity can have multiple work items at a time. Automatic activity (AgilePart only, not including AgileStub) - If audit level is set to high, all records will be kept at the movement of leaving the activity. If audit level is set to low, the record of auto work item will be deleted from database after leaving the activity. No record will be kept for this particular activity. For example, this is useful if you have a 'Delay' AgilePart that goes through a large number of loops. Usually customers are not interested in the information about the 'Delay' AgilePart, so setting the audit level for 'Delay' AgilePart to low, will delete the record immediately and the database size will not grow as fast.
SessionMode	Default value: Single This property can be set to the following specific values: Single - If it is set to single, then ONLY one session is effective, meaning that in a loop scenario as shown below, the engine would cancel a task from the previous session automatically. Multiple - If it is set to multiple, then multiple sessions can be effective in a loop scenario, and the process will wait for all tasks to complete and not cancel any previous tasks.
Wait All Incoming	Note: This property is ignored at runtime unless the AgileShape has multiple In-Connectors. Default value: False When an AgileShape has multiple direct predecessors (i.e. it has multiple In-Connectors leading directly from other AgileShapes), this property determines how many of the predecessor AgileShapes must be exited (at runtime) before this AgileShape can be entered (at runtime). This property can be set to the following specific values: False - This value indicates that the AgileShape will be entered (at runtime) as soon as any one of the AgileShape’s direct predecessors are exited (at runtime). Note: This value is functionally equivalent to using the Or AgileShape (with Exclusive property set to False) between this AgileShape and its direct predecessors. True (Dynamic) - This value indicates that the AgileShape will be entered (at runtime) only after all of the AgileShape’s “enterable” direct predecessors are exited (at runtime). If any of the direct predecessors are un-enterable (e.g. they will never be entered in this process instance because conditional logic in the process bypassed them) and therefore un-exitable, then those predecessors are not required to be exited before this AgileShape is entered. Note: This value is functionally equivalent to using the And AgileShape (with the Dynamic property set to True) between this AgileShape and its direct predecessors. True (Static) - This value indicates that the AgileShape will be entered (at runtime) only after all of the AgileShape’s direct predecessors are exited (at runtime). Note: This value is functionally equivalent to using the And AgileShape (with the Dynamic property set to True) between this AgileShape and its direct predecessors. Note: If any of the direct predecessors are un-enterable (see above), then this AgileShape will never be entered, and the process instance may be permanently delayed at this AgileShape. Design-Time Operations Changing the positions of the Yes and No connections You can adjust the positions of the Yes and No connections by right-clicking the AgileShape, then using the Flip Yes, Flip No, and/or Swap Yes and No commands.
AssemblyName	Read Only Default value: Automatically determined (based upon the user’s input) when the AgileShape is first added to the process template. The name of the .NET assembly containing the specific AgilePart component that this AgileShape instance is associated with.
ClassName	Read Only Default value: Automatically determined (based upon the user’s input) when the AgileShape is first added to the process template. The fully qualified Type name (including the namespace) of the .NET class (in the .NET assembly specified by the AssemblyName property) that represents the specific AgilePart component that this AgileShape instance is associated with.
Method	Read Only Default value: Automatically determined (based upon the user’s input) when the AgileShape is first added to the process template. The name of the method (in the .NET class specified by the ClassName property) that will be invoked at runtime (when the AgileShape is entered).
ConfigureAttachments	Allows you to attach documents to the activity that are accessed from the process repository. (For more information, see Process Repository.) If you select the View check box and you are using SharePoint Integration, you can the attachments also display in the process model in SharePoint.
Reference URL	Allows you to associate an URL to the shape that is used in the process repository. For more information, see Process Repository.
ExceptionHandlerScope	Default value: Local Specifies the value which determines how AgilePoint Server will handle an AgilePart exception. Two options are available: Local or Global. Local - This value indicates the exception handling (i.e., error message and status) information for an AgilePart within a single process instance will be saved to the custom attribute defined in the SaveErrorMessageTo and SaveStatusTo properties. Global - This value indicates the exception handling (i.e., error message and status) information is handled the same as the Local option, but includes the extended ability to call on a custom AgileConnector to handle the exception as desired.
SaveErrorMessageTo	Default value: ErrorMessage Specifies the name of a custom process attribute that should be updated if the AgileShape causes an error at runtime. If such an error occurs, a message containing information about the error will be stored in the custom process attribute specified by this property.
SaveStatusTo	Default value: Success Specifies the name of a custom process attribute that should be updated when the AgileShape is exited. When the AgileShape is exited at runtime, the value of the AgileShape’s Status property will be stored (as a String) in the custom process attribute specified by this property.