?
Solved

DataTable row summation

Posted on 2012-12-31
5
Medium Priority
?
812 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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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