Table Functions

The example below explains how to maximize the use of tables when creating an automation process. It presents activities such as Filter Table, Sort Table, Get Table Range and Read Range. You can find these activities in the UiPath.Excel.Activities package.

This is how the automation process can be built:

1. Open Studio and create a new Process.

2. Drag a Sequence to the Workflow Designer

   • Create the following variables:

     Variable name	Variable type	Default value
     Password	GenericValue	N/A
     FilteredEmployees	DataTable	N/A

     Note: Add the file Employees.xlsx into the project folder. All data is retrieved from this file.
3. Drag a new Sequence container inside the previously created one and name it Retrieve Excel Password.

4. Add an Assign activity inside the Retrieve Excel Password sequence.

   • In the Properties panel, add Password in the To field and "1o2Pqdf6A" in the Value field. You need to do this because the Employees.xlsx file is password protected.

5. Drag an Excel Application Scope activity below the Retrieve Excel Password sequence.

   • WorkbookPath field must be populated with "Employees.xlsx".
   • In the Properties panel, select the check boxes for ReadOnly and Visible options. This makes the "Employees.xlsx" file visible to anyone in a Read only mode.
6. Add a new Sequence container in the body of the Excel Application Scope.

7. Drag a Filter Table activity inside the newly created Sequence.

   • In the Properties panel add Field value in the ColumnName field, {"Police", "Fire"} in the FilterOptions field , "DataExtract" in the SheetName field and "Employees" in the TableName field.

8. Add a Sort Table activity under the Filter Table activity.

   • In the Properties panel, add "Income" in the ColumnName field, "DataExtract" in the SheetName field and "Employees" in the TableName field.

9. Drag a Get Table Range activity below the Sort Table activity.

   • Go to the Properties panel and add the following information: add "DataExtract" in the SheetName field, add "Employees" in the TableName field and AllEmployees in the Range field.

10. Add a Write Line activity underneath the Get Table Range activity.

    • Add this syntax "Employees list: " + AllEmployees in the Text field.

11. Drag an Excel Read Range activity below the Write Line activity.

    • In the Properties panel, add AllEmployees in the Range field, "DataExtract" in the SheetName field and FilteredEmployees in the DataTable field.
    • The check boxes for AddHeaders and UseFilter must be selected because the first row contains the table headers and we only need to retrieve the filtered data.

12. Drag a For Each Row activity and place it below the Excel Application Scope activity.

    • In the Properties panel, add the variable FilteredEmployees in the DataTable field.
13. Add a new Write Line activity in the body of the For Each Row activity.

• In the Text field, add the following syntax String.Format("{0,-15} {1, -30} {2, 10}", row("Income"), row("Name"), row("Work")).

Download example