JustSomeNewbie JustSomeNewbie - 3 months ago 57
C# Question

Updating SUM when cell value change in DataTable

I'm having some problem with my c# code.
What I'm trying to do is to add new row to my dataTable and then calculate some things and insert into specific cells.

Here is what I'v got :

object sumAmount;

object sumDest;

object sumWhole;

DataRow row = dataTable.NewRow();
row["number"] = "1";
dataTable.Rows.Add(row);

sumAmount = dataTable.Compute("Sum(amount)", "");

dataTable.Rows[dataTable.Rows.Count - 1][all] = Int32.Parse(sumAmount.ToString());

sumDest = dataTable.Compute("Sum(dest)", "");

dataTable.Rows[dataTable.Rows.Count - 1][columnNumber] = Int32.Parse(sumDest.ToString());

sumWhole = dataTable.Compute("Sum(wholeAmount)", "");

dataTable.Rows[dataTable.Rows.Count - 1][allTogether] = Int32.Parse(sumWhole.ToString());


It works correctly for the first time.
But when i change some cell value then I'm recalculating this with this same code (part with dataTable =) and when I'm doing it the cell value isnt replaced, this added.

Example:

When I had this :

10 3 13
6 1 7
2 0 2
18 4 22 << This is SUM


And I insert 1 in some cell in 2nd column im expecting this :

10 3 13
6 1 7
2 1 3
18 5 23


But what im getting is :

10 3 13
6 1 7
2 1 3
38 9 45


As you can see these values just being added, but I don't get it why?
Can someone help me finding bug in this code?

Answer

Its not a spreadsheet, so it doesnt know to exclude the last row from the formula. It looks like the 3rd column is also a Total? If so, that can be an expression which the DataTable maintains:

dtXYZ = new DataTable();
dtXYZ.Columns.Add("X", typeof(Int32));
dtXYZ.Columns.Add("Y", typeof(Int32));
dtXYZ.Columns.Add("Z", typeof(Int32));
dtXYZ.Columns["Z"].Expression="X+Y";

The columns are typed so that X+Y adds. Add some data probably like you already are:

dtXYZ.Rows.Add(new object[]{10, 3});
dtXYZ.Rows.Add(new object[]{6, 1});
dtXYZ.Rows.Add(new object[]{2, 0});

dtXYZ.Rows.Add(dtXYZ.Compute("sum(X)", ""),
            dtXYZ.Compute("sum(Y)", ""));

The last column does need to be computed, the Expression will take care of that. One way to use Compute and not include the grand total row, would be to remove and readd it each time:

int nRows = dtXYZ.Rows.Count - 1;
dtXYZ.Rows.RemoveAt(nRows);
dtXYZ.Rows.Add( dtXYZ.Compute("sum(X)", ""),
               dtXYZ.Compute("sum(Y)", ""));

But rather than doing that just to be able to use Compute, you can do the summing yourself with some extension methods and post the new value:

int nRows = dtXYZ.Rows.Count - 1;
dtXYZ.Rows[nRows ]["X"] = dtXYZ.AsEnumerable()
               .Take(nRows)
               .Sum(x => x.Field<int>("X"));

dtXYZ.Rows[nRows ]["Y"] = dtXYZ.AsEnumerable()
               .Take(nRows)
               .Sum(x => x.Field<int>("Y"));

enter image description here

Or even a loop would work. Either way, remember you dont have to fiddle with the last column if it is based on an Expression - in fact you cant.

Comments