How to add rows below each subject showing the sum

Help StudioX
,
1

Hi, I am having trouble to add rows below each employees to show the sum of the salaries for a certain period. I tried using Excel add rows, however I have no idea how to use it. Is there any other way to do it?

I gave a sample below. I would appreciate if someone could assist me adding rows below each employees (James, Jenny & Stephen) showing the total sum for that certain period. I really appreciate any help. Thanks!

Payroll Sum.xlsx (16.6 KB)

2
  1. Read Excel Data: Use the “Read Range” activity to read the data from the Excel sheet into a DataTable variable.
  2. Calculate the Sum: Iterate through the DataTable to calculate the sum of the salaries for each employee based on the specific period. Store the calculated sum in a separate variable.
  3. Add Rows to Excel: Use the “Write Cell” or “Write Range” activity to add a new row below each employee’s data in the Excel sheet.
3

@desperATE

Use the “Excel Application Scope” activity to open your Excel file and the “Read Range” activity to read the data into a DataTable variable.

Calculate the sums:

DTEmployeeData.AsEnumerable().GroupBy(row => row.Field<string>("EmployeeName"))
.Select(group => new
    {
        EmployeeName = group.Key,
        TotalSalary = group.Sum(row => row.Field<decimal>("Salary"))
    })
    .ToList();

Add Rows with Sum Data:
You can use a For Each activity to iterate through the “sums” variable and add rows to the DataTable for each employee’s sum.

{
    dtEmployeeData.Rows.Add(sum.EmployeeName, sum.TotalSalary);
}

Use the “Write Range” activity to write the updated DataTable

Cheers…!

4

Hi thanks for the prompt reply and solution.
However, I am quite new to UiPath and a bit lost :sweat_smile:, where do I input these “Calculate the Sums”.

5

Use read range activity to read the data into datatable variable
then use assign activity calculate the sum with linq query

6

Hi, I apologise for bothering. Am I suppose to input everything under the “Calculate the Sums:” that you stated above into 1 assign activity? I am confuse about the calculation part. And regarding the adding rows in the DataTable, how do I do it?

7

Create variables to store the sums. For example, you can create variables like totalSum1 , totalSum2 , and totalSum3 to hold the sums of three columns.

use assign activity

totalSum1 = YourDataTable.AsEnumerable().Sum(Function(row) If(IsNumeric(row("Column1").ToString), CDbl(row("Column1")), 0))

If you want to add rows to a DataTable, you can use the Add Data Row activity.

8

Hi, I am a little bit confuse about the solution you’ve given above.

Is the solution you’ve given above for the excel to go from this:

image
image710×282 6.54 KB

To this:
image
image712×365 10.2 KB

or at least something similar to this? because I can’t seem to find a way to do it.