Reading Data from an Excel File

Instructional Goals

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

Example Process: Expense Reporting Application: View Existing Expense Report

Problem Statement

View the data from an existing expense report stored in a Microsoft Excel file.

This functionality is a subset of the expense reporting application.

Business Requirements

• View expense report data stored in a Microsoft Excel file.
• Integrate with an expense report management application.

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

Navigation

1. In AgilePoint Envision, open a process template.
2. Navigate to the SharePoint stencil.
3. On the SharePoint stencil, drag the Excel Read 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

2. On the Read tab, use the arrow buttons to map the following Excel fields and process values.

   ExcelField	ProcessValue
   ExcelField1	field1
   ExcelField2	field2
   ExcelField3	field3
   ExcelField4	field4
   ExcelField6Range	field6

3. For each of the mapped values, select Formatted.
4. When you have finished mapping the values, click OK.
5. On the SharePoint stencil, drag the Update InfoPath AgileShape, and drop it on the process template.
6. On the XMLMappings field, click the Ellipses button.
7. On the XML Schema Mappings Window, use the map the following Excel fields and InfoPath schema nodes.

   InfoPath XPath	Value
   (XPATH)/my:myFields/my:field1	${ExcelField1}
   (XPATH)/my:myFields/my:field2	${ExcelField2}
   (XPATH)/my:myFields/my:field3	${ExcelField3}
   (XPATH)/my:myFields/my:field4	${ExcelField4}
   (XPATH)/my:myFields/my:RepeatingGroup/my:field6	${ExcelField6Range}
   (XPATH)/my:myFields/my:RepeatingGroup[my:field5='Type 2']/my:field6	${ExcelField6RangeType2}
   (XPATH)/my:myFields/my:ResultRepeatingAll	${ResultTest3}
   (XPATH)/my:myFields/my:ResultRepeatingType2	${ResultTest4}