在 Excel 中，在结束行下方动态显示 Datagridview 列总和的最佳方法? [英] Best way to show sum of Datagridview column sum dynamically just below the end row as in Excel?

问题描述

我知道这个问题被问了很多次，但我没有找到合适的答案:(

I know this question is asked many times but i didn't find suitable answer :(

我想在价格列的末尾显示例如总价的总和.

I would like to show sum of e.g.Total Price at the end of price column.

随着值动态变化(在程序内)，总和也应该动态变化.我无法添加自定义额外行，因为 DatagridViews 是数据绑定的.

As the values changes dynamically(within program) sum also should be changed dynamically. I cannot add custom extra row as DatagridViews are databound.

因为有动态行和许多 Datagridviews 放置在表格布局中.(自定义绘制事件不可取，滚动条有时也会出现)

As there are dynamic rows and many Datagridviews placed inside Table Layout. (It is not desirable to custom paint event as scroll bar also appears sometimes)

我完全迷路了.有人可以建议更好的方法吗?

I am totally lost. Could anyone suggest the better approach ?

推荐答案

如果您从 DataTable 获取 DataGridView，我已经创建了以下解决方案我曾经遇到过这个问题.*

If you are sourcing your DataGridView from a DataTable, I've already created the following solution when I once encountered this issue.*

这个想法是指出哪些列需要求和，哪些列和文本将指示总计"标签，然后取底层 DataTable 并手动对指示的列求和.这些总和和标签用于创建一个新的 DataRow，并将其添加到表的末尾.进行任何更改时 - 以编程方式添加新行、删除行、排序 - 删除旧的 sum 行，然后计算新行.

The idea is to indicate which columns require summing, which column and text will indicate the "Total" label, then take the underlying DataTable and manually sum the indicated columns. These sums and the label are used to create a new DataRow, which is added to the end of the table. When any changes are made - programmatic adding of a new row, deleting a row, sorting - the old sum row is removed and a new one is calculated then added.

DataGridView 类

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.Data;
using System.Windows.Forms;

namespace TestSortWithSum
{
  public class DataTableSumSortableDGV : DataGridView
  {
    /// <summary>
    /// Column index for the sum label.
    /// </summary>
    private int labelColumnIndex = -1;

    /// <summary>
    /// Text for the sum label.
    /// </summary>
    private string labelColumnText = string.Empty;

    /// <summary>
    /// Constructor. Initialize sort direction and subscribe event.
    /// </summary>
    public DataTableSumSortableDGV()
      : base()
    {
      this.SumColumnIndices = new ObservableCollection<int>();
      this.Direction = string.Empty;
      this.AllowUserToAddRows = false;
      this.AllowUserToAddRowsChanged += DataTableSumSortableDGV_AllowUserToAddRowsChanged;
      this.DataBindingComplete += DataTableSumSortableDGV_DataBindingComplete;
      this.DataSourceChanged += DataTableSumSortableDGV_DataSourceChanged;
      this.SumColumnIndices.CollectionChanged += SumColumnIndices_CollectionChanged;
    }

    /// <summary>
    /// Text for the sum label.
    /// </summary>
    public string LabelColumnText
    {
      get
      {
        return this.labelColumnText;
      }

      set
      {
        Action action = () =>
          {
            if (this.HasSumColumns())
            {
              this.RemoveSumRow();
            }

            this.labelColumnText = value;

            if (this.HasSumColumns())
            {
              this.AddSumRow();
            }
          };

        this.MakeInternalChanges(action);
      }
    }

    /// <summary>
    /// Column index for the sum label.
    /// </summary>
    public int LabelColumnIndex
    {
      get
      {
        return this.labelColumnIndex;
      }

      set
      {
        Action action = () =>
          {
            if (this.HasSumColumns())
            {
              this.RemoveSumRow();
            }

            this.labelColumnIndex = value;

            if (this.HasSumColumns())
            {
              this.AddSumRow();
            }
          };

        this.MakeInternalChanges(action);
      }
    }

    /// <summary>
    /// Column indices for the sum(s).
    /// </summary>
    public ObservableCollection<int> SumColumnIndices { get; set; }

    /// <summary>
    /// The DataTable sort direction.
    /// </summary>
    private string Direction { get; set; }

    /// <summary>
    /// The DataTable source.
    /// </summary>
    private DataTable DataTable { get; set; }

    /// <summary>
    /// The DataTable sum row.
    /// </summary>
    private DataRow SumRow { get; set; }

    /// <summary>
    /// DataGridView Sort method.
    /// If DataSource is DataTable, special sort the source.
    /// Else normal sort.
    /// </summary>
    /// <param name="dataGridViewColumn">The DataGridViewColumn to sort by header click.</param>
    /// <param name="direction">The desired sort direction.</param>
    public override void Sort(DataGridViewColumn dataGridViewColumn, System.ComponentModel.ListSortDirection direction)
    {
      if (this.HasSumColumns())
      {
        Action action = () =>
        {
          this.RemoveSumRow();

          string col = this.DataTable.Columns[dataGridViewColumn.Index].ColumnName;

          if (!this.Direction.Contains(col))
          {
            this.ClearOldSort();
          }

          string sort = this.Direction.Contains("ASC") ? "DESC" : "ASC";
          this.Direction = string.Format("{0} {1}", col, sort);

          this.SortRows(this.Direction);
          this.AddSumRow();
        };

        this.MakeInternalChanges(action);
        dataGridViewColumn.HeaderCell.SortGlyphDirection = this.Direction.Contains("ASC") ? SortOrder.Ascending : SortOrder.Descending;
      }
      else
      {
        this.DataTable.DefaultView.Sort = string.Empty;
        base.Sort(dataGridViewColumn, direction);
      }
    }

    /// <summary>
    /// DataBindingComplete event handler.
    /// Add the sum row when DataSource = a new DataTable.
    /// </summary>
    /// <param name="sender">This DataGridView object.</param>
    /// <param name="e">The DataGridViewBindingCompleteEventArgs.</param>
    private void DataTableSumSortableDGV_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
    {
      this.DataTable = (DataTable)this.DataSource;
      this.AddInitialSumRow();
    }

    /// <summary>
    /// For a new DataSource, start with a fresh SumRow.
    /// </summary>
    /// <param name="sender">The DataGridView object.</param>
    /// <param name="e">The EventArgs.</param>
    void DataTableSumSortableDGV_DataSourceChanged(object sender, EventArgs e)
    {
      this.SumRow = null;
    }

    /// <summary>
    /// Prevent users from adding a row as this is DataSourced and rows should be added to the DataTable instead.
    /// </summary>
    /// <param name="sender">The DataGridView object.</param>
    /// <param name="e">The EventArgs.</param>
    private void DataTableSumSortableDGV_AllowUserToAddRowsChanged(object sender, EventArgs e)
    {
      if (this.AllowUserToAddRows)
      {
        this.AllowUserToAddRows = false;
      }
    }

    /// <summary>
    /// The sum columns have been altered. Reflect the change immediately.
    /// </summary>
    /// <param name="sender">The SumColumnIndices object.</param>
    /// <param name="e">The NotifyCollectionChangedEventArgs.</param>
    private void SumColumnIndices_CollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
    {
      this.AddInitialSumRow();
    }

    /// <summary>
    /// Add the sum row for the first time: once the DataTable is sourced and
    /// the label column index, label text, and sum column index are set.
    /// </summary>
    private void AddInitialSumRow()
    {
      if (this.HasSumColumns())
      {
        Action action = () =>
        {
          this.RemoveSumRow();
          this.AddSumRow();
        };

        this.MakeInternalChanges(action);
      }
    }

    /// <summary>
    /// Add the sum row to the DataTable as a ReadOnly row.
    /// </summary>
    private void AddSumRow()
    {
      List<decimal> sum = this.CreateListOfSums();
      List<int> exclude = new List<int>();

      for (int row = 0; row < this.DataTable.Rows.Count; row++)
      {
        for (int index = 0; index < this.SumColumnIndices.Count; index++)
        {
          try
          {
            int col = this.SumColumnIndices[index];
            decimal value = 0;

            if (Decimal.TryParse(this.DataTable.Rows[row].ItemArray[col].ToString(), out value))
            {
              sum[index] += value;
            }
            else if (!exclude.Contains(col))
            {
              exclude.Add(col);
            }
          }
          catch (RowNotInTableException)
          {
            continue;
          }
        }
      }

      object[] items = this.CreateItemsArray(this.DataTable.Columns.Count, sum, exclude);

      if (Array.TrueForAll<object>(items, item => { return item == null; }))
      {
        this.SumRow = null;
      }
      else
      {
        this.SumRow = this.DataTable.NewRow();
        this.SumRow.ItemArray = items;
        this.DataTable.Rows.Add(this.SumRow);

        if (this.Rows.Count > 0)
        {
          this.Rows[this.Rows.Count - 1].ReadOnly = true;
        } 
      }
    }

    /// <summary>
    /// Clear the old sort string and any set glyph directions in header cells.
    /// </summary>
    private void ClearOldSort()
    {
      if (!string.IsNullOrEmpty(this.Direction))
      {
        string[] sortVals = this.Direction.Split(new char[] { ' ' }); // [ "ColName", "ASC/DESC" ]
        this.Columns[sortVals[0]].HeaderCell.SortGlyphDirection = SortOrder.None;
      }

      this.Direction = string.Empty;
    }

    /// <summary>
    /// Create the items array for the new sum row.
    /// </summary>
    /// <param name="length">The array length for the items.</param>
    /// <param name="sum">The list of sums.</param>
    /// <param name="exclude">The list of sum columns that aren't actually sum columns.</param>
    /// <returns>Object array for the sum row.</returns>
    private object[] CreateItemsArray(int length, List<decimal> sum, List<int> exclude)
    {
      object[] items = new object[length];

      if (this.IsValidIndex())
      {
        items[this.LabelColumnIndex] = this.LabelColumnText;
      }

      for (int index = 0; index < this.SumColumnIndices.Count; index++)
      {
        int col = this.SumColumnIndices[index];

        if (!exclude.Contains(col))
        {
          items[col] = sum[index];
        }
      }
      return items;
    }

    /// <summary>
    /// Create a list of sums for each sum column index.
    /// </summary>
    /// <returns>A new list of sums.</returns>
    private List<decimal> CreateListOfSums()
    {
      List<decimal> sum = new List<decimal>();

      foreach (int index in this.SumColumnIndices)
      {
        sum.Add(0m);
      }

      return sum;
    }

    /// <summary>
    /// Determine if the index is a valid column for the label.
    /// </summary>
    /// <returns>True if the index is valid.</returns>
    private bool IsValidIndex()
    {
      return
        this.LabelColumnIndex >= 0 &&
        this.LabelColumnIndex < this.DataTable.Columns.Count &&
        this.DataTable.Columns[this.LabelColumnIndex].DataType == typeof(string);
    }

    /// <summary>
    /// Unsubscribe the DataBindingComplete event handler, call internal sorting changes,
    /// then re-subscribe to the DataBindingComplete event handler. This must be done
    /// with any item removal/addition to the DataSource DataTable to prevent recursion
    /// resulting in a Stack Overflow.
    /// </summary>
    /// <param name="operation">The internal changes to be made to the DataSource.</param>
    private void MakeInternalChanges(Action operation)
    {
      this.DataBindingComplete -= DataTableSumSortableDGV_DataBindingComplete;
      operation();
      this.DataBindingComplete += DataTableSumSortableDGV_DataBindingComplete;
    }

    /// <summary>
    /// Remove any existing sum row.
    /// </summary>
    private void RemoveSumRow()
    {
      if (this.SumRow != null)
      {
        this.DataTable.Rows.Remove(this.SumRow);
      }
    }

    /// <summary>
    /// Determine if the grid has sum sortable columns.
    /// </summary>
    /// <returns>
    /// True if the source and sum column(s) exist.
    /// False if any one condition fails = sort as normal DataGridView.
    /// </returns>
    private bool HasSumColumns()
    {
      return this.DataTable != null && this.SumColumnIndices.Count > 0;
    }

    /// <summary>
    /// Sort the DataTable by re-ordering the actual items.
    /// Get the sorted row order. For each sorted row,
    /// remove it from the original list, then re-add it to the end.
    /// </summary>
    /// <param name="sort">The "ColumnName ASC/DESC" sort string.</param>
    private void SortRows(string sort)
    {
      DataRow[] sortedRows = this.DataTable.Select(string.Empty, sort);

      foreach (DataRow row in sortedRows)
      {
        object[] items = (object[])row.ItemArray.Clone();
        this.DataTable.Rows.Remove(row);
        this.DataTable.Rows.Add(items);
      }
    }
  }
}

使用

只需用此类替换您的 DataGridView 实例:

Simply replace your instance of the DataGridView with this class:

DataTableSumSortableDGV dataGridView1 = new DataTableSumSortableDGV();

然后，像这样指出您的总和列和标签列:

Then, indicate your sum columns and label column like so:

this.dataGridView1.DataSource = GoGetTheDataTable();
this.dataGridView1.SumColumnIndices.Add(3);
this.dataGridView1.SumColumnIndices.Add(4);
this.dataGridView1.LabelColumnIndex = 2;
this.dataGridView1.LabelColumnText = "Total";

以上几行的顺序无关紧要，它应该可以工作.下面我捕获了一个演示实际行为的示例:

Order of the above lines doesn't matter, it should work. Below I captured an example demonstrating the behaviors in action:

_{* 或者你当然可以只添加一个 TextBox，但我个人不喜欢这种方法的外观.}

_{* Or you could of course just add a TextBox, but I personally didn't like the look of that approach.}

这篇关于在 Excel 中，在结束行下方动态显示 Datagridview 列总和的最佳方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！