显示的DataGridView列动态总和刚刚结束的行下方的总和在Excel中的最佳方式？ [英] 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 :(

我想在年底显示egTotal价格的总和价格列。

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 ?

推荐答案

如果您正在输出的 DataGridView的从数据表，我已经创建的时候我曾经遇到过这个问题下面的解决方案。*

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

我们的想法是指示哪些列需要求和，这柱和文字会显示总计标签，然后采取底层数据表和手动综上所述指定列。这些总和和标签被用来创建一个新的DataRow，这是添加到该表的末尾。当任何变更时 - 程序添加一个新行，删除行，排序 - 古老的之行的被删除，一个新的计算方法，加入

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);
      }
    }
  }
}

< STRONG>用法

只需使用这个类取代你的 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:

的

<子> *或者你当然可以只需添加文本框，但我个人不喜欢这种做法的外观。

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

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