Interpret Excel Results

The example below explains how to analyze the results from an .xlsx file and rearrange them in the correct order. It presents activities such as Get Workbook Sheet, Read Cell, Write Cell, Get Cell Color, and Set Range Color. 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 named by default Main.

2. Drag a Sequence container in the Workflow Designer.

   • Create the following variables:

   Variable Name	Variable Type	Default Value
   SheetName	String	N/A
   DT1	DataTable	N/A
   Counter	Int32	1
   CellColor	System.Drawing.Color	N/A

3. Drag an Excel Application Scope activity under the Sequence container.

   • In the Properties panel add the value "ExamResults.xlsx" in the WorkbookPath field and select the check boxes for the AutoSave, CreateNewFile and Visible options. These options ensure that the process automatically saves all changes, creates a new file whenever needed, and is visible for all users.

4. Add a Sequence container in the body of the Excel Application Scope activity.

5. Drag a Get Workbook Sheet activity inside the Sequence.

   • In the Properties panel add the value 0 in the Index field and the variable SheetName in the Sheet field.

6. Drag an Excel Read Range activity under the Get Workbook Sheet activity.

   • In the Properties panel add the variable SheetName in the Sheet field and the variable DT1 in the DataTable field.
   • Select the check box for the AddHeaders option for considering the first row of the table a header.

7. Add a For Each Row activity below the Read Range activity.

   • Insert the variable DT1 in the DataTable field.

8. Place a Sequence container inside the body of the For Each Row activity.

   • Create the following variables:

   Variable Name	Variable Type	Default Value
   CellValue	String	N\A
   Grade	Double	N\A

9. Drag an Assign activity in the body of the newly created Sequence.

   • Add the variable Counter in the To field and the value Counter + 1 in the Value field.

10. Drag a Get Cell Color activity below the Assign activity.

    • In the Properties panel add the value "D" + Counter.ToString in the Cell field, the variable SheetName in the SheetName field and the variable CellColor in the Color field.

11. Add an Excel Read Cell activity below the Get Cell Color activity. This activity reads the results from the .xlsx file.

    • In the Properties panel, add the value "D" + Counter.ToString in the Cell field, the variable SheetName in the SheetName field and the variable CellValue in the Result field.

12. Add another Excel Read Cell activity below the first one. This activity reads the grades from the .xlsx file.

    • In the Properties panel, add the value "C" + Counter.ToString in the Cell field, the variable SheetName in the SheetName field and the variable Grade in the Result field.

13. Place an If activity underneath the last Read Cell activity.

    • Populate the Condition field with this value Grade >= 5.

14. Add two Sequence containers, one for the Then field and another one for the Else field. These activities set different colors for each result.

15. In the body of the Then Sequence add a Set Range Color activity. This writes the result Passed in blue.

    • In the Properties panel add the value System.Drawing.Color.Blue in the Color field, the value "D" + Counter.ToString in the Range field and the variable SheetName in the SheetName field.

16. Drag a Write Cell activity below the Set Range Color activity.

    • In the Properties panel add the value "D" + Counter.ToString in the Range field, the variable SheetName in the SheetName field and the value "Passed" in the Value field.

17. In the body of the Else Sequence add a Set Range Color activity. This writes the result Failed in red.

    • In the Properties panel add the value System.Drawing.Color.Red in the Color field, the value "D" + Counter.ToString in the Range field and the variable SheetName in the SheetName field.

18. Drag a Write Cell activity below the Set Range Color activity.

    • In the Properties panel add the value "D" + Counter.ToString in the Range field, the variable SheetName in the SheetName field and the value "Failed" in the Value field.

19. Place a Sequence container below the If activity.

20. Drag an Excel Write Cell activity in the body of the sequence.

    • In the Properties panel add the value "E" + Counter.ToString in the Range field, the variable SheetName in the SheetName field and the CellValue in the Value field.

21. Add a Set Range Color below the Write Cell activity.

    • In the Properties panel add the variable CellColor in the Color field, the value "E" + Counter.ToString in the Range field and the variable SheetName in the SheetName field.

22. Run the process. The automation process filters the data and creates a new file with the sorted results.

Download example