How to load Excel data into a DataTable and calculate row and column sums?

Help Activities
1

Hi UiPath Forum community, I’m facing an issue while trying to work with Excel data in UiPath. I have successfully loaded the Excel data into a DataTable using the Excel Application Scope and Read Range activity. However, I’m unsure how to proceed with calculating the sum of all row values and column values in the DataTable.

  1. How can I calculate the sum of all row values in the DataTable?
  2. How can I calculate the sum of all column values in the Data Table?

image

2

Hi @ram_pv

dt.AsEnumerable.Sum(Function(r) r.ItemArray.Sum(Function(a) cint(a.ToString)))

gives Total Sum of rows the datatable

dt.Columns.Cast(of DataColumn).Select(Function(c) dt.AsEnumerable.Sum(Function(r) Cint(r(c.ToString)))).Toarray

Gives the columns count of datatable

cheers.

3 Likes
3

@ram_pv

Assign totalRowSum = 0.0

For Each row In dataTable.Rows
rowSum = row.ItemArray.Sum(Function(item) Convert.ToDouble(item))
totalRowSum = totalRowSum + rowSum
End For

Log Message ("Total Row Sum: " + totalRowSum.ToString())

Assign totalColumnSum = 0.0

For Each colIndex In Enumerable.Range(0, dataTable.Columns.Count)
colSum = dataTable.AsEnumerable().Sum(Function(row) Convert.ToDouble(row(colIndex)))
totalColumnSum = totalColumnSum + colSum
End For

Log Message ("Total Column Sum: " + totalColumnSum.ToString())

1 Like
5

Hi @ram_pv ,
Sum in column
sum= yourDataTable.AsEnumerable().Sum(Function(row) If(TypeOf row(column) Is Double, CDbl(row(column)), 0))
Sum in row
sum = row.ItemArray.Sum(Function(item) If(TypeOf item Is Double, CDbl(item), 0))
Regards,
LNV

1 Like
6

Hey @ram_pv , To Find Sum of

  1. Rows - (From row in dtSum.AsEnumerable Let val=(From item in row.ItemArray Select Cint(item)).ToList.Sum Select val).ToArray

  2. Columns - (From col in dtSum.Columns Let val=(From row in dtSum Select Cint(row(col.ToString))).ToList.sum Select val).ToArray

Output-:
image

Xaml:-
Main.xaml (9.7 KB)
Sheet:-
SumSample.xlsx (7.8 KB)

1 Like