Read and Create an Excel File in SharePoint

To write (temporarily or permanently) and read from a Microsoft Excel file in the same step, ​do the procedure in this topic.

Figure: Excel Calculate Configuration screen

Excel Calculate Configuration screen

Examples

Good to Know

  • In most text fields, you can use process data variables as an alternative to literal data values.
  • You can configure whether this activity waits for other activities before it runs.

    For more information, refer to How Do I Configure an Activity to Wait for Other Incoming Activities?

  • Some information about third-party integrations is outside the scope of the AgilePoint NX Product Documentation, and it is the responsibility of the vendors who create and maintain these technologies to provide this information. This includes specific business uses cases and examples; explanations for third-party concepts; details about the data models and input and output data formats for third-party technologies; and various types of IDs, URL patterns, connection string formats, and other technical information that is specific to the third-party technologies. For more information, refer to Where Can I Find Information and Examples for Third-Party Integrations?

How to Start

  1. On the Application Explorer screen, do one of these:
  2. Do one of these:
    • Add an activity:
      1. In the Process Builder, go to the Activity Library, and open the SharePoint SharePoint icon tab.
      2. On the SharePoint SharePoint icon tab, drag the Excel Calculate Excel Calculate icon activity onto your process.
    • Change an activity:
      1. In your process, double-click your activity.
  3. Click Excel Calculate Configuration Excel Calculate Configuration icon.

Procedure

  1. On the Excel Calculate Configuration screen, in the SharePoint field, select your SharePoint access token.

    To create a new access token, click Add Token Add Token icon. For more information, refer to Access Token for SharePoint.

  2. Complete these fields as necessary.
    Field Name Definition

    Excel Services URL

    Function:
    Specifies the URL of SharePoint Excel Services.
    Accepted Values:
    Default Value:
    None
    Accepts Process Data Variables:
    Yes
    Example:
    • http://ind-01:8888/_vti_bin/ExcelService.asmx
    • $\{SharePointURL\}$\{ExcelService\}

    Runtime Template

    Function:
    Specifies the name of Microsoft Word or Microsoft Excel template file the activity uses to create Microsoft Word or Excel file when the process runs.
    Accepted Values:
    • A valid template file name.
    • A process data variable.

      You can use Process Data screen to specify a process data variable.

    Default Value:
    None
    Accepts Process Data Variables:
    Yes
    Limitations:

    Some information about third-party integrations is outside the scope of the AgilePoint NX Product Documentation, and it is the responsibility of the vendors who create and maintain these technologies to provide this information. This includes specific business uses cases and examples; explanations for third-party concepts; details about the data models and input and output data formats for third-party technologies; and various types of IDs, URL patterns, connection string formats, and other technical information that is specific to the third-party technologies. For more information, refer to Where Can I Find Information and Examples for Third-Party Integrations?

    Design Time Template

    Function:
    Specifies the name of Microsoft Excel template file that is used when you are creating or changing a process.

    After you enter the Microsoft Excel template file name in the Design time Template box, the named fields box is populated with all the named fields from Microsoft Excel sheet.

    The design time template document is used only to access the named fields of Microsoft Excel sheet. This document is not used at runtime.

    Accepted Values:

    A valid Microsoft Excel template file name with the full path.

    Note the following:

    • The runtime 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 Process Builder, the file must be accessible to the application designer from their computer using the Browse button.
    Default Value:
    None
    Accepts Process Data Variables:
    No
    Example:
    C:\Excel\ExcelSheet\ExcelServices.xlsx

    Save To

    Function:
    Specifies the location and file name to save the resulting Microsoft Excel document.
    Accepted Values:
    Default Value:
    None
    Accepts Process Data Variables:
    Yes
    Example:
    http://ind-01:8888/ExcelServicesDemo/${/my:myFields/my:SaveWrite}.xlsx
  3. To configure the activity to create the Excel file, do this procedure.
    1. Select the Writetab.
    2. Click Click to Load Excel Fields.
    3. Drag the fields from the Click to Load Excel Fields box to the Excel Fields column.
      Field Name Definition

      Excel Field

      Function:
      Specifies Microsoft Excel field to map to the process value. The mapping is performed to change the fields in Microsoft Excel document, supplied as output.
      Accepted Values:
      Read only - A Microsoft Excel field.

      Use the Move All button to move the fields from Click to Load Excel Fields box to the Excel Field column.

      You can also drag the Microsoft Excel fields from the Click to Load Excel Fields box to the Excel Field column.

      Default Value:
      None
    4. (Optional) Click Move all to move all the fields from the Click to Load Excel Fields box to the Excel Fields column.
    5. In the Process Value field, enter a process data variable to store the values.
      Field Name Definition

      Process Value

      Function:
      Specifies the process data variable to map to the Excel Field to change each field in Microsoft Excel document.
      Accepted Values:
      • A number or a text string that can contain spaces.

        This must be in accordance with the data type of Microsoft Excel field.

      • A process data variable

      Note:

      • Once Microsoft Excel field is in the Excel Field column, to map a Excel Field to a process value, select the row, and use Process Data screen.
      Default Value:
      None
      Accepts Process Data Variables:
      Yes
  4. To configure the activity to read the Microsoft Excel template, do this procedure.
    1. Select the Readtab.
    2. Click Click to Load Excel Fields.
    3. Drag the fields from the Click to Load Excel Fields box to the Excel Fields column.
      Field Name Definition

      Excel Field

      Function:
      Specifies Microsoft Excel field to map to the process value. The mapping is performed to change the fields in Microsoft Excel document, supplied as output.
      Accepted Values:
      Read only - A Microsoft Excel field.

      Use the Move All button to move the fields from Click to Load Excel Fields box to the Excel Field column.

      You can also drag the Microsoft Excel fields from the Click to Load Excel Fields box to the Excel Field column.

      Default Value:
      None
    4. (Optional) Click Move all to move all the fields from the Click to Load Excel Fields box to the Excel Fields column.
    5. In the Process Value field, enter a process data variable to store the values.
      Field Name Definition

      Process Value

      Function:
      Specifies the process data variable to map to the Excel Field to change each field in Microsoft Excel document.
      Accepted Values:
      • A number or a text string that can contain spaces.

        This must be in accordance with the data type of Microsoft Excel field.

      • A process data variable

      Note:

      • Once Microsoft Excel field is in the Excel Field column, to map a Excel Field to a process value, select the row, and use Process Data screen.
      Default Value:
      None
      Accepts Process Data Variables:
      Yes
    6. (Optional) Select Formatted to format value of the field.