Excel Automation in UiPath

EXCEL AUTOMATION

                       The data in the excel is stored in the form of rows and columns. The excel activities in uipath provides a wide range  of scope to automate excel.  UiPath provides a set of Excel activities that can be used inside Excel Application scope activity and workbook activities. Let see those activities in detail.

Excel Application scope

                       This activity provides a container in which all the excel activities can be used. The scope of the excel ends once this activity completed execution. This can be used even if MS Office is not installed in your system by marking the visible property to false. The main property that is required is the file name  for which we are creating the scope.

Please see the below screen shot

                       Inside this activity we can use all Excel activities, now lets see the Excel activities in detail.

Read Range:

The read range activity is used to read the data from the excel from the specified range. If the range is not specified then entire sheet is read. The sheet from which data needs to be read is specified. We can use either variable or direct value enclosed in double quotation marks.

Write Range:

The write range activity is used to write data to the excel in the specified range. If the range is not specified then it writes from A1. The sheet in which data needs to be written is specified.

Read cell:

The read cell activity is used to read the data from the excel cell specified. The sheet from which the read cell reads data should also be specified to read the data.

Write Cell:

The write cell activity is used to write the data to the excel cell specified. The sheet to which the write cell write data should also be specified to write the data.

Workbook activities

                  These activities doesn’t require to be placed inside an excel application scope. The following are the list of workbook activities.

Read Range:

The read range activity is used to read the data from the excel from the specified excel from the specified range. If the range is not specified then entire sheet is read. The sheet from which data needs to be read is specified.

In: Workbook path or an workbook object
Out: Data table
Must: Sheet Name
Optional: Range

Check “Add Headers” if you want the first row from your document to become header for your data table.

Write Range:

The write range activity is used to write data to the excel specified in the specified range. If the range is not specified then it writes from A1. The sheet in which data needs to be written is specified. If the sheet doesn’t exist it will create a new sheet with that name.

In: Data table
Out: Workbook path or an workbook object
Must: Sheet Name and the starting cell

Optional: Check “Add Headers” if you want to add the header for your data table.

Read cell:

The read cell activity is used to read the data from the specified excel cell. The sheet from which the read cell reads data should also be specified to read the data.

Write Cell:

The write cell activity is used to write the data to the specified excel cell. The sheet to which the write cell write data should also be specified to write the data.

Please check the below chart of various excel activities vs inputs and outputs

Activity Name	Read Range	Read Cell	Read Column	Read Row	Write Range	Write Cell
Input	Range, Sheet name, Workbook path, workbook object				Data Table	String
Output	Data Table	String	IEnumerable	IEnumerable	Workbook Path or Workbook object

Other Excel Terminology

Workbook: a collection of spreadsheets, used for organizing tabular data.

Worksheet: a single spreadsheet that contains cells, organized by Rows and Columns

Cell: The basic storage unit for data in a worksheet (at the intersection point of a vertical column and a horizontal row)

Range: a group or block of cells in a worksheet

Open Workbook

Use Excel Application

Ms Office Excel Installed

Share Files Between Processes

Visible Real Time Changes

File Access Level

Works Only for Xlsx Format

No Need for Office

Access Restriction

Background automation

Best Practices:

                          You can access only xlsx files using UiPath Excel activities otherwise if you have a xls file you have to work with the Excel application.
If you want to apply a formula over a cell you need to use Excel application.
                         The Excel file is a unique resource and this resource could not be used by 2 applications in the same time. You have two approaches:
1. Open the workbook without checking “Use Excel Application” , in this case you are able to access the information from a xslx file. There is no need to install MS Office.

2. Open the workbook with “Use Excel Application” checked. You will be able to see in real time the changes.