时间戳

首页 > 解决方案 > How do I Add Sum of DataGridView Rows of Same Date as New "Total" rows at the end of rows of same date?

c# - How do I Add Sum of DataGridView Rows of Same Date as New "Total" rows at the end of rows of same date?

问题描述

I have a DataGridView to display Data from DataTable "dtGrid". dtGrid has two Columns as "MyDate" and "Amount" ,Rows are like these,

dtGrid.Rows.Add(new object[] { "2020-09-14", 100 });
dtGrid.Rows.Add(new object[] { "2020-09-14", 150 });
dtGrid.Rows.Add(new object[] { "2020-09-14", 110 });
dtGrid.Rows.Add(new object[] { "2020-09-16", 200 });
dtGrid.Rows.Add(new object[] { "2020-09-16", 170 });

Now, I need to populate the DataGridView as

2020-09-14 |100
2020-09-14 |150
2020-09-14 |110
Total|360
2020-09-16 |200
2020-09-16 |170
Total|370

I have tried to check next row date of dtGrid in loop like,

for (int i = 0; i < dtGrid.Rows.Count; i++)
        {
            if (i + 1 < dtGrid.Rows.Count && dtGrid.Rows[i][0] != dtGrid.Rows[i + 1][0])
            {
                GridReport.Rows.Add(dtGrid.Rows[i][0], dtGrid.Rows[i][1]);
                var dtFiltered = dtGrid.AsEnumerable()
                        .Where(x => x.Field<DateTime>("MyDate").ToString().Equals(dtGrid.Rows[i][0].ToString()))
                        .CopyToDataTable();
                var Total = dtFiltered.AsEnumerable()
                         .Sum(x => x.Field<double>("Amount"));

                GridReport.Rows.Add("Total:", Total);
            }
            else if(i + 1 < dtGrid.Rows.Count && dtGrid.Rows[i][0] == dtGrid.Rows[i + 1][0])
            {
                GridReport.Rows.Add(dtGrid.Rows[i][0], dtGrid.Rows[i][1]);
            }
            else if (i + 1 == dtGrid.Rows.Count)
            {
                GridReport.Rows.Add(dtGrid.Rows[i][0], dtGrid.Rows[i][1]);
                var dtFiltered = dtGrid.AsEnumerable()
                        .Where(x => x.Field<DateTime>("MyDate").ToString().Equals(dtGrid.Rows[i][0].ToString()))
                        .CopyToDataTable();
                var Total = dtFiltered.AsEnumerable()
                         .Sum(x => x.Field<double>("Amount"));

                GridReport.Rows.Add("Total:", Total);
            }
        }

It just doesn't work. Any suggestion would be greatly appreciated.

标签： c#winformsdatagridview

解决方案

尝试以下解决方案

dtGrid = new DataTable();
dtGrid.Columns.Add("MyDate", typeof(string));
dtGrid.Columns.Add("Amount", typeof(decimal));

dtGrid.Rows.Add(new object[] { "2020-09-14", 100 });
dtGrid.Rows.Add(new object[] { "2020-09-14", 150 });
dtGrid.Rows.Add(new object[] { "2020-09-14", 110 });
dtGrid.Rows.Add(new object[] { "2020-09-16", 200 });
dtGrid.Rows.Add(new object[] { "2020-09-16", 170 });

var rows = dtGrid.AsEnumerable()
    .GroupBy(row => row.Field<string>("MyDate"), row => row.Field<decimal>("Amount"))
    .Select(g => new { Date = g.Key, Amounts = string.Join(", ", g), Total = g.Sum() });

var dt = new DataTable();
dt.Columns.Add("Date", typeof(string));
dt.Columns.Add("Amounts", typeof(string));
dt.Columns.Add("Total", typeof(decimal));

foreach (var row in rows)
    dt.Rows.Add(row.Date, row.Amounts, row.Total);

GridReport.DataSource = dt;

它将给出以下结果

Date       | Amounts       | Total
2020-09-14 | 100, 150, 110 | 360
2020-04-16 | 200, 170      | 370

推荐阅读