# 在DataTable中增加一条新的行，用于显示子总数。[英] Add a new row to DataTable for sub total

### 问题描述

```| Project Name | Source |   Source Amount
A         Abc            \$10000
B         Xyz            \$12990
C         Mnf            \$10000
A         Dqe            \$50200
B         Pgp            \$14000
A         Rsy            \$27000
```

，我需要按行分组，以在

```| Project Name | Source |   Source Amount
A         Abc            \$10000
A         Dqe            \$50200
A         Rsy            \$27000
Total of A                     \$87200
B         Xyz            \$12990
B         Pgp            \$14000
Total of B                     \$26990
C         Mnf            \$10000
Total of C                     \$10000
All Total                      \$124190
```

.

```   dt.AsEnumerable()
.GroupBy(d => d.Field<string>("Project Name"))
.Sum(d=>d.Field<string>("Source Amount"))
```

## 推荐答案

```    // Find distinct projects
var distinctProjects = (from r in dt.AsEnumerable()
select r["ProjectName"]).Distinct().OrderBy(project => project);

// Get all amounts to tally later for the All Total entry
var allEntries = from r in dt.AsEnumerable()
select r["SourceAmount"];

// Loop through distinct project list and add project subtotal row to table
foreach (var p in distinctProjects)
{
var amt = from r in dt.AsEnumerable()
where r["ProjectName"] == p
select r["SourceAmount"];

DataRow dr = dt.NewRow();
dr["ProjectName"] = p + " Total:";
dr["SourceAmount"] = amt.Sum(x => Convert.ToDecimal(x));
}

// Sort table so the sub totals fall under the project it belongs to
DataView dv = dt.DefaultView;
dv.Sort = "ProjectName ASC, Source ASC";
dt = dv.ToTable();

// Create and add the final total row
DataRow finalTotal = dt.NewRow();
finalTotal["ProjectName"] = "All Total:";
finalTotal["SourceAmount"] = allEntries.Sum(x => Convert.ToDecimal(x));

// Display correct results with message box
foreach (DataRow r in dt.Rows) {
MessageBox.Show(
r["ProjectName"].ToString() + "    " +
r["Source"].ToString() + "    " +
r["SourceAmount"].ToString()
);
}
```

```    //Build Data Table
DataTable dt = new DataTable();

DataRow rowA1 = dt.NewRow();
rowA1["ProjectName"] = "A";
rowA1["Source"] = "ABC";
rowA1["SourceAmount"] = "10000";
DataRow rowA2 = dt.NewRow();
rowA2["ProjectName"] = "A";
rowA2["Source"] = "Dqe";
rowA2["SourceAmount"] = "50200";
DataRow rowA3 = dt.NewRow();
rowA3["ProjectName"] = "A";
rowA3["Source"] = "Rsy";
rowA3["SourceAmount"] = "27000";
DataRow rowB1 = dt.NewRow();
rowB1["ProjectName"] = "B";
rowB1["Source"] = "Xyz";
rowB1["SourceAmount"] = "12990";
DataRow rowB2 = dt.NewRow();
rowB2["ProjectName"] = "B";
rowB2["Source"] = "Pgp";
rowB2["SourceAmount"] = "14000";
DataRow rowC1 = dt.NewRow();
rowC1["ProjectName"] = "C";
rowC1["Source"] = "Mnf";
rowC1["SourceAmount"] = "10000";
```

https://stackoverflow.com/a/a/3521137/1967692

### 问题描述

I have datatable like this

```| Project Name | Source |   Source Amount
A         Abc            \$10000
B         Xyz            \$12990
C         Mnf            \$10000
A         Dqe            \$50200
B         Pgp            \$14000
A         Rsy            \$27000
```

and I need to group by rows for sub total totals in between as

```| Project Name | Source |   Source Amount
A         Abc            \$10000
A         Dqe            \$50200
A         Rsy            \$27000
Total of A                     \$87200
B         Xyz            \$12990
B         Pgp            \$14000
Total of B                     \$26990
C         Mnf            \$10000
Total of C                     \$10000
All Total                      \$124190
```

I used groupBy and sum in lambda but couldnot figureout how to include a new row in the existing datatable from withing the lambda expression.

Code I was using like

```   dt.AsEnumerable()
.GroupBy(d => d.Field<string>("Project Name"))
.Sum(d=>d.Field<string>("Source Amount"))
```

## 推荐答案

Here is one way to do it:

```    // Find distinct projects
var distinctProjects = (from r in dt.AsEnumerable()
select r["ProjectName"]).Distinct().OrderBy(project => project);

// Get all amounts to tally later for the All Total entry
var allEntries = from r in dt.AsEnumerable()
select r["SourceAmount"];

// Loop through distinct project list and add project subtotal row to table
foreach (var p in distinctProjects)
{
var amt = from r in dt.AsEnumerable()
where r["ProjectName"] == p
select r["SourceAmount"];

DataRow dr = dt.NewRow();
dr["ProjectName"] = p + " Total:";
dr["SourceAmount"] = amt.Sum(x => Convert.ToDecimal(x));
}

// Sort table so the sub totals fall under the project it belongs to
DataView dv = dt.DefaultView;
dv.Sort = "ProjectName ASC, Source ASC";
dt = dv.ToTable();

// Create and add the final total row
DataRow finalTotal = dt.NewRow();
finalTotal["ProjectName"] = "All Total:";
finalTotal["SourceAmount"] = allEntries.Sum(x => Convert.ToDecimal(x));

// Display correct results with message box
foreach (DataRow r in dt.Rows) {
MessageBox.Show(
r["ProjectName"].ToString() + "    " +
r["Source"].ToString() + "    " +
r["SourceAmount"].ToString()
);
}
```

Code to build datatable:

```    //Build Data Table
DataTable dt = new DataTable();

DataRow rowA1 = dt.NewRow();
rowA1["ProjectName"] = "A";
rowA1["Source"] = "ABC";
rowA1["SourceAmount"] = "10000";
DataRow rowA2 = dt.NewRow();
rowA2["ProjectName"] = "A";
rowA2["Source"] = "Dqe";
rowA2["SourceAmount"] = "50200";
DataRow rowA3 = dt.NewRow();
rowA3["ProjectName"] = "A";
rowA3["Source"] = "Rsy";
rowA3["SourceAmount"] = "27000";
DataRow rowB1 = dt.NewRow();
rowB1["ProjectName"] = "B";
rowB1["Source"] = "Xyz";
rowB1["SourceAmount"] = "12990";
DataRow rowB2 = dt.NewRow();
rowB2["ProjectName"] = "B";
rowB2["Source"] = "Pgp";
rowB2["SourceAmount"] = "14000";
DataRow rowC1 = dt.NewRow();
rowC1["ProjectName"] = "C";
rowC1["Source"] = "Mnf";
rowC1["SourceAmount"] = "10000";