熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

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

2013-11-13 10:29:32  來源: .NET編程 

  數據透視表提供的數據三維視圖效果在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 PersonProduct字段可以分配到的X軸或Y軸但不能給z軸QuantitySale 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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.