Write filtered results back to the same sheet (e.g., “SABB”)
Add a “Total” row at the end of the Amount in local currency column
The logic works fine, but the workflow takes a long time to run, especially as the number of rows grows. It sometimes freezes or delays heavily when writing the data or calculating the total.
Here’s what I’m currently doing:
Reading Excel using Read Range
Filtering with a For Each Row and If condition
Summing with a loop
Writing each row in the output sheet
Adding total with another Append range
What I Need Help With:
How can I speed this up to (5 to 10 minites)?
Should I use Filter DataTable or LINQ instead of looping?
What’s the fastest way to write data and totals to Excel?
in the excel file have 17500 row
Could you please try with the extension method or LINQ mentioned in the below doc? These both are faster. Try with both one by one and see which one gives you faster response.
Also linking 1 forum post that shares LINQ syntax for such scenario.
this step here is what causing high execution time. Not sure why you are writing for each Row . Imagine opening excel, writing some data, saving, closing it and doing it for 17500 times
When you are in loop update your data in datatable itself and use Add Data Row to add the Total amount and only once write it back to excel file. You might need to clear the Sheet before writing the data, if possible create a new file.
for below: Are you summing the numbers in loop itself?
What I Need Help With:
. Imagine opening excel, writing some data, saving, closing it and doing it for 17500 times