Extended Excel Activities
These activities are meant to work on the excel. These activities will mimic exact human actions.
- Sometimes UiPath Studio/Robot throws an out of memory exception when excel data is huge.
- Manipulating Datatable becomes tough
- Maintaining the format
Problem Statement 1:
For example, if I have an excel which has Vendor details in it. It has 500K rows and 60 columns in it.
I need to edit the details of one Vendor which has only 4 to 5 rows out of 500k rows.
I don’t want to store entire data into Datatable as I need only 4 to 5 rows to work on. So if I filter Datatable to get these four rows, it will be difficult to update the excel back with these updated rows.
Solution: FindAll Activity will return CellName like List which will have $D$78, $D$111, $D$130,
Using For each CellName you can traverse to the different cells of that row.
For example, If you want to change the price, you can use write cell activity on F78, F111, F130 for Price. In this case we are not storing the entire data in a Datatable instead you are working on the specific row.
Problem Statement 2: Manipulating Datatable is tough for a non-Developer
Solution: Use the below activities which will mimic human action like “FindAll, GoTo cell, GoRight, Goto Last Column/row” etc
Problem Statement 3: Maintaining the format
For example, if we have a template for Invoice and we want to add items into invoice list. If I use read range activity and get the data in datatable, then if I push it back to excel after manipulation then the formatting of the excel will be lost.
As we cannot insert or delete rows.
Solution: Insert and Delete row/column activity will add rows or column and then we can use Write range or we can write data cell by cell.
Using Fill Color and Font style activity, we can change the format and font the workbook.
I have created a demo project with sample excel. Please install nugetPackage before running the project.
Excel.Extended.Activities.1.0.2.nupkg (24.4 KB)
OnExcel.zip (65.1 KB)