透视 DataTable 简化
4.92/5 (126投票s)
一个用于透视具有各种聚合函数的 DataTable 的类。
- 下载 PivotScript - 3.4 KB
- 下载演示 - 411.8 KB
- 下载 pivot_vb - 2 KB
- 下载 pivotdatatable_vb - 15.1 KB
- 下载 pivot - 1.6 KB
引言
如今，以表格形式显示数据是任何应用程序的基本组成部分。但有时，您需要显示大量行数的数据。如果行数很多，将很难进行分析。在这种情况下，您可能希望将数据汇总为其他格式，如图表、图形、组、透视表等。本文介绍了一种使用适当的聚合函数对数据进行透视的简化方法，以便您可以轻松地增强报表。根据各位读者的反馈，为 pivot 类提供了更多功能。pivot 类现在能够一次在两个轴上进行数据透视。此外，它还提供了按列进行小计的功能。
下面是 GridView 中透视数据的屏幕截图
工作原理
为简化场景，我将 result 表分为三个区域：RowField、DataField 和 ColumnFields。如果您希望在两个轴上进行透视，可以使用同一个方法的另一个重载，只需将 RowFields 参数作为数组传递即可。除了区域之外，Pivot 类还提供了根据某些聚合函数绑定数据的选项。可用的各种聚合选项包括：
Count：返回匹配数据的计数Sum：返回匹配数据的总和（要获取总和，DataField的类型必须可转换为十进制类型）First：返回匹配数据的第一个出现Last：返回匹配数据的最后一个出现Average：返回匹配数据的平均值（要获取平均值，DataField的类型必须可转换为十进制类型）Max：返回匹配数据中的最大值Min：返回匹配数据中的最小值Exists：如果存在匹配数据，则返回“true”，否则返回“false”
代码主要包含一个名为“Pivot”的类，该类在构造函数中接受 DataTable。ColumnFields 作为 string 数组参数，允许您对多个列进行数据透视。它包含一个名为 PivotData() 的函数，该函数实际执行数据透视。
public DataTable PivotData(string RowField, string DataField,
AggregateFunction Aggregate, params string[] ColumnFields)
{
DataTable dt = new DataTable();
string Separator = ".";
var RowList = (from x in _SourceTable.AsEnumerable()
select new { Name = x.Field<object>(RowField) }).Distinct();
var ColList = (from x in _SourceTable.AsEnumerable()
select new { Name = ColumnFields.Select(n => x.Field<object>(n))
.Aggregate((a, b) => a += Separator + b.ToString()) })
.Distinct()
.OrderBy(m => m.Name);
dt.Columns.Add(RowField);
foreach (var col in ColList)
{
dt.Columns.Add(col.Name.ToString());
}
foreach (var RowName in RowList)
{
DataRow row = dt.NewRow();
row[RowField] = RowName.Name.ToString();
foreach (var col in ColList)
{
string strFilter = RowField + " = '" + RowName.Name + "'";
string[] strColValues =
col.Name.ToString().Split(Separator.ToCharArray(),
StringSplitOptions.None);
for (int i = 0; i < ColumnFields.Length; i++)
strFilter += " and " + ColumnFields[i] +
" = '" + strColValues[i] + "'";
row[col.Name.ToString()] = GetData(strFilter, DataField, Aggregate);
}
dt.Rows.Add(row);
}
return dt;
}
PivotData 方法还有 2 个重载。如果您希望显示列小计，可以使用通过传递 bool 变量 showSubTotal 的重载。如果您希望在两个方向上进行数据透视，即行方向和列方向，可以使用另一个重载，其中您可以将 rowFields 和 columnFields 作为数组传递。
首先，该函数通过获取 RowList 中的唯一值来确定行数，并通过获取 ColList 中的唯一值来确定列数。然后，创建列。接着，它遍历每一行，并根据提供的聚合函数获取与相应单元格匹配的值。为了检索匹配值，会调用 GetData() 函数。
private object GetData(string Filter, string DataField, AggregateFunction Aggregate)
{
try
{
DataRow[] FilteredRows = _SourceTable.Select(Filter);
object[] objList =
FilteredRows.Select(x => x.Field<object>(DataField)).ToArray();
switch (Aggregate)
{
case AggregateFunction.Average:
return GetAverage(objList);
case AggregateFunction.Count:
return objList.Count();
case AggregateFunction.Exists:
return (objList.Count() == 0) ? "False" : "True";
case AggregateFunction.First:
return GetFirst(objList);
case AggregateFunction.Last:
return GetLast(objList);
case AggregateFunction.Max:
return GetMax(objList);
case AggregateFunction.Min:
return GetMin(objList);
case AggregateFunction.Sum:
return GetSum(objList);
default:
return null;
}
}
catch (Exception ex)
{
return "#Error";
}
return null;
}
此函数首先在 DataRow[] 数组中筛选出匹配的 RowField 和 ColumnFields 数据，然后对其应用聚合函数。
Using the Code
使用代码很简单。创建一个 Pivot 类的实例，然后使用所需的参数调用 PivotData 方法。PivotData() 方法返回的 DataTable 可以直接用作 GridView 的 DataSource。
DataTable dt = ExcelLayer.GetDataTable("_Data\\DataForPivot.xls", "Sheet1$");
Pivot pvt = new Pivot(dt);
grdPivot.DataSource = pvt.PivotData("Designation", "CTC",
AggregateFunction.Max, "Company", "Department", "Year");
grdPivot.DataBind();
用作示例的数据库是一个 Excel 工作表，位于示例应用程序根文件夹的“_Data”文件夹中。
合并 GridView 标题单元格
创建了 MergeHeader 函数来合并标题单元格，以提供更简化的外观。
private void MergeHeader(GridView gv, GridViewRow row, int PivotLevel)
{
for (int iCount = 1; iCount <= PivotLevel; iCount++)
{
GridViewRow oGridViewRow = new GridViewRow(0, 0,
DataControlRowType.Header, DataControlRowState.Insert);
var Header = (row.Cells.Cast<tablecell>()
.Select(x => GetHeaderText(x.Text, iCount, PivotLevel)))
.GroupBy(x => x);
foreach (var v in Header)
{
TableHeaderCell cell = new TableHeaderCell();
cell.Text = v.Key.Substring(v.Key.LastIndexOf(_Separator) + 1);
cell.ColumnSpan = v.Count();
oGridViewRow.Cells.Add(cell);
}
gv.Controls[0].Controls.AddAt(row.RowIndex, oGridViewRow);
}
row.Visible = false;
}
该函数为每个透视级别创建一个新行并进行相应合并。这里的 PivotLevel 是进行透视的列数。
Header 获取所有列值到一个数组中，对 GetHeaderText() 函数返回的重复值进行分组，根据重复的 HeaderText 的数量设置新创建单元格的 ColumnSpan 属性，然后将单元格添加到 GridViewRow。最后，将 GridViewRow 添加到 GridView。
GetHeaderText() 函数根据 PivotLevel 返回标题文本。
例如，假设对三个 ColumnField 进行透视，即 Company、Department 和 Year。对于 PivotLevel 1，GridView 的结果标题最初将具有类似 Company.Department.Year 的标题。GetHeaderText() 将返回 Company。对于 PivotLevel 2，GetHeaderText() 将返回 Company.Department。对于 PivotLevel 3，GetHeaderText() 将返回 Company.Department.Year，依此类推...
合并 GridView 行标题单元格
当您也按行透视数据时，可能需要执行此操作。在这里，我们只是合并具有相同文本的单元格。
private void MergeRows(GridView gv, int rowPivotLevel)
{
for (int rowIndex = gv.Rows.Count - 2; rowIndex >= 0; rowIndex--)
{
GridViewRow row = gv.Rows[rowIndex];
GridViewRow prevRow = gv.Rows[rowIndex + 1];
for (int colIndex = 0; colIndex < rowPivotLevel; colIndex++)
{
if (row.Cells[colIndex].Text == prevRow.Cells[colIndex].Text)
{
row.Cells[colIndex].RowSpan = (prevRow.Cells[colIndex].RowSpan < 2) ?
2 : prevRow.Cells[colIndex].RowSpan + 1;
prevRow.Cells[colIndex].Visible = false;
}
}
}
}
合并标题行的代码非常简单。它只是从下到上遍历所有行标题单元格，将文本与前一个相应的行单元格进行比较，如果相同则将行跨度增加 1，并隐藏前一个相应的行。
双向透视的屏幕截图
下面是包含第三级透视数据的 GridView 的屏幕截图
关注点
除了透视 DataTable 之外，该代码还可以帮助您以所需的格式合并 GridView 中的标题单元格。此外，您可以更深入地了解 PivotData 方法，以了解如何使用 Linq 在 DataTable 中搜索或过滤数据。除此之外，MergeRows 方法可以作为合并 GridView 中行的示例。对于初学者，ExcelLayer.GetDataTable() 方法将是 从 Excel 工作表中获取数据的示例。
根据许多读者的要求，我没有提供从数据库查询数据的示例。您可以找到附加的 SQL 脚本来创建 SQL Server 数据库表以及从 SQL Server 读取数据到 DataTable 的代码。
您还可以参考以下链接来透视 DataTable：C# Pivot Table。
未来考量
目前，该代码只能透视 DataTable 的数据。该代码将得到增强，以透视从 IListSource 或 ICollection 派生的任何对象。
历史
- 2009 年 12 月 9 日：发布第一个版本
- 2010 年 3 月 19 日：添加了 VB.NET 源代码和演示