• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 823
  • Last Modified:

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

0
cso2013
Asked:
cso2013
  • 2
  • 2
1 Solution
 
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
 
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)Commented:
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now