Solved

DataTable row summation

Posted on 2012-12-31
5
806 Views
Last Modified: 2013-01-04
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
Comment
Question by:cso2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38732779
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
 

Author Comment

by:cso2013
ID: 38732844
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
 
LVL 40
ID: 38733671
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38734427
i concur with above statement.
0
 

Author Comment

by:cso2013
ID: 38746053
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question