使用 C# 和 ASP.NET 创建简单和高级透视表

Nitesh Maharaj

4.89/5 (33投票s)

2009 年 1 月 29 日

CPOL

5分钟阅读

150525

4443

如何在 C# 和 ASP.NET 中创建简单和高级透视表

下载 Pivot.zip - 27.76 KB

引言

透视表提供数据的三维视图。Microsoft Excel 具有创建透视表的功能，但使用 Excel 并不总是方便。例如，您可能希望在 Web 应用程序中创建透视报表。创建简单的透视表可能是一项复杂的任务。因此，我的目的是不仅为您提供一个用于创建简单和高级透视报表的优秀工具，而且还揭开它们神秘的面纱。

目标

我们希望能够将二维数据表转换成三维（透视）报表。

数据表

在大多数情况下，您将从数据库查询中填充数据表，如下所示：

SELECT 
    SalesPeople.FullName AS [Sales Person] 
    , Products.FullName AS [Product] 
    , SUM(Sales.SalesAmount) AS [Sale Amount] 
    , SUM(Sales.Qty) AS [Quantity] 
FROM 
    Sales 
JOIN 
    SalesPeople WITH (NOLOCK) 
    ON SalesPeople.SalesPersonID = Sales.SalesPersonID 
JOIN 
    Products WITH (NOLOCK) 
    ON Products.ProductCode = Sales.ProductCode 
GROUP BY 
    SalesPeople.FullName 
    , Products.FullName

查询将生成如下数据表：

销售人员	产品	数量	销售金额
John	笔	200	350
John	铅笔	400	500
John	笔记本	100	300
John	尺子	50	100
John	计算器	120	1200
John	背包	75	1500
Jane	笔	225	393.75
Jane	铅笔	335	418.75
Jane	笔记本	200	600
Jane	尺子	75	150
Jane	计算器	80	800
Jane	背包	97	1940
Sally	笔	202	353.5
Sally	铅笔	303	378.75
Sally	笔记本	198	600
Sally	尺子	98	594
Sally	计算器	80	800
Sally	背包	101	2020
Sarah	笔	112	196
Sarah	铅笔	245	306.25
Sarah	笔记本	198	594
Sarah	尺子	50	100
Sarah	计算器	66	660
Sarah	背包	50	2020

您可以看到，这是一个二维表，作为报表并不是很有用。所以我们必须将这个数据表转换成更具可读性的东西。

透视结构

透视表有三个维度。

x 轴构成了表格顶部的标题。y 轴构成了表格的左列，z 轴构成了对应于 x 轴和 y 轴的值。简单的透视表对于每个 x 轴值都有一个 z 轴列，而高级透视表对于每个 x 轴值有多个 z 轴列。

一个非常重要的点是 z 轴值只能是数字。这是因为 z 轴值是按水平和垂直方向合计的。使用非数字字段作为 z 轴字段将引发异常。

所以，如果您查看上面的数据表，您会注意到“销售人员”和“产品”字段可以分配给 x 轴或 y 轴，但不能分配给 z 轴。“数量”和“销售金额”字段可以分配给 z 轴。

Pivot 类

Pivot 类将数据表转换为 HTML 表，然后您可以将其添加到 Web 窗体中。这只是一种实现方式。如果您愿意，可以创建一个包含此类逻辑的用户控件。

#region Variables 
    private DataTable _DataTable; 
    private string _CssTopHeading; 
    private string _CssSubHeading; 
    private string _CssLeftColumn; 
    private string _CssItems; 
    private string _CssTotals; 
    private string _CssTable; 
#endregion Variables 
#region Constructors 
public Pivot(DataTable dataTable) 
{ 
    Init(); 
    _DataTable = dataTable; 
} 
#endregion Constructors

这部分代码非常直观。您将通过将数据表作为参数传递来构造一个 Pivot 对象。Init() 方法只是将一个空字符串值赋给 CSS 变量。如果 CSS 变量是空字符串，样式方法将使用默认样式。每个 CSS 变量都有一个相应的属性。

private string FindValue(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string zAxisField) 
{ 
    string zAxisValue = ""; 
    try 
    { 
        foreach (DataRow row in _DataTable.Rows) 
        { 
            if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue) 
            { 
                zAxisValue = Convert.ToString(row[zAxisField]); 
                break; 
            } 
        } 
    } 
    catch 
    { 
        throw; 
    } 
    return zAxisValue; 
}

FindValue(...) 方法会搜索数据表，以查找对应于 x 轴和 y 轴值的 z 轴值。xAxisField 是 x 轴字段的列名（例如“产品”），xAxisValue 是该列中的一个值。yAxisField 是 y 轴字段的列名（例如“销售人员”），yAxisValue 是该列中的一个值。zAxisField 是您要查找的 z 轴值所在的列的名称（例如“销售金额”）。

private string[] FindValues(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string[] zAxisFields) 
{ 
    int zAxis = zAxisFields.Length; 
    if (zAxis < 1) 
        zAxis++; 
    string[] zAxisValues = new string[zAxis]; 
    //set default values 
    for (int i = 0; i <= zAxisValues.GetUpperBound(0); i++) 
    { 
        zAxisValues[i] = "0"; 
    } 
    try 
    { 
        foreach (DataRow row in _DataTable.Rows) 
        { 
            if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue) 
            { 
                for (int z = 0; z < zAxis; z++) 
                { 
                    zAxisValues[z] = Convert.ToString(row[zAxisFields[z]]); 
                } 
                break; 
            } 
        } 
    } 
    catch 
    { 
        throw; 
    } 
    return zAxisValues; 
}

FindValues(...) 方法与 FindValue(...) 方法类似，但是它会返回多个 z 轴值。这用于高级透视表，其中一个 x 轴值对应多个 z 轴字段。

private void MainHeaderTopCellStyle(HtmlTableCell cell) 
{ 
    if (_CssTopHeading == "") 
    { 
        cell.Style.Add("font-family", "tahoma"); 
        cell.Style.Add("font-size", "10pt"); 
        cell.Style.Add("font-weight", "normal"); 
        cell.Style.Add("background-color", "black"); 
        cell.Style.Add("color", "white"); 
        cell.Style.Add("text-align", "center"); 
    } 
    else 
        cell.Attributes.Add("Class", _CssTopHeading); 
}

这是 CSS 样式方法之一。此方法特别用于样式化 x 轴（表格顶部的行）。如果您不为属性分配 CSS 类名，则该方法将使用默认样式。CSS 类将从您将 HTML 表添加到哪个页面中获取。

/// <summary> 
/// Creates an advanced 3D Pivot table. 
/// </summary> 
/// <param name="xAxisField">The main heading at the top of the report.</param> 
/// <param name="yAxisField">The heading on the left of the report.</param> 
/// <param name="zAxisFields">The sub heading at the top of the report.</param> 
/// <returns>HtmlTable Control.</returns> 
public HtmlTable PivotTable(string xAxisField, string yAxisField, string[] zAxisFields) 
{ 
    HtmlTable table = new HtmlTable(); 
    //style table 
    TableStyle(table); 
    /* 
    * The x-axis is the main horizontal row. 
    * The z-axis is the sub horizontal row. 
    * The y-axis is the left vertical column. 
    */ 
    try 
    { 
        //get distinct xAxisFields 
        ArrayList xAxis = new ArrayList(); 
        foreach (DataRow row in _DataTable.Rows) 
        { 
            if (!xAxis.Contains(row[xAxisField])) 
                xAxis.Add(row[xAxisField]); 
        } 
        //get distinct yAxisFields 
        ArrayList yAxis = new ArrayList(); 
        foreach (DataRow row in _DataTable.Rows) 
        { 
            if (!yAxis.Contains(row[yAxisField])) 
                yAxis.Add(row[yAxisField]); 
        } 
        //create a 2D array for the y-axis/z-axis fields 
        int zAxis = zAxisFields.Length; 
        if (zAxis < 1) 
            zAxis = 1; 
        string[,] matrix = new string[(xAxis.Count * zAxis), yAxis.Count]; 
        string[] zAxisValues = new string[zAxis]; 
        for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis fields 
        { 
            //rows 
            for (int x = 0; x < xAxis.Count; x++) //loop thru x-axis fields 
            { 
                //main columns 
                //get the z-axis values 
                zAxisValues = FindValues(xAxisField, Convert.ToString(xAxis[x]) 
                    , yAxisField, Convert.ToString(yAxis[y]), zAxisFields); 
                for (int z = 0; z < zAxis; z++) //loop thru z-axis fields 
                { 
                    //sub columns 
                    matrix[(((x + 1) * zAxis - zAxis) + z), y] = zAxisValues[z]; 
                } 
            } 
        } 
        //calculate totals for the y-axis 
        decimal[] yTotals = new decimal[(xAxis.Count * zAxis)]; 
        for (int col = 0; col < (xAxis.Count * zAxis); col++) 
        { 
            yTotals[col] = 0; 
            for (int row = 0; row < yAxis.Count; row++) 
            { 
                yTotals[col] += Convert.ToDecimal(matrix[col, row]); 
            } 
        } 
        //calculate totals for the x-axis 
        decimal[,] xTotals = new decimal[zAxis, (yAxis.Count + 1)]; 
        for (int y = 0; y < yAxis.Count; y++) //loop thru the y-axis 
        { 
            int zCount = 0; 
            for (int z = 0; z < (zAxis * xAxis.Count); z++) //loop thru the z-axis 
            { 
                xTotals[zCount, y] += Convert.ToDecimal(matrix[z, y]); 
                if (zCount == (zAxis - 1)) 
                    zCount = 0; 
                else 
                    zCount++; 
            } 
        } 
        for (int xx = 0; xx < zAxis; xx++) //Grand Total 
        { 
            for (int xy = 0; xy < yAxis.Count; xy++) 
            { 
                xTotals[xx, yAxis.Count] += xTotals[xx, xy]; 
            } 
        } 
        //Build HTML Table 
        //Append main row (x-axis) 
        HtmlTableRow mainRow = new HtmlTableRow(); 
        mainRow.Cells.Add(new HtmlTableCell()); 
        for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1 
        { 
            HtmlTableCell cell = new HtmlTableCell(); 
            cell.ColSpan = zAxis; 
            if (x < xAxis.Count) 
                cell.InnerText = Convert.ToString(xAxis[x]); 
            else 
                cell.InnerText = "Grand Totals"; 
            //style cell 
            MainHeaderTopCellStyle(cell); 
            mainRow.Cells.Add(cell); 
        } 
        table.Rows.Add(mainRow); 
        //Append sub row (z-axis) 
        HtmlTableRow subRow = new HtmlTableRow(); 
        subRow.Cells.Add(new HtmlTableCell()); 
        subRow.Cells[0].InnerText = yAxisField; 
        //style cell 
        SubHeaderCellStyle(subRow.Cells[0]); 
        for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1 
        { 
            for (int z = 0; z < zAxis; z++) 
            { 
                HtmlTableCell cell = new HtmlTableCell(); 
                cell.InnerText = zAxisFields[z]; 
                //style cell 
                SubHeaderCellStyle(cell); 
                subRow.Cells.Add(cell); 
            } 
        } 
        table.Rows.Add(subRow); 
        //Append table items from matrix 
        for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis 
        { 
            HtmlTableRow itemRow = new HtmlTableRow(); 
            for (int z = 0 ; z <= (zAxis * xAxis.Count); z++) //loop thru z-axis + 1 
            { 
                HtmlTableCell cell = new HtmlTableCell(); 
                if (z == 0) 
                { 
                    cell.InnerText = Convert.ToString(yAxis[y]); 
                    //style cell 
                    MainHeaderLeftCellStyle(cell); 
                } 
                else 
                { 
                    cell.InnerText = Convert.ToString(matrix[(z-1), y]); 
                    //style cell 
                    ItemCellStyle(cell); 
                } 
                itemRow.Cells.Add(cell); 
            } 
            //append x-axis grand totals 
            for (int z = 0; z < zAxis; z++) 
            { 
                HtmlTableCell cell = new HtmlTableCell(); 
                cell.InnerText = Convert.ToString(xTotals[z, y]); 
                //style cell 
                TotalCellStyle(cell); 
                itemRow.Cells.Add(cell); 
            } 
            table.Rows.Add(itemRow); 
        } 
        //append y-axis totals 
        HtmlTableRow totalRow = new HtmlTableRow(); 
        for (int x = 0; x <= (zAxis * xAxis.Count); x++) 
        { 
            HtmlTableCell cell = new HtmlTableCell(); 
            if (x == 0) 
                cell.InnerText = "Totals"; 
            else 
                cell.InnerText = Convert.ToString(yTotals[x-1]); 
            //style cell 
            TotalCellStyle(cell); 
            totalRow.Cells.Add(cell); 
        } 
        //append x-axis/y-axis totals 
        for (int z = 0; z < zAxis; z++) 
        { 
            HtmlTableCell cell = new HtmlTableCell(); 
            cell.InnerText = Convert.ToString(xTotals[z, xTotals.GetUpperBound(1)]); 
            //style cell 
            TotalCellStyle(cell); 
            totalRow.Cells.Add(cell); 
        } 
        table.Rows.Add(totalRow); 
    } 
    catch 
    { 
        throw; 
    } 
    return table; 
}

PivotTable(...) 方法是所有魔术发生的地方。此方法有两个重载；一个创建简单透视表，另一个（上面显示的）创建高级透视表。唯一的区别是简单透视表有一个 z 轴字段，而高级透视表有多个。

使用 Pivot 类

我已将两个解决方案包含在 Pivot.zip 文件中。Pivot 解决方案是一个类库。您可以编译该解决方案并在您的 Web 应用程序中引用 Pivot.dll。另一个名为 PivotTest 的解决方案是一个 ASP.NET 应用程序。它演示了如何实现 Pivot 类。

public DataTable DataTableForTesting 
{ 
    get 
    { 
        DataTable dt = new DataTable("Sales Table"); 
        dt.Columns.Add("Sales Person"); 
        dt.Columns.Add("Product"); 
        dt.Columns.Add("Quantity"); 
        dt.Columns.Add("Sale Amount"); 
        dt.Rows.Add(new object[] { "John", "Pens", 200, 350.00 }); 
        dt.Rows.Add(new object[] { "John", "Pencils", 400, 500.00 }); 
        dt.Rows.Add(new object[] { "John", "Notebooks", 100, 300.00 }); 
        dt.Rows.Add(new object[] { "John", "Rulers", 50, 100.00 }); 
        dt.Rows.Add(new object[] { "John", "Calculators", 120, 1200.00 }); 
        dt.Rows.Add(new object[] { "John", "Back Packs", 75, 1500.00 }); 
        dt.Rows.Add(new object[] { "Jane", "Pens", 225, 393.75 }); 
        dt.Rows.Add(new object[] { "Jane", "Pencils", 335, 418.75 }); 
        dt.Rows.Add(new object[] { "Jane", "Notebooks", 200, 600.00 }); 
        dt.Rows.Add(new object[] { "Jane", "Rulers", 75, 150.00 }); 
        dt.Rows.Add(new object[] { "Jane", "Calculators", 80, 800.00 }); 
        dt.Rows.Add(new object[] { "Jane", "Back Packs", 97, 1940.00 }); 
        dt.Rows.Add(new object[] { "Sally", "Pens", 202, 353.50 }); 
        dt.Rows.Add(new object[] { "Sally", "Pencils", 303, 378.75 }); 
        dt.Rows.Add(new object[] { "Sally", "Notebooks", 198, 600.00 }); 
        dt.Rows.Add(new object[] { "Sally", "Rulers", 98, 594.00 }); 
        dt.Rows.Add(new object[] { "Sally", "Calculators", 80, 800.00 }); 
        dt.Rows.Add(new object[] { "Sally", "Back Packs", 101, 2020.00 }); 
        dt.Rows.Add(new object[] { "Sarah", "Pens", 112, 196.00 }); 
        dt.Rows.Add(new object[] { "Sarah", "Pencils", 245, 306.25 }); 
        dt.Rows.Add(new object[] { "Sarah", "Notebooks", 198, 594.00 }); 
        dt.Rows.Add(new object[] { "Sarah", "Rulers", 50, 100.00 }); 
        dt.Rows.Add(new object[] { "Sarah", "Calculators", 66, 660.00 }); 
        dt.Rows.Add(new object[] { "Sarah", "Back Packs", 50, 2020.00 }); 
        return dt; 
    } 
}

我创建了这个数据表属性，它构建了上面示例中的数据表。这仅用于演示目的。

protected void Page_Load(object sender, EventArgs e) 
{ 
    //Advanced Pivot 
    Pivot advPivot = new Pivot(DataTableForTesting); 
    HtmlTable advancedPivot = advPivot.PivotTable("Sales Person", "Product", new string[] { "Sale Amount", "Quantity" }); 
    div1.Controls.Add(advancedPivot); 
    //Simple Pivot 
    Pivot pivot = new Pivot(DataTableForTesting); 
    //override default style with css 
    pivot.CssTopHeading = "Heading"; 
    pivot.CssLeftColumn = "LeftColumn"; 
    pivot.CssItems = "Items"; 
    pivot.CssTotals = "Totals"; 
    pivot.CssTable = "Table"; 
    HtmlTable simplePivot = pivot.PivotTable("Product", "Sales Person", "Sale Amount"); 
    div2.Controls.Add(simplePivot); 
}

上面的代码实例化了两个透视对象。第一个是用于高级透视表，第二个是用于简单透视表。您可以看到我已经将 HtmlTable 控件添加到了 div 中。我创建了带有 runat="server" 属性的 div，以便我可以在代码中访问它。div 仅用于帮助放置 HtmlTable。

具有默认样式的复杂透视表。

	John		Jane		Sally		Sarah		总计
产品	销售金额	数量	销售金额	数量	销售金额	数量	销售金额	数量	销售金额	数量
笔	350	200	393.75	225	353.5	202	196	112	1293.25	739
铅笔	500	400	418.75	335	378.75	303	306.25	245	1603.75	1283
笔记本	300	100	600	200	600	198	594	198	2094	696
尺子	100	50	150	75	594	98	100	50	944	273
计算器	1200	120	800	80	800	80	660	66	3460	346
背包	1500	75	1940	97	2020	101	2020	50	7480	323
总计	3950	945	4302.50	1012	4746.25	982	3876.25	721	16875.00	3660

使用 CSS 进行自定义样式的简单透视表。

销售人员	笔	铅笔	笔记本	尺子	计算器	背包	总计
John	350	500	300	100	1200	1500	3950
Jane	393.75	418.75	600	150	800	1940	4302.50
Sally	353.5	378.75	600	594	800	2020	4746.25
Sarah	196	306.25	594	100	660	2020	3876.25
总计	1293.25	1603.75	2094	944	3460	7480	16875.00