How do I get the sum of multiple columns dynamically?

Question

I have a ton of columns that are created dynamically based on another field, the columns contain 1s in different places based on an ID. I want to sum each column and total it at the bottom.

I have the data in a datatable and I am writing it to an excel file.

I have tried creating an excel range based on rows and then sum that but it does not work.

Excel.Range formatRange3 = (Excel.Range)xlsNewSheet.Range[xlsNewSheet.Cells[2, 6], xlsNewSheet.Cells[rw, 6]].Cells; (Range of rows in one column) 

xlsNewSheet.Cells[rw + 1, 6] = "=sum(" + formatRange3 + " )";

I get this in the row result =SUM(System.__ComObject) so I guess its not possible it to do that.

Is there a way to sum the columns in the datatable and them put the results in a list which I can then write to the excel sheet?

Answer 1

The problem is that you are building up your function as a string and trying to pass in a Excel.Range object, which will call ToString() that ultimately just gives you the type name (in this case System.__ComObject).

You need to get the address of the range, and for that you use the range Address method. For example:

xlsNewSheet.Cells[rw + 1, 6] = "=sum(" + formatRange3.Address + " )";

And if you are using a modern version of C#, you can use string interpolation to make it more readable:

xlsNewSheet.Cells[rw + 1, 6] = $"=sum({formatRange3.Address})";