在FindValue（...）方法在数据表中搜索的对应x轴和y轴值的Z轴值。xAxisField是X轴字段的列名（例如“Product”），而xAxisValue是在该列的值。该yAxisField是的Y轴字段的列名（例如“Sales Person”），并yAxisValue是在该列的值。该zAxisField是列名，在其中Z轴值，是您正在寻找地（例如“Sale Amount”）。

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轴上使用流行的样式（table的顶行）。如果您没有指定一个CSS类名给这个属性，该方法将使用默认的样式。 CSS类将会被应用到网页中的HTML table。

/// <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轴，而高级的，不止一个。

出处：朱祁林
责任编辑：bluehearts

上一页 ASP.NET实现类似Excel的数据透视表 [2] 下一页 ASP.NET实现类似Excel的数据透视表 [4]

◎进入论坛网络编程版块参加讨论