在Excel中动态显示Datagridview列总和的最佳方式是在结尾行的下方？ [英] Best way to show sum of Datagridview column sum dynamically just below the end row as in Excel?
本文介绍了在Excel中动态显示Datagridview列总和的最佳方式是在结尾行的下方？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我知道这个问题被问了很多次，但是我没有找到合适的答案：（
我想在...结束时显示egTotal Price的总和价格栏
随着值动态变化（程序内）sum也应该动态更改，因为DatagridViews是数据绑定，我无法添加自定义额外的行。
由于表格布局中放置了动态行和许多Datagridview（有时候，滚动条也会出现，自定义绘画事件是不可取的）
/ p>
解决方案
如果您正在采购您的 DataGridView 从 DataTable ，当我遇到此问题时，我已经创建了以下解决方案。*
这个想法是指出哪些列需要求和，哪个列和文本将指示总计标签，然后将底层的 DataTable 和man对指定的列进行总和。这些和和标签用于创建一个新的DataRow，它被添加到表的末尾。当进行任何更改时 - 删除一行，编排新行，删除行，排序 - 旧的和行将被删除，然后添加一个新行。
DataGridView类
using System;
使用System.Collections.Generic;
使用System.Collections.ObjectModel;
使用System.Collections.Specialized;
使用System.Data;
使用System.Windows.Forms;
命名空间TestSortWithSum
{
public class DataTableSumSortableDGV：DataGridView
{
///< summary>
///总和标签的列索引。
///< / summary>
private int labelColumnIndex = -1;
///< summary>
///总和标签的文本。
///< / summary>
private string labelColumnText = string.Empty;
///< summary>
///构造函数。初始化排序方向和订阅事件。
///< / 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>
///总和标签的文本。
///< / 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>
///总和标签的列索引。
///< / 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>
///总和的列索引。
///< / summary>
public ObservableCollection< int> SumColumnIndices {get;组; }
///< summary>
/// DataTable排序方向。
///< / summary>
private string Direction {get;组; }
///< summary>
///数据表源。
///< / summary>
private DataTable DataTable {get;组; }
///< summary>
/// DataTable和行。
///< / summary>
private DataRow SumRow {get;组; }
///< summary>
/// DataGridView Sort方法。
///如果DataSource是DataTable，则对源进行特殊排序。
///其他正常排序。
///< / summary>
///< param name =dataGridViewColumn>要按标题排序的DataGridViewColumn单击。< / param>
///< param name =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事件处理程序。
///在DataSource =新数据表时添加和行。
///< / summary>
///< param name =sender>此DataGridView对象。< / param>
///< param name =e> DataGridViewBindingCompleteEventArgs。< / param>
private void DataTableSumSortableDGV_DataBindingComplete（object sender，DataGridViewBindingCompleteEventArgs e）
{
this.DataTable =（DataTable）this.DataSource;
this.AddInitialSumRow（）;
}
///< summary>
///对于新的DataSource，从一个新的SumRow开始。
///< / summary>
///< param name =sender> DataGridView对象< / param>
///< param name =e> EventArgs。< / param>
void DataTableSumSortableDGV_DataSourceChanged（object sender，EventArgs e）
{
this.SumRow = null;
}
///< summary>
///阻止用户添加一行，因为这是DataSourced，行应该添加到DataTable中。
///< / summary>
///< param name =sender> DataGridView对象< / param>
///< param name =e> EventArgs。< / param>
private void DataTableSumSortableDGV_AllowUserToAddRowsChanged（object sender，EventArgs e）
{
if（this.AllowUserToAddRows）
{
this.AllowUserToAddRows = false;
}
}
///< summary>
///总和列已更改。立即反映变化。
///< / summary>
///< param name =sender> SumColumnIndices对象< / param>
///< param name =e> NotifyCollectionChangedEventArgs。< / param>
private void SumColumnIndices_CollectionChanged（object sender，NotifyCollectionChangedEventArgs e）
{
this.AddInitialSumRow（）;
}
///< summary>
///第一次添加和行：一旦DataTable来源，并且
///标签列索引，标签文本和和列索引被设置。
///< / summary>
private void AddInitialSumRow（）
{
if（this.HasSumColumns（））
{
Action action =（）=>
{
this.RemoveSumRow（）;
this.AddSumRow（）;
};
this.MakeInternalChanges（action）;
}
}
///< summary>
///将和行作为ReadOnly行添加到DataTable。
///< / summary>
private void AddSumRow（）
{
列表< decimal> sum = this.CreateListOfSums（）;
列表< int> exclude = new List< int>（）;对于（int row = 0; row< this.DataTable.Rows.Count; row ++）
{
for（int index = 0; index< this.SumColumnIndices .Count; index ++）
{
try
{
int col = this.SumColumnIndices [index];
十进制值= 0;
if（Decimal.TryParse（this.DataTable.Rows [row] .ItemArray [col] .ToString（），out value）
{
sum [index] + =值;
}
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>
///清除标题单元格中的旧排序字符串和任何设置的字形方向。
///< / 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>
///为新的和行创建items数组。
///< / summary>
///< param name =length>项的数组长度< / param>
///< param name =sum>总和列表< / param>
///< param name =exclude>实际上不是sum列的sum列的列表。< / param>
///< returns>总和行的对象数组。< / 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];
如果（！exclude.Contains（col））
{
items [col] = sum [index];
}
}
返回项目;
}
///< summary>
///为每个sum列索引创建一个和的列表。
///< / summary>
///< returns>一个新的和列表。< / returns>
私人列表< decimal> CreateListOfSums（）
{
列表< decimal> sum = new List< decimal>（）;
foreach（this.SumColumnIndices中的int index）
{
sum.Add（0m）;
}
返还金额;
}
///< summary>
///确定索引是否是标签的有效列。
///< / summary>
///< returns>如果索引有效，则为真。< / returns>
private bool IsValidIndex（）
{
return
this.LabelColumnIndex> = 0&&
this.LabelColumnIndex< this.DataTable.Columns.Count&&
this.DataTable.Columns [this.LabelColumnIndex] .DataType == typeof（string）;
}
///< summary>
///取消订阅DataBindingComplete事件处理程序，调用内部排序更改，
///然后重新订阅DataBindingComplete事件处理程序。这必须完成
///与DataSource DataTable的任何项目删除/添加，以防止递归
///导致堆栈溢出。
///< / summary>
///< param name =operation>要对DataSource进行的内部更改。< / param>
private void MakeInternalChanges（Action操作）
{
this.DataBindingComplete - = DataTableSumSortableDGV_DataBindingComplete;
operation（）;
this.DataBindingComplete + = DataTableSumSortableDGV_DataBindingComplete;
}
///< summary>
///删除任何现有的和行。
///< / summary>
private void RemoveSumRow（）
{
if（this.SumRow！= null）
{
this.DataTable.Rows.Remove（this.SumRow）;
}
}
///< summary>
///确定网格是否有可排序的列。
///< / summary>
///< returns>
///如果源和和列存在，则为True。
///如果任何一个条件失败，则为False =按照正常DataGridView排序。
///< / returns>
private bool HasSumColumns（）
{
return this.DataTable！= null&& this.SumColumnIndices.Count> 0;
}
///< summary>
///通过重新排序实际项目对DataTable进行排序。
///获取排序的行顺序。对于每个排序的行，
///将其从原始列表中删除，然后重新添加到最后。
///< / summary>
///< param name =sort>ColumnName ASC / DESC排序字符串< / param>
private void SortRows（string sort）
{
DataRow [] sortedRows = this.DataTable.Select（string.Empty，sort）;
foreach（SortRows中的DataRow行）
{
object [] items =（object []）row.ItemArray.Clone（）;
this.DataTable.Rows.Remove（row）;
this.DataTable.Rows.Add（items）;
}
}
}
}
strong>用法
只需将您的 DataGridView 的实例替换为此类：
DataTableSumSortableDGV dataGridView1 = new DataTableSumSortableDGV（）;
然后，如下所示，指示您的总额列和标签列：
this.dataGridView1.DataSource = GoGetTheDataTable（）;
this.dataGridView1.SumColumnIndices.Add（3）;
this.dataGridView1.SumColumnIndices.Add（4）;
this.dataGridView1.LabelColumnIndex = 2;
this.dataGridView1.LabelColumnText =Total;
以上行的顺序并不重要，它应该可以工作。下面我捕获了一个演示行为行为的例子：
*或者你当然可以添加一个 TextBox ，但是我个人并不喜欢这种方式。
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.
As the values changes dynamically(within program) sum also should be changed dynamically. I cannot add custom extra row as DatagridViews are databound.
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 ?
解决方案
If you are sourcing your DataGridView from a DataTable, I've already created the following solution when I once encountered this issue.*
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.
The DataGridView Class
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);
}
}
}
}
Usage
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.
这篇关于在Excel中动态显示Datagridview列总和的最佳方式是在结尾行的下方？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
