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的定位

使用默认样式的高级的数据透视表

JohnJaneSallySarahGrand TotalsProductSale AmountQuantitySale AmountQuantitySale AmountQuantitySale AmountQuantitySale AmountQuantityPensPencilsNotebooksRulersCalculatorsBack PacksTotals

使用自定义的CSS样式简单的数据透视表

Sales Person

Pens

Pencils

Notebooks

Rulers

Calculators

Back Packs

Grand Totals

John

Jane

Sally

Sarah

Totals