CODEWITHSUNDEEP
c#
scottO
scottO

Reputation: 95

Sum of rows in DataTable -- invalid cast?

1.) ***Ok, so I have my DataTable that is imported from an Excel SpreadSheet and it is filled.I would like to sift through the DataTable and sum each row. I have to skip the first column & the first rows because they are labels. I am trying to reach each row in the table and total it and output to a "Row Total" column .. I am getting a "invalid cast specified" when I am trying to assign the 'number' variable to try and sum each cells value.

Example:
Row(0) --------------------ItemA......ItemB.......ItemC..............RowTotal

Column(1) CompanyA .....12 ..........12.............10....................34

2.) Also, I haven't reached it yet -- there is a potential issue with my trying to output it to the last column in the DataTable.

noted by: dr[dt.Columns.Count - 1] = Convert.ToInt32(sum);

Any thoughts or suggestions?

            DataRow dr = dt.NewRow();

            int sum = 0;
            dt.Columns.Add("Row Totals", typeof(int));

         for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName == "Client")
                {
                    dt.Columns.Cast<DataColumn>().Skip(1);
                }
                else
                {
                    for (int j = 0; j < dt.Rows.Count - 1; j++)
                    {
                        dt.Rows.Cast<DataRow>().Skip(1);

                       int number =0;

                       number = (dt.Rows[j].Field<int>(i));

                        sum += number;

                        dr[dt.Columns.Count - 1] = Convert.ToInt32(sum);


               Console.WriteLine("Row : {0} , Column : {1} , Value : {2}", i,j, dt.Rows[i][j].ToString());
                        Console.WriteLine(sum);
                    }
                    Console.ReadLine();
                }
            }

UPDATE: 12/27/12 ************

So, a solution I'm trying is to just skip the rows and column I know are text. I am still getting the "specified cast not valid" when it tries to filter through each cell and sum it. Any more suggestions?

Thank you in advance.


   DataRow dr = dt.NewRow();

            int sum = 0;
            int number = 0;

            for (int i = 0; i < dt.Columns.Count-1; i++)
            {
                if (dt.Columns[i].ColumnName == "column1")
                {
                    dt.Columns.Cast<DataColumn>().Skip(0);

                }
                if (dt.Columns[i].ColumnName == "column2")
                {
                    dt.Columns.Cast<DataColumn>().Skip(1);
                }
                else
                {

                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        dt.Rows.Cast<DataRow>().Skip(0);
                        dt.Rows.Cast<DataRow>().Skip(1);


                        //if (number != -1 && number != 0)
                        //{

                            number = (dt.Rows[j].Field<int>(i));
                            sum += number;
                            dr[dt.Rows.Count] = Convert.ToInt32(sum);
                        //}
                        //else
                        //{
                        //    number = 0;
                        //}


                        Console.WriteLine("Row : {0} , Column : {1} , Value : {2}", i, j, dt.Rows[i][j].ToString());
                        Console.WriteLine(sum);


                    }
                    Console.ReadLine();

                   dataGridView1.DataSource = dt;
                }


**UPDATE 12/27 1:30pm I have scratched all that previous code and am attempting a test sheet and output. It seems to be working except now I can't seem to get the items to total when I am adding them to the last row. I'm stuck in the "else"" section of the code.

    for (int i = 0; i < dt.Columns.Count - 2; i++)
        {

            for (int j = 0; j < dt.Rows.Count - 1; j++)
            {
                string value = dt.Rows[i][j].ToString();
                int num = 0;
                bool res = int.TryParse(value, out num);
                if (res == false)
                {
                    num = 0;
                }
                else
                {
                    int sum = 0;

                    sum += num;
                    DataRow dr;
                   dr["Totals"] = sum;
                   dt.Rows.Add(dr);
                }
            }


            dataGridView1.DataSource = dt;
        }
    }

Upvotes: 1

Views: 1170

Answers (1)

BrianC
BrianC

Reputation: 314

One possibility is that there is a blank value in your data table - you can't cast a null object to an integer, so the Field extension method may be failing.

Upvotes: 1

Related Questions