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.
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).
The following example demonstrates how to create an excel file and write something in it.
Create a process called Excel_Automation_Process_Example
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.
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.
Now, you can see a lot of activities present in the Excel activities pane.
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.
Once you save and run the sequence, the readexcel.xlsx file has been created in the UiPath directory with no data in it.
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.
Next, add one more Write Cell activity inside the Excel Application Scope activity and enter the details as shown below.
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.
Create a new sequence called Read_Excel_Example.
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.
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
I am going to read the complete sheet, so I am not going to mention any cell name here.
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.
Set the scope of the
Excel_DataTable_Variable variable to global(Read_Excel_Example)
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)*//
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.
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.
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.
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.
Now, create a new sequence called Write_to_Excel_Example.
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.
Use the Excel_DataTable_Variable in the above Read Range Activity and set the scope of the variable to global.
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.
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.
Set Color to Cells in the Excel Sheet by using Set Range Color activity
A1 to A4(Should be in double-quotes, delimited by a colon:)
system.drwaing.colorin the variable pane, as shown below.
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.
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.
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.
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 is the name of the cell, which I want to extract from the background color.
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.
By clicking on the variable pane, set the scope of the variable to Get_Cell_Color_Example, as shown below.
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.
Now save and run the sequence. After execution, the background color of a Banana cell has been extracted as yellow, as shown below.
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
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.
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
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
Next, add For each activity to read range elements from the column and enter the details as shown below.
Now, save and run the sequence; after execution, the pop-up message displays the Elements from the column Phone Number.
Once you click on the Ok button in the pop-up message, it will display the next element from that column.
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
sheet2 as shown below.
I want to add these details to the existing excel file called
LoginData.xlsx which contains the following details.
Now create a new sequence called Append_Range_Activity
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.
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
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.
Add one more Read Range activity and enter the sheet name and then create a variable for Sheet2 as Sheet2_Variabe.
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
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.
Now save and run the sequence. After execution, the Logindemo.xlsx contains the data from exceldemo.xlsx.
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.
Let us sort the candidate attendance. I am going to sort the Day in accending order, Create a new sequence called Sort_Data_Table
Add the Excel Application Scope activity inside the sequence and enter the excel file name in which you want to sort the data.
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 :
Now, enter the Table name as Table1 and Sheet name as Sheet1 and then Column name as Day in Sort Table activity.
Now, save and run the sequence, After the execution, The Day Content in the table has been sorted in the ascending order.
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 :
After the execution, the date in the data table has been sorted in the ascending order.
Create a New Process called Real-Time Examples
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.
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
Var_PDF_Filesvariable in it.
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.
1in the function because the line count will start from zero(0).
To. Write the function to split the From and To from the invoice.
Fromand Client1 is
fromcompany) and then write the function to split them as shown below.
Clientonly and then write the split function as shown below.
clientinside the Message box activity and save and run the flowchart.
Counterand then increment by 2.
in_counterarguments in the Writetoexcel sequence in the arguments pane and set the datatype to Int.
"A"+in_counter.ToString "B"+in_counter.ToString "C"+in_counter.ToString
In the same way, we can perform May other activities using UiPath Automation.