Sum of column with loop

Help
1

Hi
I would like to get sum of everytime column in my data table using loop. How should I do this if I don’t want to use column names?
Thanks in advance for every help

1 Like
2

Buddy @qlka08

Could you kindly elaborate a bit more about your query buddy that could help us to go in the right direction
Cheers @qlka08

1 Like
3

I’ve got table with variable amount of columns. I need to have row with sum of every column at the end of it. I Think that I should use for each activity but I have problem how to get column as variable and how to assign sum using this variable. However I am open from other solution

4

Notatka2_0
Notatka2_01440×2560 233 KB

Something like this

1 Like
5

alright buddy @qlka08
here you go

  1. use excel application scope to open the excel file
  2. use read range activity to get the data fromthe excel and get the output from this with variable name out_dt of type datatable
  3. use a for each row activity and pass the variable out_dt to this
  4. use a assign activity like this buddy
    out_sum_value = Convert.ToInt32(row(0).ToString)
    where out_sum_value is a variable of type integer
    then another activity like this with out_sum_value_final of type integer and a default value 0
    out_sum_value_final = out_sum_value_final + out_sum_value

finally we will get the sum value in out_sum_value_final variable for first column as we mentioned as row(0), where 0 represents the first column
similar for column two as well but all within the for each row loop

  1. now use a write cell activity to fill this value at the last cell of each column along the last row
    Thats all buddy you are done
    Kindly try this and let know buddy
    Cheers @qlka08
2 Likes
6

hey @qlka08
I tried this using linq,

Find the zip below,
new.zip (13.0 KB)

I hope this will help you.

7

did that work buddy @qlka08

8

None of this two solutions gave me a good answer. @Palaniyappan your solution gave me one of value in a column not total value of column. @samir when i runned your robot it gave me some numbers but they aren’t sum of columns, but I liked this solution with While. Could you tell me something more how does it works?

9

Hi buddy @qlka08
its resolved
sum.zip (8.7 KB)

Kindly try this and let know buddy
Cheers

10

@Palaniyappan,
I think that I am really close but I’ve got problem with last Write Cell activity. It give me error: The given key was not present in the dictionary. Exception Type: System.Collection.Generic.KeyNotFoundException.

Can I rely on your help again?

1 Like
11

buddy @qlka08
yah sure
can i have a view on your xaml buddy if possible that could help us resolve ths issue very easily
Cheers

12

My data are double. I’ve changed out_sum and total_sum for double. Is there posibility that there is problem with write cell activity and convertion total_sum.toString?
What is interesting Excel give me this new row with correct sum but it throw an error

14

hi @qlka08
yes sure,

Explanation:

  1. After reading xls file, I’ve taken count of columns using dt.Columns.Count to run while loop accordingly.
  2. read cell to get column headers and for that I’ve used chr variable which is of (System.Char) type.
    you can see the default values of chr —> "A"C
    This is cell location of Read cell chr+“1” —> to get value of “A1” (Header value of A)
  3. Linq:
    linq
    here,
    colName is output variable of previous read cell activity (header value of 1st column)
    i.Item(colName).ToString —> to get value of A in string format
    Convert.ToDouble(i.Item(colName).ToString) —> to convert those values in System.Double type, to apply Sum.
    dt.AsEnumerable() —> used in a LINQ expression to get access enumerable methods on DataTable.
    by this linq expression we’ll get summation of values of column AS (A —> 1st col)
  4. write cell, to write that value(summation) in cell position chr+“7” —> “A7”
  5. Reason behind using chr
    chr
    Convert.ToUInt16(chr) —> to convert chr(A) into UInt which is 1
    Convert.ToUInt16(chr)+1 —> 1 + 1 —> 2
    Convert.ToChar(Convert.ToUInt16(chr) +1) —> to convert that UInt16 into System.Char (2 into B)
    So using this while loop runs for B and C.

and lastly,

I don’t know why you’re not getting exact sum.
Because, I’ve got the expected output, as shown below.
Capture

here you can see correct sum of all columns values in row no 7 (highlighted)

15

Thank you guys for help. I’ve got this! I’ve used @Palaniyappan 's solution with while activity but I changed data for double. My problem was that I’ve got column in cells AA,AB etc. I’ve changed write cell activity and everything works.
Thank you thank you thank you

16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.