Excel Automation Using UiPath

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android, and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms.

Basic Operation of Microsoft Excel :

Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations.

The Microsoft excel can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display. It allows sectioning of data to view its dependencies on various factors for different perspectives (using pivot tables and the scenario manager).

Basics of Automation in UiPath Studio

Writing an Excel Sheet in UiPath

The following example demonstrates how to create an excel file and write something in it.

Create a process called Excel_Automation_Process_Example

excel-automation-process-exampple

Once the Process opened in the UiPath Studio, Install the Excel-related Package into your Process. Search for Excel package in the Official under Manage Packages.

search-exce-activity-rpa-uipath

Click on the UiPath.Excel.Activities. If it is already installed, then check for the new version to update and then click on the Save button.

clikc-on update-and-save-rpa-uipath

Now, you can see a lot of activities present in the Excel activities pane.

excel-activities-prsent-at-activity-pane-rpa-uipath

Drag and drop the sequence into the Process. All the excel related activities are work under Excel Application Scope only. So whatever the activities you are going to work should be under the Excel Application Scope.

Next, add the Excel Application Scope activity inside the sequence, which actually creates an excel file in the UiPath directory. Enter the name of the excel file you want to create, and I am creating readexcel.xlsx, as shown below.

create-an-excel-file-rpa-uipath

Once you save and run the sequence, the readexcel.xlsx file has been created in the UiPath directory with no data in it.
readexcel-file-rpa-uipath

Next, add Write Cell activity inside the sequence, to write inside the excel sheet we have to enter the sheet name and cell name in the Write Cell activity as shown below.
write-cell-rpa-uipath

Next, add one more Write Cell activity inside the Excel Application Scope activity and enter the details as shown below.
write-cel-activity-rpa-uipat

Save and run the sequence. After the execution, open the excel file, you can see that the excel file contains three columns with the column name as Name, Email id, and Phone Number.

excel-file-with-rpa-uipath

PDF Automation by Using UiPath Studio

Read Excel Document in UiPath

Create a new sequence called Read_Excel_Example.

read-excel-example-sequence-rpa-uipath

Add the Excel Application Scope inside the sequence and add the path of the excel file which you want to read. I am going to read the readexcel.xlsx file.
reading-file-read-excel-rpa-uipath

Next, add Read Range Activity inside the application scope. The Read Range activity going to read the value of an Excel range and stores it in the DataTable variable

  • If the Range is not mentioned, the Read Range Activity is going to read All the data from the spreadsheet.
  • If the Range is specified as sheet1, then Reads only the Sheet1,
  • If the Range is specified as from A1 to C1, It will read from A1 cell to C1 cell(with given sheet name)

I am going to read the complete sheet, so I am not going to mention any cell name here.
add-read-range-activity-rpa-uipath

The data will be stored in a data table, so create a data table variable as Excel_DataTable_Variable in the output section under the property pane. Once you click on the Variable by selecting Read Range activity, you will see an Excel_DataTable_Variable.

datatable-type-variable-excel-data-variable-rpa-uipath

Set the scope of the Excel_DataTable_Variable variable to global(Read_Excel_Example)
set-scope-variable-to-glob-rpa-uipath

Add For Each Row Activity inside the sequence and then enter the name of the data type variable inside the text box and then add Write Line Activity inside the For Each activity and write the code to read the rows and columns of the excel sheet as shown below.

row.item(0).ToString //*item(0) reads the data row wise from first column(Name)*//
row.item(2).ToString//*item(1) reads the data row wise from second column(Email id)*//

for-each-activity-rpa-uipath

Save and run the sequence, the Read Range activity is going to read content from the excel sheet except the header, So for testing purposes, I am entering Some data into the Excel Sheet.
excel-with-data-rpa-uipath

Once you run the sequence, you will see the output in the output pane, where the data has been displayed row by row, as shown below.
the-output-of-read-excel-rpa-uipath

Project Structure, Annotations, and Logs

Write to an Excel by reading another Excel in UiPath

In this example, we are going to read from one excel and write it to another excel. I am going to read from the readexcel.xlsx file which is as shown below.
readexcel-excel-sheet-rpa-uipath

I am going to create a new excel file writexcel_example.xlsx, to which I am going to write from readexcel.xlsx file, which is as shown below.
write-excel-file-rpa-uipath

Now, create a new sequence called Write_to_Excel_Example.

write-to-excel-example-rpa-uipath

Add Excel Application Scope activity into the sequence and mention the name of the excel file(readexcel.xlsx) from which you are going to read the data. Next, add the Read Range activity inside the sequence.
add-excel-application-scope-rpa-uipath

Use the Excel_DataTable_Variable in the above Read Range Activity and set the scope of the variable to global.

set-scope-of-the-variable-to-global-rpa-uipath

Next, add one More Excel Application Scope activity into the sequence and enter the name of the Excel file to which you are going to write. I am entering the name of the excel as Writexcel_example.xlsx.

Add Write Range Activity in the Excel Application Scope. Use the Excel_DataTable_Variable in the Write Range Activity.
write-excel-example-rpa-uipath

Now, save and run the sequence. Once the sequence starts to execute, the content from the readexcel.xlsx has been written to the Writexcel_example.xlsx file.
write-excel-example-output-rpa-uipath

Conditional Statements in UiPath Studio

Set Color to Cells in the Excel using UiPath

Set Color to Cells in the Excel Sheet by using Set Range Color activity

  • Create a new sequence called Set_Range_Color_Example
  • Add Excel Application Scope activity inside the sequence
  • Mention the Excel file name you want to change the color of the cells in it
  • I am using the readexcel.xlsx file here.
  • Next, add Set Range Color activity inside the DO container of Excel Application Scope activity
    set-range-color-rpa-uipath
  • Click on the Set Range Color activity and create a variable called Color_Variable in the Properties pane under the Color property.
    create-new-variable-color-variable-rpa-uipat
  • Next, enter the cell range for the Range in the properties panel, I am selecting A1 to A4(Should be in double-quotes, delimited by a colon:)
  • Next, select the SheetName in which sheet of excel file you want to set the cell color(Should be in Double quotes)
    set-range-and-sheet-name-rpa-uipath
  • Next, click on the Set Range Color activity and select the variable type as system.drwaing.color in the variable pane, as shown below.
    set-variable-type-to-system-drwaing-color-rpa-uipath
  • Now, go to the properties panel and click on the Color box and enter color., once you enter color. you will find the drop-down with a list of colors, select your preferred color, and press enters, then click on the Ok button.
    select-color-from-color-drop-down-list-rpa-uipath
  • Now Save the sequence and run. In the output, you can see that the color of the Cells from A1 to A4(1st Column) has changed to blue, as shown below.
    output-set-range-colour-blue-rpa-uipath
  • By setting the Range in the properties pane, you can also change the color of the remaining cells with other colors.

Loops in UiPath Studio

Get Cell Color using UiPath

The Get Cell Color activity Extracts the background color of a cell and saves it as a Color variable. It can only be used within the Excel Application Scope activity.

The following example demonstrates how to extract the cell color from the excel sheet.

I am creating a new excel file called as shown below, which contains a background color in each cell. We can extract the background color of the cell by using Get Cell activity.
Fruit-list-file-rpa-uipath

Create a new sequence called Get_Cell_Color_Example as shown below and then add Excel Application Scope activity inside the sequence and enter the path of the Fruit_List.xlsx inside the activity, as shown below.
add-excel-scope-and-fruit-list-file-rpa-uipath

Next, add Get Cell Color Activity inside the do container of the Excel Application Scope activity. You will find the default sheet name and cell name as Sheet1 and cell name as written in double-quotes.

I am going to keep the Sheet1 as it is and changes the cell name to A2, where A2 is the name of the cell, which I want to extract from the background color.
add-get-cell-color-activity-rpa-uipat

Click on the Get Cell Color activity and create a variable in the Properties pane for Color property under Output. I am creating a variable called Cell_Color_Variable.

create-color-variable-rpa-uipath

By clicking on the variable pane, set the scope of the variable to Get_Cell_Color_Example, as shown below.
set-scope-of-variable-rpa-uipath

Next, add write cell activity inside the sequence and then enter the sheet name as Sheet1 and Cell name as B2. The cell name B2 represents the cell where you want to extract the background-color. And join the variable name to store the name of the color in the activity, as shown below.
write-cell-activity-rpa-uipath

Now save and run the sequence. After execution, the background color of a Banana cell has been extracted as yellow, as shown below.
out-put-of-get-cell-colcor-rpa-uipath

Creating an Automation Process in the UiPath Studio

Read Column using UiPAth

The Read column activity is going to read the content of the column with a mentioned range.

Create a new sequence called Read_Column_Example

new-sequence-read-column-rpa-uipath

Next, add the Excel Application Scope activity inside the sequence and enter the excel file name in which you want to read the column. Here I am going to read the readexcel.xlsx file which is as shown below.
readexcel-file-for-read-column-rpa-uipath

Next, add Read Column activity inside the sequence and enter the sheet name and column which you want to read. I am coming Sheet1 and cell name as C1
enter-read-column-actitvity-rpa-uipath

Create a new variable to store the output by clicking on the Read Column in the Properties pane. I am creating a variable called Read_Column_Variable and set the scope of the variable to Read_Column_Example

creating-new-variable-rpa-uipath

Next, add For each activity to read range elements from the column and enter the details as shown below.
add-for-each-activty-rpa-uipath

Now, save and run the sequence; after execution, the pop-up message displays the Elements from the column Phone Number.
output-of-read-column-rpa-uipath

Once you click on the Ok button in the pop-up message, it will display the next element from that column.
next-element-from-read-column-rpa-uipath

Introduction and Installation of RPA UiPath

Append Range Using UiPAth

The Append Range activity adds the information stored in a DataTable variable to the end of a specified Excel spreadsheet. If the sheet does not exist, a new one is created with the name indicated in the SheetName field.

I have data in the excel file called Exceldemo.xlsx in. sheet1
excel-demo-sheet1-rpa-uipath

And sheet2 as shown below.
excel-demo-sheet2-rpa-uipath

I want to add these details to the existing excel file called LoginData.xlsx which contains the following details.
login-data-rpa-uipath

Now create a new sequence called Append_Range_Activity

append-range-activity-rpa-uipath

Next, add Excel Application Scope activity inside the sequence, as shown below, and enter the excel file name(Exceldemo.xlsx) from which you want to read the data.

excel-application-scope-inside-append-rpa-uipath

Next, add Read Range activity inside the Do container of the Excel Application Scope activity and specify the sheet name from which sheet you want to read the data. From Exceldemo.xlsx file, I have data in two sheets, namely Sheet1 and Sheet2. So enter the sheet name as Sheet1
add-read-range-activity-for-sheet1-rpa-uipath

Create a new variable to store the data from the sheet by clicking on Read Ramge activity in the Properties pane for DataTable. I am creating a variable called Sheet1_Variable.

creating-sheet1-variable-and-set-scope-to-global-rpa-uipath

Add one more Read Range activity and enter the sheet name and then create a variable for Sheet2 as Sheet2_Variabe.

sheet2-rpa-uipath

Next, add Append Range activity inside the sequence and then add the Excel Filename(LoginData.xlsx) to which you want to append the data from exceldemo.xlsx and mention the Sheet name (Master Sheet) and enter the variable name
append-range-master-sheet-with-sheetq-rpa-uipath

As we are reading from Two sheets from Exceldemo.xlsx, so add on more Append Range Activity and mention the File name and Sheet name as shown below.
master-sheet-with-sheet2-rpa-uipath

Now save and run the sequence. After execution, the Logindemo.xlsx contains the data from exceldemo.xlsx.

login-data-output-rpa-uipath

UiPath Selectors

Sorting of Data using UiPath

By using the Sort Table activity, we can sort the content of the excel sheet according to user requirements.

I have an Excel file that contains the candidate attends, which is as shown below.
candidate-attendance-rpa-uipath

Let us sort the candidate attendance. I am going to sort the Day in accending order, Create a new sequence called Sort_Data_Table

sort-data-table-sequence-rpa-uipath

Add the Excel Application Scope activity inside the sequence and enter the excel file name in which you want to sort the data.
add-excel-application-scope-activity-rpa-uipath

Next, add Sort Table activity inside the Do container of the Excel Application Scope activity and enter the Sheet Name, Table Name, Column Name.

Convert the excel sheet data into a table format :

  • Select the Content of the Data Sheet
    seklect-the-content-of-the-sheet-rpa-uipath
  • Press Ctrl+T
  • Create Table pop-up message will occur
  • Select My table has headers
  • Click on Ok button

    craete-table-rpa-uipath
  • The content has been converted into the table format
  • The Default table name will Table1
    table-name-rpa-uipath
  • If you want to change the name of the table, then you can change it.

Now, enter the Table name as Table1 and Sheet name as Sheet1 and then Column name as Day in Sort Table activity.

sort-table-details-rpa-uipath

Now, save and run the sequence, After the execution, The Day Content in the table has been sorted in the ascending order.
Day-content-rpa-uipath

In the same way, we can sort the Date also. For first, you need to change the Date in the DD-MM-YY format.

To Covert the Date into DD-MM-YY format :

  • Select the Date in the Date Column
    select-date-rpa-uipath
  • Under the Home Tab, Click on the Drop-down list in the Number pane and then select More options.
    click-on-more-option-in-drop-down-list-rpa-uipath
  • Once you click on the more options, the Format Cells wizard will appear
  • Select the Date and select the date format and then click on the Ok button.
    select-data-and-date-format-rpa-uipath
    Now, enter the Column name as Date in the Sort Table in the UiPath and run the sequence.
    sort-table-for-date-rpa-uipath

After the execution, the date in the data table has been sorted in the ascending order.

sorted-list-of-date-in-accending-format-rpa-uipath

Debugging in UiPath Studio

Real-Time Examples using Excel Automation in Uipath

Example.1: Extracting Invoices in PDF to single Excel file

Create a New Process called Real-Time Examples


real-time-example-sequence-rpa-uipath

In the example, we are going to extract some details from invoices for that I have downloaded some of the sample invoices which are in the PDF format and saved inside the Real_Time_Example project, under the input folder.

invoice-in-pdf-format-rpa-uipath

invoice-pdf-rpa-uipath

invoice-three-rpa-uipath

The Invoice is having several fields such as Customer name, vendor name, Item Description, Unit Price, Quantity, and Total price. I want to extract Invoice Number from these invoices.

Let us do this in a flowchart. Create a new flowchart called Invoice_Automation_example

  • Add Assign activity inside the flowchart and create a new variable in the To field. I have created a new variable called. Var_PDF_Files
    assign-activity-and-pdf-var-rpa-uipath
  • Click on the Variables pane and set the variable type to Array of [T] and then select String from Select Types wizard, because it is going to contain the string values.
    set-pdf-var-to-array-of-string-rpa-uipath
  • Next, we have to access all the PDF files from the the flowchart and enter the Var_PDF_Files variable in it.
    for-each-activity-rp-auipath
  • Next, add Read PDF Text activity to read the text from the file, inside the For Each activity.
  • To avail the Read PDF Text activity, first, we need to install the PDF activity from the Manage packages.
  • Go to Manage Packages and search for PDF in the Official package.
    download-pdf-factivity-rpa-uipath
  • Next, click on Install and Save.
  • Now add Read PDF Text activity inside the For Each Activity.
    read-pdf-text-activity-inside-for-each-activity-rpa-uipath
  • Enter the File name as the item.tostring inside the Read PDF Text activity
    item-to-string-activity-rpa-uipath
  • Click on the Read PDF Text activity and create a new variable in the Properties pane for the Output variable property.
    pdf-to-text-out-put-variable-rpa-uipath
  • Next, add Message Box activity inside the flowchart and enter the output variable inside it.
    message-box-with-out-put-variable-rpa-uipath
  • Now, save and run the sequence; after the execution, the pop-up message box displays the text from the PDF invoice.
    invoice-message-rpa-uipath

invoice-two-rpa-uipath invoice-three-message-box-rpa-uipath

The pattern of the PDF is the same for all the Invoices, so you can easily write the automation for this. In the above image, the message box is displaying the invoice line by line. Now you know that in which line Invoice number is present.

Now we have to extract only the required data from the Invoice, I want to extract the Invoice Number, which is present in the second line.

To get only the required field we have a function called Split(), Where split is a function that can split a string into multiple strings.

Example.2: Splitting Invoice into Line by Line:
  • Add the Assign activity before the Message box activity and create a new variable in it and then write the function to split the string.
    craet-invoice-variable-in-assign-activity
  • And write the split function to split the string as shown below, We know that the Invoice number is in the second line, that means we have to give as 1 in the function because the line count will start from zero(0).
    Split(pdf2text,Environment.NewLine)(1)
  • Next, add the Message box into the Flowchart and then enter the output variable name in it as shown below.
    add-message-box-activity-rpa-uipath
  • Now, Save and Run the flowchart, After the execution, the pop-up message will be displaying the Invoice number of all the three invoices.
    invoie-number-invoice-one
    invoie-number-two-rp-auipath
    invoice-number-three-rpa-uipath
Example.3: Extract required fields from Invoice into the Excel sheet.
  • For this example, I am using the Following three invoices.
    invoice-one-rpa-uipath
    second-invoice-rpa-uipath
    third-invoice-rpa-uipath
  • From these invoices, I wanted to extract From, Client and Email. For that, I will create a new folder by the name-calling Output in the same project file. And creating a new excel file called Invoice.xlsx in it.
    invoice-with-extract-data-rpa-uipath
  • Let us try to print email id from all the invoices in the pop-up message box, For that add an Assign activity inside the sequence and create a new variable called id.
  • And set the variable type of id to String and write the function to split the email, we have email id in the 7th line (the line count will start from zero(0), while counting, you have to consider the blank line also)
    Split(pdf2text,Environment.NewLine)(7)​
    read-text-with-id-rpa-uipath
  • Now save and run the flowchart, after the execution, the pop-up message will be displayed.
    three-email-id-rpa-uipath
  • Add one more Assign activity inside the flowchart and try to get From and To . Write the function to split the From and To from the invoice.
    Split(pdf2text,Environment.NewLine)(6)​
  • Now save and run the flowchart, After the execution, the pop-up message will be displayed. Here Sha is From and Client1 is To.
    From-and-to-sha-client-rpa-uipat
  • Here From and To details are in one line so let us split them by using space.
  • Add Assign activity inside the flowchart and create a new variable(I have created a new variable called fromcompany) and then write the function to split them as shown below.
    Split(FromTo," ")(0)​
  • And also add one more Assign activity into the flowchart and create a variable for Client as Client only and then write the split function as shown below.
    Split(FromTo," ")(1)​
  • Now put the body of the For Each activity into Try Catch activity.
  • Select the For Each activity and press Ctrl+T and the whole For Each activity will be inside the Try-Catch block.
    for-ecah-inside-try-catch-activity-rpa-uipath
  • Click on the Exception and select System.Exception
    system
  • Next, Add Write Line activity inside the Exception and write the text as follows.
    write-line-inside-try-ccatch-rpa-uipath
  • Now close the Try block and enter the variable name client inside the Message box activity and save and run the flowchart.
    client-variable-inside-message-box-rpa-uipath
  • Now run the sequence, after the execution, the pop-up message will display the client names as follows.
    client-one-two-three-rpa-uipath
  • The next thing is we have to write this information into excel. But we cannot write this information directly into the Excel, let us create a new sequence and invoke it.
  • We are going to get the details of Invoice_Automation_Example into WritetoExcel.
  • Add Invoke Workflow File activity inside the flowchart and enter the file name to import the details.
    write-to-excel-rpa-uipath
    write-invoke-workflow-file-rpa-uipath
  • Next, click on the import arguments and create new arguments and pass the value for it. We are passing the values from one flow to another flow.
    invoked-workflow-arguments-rpa-uipath
Example.4: Writing information into the Excel
  • Create a new sequence called WritetoExcel
    write-to-excel-sequence-rpa-uipath
  • Open the WritetoExcel sequence and here we have to receive the arguments by creating arguments in the arguments pane.

arguements-in-write-to-excel-rpa-uipath

  • Next, add Excel Application Scope activity inside the sequence and enter the output file path.
    output-file-invoice-xl-rpa-uipath
  • Next, add Write Cell activity into the Do container of the Excel Application Scope activity and enter the Variable name as shown below.
    write-cell-activity-insidethedo-container-rpa-uipath
  • Now Save and run the sequence, After the execution, you can see the details in the excel file.
    output-in-excel-sheet-rpa-uipath
  • You can see that in the above excel sheet the details are overwritten, this is because we had given constant values as A2, B2, and C2.
  • So, let us increment the values.
  • We can increment the value by creating a variable, go to main workflow and add Assign activity between the first Assign activity and For Each loop.
    cadd-assign-activity-between-assign-and-loop-rpa-uipath
  • Create a variable in the Assign activity called Counterand then increment by 2.
    craete-variable-counter-rpa-uipath
  • Add Assign activity inside the Invoke workflow file activity and then increment the counter value by 2 every time.
    increment-counter-by-one-rpa-uipath
  • Click on the Import arguments, and pass the counter-arguments as follows.
    in-counter-rpa-uipath
  • Now, create an in_counter arguments in the Writetoexcel sequence in the arguments pane and set the datatype to Int.
    in-counter-argument-rpa-uipath
  • Next, change the constant values into as shown below in each Write Cell activity
    "A"+in_counter.ToString
    "B"+in_counter.ToString
    "C"+in_counter.ToString​

increment-constant-values-rpa-uipath

  • Save and run the sequence, after the execution you will find the details in the excel file.
    out-put-of-invoice-excel-file-rpa-uipath

In the same way, we can perform May other activities using UiPath Automation.

  • Closing Workbook
  • Write Cell
  • Delete Range
  • Copy Sheet
  • Get Workbook sheet
  • Get Workbook Sheets
  • Read Cell Formula...Etc..