DataTable row summation

I have been tasked with something I thought would be relatively simple. I have a datatable that I am creating from an Excel Spreadsheet. I can sum each column and add a row for the column totals. However (I've been at this for a week), I can't figure out how to sum the rows horizontally.

I've tried looping through and adding each column and posting it in the additional column (which appears when I run it). The furthest I can get is to an "invalid cast exception" --

This is the code that I am trying to use. It works fine if you are using a self-made datatable, but throws the invalid cast  -- I am using C#.

  int sum = 0;
   foreach (DataRow rows in dt.Rows)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            for (int j = 0; j < dt.Rows.Count; j++)
            {


                int number = Convert.ToInt32(dt.Rows[j].Field<int>(i));
                sum += number;
            }
        }

              rows["testrow"] = sum;
        }
            dataGridView1.DataSource = dt;
    }

Open in new window

cso2013Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you loop through the rows, and then "matrix" through all columns AND rows ?

so, you want for each row to compute the sum ...
and what do you want to do with it?
I presume you want to put that value into 1 of the colums, aka the last one?

   foreach (DataRow rows in dt.Rows)
    {
       int sum_row = 0;
        for (int i = 0; i < dt.Columns.Count  ; i++)
        {
                int number = Convert.ToInt32(rows.Field<int>(i));
                sum_row += number;
        }
       rows.Field( dt.Columns) = sum_row;

    }
    dataGridView1.DataSource = dt;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cso2013Author Commented:
Thank you :)

I used this though --
        System.Data.DataTable dt = ds.Tables[0];
            dt.Columns.Add("testrow", typeof(int));
            DataRow dr = dt.NewRow();
            int sum = 0;
            for (int i = 1; i < dt.Columns.Count; i++)
            {
                for (int j = 1; j < dt.Rows.Count; j++)
                {
                  
                    if (j == dt.Rows.Count - 1)
                    {
                        dt.Rows[i][j] = Convert.ToInt32(sum);
                        sum = 0;
                }
                    else
                    {
                        object number = dt.Rows[i][j];
                       sum += Convert.ToInt32(number);
                    }
                   
                  }
                dataGridView1.DataSource = dt;
            }

Open in new window

0
Jacques Bourgeois (James Burger)PresidentCommented:
Do you do something else with the DataTable? For the operation your are doing here, a 2 dimensions array would be a far better solution than a DataTable to handle integers on which you perform operations.

What is the line that triggers the error? It is very hard for us to help you if we do not know where the error is happening.

In your first post, if you want to "sum the rows horizontally", why do you add the sum to a row instead of a column: rows["testrow"] = sum;?

In you second post, you seem to have corrected that by creating an extra column, but there is no code that adds the sum to that column.

And this has nothing to do with your problem, but what is ds? A DataSet? Do you have more than one table? Do you have relations? If so a DataSet is useless. A DataSet is a collection of tables linked with relations. If you have only one table, no need for a DataSet. This is a too common error propagated from old code dating from the first versions of the framework.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i concur with above statement.
0
cso2013Author Commented:
I had been approaching it backwards as you have mentioned. The idea was to go to the end of each row horizontally and insert the total into the new column. Which is what I have ended up doing. I appreciate the feedback and will approach questions in the future more thoroughly.

Also, sorry for the delay in response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual C++.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.