Verify Excel Workbook Data

The example below explains how to use Excel activities for analyzing and verifying stock, and informing the user about restocking needs. It presents activities such as Read Row,Read Cell, or Read Cell Formula. You can find these activities in the UiPath.Excel.Activities package, under the Workbook section.

note

Download and extract the archive with the project in this example and copy the file "stock.xlsx" to your project folder.

This is how the automation process can be built:

1. Open Studio and create a new Process.

2. Drag a Sequence container in the Workflow Designer.

   • Create the following variables:

   Variable Name	Variable Type	Default Value
   header	System.Collections.Generic.IEnumerable<System.Object>	N/A
   product	System.Collections.Generic.IEnumerable<System.Object>	N/A
   orderList	DataTable	N/A
   index	Int32	N/A

3. Drag a Read Row activity inside the Sequence container.

   • Add the expression "stock.xlsx" in the Document path field.
   • Add the value "Bucharest" in the Sheet Name field.
   • Add the value "A1" in the Starting Cell field.
   • In the Properties panel, add the variable header in the Result field.

4. Drag another Read Row activity below the previous Read Row activity.

   • Add the expression "stock.xlsx" in the Document path field.
   • Add the value "Bucharest" in the Sheet Name field.
   • Add the value "A2" in the Starting Cell field.
   • In the Properties panel, add the variable product in the Result field.

5. Drag an Assign activity below the Read Row activity.

   • Add the variable index in the To field.
   • Add the value 2 in the Value field.

6. Drag a While activity below the Assign activity.

   • Add the expression product.First.ToString.Length > 0 in the Condition field.
   • Select the Sequence container and create the following variable:

   Variable Name	Variable Type	Default Value
   requiredAmount	Double	N/A

7. Drag a Read Cell activity inside the Sequence container of the While activity.

   • Add the expression "stock.xlsx" in the Document path field.
   • Add the value "Bucharest" in the Sheet Name field.
   • Add the value "D" + index.ToString in the Starting Cell field.
   • In the Properties panel, add the variable requiredAmount in the Result field.

8. Drag an If activity below the Read Cell activity.

   • Add the expression requiredAmount > 0 in the Condition field.

9. Drag a Sequence container in the Then field of the If activity.

   • Create the following variables:

   Variable Name	Variable Type	Default Value
   amountFormula	String	N/A
   index2	String	N/A

10. Drag a Read Cell Formula activity inside the Sequence container from the Then field.

    • Add the expression "stock.xlsx" in the Document path field.
    • Add the value "Bucharest" in the Sheet Name field.
    • Add the value "D2" in the Starting Cell field.
    • In the Properties panel, add the variable amountFormula in the Result field.

11. Drag a Message Box activity below the Read Cell Formula activity.

    • Add the expression "According to the formula (" + amountFormula + ") you have to order " + requiredAmount.ToString + " more " + product.ElementAt(0).ToString + " from " + product.ElementAt(1).ToString in the Text field.

12. Drag an Assign activity below the If activity.

    • Add the variable index in the To field.
    • Add the expression index + 1 in the Value field.

13. Drag a Read Row activity below the Assign activity.

• Add the expression "stock.xlsx" in the Document path field.
• Add the value "Bucharest" in the Sheet Name field.
• Add the value "A" + index.ToString in the Starting Cell field.
• In the Properties panel, add the variable product in the Result field.

This is how your workflow should look:

14. Run the process. The Excel file is analyzed, verified, and a message is displayed, informing the user about the needed changes.

Download example