ASP.NET實現類似Excel的數據透視表

　　數據透視表提供的數據三維視圖效果在Microsoft Excel能創建數據透視表但是它並不會總是很方便使用Excel您可能希望在Web應用程序中創建一個數據透視報表創建一個簡單的數據透視表可能是一件非常復雜的任務所以我打算不但為你提供一個非常有用的工具創建簡單和高級的數據透視表而且為你移除一些籠罩他們的神秘面紗

　　目標是我們想要有能力將datatable中的二維的數據轉換成三維視圖

　　在大多數情況下你會從數據庫的查詢數據填充數據表例如

　　代碼

　　SELECT

　　SalesPeopleFullName AS [Sales Person]

　　 ProductsFullName AS [Product]

　　 SUM(SalesSalesAmount) AS [Sale Amount]

　　 SUM(SalesQty) AS [Quantity]

　　FROM

　　Sales

　　JOIN

　　SalesPeople WITH (NOLOCK)

　　ON SalesPeopleSalesPersonID = SalesSalesPersonID

　　JOIN

　　Products WITH (NOLOCK)

　　ON ProductsProductCode = SalesProductCode

　　GROUP BY

　　SalesPeopleFullName

　　 ProductsFullName

　　該查詢會產生下面的數據表

　　

　　Sales Person

　　Product

　　Quantity

　　Sale Amount

　　John

　　Pens

　　

　　

　　John

　　Pencils

　　

　　

　　John

　　Notebooks

　　

　　

　　John

　　Rulers

　　

　　

　　John

　　Calculators

　　

　　

　　John

　　Back Packs

　　

　　

　　Jane

　　Pens

　　

　　

　　Jane

　　Pencils

　　

　　

　　Jane

　　Notebooks

　　

　　

　　Jane

　　Rulers

　　

　　

　　Jane

　　Calculators

　　

　　

　　Jane

　　Back Packs

　　

　　

　　Sally

　　Pens

　　

　　

　　Sally

　　Pencils

　　

　　

　　Sally

　　Notebooks

　　

　　

　　Sally

　　Rulers

　　

　　

　　Sally

　　Calculators

　　

　　

　　Sally

　　Back Packs

　　

　　

　　Sarah

　　Pens

　　

　　

　　Sarah

　　Pencils

　　

　　

　　Sarah

　　Notebooks

　　

　　

　　Sarah

　　Rulers

　　

　　

　　Sarah

　　Calculators

　　

　　

　　Sarah

　　Back Packs

　　

　　

　　正如你所看到的這是一個二維表它不是一個非常有用的報表因此我們得改變將它變成更可讀的數據表

　　數據透視表有個面

　　X軸構成了在表格上方的大標題Y軸構成表的左欄Z軸構成了X軸和Y軸對應的值簡單的數據透視表將會對每一個x軸值都只有一個z軸列高級的數據透視表將對於每個X軸的值會對應有多個Z軸的值

　　一個非常重要的一點是Z軸的值只能是數字這是因為Z軸值為橫軸和縱軸的總額使用一個非數值Z軸字段將拋出一個異常

　　因此如果你注意上面的數據表你會發現Sales Person和Product字段可以分配到的X軸或Y軸但不能給z軸在Quantity和Sale Amount字段可以被分配到z軸

　　Pivot 類將數據表轉換成html table然後您可以將它輸出到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 對象通過傳遞一個datatable作為參數在init（）方法只分配一個空字符串值給CSS變量如果CSS的變量是一個空字符串構造方法將使用默認的樣式每一個CSS變量都有一個相應的屬性

　　代碼

　　private string FindValue(string xAxisField string xAxisValue string yAxisField string yAxisValue string zAxisField)

　　{

　　string zAxisValue = ;

　　try

　　{

　　foreach (DataRow row in _DataTableRows)

　　{

　　if (ConvertToString(row[xAxisField]) == xAxisValue && ConvertToString(row[yAxisField]) == yAxisValue)

　　{

　　zAxisValue = ConvertToString(row[zAxisField]);

　　break;

　　}

　　}

　　}

　　catch

　　{

　　throw;

　　}

　　return zAxisValue;

　　}

　　在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 = zAxisFieldsLength;

　　if (zAxis < )

　　zAxis++;

　　string[] zAxisValues = new string[zAxis];

　　//set default values

　　for (int i = ; i <= zAxisValuesGetUpperBound(); i++)

　　{

　　zAxisValues[i] = ;

　　}

　　try

　　{

　　foreach (DataRow row in _DataTableRows)

　　{

　　if (ConvertToString(row[xAxisField]) == xAxisValue && ConvertToString(row[yAxisField]) == yAxisValue)

　　{

　　for (int z = ; z < zAxis; z++)

　　{

　　zAxisValues[z] = ConvertToString(row[zAxisFields[z]]);

　　}

　　break;

　　}

　　}

　　}

　　catch

　　{

　　throw;

　　}

　　return zAxisValues;

　　}

　　在FindValues（）方法類似FindValue（）方法然而它會返回多個z軸的值這是用於高級的數據透視表對應於x軸的值您會有多個Z軸列

　　代碼

　　private void MainHeaderTopCellStyle(HtmlTableCell cell)

　　{

　　if (_CssTopHeading == )

　　{

　　cellStyleAdd(fontfamily tahoma);

　　cellStyleAdd(fontsize pt);

　　cellStyleAdd(fontweight normal);

　　cellStyleAdd(backgroundcolor black);

　　cellStyleAdd(color white);

　　cellStyleAdd(textalign center);

　　}

　　else

　　cellAttributesAdd(Class _CssTopHeading);

　　}

　　這是CSS樣式的方法之一這在X軸上使用流行的樣式（table的頂行）如果您沒有指定一個CSS類名給這個屬性該方法將使用默認的樣式 CSS類將會被應用到網頁中的HTML table

　　代碼

　　/// <summary>

　　/// Creates an advanced D 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 xaxis is the main horizontal row

　　* The zaxis is the sub horizontal row

　　* The yaxis is the left vertical column

　　*/

　　try

　　{

　　//get distinct xAxisFields

　　ArrayList xAxis = new ArrayList();

　　foreach (DataRow row in _DataTableRows)

　　{

　　if (!xAxisContains(row[xAxisField]))

　　xAxisAdd(row[xAxisField]);

　　}

　　//get distinct yAxisFields

　　ArrayList yAxis = new ArrayList();

　　foreach (DataRow row in _DataTableRows)

　　{

　　if (!yAxisContains(row[yAxisField]))

　　yAxisAdd(row[yAxisField]);

　　}

　　//create a D array for the yaxis/zaxis fields

　　int zAxis = zAxisFieldsLength;

　　if (zAxis < )

　　zAxis = ;

　　string[] matrix = new string[(xAxisCount * zAxis) yAxisCount];

　　string[] zAxisValues = new string[zAxis];

　　for (int y = ; y < yAxisCount; y++) //loop thru yaxis fields

　　{

　　//rows

　　for (int x = ; x < xAxisCount; x++) //loop thru xaxis fields

　　{

　　//main columns

　　//get the zaxis values

　　zAxisValues = FindValues(xAxisField ConvertToString(xAxis[x])

　　 yAxisField ConvertToString(yAxis[y]) zAxisFields);

　　for (int z = ; z < zAxis; z++) //loop thru zaxis fields

　　{

　　//sub columns

　　matrix[(((x + ) * zAxis zAxis) + z) y] = zAxisValues[z];

　　}

　　}

　　}

　　//calculate totals for the yaxis

　　decimal[] yTotals = new decimal[(xAxisCount * zAxis)];

　　for (int col = ; col < (xAxisCount * zAxis); col++)

　　{

　　yTotals[col] = ;

　　for (int row = ; row < yAxisCount; row++)

　　{

　　yTotals[col] += ConvertToDecimal(matrix[col row]);

　　}

　　}

　　//calculate totals for the xaxis

　　decimal[] xTotals = new decimal[zAxis (yAxisCount + )];

　　for (int y = ; y < yAxisCount; y++) //loop thru the yaxis

　　{

　　int zCount = ;

　　for (int z = ; z < (zAxis * xAxisCount); z++) //loop thru the zaxis

　　{

　　xTotals[zCount y] += ConvertToDecimal(matrix[z y]);

　　if (zCount == (zAxis ))

　　zCount = ;

　　else

　　zCount++;

　　}

　　}

　　for (int xx = ; xx < zAxis; xx++) //Grand Total

　　{

　　for (int xy = ; xy < yAxisCount; xy++)

　　{

　　xTotals[xx yAxisCount] += xTotals[xx xy];

　　}

　　}

　　//Build HTML Table

　　//Append main row (xaxis)

　　HtmlTableRow mainRow = new HtmlTableRow();

　　mainRowCellsAdd(new HtmlTableCell());

　　for (int x = ; x <= xAxisCount; x++) //loop thru xaxis +

　　{

　　HtmlTableCell cell = new HtmlTableCell();

　　cellColSpan = zAxis;

　　if (x < xAxisCount)

　　cellInnerText = ConvertToString(xAxis[x]);

　　else

　　cellInnerText = Grand Totals;

　　//style cell

　　MainHeaderTopCellStyle(cell);

　　mainRowCellsAdd(cell);

　　}

　　tableRowsAdd(mainRow);

　　//Append sub row (zaxis)

　　HtmlTableRow subRow = new HtmlTableRow();

　　subRowCellsAdd(new HtmlTableCell());

　　subRowCells[]InnerText = yAxisField;

　　//style cell

　　SubHeaderCellStyle(subRowCells[]);

　　for (int x = ; x <= xAxisCount; x++) //loop thru xaxis +

　　{

　　for (int z = ; z < zAxis; z++)

　　{

　　HtmlTableCell cell = new HtmlTableCell();

　　cellInnerText = zAxisFields[z];

　　//style cell

　　SubHeaderCellStyle(cell);

　　subRowCellsAdd(cell);

　　}

　　}

　　tableRowsAdd(subRow);

　　//Append table items from matrix

　　for (int y = ; y < yAxisCount; y++) //loop thru yaxis

　　{

　　HtmlTableRow itemRow = new HtmlTableRow();

　　for (int z = ; z <= (zAxis * xAxisCount); z++) //loop thru zaxis +

　　{

　　HtmlTableCell cell = new HtmlTableCell();

　　if (z == )

　　{

　　cellInnerText = ConvertToString(yAxis[y]);

　　//style cell

　　MainHeaderLeftCellStyle(cell);

　　}

　　else

　　{

　　cellInnerText = ConvertToString(matrix[(z) y]);

　　//style cell

　　ItemCellStyle(cell);

　　}

　　itemRowCellsAdd(cell);

　　}

　　//append xaxis grand totals

　　for (int z = ; z < zAxis; z++)

　　{

　　HtmlTableCell cell = new HtmlTableCell();

　　cellInnerText = ConvertToString(xTotals[z y]);

　　//style cell

　　TotalCellStyle(cell);

　　itemRowCellsAdd(cell);

　　}

　　tableRowsAdd(itemRow);

　　}

　　//append yaxis totals

　　HtmlTableRow totalRow = new HtmlTableRow();

　　for (int x = ; x <= (zAxis * xAxisCount); x++)

　　{

　　HtmlTableCell cell = new HtmlTableCell();

　　if (x == )

　　cellInnerText = Totals;

　　else

　　cellInnerText = ConvertToString(yTotals[x]);

　　//style cell

　　TotalCellStyle(cell);

　　totalRowCellsAdd(cell);

　　}

　　//append xaxis/yaxis totals

　　for (int z = ; z < zAxis; z++)

　　{

　　HtmlTableCell cell = new HtmlTableCell();

　　cellInnerText = ConvertToString(xTotals[z xTotalsGetUpperBound()]);

　　//style cell

　　TotalCellStyle(cell);

　　totalRowCellsAdd(cell);

　　}

　　tableRowsAdd(totalRow);

　　}

　　catch

　　{

　　throw;

　　}

　　return table;

　　}

　　PivotTable(…) 方法是所有神奇發生的地方有兩種重載方法一個創建了一個簡單的數據透視表而其他（上面的方法）創建一個高級的數據透視表唯一的區別在於一個簡單只有一個的z軸而高級的不止一個

　　Pivotzip文件中包括兩個解決方案Pivot 是一個類庫解決方案是您可以編譯此解決方案和在Web應用程序中引用Pivotdll另一個解決方案是PivotTest它是是一個ASPNET應用程序這說明如何實現Pivot類

　　代碼

　　public DataTable DataTableForTesting

　　{

　　get

　　{

　　DataTable dt = new DataTable(Sales Table);

　　dtColumnsAdd(Sales Person);

　　dtColumnsAdd(Product);

　　dtColumnsAdd(Quantity);

　　dtColumnsAdd(Sale Amount);

　　dtRowsAdd(new object[] { John Pens });

　　dtRowsAdd(new object[] { John Pencils });

　　dtRowsAdd(new object[] { John Notebooks });

　　dtRowsAdd(new object[] { John Rulers });

　　dtRowsAdd(new object[] { John Calculators });

　　dtRowsAdd(new object[] { John Back Packs });

　　dtRowsAdd(new object[] { Jane Pens });

　　dtRowsAdd(new object[] { Jane Pencils });

　　dtRowsAdd(new object[] { Jane Notebooks });

　　dtRowsAdd(new object[] { Jane Rulers });

　　dtRowsAdd(new object[] { Jane Calculators });

　　dtRowsAdd(new object[] { Jane Back Packs });

　　dtRowsAdd(new object[] { Sally Pens });

　　dtRowsAdd(new object[] { Sally Pencils });

　　dtRowsAdd(new object[] { Sally Notebooks });

　　dtRowsAdd(new object[] { Sally Rulers });

　　dtRowsAdd(new object[] { Sally Calculators });

　　dtRowsAdd(new object[] { Sally Back Packs });

　　dtRowsAdd(new object[] { Sarah Pens });

　　dtRowsAdd(new object[] { Sarah Pencils });

　　dtRowsAdd(new object[] { Sarah Notebooks });

　　dtRowsAdd(new object[] { Sarah Rulers });

　　dtRowsAdd(new object[] { Sarah Calculators });

　　dtRowsAdd(new object[] { Sarah Back Packs });

　　return dt;

　　}

　　}

　　我已創建數據表的屬性它建立在上面的例子中的數據表這只是用於演示目的

　　代碼

　　protected void Page_Load(object sender EventArgs e)

　　{

　　//Advanced Pivot

　　Pivot advPivot = new Pivot(DataTableForTesting);

　　HtmlTable advancedPivot = advPivotPivotTable(Sales Person Product new string[] { Sale Amount Quantity });

　　divControlsAdd(advancedPivot);

　　//Simple Pivot

　　Pivot pivot = new Pivot(DataTableForTesting);

　　//override default style with css

　　pivotCssTopHeading = Heading;

　　pivotCssLeftColumn = LeftColumn;

　　pivotCssItems = Items;

　　pivotCssTotals = Totals;

　　pivotCssTable = Table;

　　HtmlTable simplePivot = pivotPivotTable(Product Sales Person Sale Amount);

　　divControlsAdd(simplePivot);

　　}

　　上述代碼包括兩個實例化的pivot對象第一個高級的pivot和第二是一個簡單的pivot你可以看到我已經為div添加了HtmlTable控件我創建具有runat=server屬性的div這樣我可以在後台代碼裡面訪問它div只是幫助HtmlTable的定位

　　使用默認樣式的高級的數據透視表

　　 John Jane Sally Sarah Grand Totals Product Sale Amount Quantity Sale Amount Quantity Sale Amount Quantity Sale Amount Quantity Sale Amount Quantity Pens Pencils Notebooks Rulers Calculators Back Packs Totals

　　使用自定義的CSS樣式簡單的數據透視表

　　

　　Sales Person

　　Pens

　　Pencils

　　Notebooks

　　Rulers

　　Calculators

　　Back Packs

　　Grand Totals

　　John

　　

　　

　　

　　

　　

　　

　　

　　Jane

　　

　　

　　

　　

　　

　　

　　

　　Sally

　　

　　

　　

　　

　　

　　

　　

　　Sarah

　　

　　

　　

　　

　　

　　

　　

　　Totals

　　

　　

　　

　　

　　

　　

　　

From:http://tw.wingwit.com/Article/program/net/201311/13802.html