Link to home
Start Free TrialLog in
Avatar of cso2013
cso2013

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cso2013
cso2013

ASKER

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

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.
i concur with above statement.
Avatar of cso2013

ASKER

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.