ASP.NET实现类似Excel的数据透视表

出处：http://www.cnblogs.com/zhuqil/archive/2010/01/07/1640772.html

代码： /Files/zhuqil/Pivot.zip

数据透视表提供的数据三维视图效果，在Microsoft Excel能创建数据透视表，但是，它并不会总是很方便使用Excel。您可能希望在Web应用程序中创建一个数据透视报表。创建一个简单的数据透视表可能是一件非常复杂的任务。所以，我打算不但为你提供一个非常有用的工具创建简单和高级的数据透视表，而且为你移除一些笼罩他们的神秘面纱。

目标是：我们想要有能力将datatable中的二维的数据转换成三维视图。

在大多数情况下，你会从数据库的查询数据填充数据表，例如

代码

<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->SELECT
SalesPeople.FullNameAS[SalesPerson]
,Products.FullNameAS[Product]
,SUM(Sales.SalesAmount)AS[SaleAmount]
,SUM(Sales.Qty)AS[Quantity]
FROM
Sales
JOIN
SalesPeopleWITH(NOLOCK)
ONSalesPeople.SalesPersonID=Sales.SalesPersonID
JOIN
ProductsWITH(NOLOCK)
ONProducts.ProductCode=Sales.ProductCode
GROUPBY
SalesPeople.FullName
,Products.FullName

该查询会产生下面的数据表：

Sales Person	Product	Quantity	Sale Amount
John	Pens	200	350
John	Pencils	400	500
John	Notebooks	100	300
John	Rulers	50	100
John	Calculators	120	1200
John	Back Packs	75	1500
Jane	Pens	225	393.75
Jane	Pencils	335	418.75
Jane	Notebooks	200	600
Jane	Rulers	75	150
Jane	Calculators	80	800
Jane	Back Packs	97	1940
Sally	Pens	202	353.5
Sally	Pencils	303	378.75
Sally	Notebooks	198	600
Sally	Rulers	98	594
Sally	Calculators	80	800
Sally	Back Packs	101	2020
Sarah	Pens	112	196
Sarah	Pencils	245	306.25
Sarah	Notebooks	198	594
Sarah	Rulers	50	100
Sarah	Calculators	66	660
Sarah	Back Packs	50	2020

正如你所看到的，这是一个二维表，它不是一个非常有用的报表。因此，我们得改变，将它变成更可读的数据表。

数据透视表有3个面。

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窗体上。那么，这只是实现的方法。如果你愿意，你可以根据这个类的逻辑创建一个用户控件。

代码

<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->#regionVariables
privateDataTable_DataTable;
privatestring_CssTopHeading;
privatestring_CssSubHeading;
privatestring_CssLeftColumn;
privatestring_CssItems;
privatestring_CssTotals;
privatestring_CssTable;
#endregionVariables
#regionConstructors
publicPivot(DataTabledataTable)
{
Init();
_DataTable=dataTable;
}
#endregionConstructors

这部分的代码是非常自我解释。 你能创建一个Pivot 对象，通过传递一个datatable作为参数。在init（）方法只分配一个空字符串值给CSS变量。如果CSS的变量是一个空字符串，构造方法将使用默认的样式。每一个CSS变量都有一个相应的属性。

代码

<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->privatestringFindValue(stringxAxisField,stringxAxisValue,stringyAxisField,stringyAxisValue,stringzAxisField)
{
stringzAxisValue="";
try
{
foreach(DataRowrowin_DataTable.Rows)
{
if(Convert.ToString(row[xAxisField])==xAxisValue&&Convert.ToString(row[yAxisField])==yAxisValue)
{
zAxisValue=Convert.ToString(row[zAxisField]);
break;
}
}
}
catch
{
throw;
}
returnzAxisValue;
}

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

代码

<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->privatestring[]FindValues(stringxAxisField,stringxAxisValue,stringyAxisField,stringyAxisValue,string[]zAxisFields)
{
intzAxis=zAxisFields.Length;
if(zAxis<1)
zAxis++;
string[]zAxisValues=newstring[zAxis];
//setdefaultvalues
for(inti=0;i<=zAxisValues.GetUpperBound(0);i++)
{
zAxisValues[i]="0";
}
try
{
foreach(DataRowrowin_DataTable.Rows)
{
if(Convert.ToString(row[xAxisField])==xAxisValue&&Convert.ToString(row[yAxisField])==yAxisValue)
{
for(intz=0;z<zAxis;z++)
{
zAxisValues[z]=Convert.ToString(row[zAxisFields[z]]);
}
break;
}
}
}
catch
{
throw;
}
returnzAxisValues;
}

在FindValues（...）方法类似FindValue（...）方法，然而，它会返回多个z轴的值。这是用于高级的数据透视表，对应于x轴的值，您会有多个Z轴列。

代码

<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->privatevoidMainHeaderTopCellStyle(HtmlTableCellcell)
{
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。

代码

<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->///<summary>
///Createsanadvanced3DPivottable.
///</summary>
///<paramname="xAxisField">Themainheadingatthetopofthereport.</param>
///<paramname="yAxisField">Theheadingontheleftofthereport.</param>
///<paramname="zAxisFields">Thesubheadingatthetopofthereport.</param>
///<returns>HtmlTableControl.</returns>
publicHtmlTablePivotTable(stringxAxisField,stringyAxisField,string[]zAxisFields)
{
HtmlTabletable=newHtmlTable();
//styletable
TableStyle(table);
/*
*Thex-axisisthemainhorizontalrow.
*Thez-axisisthesubhorizontalrow.
*They-axisistheleftverticalcolumn.
*/
try
{
//getdistinctxAxisFields
ArrayListxAxis=newArrayList();
foreach(DataRowrowin_DataTable.Rows)
{
if(!xAxis.Contains(row[xAxisField]))
xAxis.Add(row[xAxisField]);
}
//getdistinctyAxisFields
ArrayListyAxis=newArrayList();
foreach(DataRowrowin_DataTable.Rows)
{
if(!yAxis.Contains(row[yAxisField]))
yAxis.Add(row[yAxisField]);
}
//createa2Darrayforthey-axis/z-axisfields
intzAxis=zAxisFields.Length;
if(zAxis<1)
zAxis=1;
string[,]matrix=newstring[(xAxis.Count*zAxis),yAxis.Count];
string[]zAxisValues=newstring[zAxis];
for(inty=0;y<yAxis.Count;y++)//loopthruy-axisfields
{
//rows
for(intx=0;x<xAxis.Count;x++)//loopthrux-axisfields
{
//maincolumns
//getthez-axisvalues
zAxisValues=FindValues(xAxisField,Convert.ToString(xAxis[x])
,yAxisField,Convert.ToString(yAxis[y]),zAxisFields);
for(intz=0;z<zAxis;z++)//loopthruz-axisfields
{
//subcolumns
matrix[(((x+1)*zAxis-zAxis)+z),y]=zAxisValues[z];
}
}
}
//calculatetotalsforthey-axis
decimal[]yTotals=newdecimal[(xAxis.Count*zAxis)];
for(intcol=0;col<(xAxis.Count*zAxis);col++)
{
yTotals[col]=0;
for(introw=0;row<yAxis.Count;row++)
{
yTotals[col]+=Convert.ToDecimal(matrix[col,row]);
}
}
//calculatetotalsforthex-axis
decimal[,]xTotals=newdecimal[zAxis,(yAxis.Count+1)];
for(inty=0;y<yAxis.Count;y++)//loopthruthey-axis
{
intzCount=0;
for(intz=0;z<(zAxis*xAxis.Count);z++)//loopthruthez-axis
{
xTotals[zCount,y]+=Convert.ToDecimal(matrix[z,y]);
if(zCount==(zAxis-1))
zCount=0;
else
zCount++;
}
}
for(intxx=0;xx<zAxis;xx++)//GrandTotal
{
for(intxy=0;xy<yAxis.Count;xy++)
{
xTotals[xx,yAxis.Count]+=xTotals[xx,xy];
}
}
//BuildHTMLTable
//Appendmainrow(x-axis)
HtmlTableRowmainRow=newHtmlTableRow();
mainRow.Cells.Add(newHtmlTableCell());
for(intx=0;x<=xAxis.Count;x++)//loopthrux-axis+1
{
HtmlTableCellcell=newHtmlTableCell();
cell.ColSpan=zAxis;
if(x<xAxis.Count)
cell.InnerText=Convert.ToString(xAxis[x]);
else
cell.InnerText="GrandTotals";
//stylecell
MainHeaderTopCellStyle(cell);
mainRow.Cells.Add(cell);
}
table.Rows.Add(mainRow);
//Appendsubrow(z-axis)
HtmlTableRowsubRow=newHtmlTableRow();
subRow.Cells.Add(newHtmlTableCell());
subRow.Cells[0].InnerText=yAxisField;
//stylecell
SubHeaderCellStyle(subRow.Cells[0]);
for(intx=0;x<=xAxis.Count;x++)//loopthrux-axis+1
{
for(intz=0;z<zAxis;z++)
{
HtmlTableCellcell=newHtmlTableCell();
cell.InnerText=zAxisFields[z];
//stylecell
SubHeaderCellStyle(cell);
subRow.Cells.Add(cell);
}
}
table.Rows.Add(subRow);
//Appendtableitemsfrommatrix
for(inty=0;y<yAxis.Count;y++)//loopthruy-axis
{
HtmlTableRowitemRow=newHtmlTableRow();
for(intz=0;z<=(zAxis*xAxis.Count);z++)//loopthruz-axis+1
{
HtmlTableCellcell=newHtmlTableCell();
if(z==0)
{
cell.InnerText=Convert.ToString(yAxis[y]);
//stylecell
MainHeaderLeftCellStyle(cell);
}
else
{
cell.InnerText=Convert.ToString(matrix[(z-1),y]);
//stylecell
ItemCellStyle(cell);
}
itemRow.Cells.Add(cell);
}
//appendx-axisgrandtotals
for(intz=0;z<zAxis;z++)
{
HtmlTableCellcell=newHtmlTableCell();
cell.InnerText=Convert.ToString(xTotals[z,y]);
//stylecell
TotalCellStyle(cell);
itemRow.Cells.Add(cell);
}
table.Rows.Add(itemRow);
}
//appendy-axistotals
HtmlTableRowtotalRow=newHtmlTableRow();
for(intx=0;x<=(zAxis*xAxis.Count);x++)
{
HtmlTableCellcell=newHtmlTableCell();
if(x==0)
cell.InnerText="Totals";
else
cell.InnerText=Convert.ToString(yTotals[x-1]);
//stylecell
TotalCellStyle(cell);
totalRow.Cells.Add(cell);
}
//appendx-axis/y-axistotals
for(intz=0;z<zAxis;z++)
{
HtmlTableCellcell=newHtmlTableCell();
cell.InnerText=Convert.ToString(xTotals[z,xTotals.GetUpperBound(1)]);
//stylecell
TotalCellStyle(cell);
totalRow.Cells.Add(cell);
}
table.Rows.Add(totalRow);
}
catch
{
throw;
}
returntable;
}

PivotTable(…) 方法，是所有神奇发生的地方。有两种重载方法，一个创建了一个简单的数据透视表，而其他（上面的方法）创建一个高级的数据透视表。唯一的区别在于，一个简单只有一个的z轴，而高级的，不止一个。

Pivot.zip文件中包括两个解决方案。Pivot 是一个类库解决方案是。您可以编译此解决方案和在Web应用程序中引用Pivot.dll。另一个解决方案是PivotTest，它是是一个ASP.NET应用程序。这说明如何实现Pivot类。

代码

<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->publicDataTableDataTableForTesting
{
get
{
DataTabledt=newDataTable("SalesTable");
dt.Columns.Add("SalesPerson");
dt.Columns.Add("Product");
dt.Columns.Add("Quantity");
dt.Columns.Add("SaleAmount");
dt.Rows.Add(newobject[]{"John","Pens",200,350.00});
dt.Rows.Add(newobject[]{"John","Pencils",400,500.00});
dt.Rows.Add(newobject[]{"John","Notebooks",100,300.00});
dt.Rows.Add(newobject[]{"John","Rulers",50,100.00});
dt.Rows.Add(newobject[]{"John","Calculators",120,1200.00});
dt.Rows.Add(newobject[]{"John","BackPacks",75,1500.00});
dt.Rows.Add(newobject[]{"Jane","Pens",225,393.75});
dt.Rows.Add(newobject[]{"Jane","Pencils",335,418.75});
dt.Rows.Add(newobject[]{"Jane","Notebooks",200,600.00});
dt.Rows.Add(newobject[]{"Jane","Rulers",75,150.00});
dt.Rows.Add(newobject[]{"Jane","Calculators",80,800.00});
dt.Rows.Add(newobject[]{"Jane","BackPacks",97,1940.00});
dt.Rows.Add(newobject[]{"Sally","Pens",202,353.50});
dt.Rows.Add(newobject[]{"Sally","Pencils",303,378.75});
dt.Rows.Add(newobject[]{"Sally","Notebooks",198,600.00});
dt.Rows.Add(newobject[]{"Sally","Rulers",98,594.00});
dt.Rows.Add(newobject[]{"Sally","Calculators",80,800.00});
dt.Rows.Add(newobject[]{"Sally","BackPacks",101,2020.00});
dt.Rows.Add(newobject[]{"Sarah","Pens",112,196.00});
dt.Rows.Add(newobject[]{"Sarah","Pencils",245,306.25});
dt.Rows.Add(newobject[]{"Sarah","Notebooks",198,594.00});
dt.Rows.Add(newobject[]{"Sarah","Rulers",50,100.00});
dt.Rows.Add(newobject[]{"Sarah","Calculators",66,660.00});
dt.Rows.Add(newobject[]{"Sarah","BackPacks",50,2020.00});
returndt;
}
}

我已创建数据表的属性，它建立在上面的例子中的数据表。这只是用于演示目的。

代码

<!--<br/ /> <br/ /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br/ /> http://www.CodeHighlighter.com/<br/ /> <br/ /> -->protectedvoidPage_Load(objectsender,EventArgse)
{
//AdvancedPivot
PivotadvPivot=newPivot(DataTableForTesting);
HtmlTableadvancedPivot=advPivot.PivotTable("SalesPerson","Product",newstring[]{"SaleAmount","Quantity"});
div1.Controls.Add(advancedPivot);
//SimplePivot
Pivotpivot=newPivot(DataTableForTesting);
//overridedefaultstylewithcss
pivot.CssTopHeading="Heading";
pivot.CssLeftColumn="LeftColumn";
pivot.CssItems="Items";
pivot.CssTotals="Totals";
pivot.CssTable="Table";
HtmlTablesimplePivot=pivot.PivotTable("Product","SalesPerson","SaleAmount");
div2.Controls.Add(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	350	200	393.75	225	353.5	202	196	112	1293.25	739
Pencils	500	400	418.75	335	378.75	303	306.25	245	1603.75	1283
Notebooks	300	100	600	200	600	198	594	198	2094	696
Rulers	100	50	150	75	594	98	100	50	944	273
Calculators	1200	120	800	80	800	80	660	66	3460	346
Back Packs	1500	75	1940	97	2020	101	2020	50	7480	323
Totals	3950	945	4302.50	1012	4746.25	982	3876.25	721	16875.00	3660

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

Sales Person	Pens	Pencils	Notebooks	Rulers	Calculators	Back Packs	Grand Totals
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
Totals	1293.25	1603.75	2094	944	3460	7480	16875.00

参考原文：http://www.codeproject.com/KB/aspnet/Pivot.aspx

作者：朱祁林
出处：http://zhuqil.cnblogs.com
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。