Creating an Excel File

Instructional Goals

• Demonstrate how to create a Microsoft Excel file from an AgilePoint process.

Example Process: Expense Reporting Application: Create New Expense Report

Problem Statement

Create an employee expense report as an Excel file.

This functionality is a subset of the expense reporting application.

Business Requirements

• Create an expense report in Microsoft Excel.
• Integrate with an expense report management application.
• Store the Excel file in SharePoint.

Prerequisites

• An SPDoc or SPList process model with the following schema values:

  Process Value
  field1
  field2
  field3
  field4
  field6

• An InfoPath form with the following fields:

  InfoPath Schema Node
  (XPATH)/my:myFields/my:field1
  (XPATH)/my:myFields/my:field2
  (XPATH)/my:myFields/my:field3
  (XPATH)/my:myFields/my:field4
  (XPATH)/my:myFields/my:RepeatingGroup/my:field6
  (XPATH)/my:myFields/my:RepeatingGroup[my:field5='Type 2']/my:field6
  (XPATH)/my:myFields/my:ResultRepeatingAll
  (XPATH)/my:myFields/my:ResultRepeatingType2

• A Microsoft Excel template file named ExcelServices.xlsx that includes the following fields:

  Excel Field
  ExcelField1
  ExcelField2
  ExcelField3
  ExcelField4
  ExcelField6Range
  ExcelField6RangeType2
  ResultTest3
  ResultTest4

• The following process attribute to indicate the file name for a new Excel file:

  ${/my:myFields/my:SaveWrite}

Navigation

1. In AgilePoint Envision, open a process template.
2. Navigate to the SharePoint stencil.
3. On the SharePoint stencil, drag the Excel Write AgileShape, and drop it on the process template.
4. To view the entire list of properties, in the Design Tasks pane, click Show Properties.
5. On the Configuration field, click the Ellipses button.

Instructions

Repeat the following steps until all required approvers are added to the list:

1. On the Excel Services Configuration window, complete the following fields as required for your environment.

   Field Name	Value
   Excel Services URL	Definition: Specifies the URL of the SharePoint Excel Services. Allowed Values: A valid SharePoint Excel Services URL. A custom attribute. XML metadata To add XML metadata, use the Ellipses button. Default Value: None Custom Attributes: Yes Example: URL - http://ind-01:8888/_vti_bin/ExcelService.asmx A custom attribute - $\{SharePointURL\}$\{ExcelService\}
   Run-time Template	Definition: Specifies the name of an Excel template file to be used at runtime. The Excel file can be either .xlsx or .xltx. Allowed Values: A valid Excel template file name with the full path. A custom attributes. For example, $\{SharePointURL\}$\{RuntimeTemplateLibrary\}$\{ExcelFilename\} XML metadata To add XML metadata, use the Ellipses button. Note the following: The Run-time Template file and the Design-time Template file must have the same named fields. This indicates that both the files must have same content. This Excel template file must be located either in a SharePoint document library or in a shared folder. Either way, the file must be added to the Trusted file locations of the Excel Services which is configured in SharePoint Central Administration - Shared Services. Default Value: None Custom Attributes: Yes Example: http://ind-01:8888/ExcelServicesDemo/ExcelServices.xlsx
   Design-time Template	Definition: Specifies the name of an Excel template file that is used at design time. Once you enter the Excel template file name in the Design-time Template box, the Named Fields box is populated with all the named fields from the Excel sheet. The design-time template document is used only to access the named fields of an Excel sheet. This document is not used at runtime. Allowed Values: A valid Excel template file name with the full path. Note the following: The Run-time Template file and the Design-time Template file must have the same named fields. This indicates that both the files must have same content. Also, while designing in Envision, the file must be accessible to the process designer from their computer using the Browse button. Default Value: None Custom Attributes: No Example: C:\Excel\ExcelSheet\ExcelServices.xlsx
   Save To	Definition: Specifies the location where you want to save the resulting Excel document. Allowed Values: A valid URI. The URI can be the URL for a SharePoint document library, or a directory path. A custom attributes. For example, $\{SharePointURL\}$\{ExcelDocLibrary\}$\{ExcelFilename\}. XML metadata To add XML metadata, use the Ellipses button. Default Value: None Custom Attributes: Yes Example: http://ind-01:8888/ExcelServicesDemo/${/my:myFields/my:SaveWrite}.xlsx

2. On the Write tab, use the arrow buttons to map the following Excel fields and InfoPath fields.

   ExcelField	ProcessValue
   ExcelField1	(XPATH)/my:myFields/my:field1
   ExcelField2	(XPATH)/my:myFields/my:field2
   ExcelField3	(XPATH)/my:myFields/my:field3
   ExcelField4	(XPATH)/my:myFields/my:field4
   ExcelField6Range	(XPATH)/my:myFields/my:RepeatingGroup/my:field6
   ExcelField6RangeType2	(XPATH)/my:myFields/my:RepeatingGroup[my:field5='Type 2']/my:field6
   ResultTest3	(XPATH)/my:myFields/my:ResultRepeatingAll
   ResultTest4	(XPATH)/my:myFields/my:ResultRepeatingType2