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.

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.

Register the Excel Services AgilePart in Envision. File->Extend AgilePoint->Register AgilePart.

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, 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:

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 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