C# 数据透视表
4.87/5 (40投票s)
本文介绍了如何读取 DataTable 并根据提供的列名返回反转或数据透视表。
引言
数据透视转换对于总结平面数据表（列和行）中的数据非常有用，可以更清晰地可视化数据。
在本文中，我们将看到两种从 DataTable 中检索数据透视表和反转表的方法。这些方法非常有用，特别适用于那些使用元数据从数据库中检索信息，或那些无法使用 SQL Server 2005 中的数据透视转换的人。
这两种方法是独立的，但以某种方式互补，可以操作数据以返回反转表或更复杂的数据透视表。
数据透视方法
第一种 - 简单反转
读取所有数据，并将所有列作为行返回，并将行作为列返回。
提供的 X 轴列用作列标题，如果需要，可以在此过程中忽略某些列。
/// <summary>
/// Gets a Inverted DataTable
/// </summary>
/// <param name="table">DataTable do invert</param>
/// <param name="columnX">X Axis Column</param>
/// <param name="nullValue">null Value to Complete the Pivot Table</param>
/// <param name="columnsToIgnore">Columns that should be ignored in the pivot
/// process (X Axis column is ignored by default)</param>
/// <returns>C# Pivot Table Method - Felipe Sabino</returns>
public static DataTable GetInversedDataTable(DataTable table, string columnX,
params string[] columnsToIgnore)
{
//Create a DataTable to Return
DataTable returnTable = new DataTable();
if (columnX == "")
columnX = table.Columns[0].ColumnName;
//Add a Column at the beginning of the table
returnTable.Columns.Add(columnX);
//Read all DISTINCT values from columnX Column in the provided DataTale
List<string> columnXValues = new List<string>();
//Creates list of columns to ignore
List<string> listColumnsToIgnore = new List<string>();
if (columnsToIgnore.Length > 0)
listColumnsToIgnore.AddRange(columnsToIgnore);
if (!listColumnsToIgnore.Contains(columnX))
listColumnsToIgnore.Add(columnX);
foreach (DataRow dr in table.Rows)
{
string columnXTemp = dr[columnX].ToString();
//Verify if the value was already listed
if (!columnXValues.Contains(columnXTemp))
{
//if the value id different from others provided, add to the list of
//values and creates a new Column with its value.
columnXValues.Add(columnXTemp);
returnTable.Columns.Add(columnXTemp);
}
else
{
//Throw exception for a repeated value
throw new Exception("The inversion used must have " +
"unique values for column " + columnX);
}
}
//Add a line for each column of the DataTable
foreach (DataColumn dc in table.Columns)
{
if (!columnXValues.Contains(dc.ColumnName) &&
!listColumnsToIgnore.Contains(dc.ColumnName))
{
DataRow dr = returnTable.NewRow();
dr[0] = dc.ColumnName;
returnTable.Rows.Add(dr);
}
}
//Complete the datatable with the values
for (int i = 0; i < returnTable.Rows.Count; i++)
{
for (int j = 1; j < returnTable.Columns.Count; j++)
{
returnTable.Rows[i][j] =
table.Rows[j - 1][returnTable.Rows[i][0].ToString()].ToString();
}
}
return returnTable;
}
第二种 - 数据透视
它使用三个轴的思想来构建新表。 X 轴列是具有唯一值的列，用于构建列标题。 Y 轴值是包含值的列，这些值将显示为第一列中的行。 Z 轴是“值”，并且是 DataSource 中 X 和 Y 的匹配项，如果在该过程中找到多个不同的值，则可以是值的总和。 如果需要用某个值填充表的空单元格，则提供 null 值。
求和值的标志用于在某个 X 和 Y 列组合有多个值的情况下；如果为“false”，则显示读取的最后一个值。
/// <summary>
/// Gets a Inverted DataTable
/// </summary>
/// <param name="table">Provided DataTable</param>
/// <param name="columnX">X Axis Column</param>
/// <param name="columnY">Y Axis Column</param>
/// <param name="columnZ">Z Axis Column (values)</param>
/// <param name="columnsToIgnore">Whether to ignore some column, it must be
/// provided here</param>
/// <param name="nullValue">null Values to be filled</param>
/// <returns>C# Pivot Table Method - Felipe Sabino</returns>
public static DataTable GetInversedDataTable(DataTable table, string columnX,
string columnY, string columnZ, string nullValue, bool sumValues)
{
//Create a DataTable to Return
DataTable returnTable = new DataTable();
if (columnX == "")
columnX = table.Columns[0].ColumnName;
//Add a Column at the beginning of the table
returnTable.Columns.Add(columnY);
//Read all DISTINCT values from columnX Column in the provided DataTale
List<string> columnXValues = new List<string>();
foreach (DataRow dr in table.Rows)
{
string columnXTemp = dr[columnX].ToString();
if (!columnXValues.Contains(columnXTemp))
{
//Read each row value, if it's different from others provided, add to
//the list of values and creates a new Column with its value.
columnXValues.Add(columnXTemp);
returnTable.Columns.Add(columnXTemp);
}
}
//Verify if Y and Z Axis columns re provided
if (columnY != "" && columnZ != "")
{
//Read DISTINCT Values for Y Axis Column
List<string> columnYValues = new List<string>();
foreach (DataRow dr in table.Rows)
{
if (!columnYValues.Contains(dr[columnY].ToString()))
columnYValues.Add(dr[columnY].ToString());
}
//Loop all Column Y Distinct Value
foreach (string columnYValue in columnYValues)
{
//Creates a new Row
DataRow drReturn = returnTable.NewRow();
drReturn[0] = columnYValue;
//foreach column Y value, The rows are selected distincted
DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");
//Read each row to fill the DataTable
foreach (DataRow dr in rows)
{
string rowColumnTitle = dr[columnX].ToString();
//Read each column to fill the DataTable
foreach (DataColumn dc in returnTable.Columns)
{
if (dc.ColumnName == rowColumnTitle)
{
//If Sum of Values is True it try to perform a Sum
//If sum is not possible due to value types, the value
// displayed is the last one read
if (sumValues)
{
try
{
drReturn[rowColumnTitle] =
Convert.ToDecimal(drReturn[rowColumnTitle]) +
Convert.ToDecimal(dr[columnZ]);
}
catch
{
drReturn[rowColumnTitle] = dr[columnZ];
}
}
else
{
drReturn[rowColumnTitle] = dr[columnZ];
}
}
}
}
returnTable.Rows.Add(drReturn);
}
}
else
{
throw new Exception("The columns to perform inversion are not provided");
}
//if a nullValue is provided, fill the datable with it
if (nullValue != "")
{
foreach (DataRow dr in returnTable.Rows)
{
foreach (DataColumn dc in returnTable.Columns)
{
if (dr[dc.ColumnName].ToString() == "")
dr[dc.ColumnName] = nullValue;
}
}
}
return returnTable;
}
Using the Code
本文提供了两种执行数据透视转换的方法。
在这两种方法中，下表将用作数据源的示例
| EmployeeID (员工编号) | OrderID | 金额 | 成本 | 日期 |
Sam | 1 | 25 | 13 | 01/10/2007 |
Sam | 2 | 512 | 1 | 02/10/2007 |
Sam | 3 | 512 | 1 | 03/10/2007 |
Tom | 4 | 50 | 1 | 04/10/2007 |
Tom | 5 | 3 | 7 | 03/10/2007 |
Tom | 6 | 78,75 | 12 | 02/10/2007 |
Sue | 7 | 11 | 7 | 01/10/2007 |
Sue | 8 | 2,5 | 66,2 | 02/10/2007 |
Sue | 9 | 2,5 | 22 | 03/10/2007 |
Jack | 10 | 6 | 23 | 02/10/2007 |
Jack | 11 | 117 | 199 | 04/10/2007 |
Jack | 12 | 13 | 2,6 | 01/10/2007 |
Jack | 13 | 11,4 | 99,8 | 03/10/2007 |
Phill | 14 | 37 | 2,1 | 02/10/2007 |
Phill | 15 | 65,2 | 99,3 | 04/10/2007 |
Phill | 16 | 34,1 | 27 | 02/10/2007 |
Phill | 17 | 17 | 959 | 04/10/2007 |
下面的代码展示了如何创建上面的表格
DataTable dt = new DataTable();
dt.Columns.Add("EmployeeID", Type.GetType("System.String"));
dt.Columns.Add("OrderID", Type.GetType("System.Int32"));
dt.Columns.Add("Amount", Type.GetType("System.Decimal"));
dt.Columns.Add("Cost", Type.GetType("System.Decimal"));
dt.Columns.Add("Date", Type.GetType("System.String"));
dt.Rows.Add(new object[] { "Sam", 1, 25.00, 13.00, "01/10/2007" });
dt.Rows.Add(new object[] { "Sam", 2, 512.00, 1.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Sam", 3, 512.00, 1.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Tom", 4, 50.00, 1.00, "04/10/2007" });
dt.Rows.Add(new object[] { "Tom", 5, 3.00, 7.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Tom", 6, 78.75, 12.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Sue", 7, 11.00, 7.00, "01/10/2007" });
dt.Rows.Add(new object[] { "Sue", 8, 2.50, 66.20, "02/10/2007" });
dt.Rows.Add(new object[] { "Sue", 9, 2.50, 22.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Jack", 10, 6.00, 23.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Jack", 11, 117.00, 199.00, "04/10/2007" });
dt.Rows.Add(new object[] { "Jack", 12, 13.00, 2.60, "01/10/2007" });
dt.Rows.Add(new object[] { "Jack", 13, 11.40, 99.80, "03/10/2007" });
dt.Rows.Add(new object[] { "Phill", 14, 37.00, 2.10, "02/10/2007" });
dt.Rows.Add(new object[] { "Phill", 15, 65.20, 99.30, "04/10/2007" });
dt.Rows.Add(new object[] { "Phill", 16, 34.10, 27.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Phill", 17, 17.00, 959.00, "04/10/2007" });
第一种 - 简单反转
提供了一个列，并且 DataTable 的“轴已转动”。此方法可以忽略反转过程中的某些列，以便返回的表仅包含感兴趣的列。如果我们要以“OrderID”列作为标题（X 轴列）进行反转，我们应该使用下面的代码
//For example, for the DataTable provided as Datasource
DataTable dtReturn = GetInversedDataTable(dt, "OrderID");
返回的表将如下所示
第二种 - 数据透视表
提供了三列，并返回一个新的 DataTable。
下面的示例将使用源表和下面的参数来构建数据透视表。
- X 轴列：“
Date” - Y 轴列：“
EmployeeID” - Z 轴列：“
Cost” - 空值：“
-”； - 值总和：
true
//For example, for the DataTable provided as Datasource
DataTable dtReturn = GetInversedDataTable(dt, "Date", "EmployeeID",
"Cost", "-", true);
返回的表将如下所示
| EmployeeID (员工编号) | 01/12/2007 | 02/12/2007 | 03/12/2007 | 04/12/2007 |
Sam | 13 | 1 | 1 | - |
Tom | - | 12 | 7 | 1 |
Sue | 7 | 66,2 | 22 | - |
Jack | 2,6 | 23 | 99,8 | 199 |
Phill | - | 27 | - | 959 |
关注点
您可以阅读更多关于数据透视表的信息
- http://en.wikipedia.org/wiki/Pivot_table
- http://books.slashdot.org/article.pl?sid=04/11/19/2012256
- http://j-walk.com/ss/excel/usertips/tip068.htm
历史
- 2007/12/10 - 首次发布