uipath "for each" being time-consuming

Question

i implemented a normal 'for each'-Loop (not the excel specific for each row) in my UIPath Project.

The for-each Loop looks through a datatable with previously retrieved data from an excel file.

The for-each-loop then itererates through the Data with an if-else behind it.

Lets say it is as follows:

for each item in dataTable:

if (content of item == "10")
{
write cell: "Test" into A + index.ToString() 
// leads for instance to writing into excel column 'A1' 
}

else 
{ write cell: "ElseTest" into for C + index.ToString() }

-- I used the syntax just for presentation-purposes.. :D

-> So the problem is: writing into the cells takes so much time, where else putting out a MessageBox with random text inside the if and elses is done in milli-seconds, so the for-each-loop can't be the problem...

I ran that process with task manager opened and found out that Excel starts up, CPU percantage increases heavily, immidiately jumps to 0%... same happens again, for each iteration through the loop.

Why is that? Is there a more optimised way to do that?

I need the for-each structure, because I need to check if it's either value 1 or value 2 inside the cell...

Answer 1

One possible option is to use the Read Range activity, manipulate the DataTable object itself, and then at the end write said object back to the same range using the Write Range activity.

Here's an example. My sheet contains 100 rows with all numbers from 1 to 100 in column A. Whenever there's a "1" in the cell, the data will be overwritten (in the DataTable object).