net做数据透视表效果.aspx

本文给大家带来的教程是使用asp.net做数据透视表功能,也就是数据三维视图效果,有点复杂,不过效果很帅,这里为你提供一个非常有用的工具创建简单和高级的数据透视表。目标是:我们想要有能力将datatable中的二维的数据转换成三维视图。 在大多数情况下,你会从数据库的查询数据填充数据表,例如 Code [] 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 该查询会产生下面的数据表: Sales Person Product Quantity Sale Amount John Pens 200 350 John Pencils 400 500 John Notebooks 100 300 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轴字段将抛出一个异常。 文章来自学IT网: 因此,如果你注意上面的数据表,你会发现,“Sales Person”和“Product”字段可以分配到的X轴或Y轴,但不能给z轴。在“Quantity”和“Sale Amount”字段可以被分配到z轴。 Pivot 类将数据表转换成html table。然后您可以将它输出到Web窗体上。那么,这只是实现的方法。如果你愿意,你可以根据这个类的逻辑创建一个用户控件。 Code [] #region V ariables private DataTable _DataTable; private string _CssTopHeading; private string _CssSubHeading; private string _CssLeftColumn; private string _CssItems; private string _CssTotals; private string _CssTable; #endregion V ariables #region Constructors public Pivot(DataTable dataTable) { Init(); _DataTable = dataTable; } #endregion Constructors 这部分的代码是非常自我解释。你能创建一个Pivot 对象,通过传递一个datatable作为参数。在init()方法只分配一个空字符串值给CSS变量。如果CSS的变量是一个空字符串,构造方法将使用默认的样式。每一个CSS变量都有一个相应的属性。 Code [] private string FindV alue(string xAxisField, string xAxisV alue, string yAxisField, string yAxisV alue, string zAxisField) { string zAxisV alue = ""; try { foreach (DataRow row in _DataTable.Rows) { if (Convert.ToString(row[xAxisField]) == xAxisV alue && Convert.ToString(row[yAxisField]) == yAxisV alue) { zAxisV alue = Convert.ToString(row[zAxisField]); break; } } } catch { throw; } return zAxisV alue; } 在FindV alue(...)方法在数据表中搜索的对应x轴和y轴值的Z轴值。xAxisField是X轴字段的列名(例如“Product”),而xAxisV alue是在该列的值。该yAxisField是的Y轴字段 的列名(例如“Sales Person”),并yAxisV alue是在该列的值。该zAxisField是列名,在其中Z轴值,是您正在寻找地(例如“Sale Amount”)。 Code [] private string[] FindV alues(string xAxisField, string xAxisV alue, string yAxisField, string yAxisV alue, string[] zAxisFields) { int zAxis = zAxisFields.Length; if (zAxis < 1) zAxis ; string[] zAxisV alues = new string[zAxis]; //set default values for (int i = 0; i <= zAxisV alues.GetUpperBound(0); i ) { zAxisV alues[i] = "0"; } try { foreach (DataRow row in _DataTable.Rows) { if (Convert.ToString(row[xAxisField]) == xAxisV alue && Convert.ToString(row[yAxisField]) == yAxisV alue) { for (int z = 0; z < zAxis; z ) { zAxisV alues[z] = Convert.ToString(row[zAxisFields[z]]); } break; } } } catch { throw; } return zAxisV alues; } 在FindV alues(...)方法类似FindV alue(...)方法,然而,它会返回多个z轴的值。这是用于高级的数据透视表,对应于x轴的值,您会有多个Z轴列。 Code [] 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。 文章来自学IT网: Code [] ///

/// Creates an advanced 3D Pivot table. ///

/// The main heading at the top of the report. /// The heading on the left of the report. /// The sub heading at the top of the report. /// HtmlTable Control. 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[] zAxisV alues = 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 zAxisV alues = FindV alues(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] = zAxisV alues[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.zip文件中包括两个解决 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 。Pivot 是一个类库解决方案是。您可以编译此解决方案和在Web应用程序中引用Pivot.dll。另一个解决方案是PivotTest,它是是一个ASP.NET 应用程序。这说明如何实现Pivot类。 文章来自学IT网: Code [] 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; } } 我已创建数据表的属性,它建立在上面的例子中的数据表。这只是用于演示目的。 Code [] 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); } 上述代码包括两个实例化的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 代码:/Files/zhuqil/Pivot.zip 作者:朱祁林 出处: 编辑: 本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。 文章来自学IT网: