现在,以表格的形式显示的数据是任何应用程序的重要组成部分。但有时你需要以数据行格式显示大量的数据。如果行数巨大,它变得非常难以分析。在这种情况下,您可能希望使用其他格式统计你的数据,如: charts, graphs, groups, pivots等 。本文将介绍一种使用适当的聚合函数的方式来透视你的数据,它可以很容易提高您的报表功能。

  下面的截图是在GridView中显示的数据透视功能。艾伟_转载:透视Datatable_ico

艾伟_转载:透视Datatable_聚合函数_02

  为了简化方案,我把表的结果分三个区域显示:RowField, ​​DataField​​, 和​​ColumnFields,除了这些,Pivot类提供一些供您来选择的聚合函数来绑定的数据。可提供的选项有:​

  • ​Count​​: 返回匹配的数据数量
  • ​Sum​​: 返回匹配的数据总和(为了得到总和,将DataField的类型必须转换为decimal类型)
  • ​First​​: 返回匹配的数据第一项
  • ​Last​​: 返回匹配的数据最后一项
  • ​Average​​: 返回匹配的数据平均(为了得到平均值,将DataField的类型必须转换为decimal类型)
  • ​Max​​: 返回匹配的数据最大值
  • ​Min​​: 返回匹配的数据最小值
  • ​Exists​​​: 如果有匹配的数据,返回“true”,否则“false”   代码主要包含了一个名字为"​​Pivot​​"的类,在它的构造函数中创建DataTable。 ​​ColumnFields​​ 是一个字符串数组参数,它允许你透视多列数据。这个类还包含了一个实际透视你数据的​​PivotData()​​ 方法。
    艾伟_转载:透视Datatable_数据_03艾伟_转载:透视Datatable_数据_04代码

    public DataTable PivotData(string RowField, string DataField, 
           AggregateFunction Aggregate, params string[] ColumnFields)
    {
        DataTable dt = new DataTable();
        string Separator = ".";
        var RowList = (from x in _SourceTable.AsEnumerable() 
            select new { Name = x.Field<object>(RowField) }).Distinct();
        var ColList = (from x in _SourceTable.AsEnumerable() 
                       select new { Name = ColumnFields.Select(n => x.Field<object>(n))
                           .Aggregate((a, b) => a += Separator + b.ToString()) })
                           .Distinct()
                           .OrderBy(m => m.Name);

        dt.Columns.Add(RowField);
        foreach (var col in ColList)
        {
            dt.Columns.Add(col.Name.ToString());
        }

        foreach (var RowName in RowList)
        {
            DataRow row = dt.NewRow();
            row[RowField] = RowName.Name.ToString();
            foreach (var col in ColList)
            {
                string strFilter = RowField + " = '" + RowName.Name + "'";
                string[] strColValues = 
                  col.Name.ToString().Split(Separator.ToCharArray(), 
                                            StringSplitOptions.None);
                for (int i = 0; i < ColumnFields.Length; i++)
                    strFilter += " and " + ColumnFields[i] + 
                                 " = '" + strColValues[i] + "'";
                row[col.Name.ToString()] = GetData(strFilter, DataField, Aggregate);
            }
            dt.Rows.Add(row);
        }
        return dt;
    }
      首先,该函数通过获取RowList的Distinct的值,确定行的成员;通过获取ColList的Distinct值,确定​​列的成员。然后,创建datatable的列。然后根据所提供的聚合函数,遍历每一行和获取匹配值到相应的单元格上。调用GetData()函数,检索匹配值。​艾伟_转载:透视Datatable_数据_03艾伟_转载:透视Datatable_数据_04代码

    private object GetData(string Filter, string DataField, AggregateFunction Aggregate)
    {
        try
        {
            DataRow[] FilteredRows = _SourceTable.Select(Filter);
            object[] objList = 
             FilteredRows.Select(x => x.Field<object>(DataField)).ToArray();

            switch (Aggregate)
            {
                case AggregateFunction.Average:
                    return GetAverage(objList);
                case AggregateFunction.Count:
                    return objList.Count();
                case AggregateFunction.Exists:
                    return (objList.Count() == 0) ? "False" : "True";
                case AggregateFunction.First:
                    return GetFirst(objList);
                case AggregateFunction.Last:
                    return GetLast(objList);
                case AggregateFunction.Max:
                    return GetMax(objList);
                case AggregateFunction.Min:
                    return GetMin(objList);
                case AggregateFunction.Sum:
                    return GetSum(objList);
                default:
                    return null;
            }
        }
        catch (Exception ex)
        {
            return "#Error";
        }
        return null;
    }
    这个函数首先过滤到DataRow []数组中匹配RowField和ColumnFields数据 ,然后调用相应的聚合函数。
      如何使用代码:
      代码使用起来很简单。创建一个Pivot类的实例,然后使用要求的参数调用PivotData方法。PivotData()方法返回DataTable,他可直接作为GridView的数据源。
    艾伟_转载:透视Datatable_数据_03艾伟_转载:透视Datatable_数据_04代码

    DataTable dt = ExcelLayer.GetDataTable("_Data\\DataForPivot.xls", "Sheet1$");
    Pivot pvt = new Pivot(dt);

    grdPivot.DataSource = pvt.PivotData("Designation", "CTC", 
       AggregateFunction.Max, "Company", "Department", "Year");
    grdPivot.DataBind();
    示例中使用的数据库是一个Excel Sheet ,放在示例应用程序的根文件夹下的“_Data”文件夹中。
        该MergeHeader函数创建合并头部的单元格,并提供了一个简化的外观。
    艾伟_转载:透视Datatable_数据_03艾伟_转载:透视Datatable_数据_04代码

    private void MergeHeader(GridView gv, GridViewRow row, int PivotLevel)
    {
        for (int iCount = 1; iCount <= PivotLevel; iCount++)
        {
            GridViewRow oGridViewRow = new GridViewRow(0, 0, 
              DataControlRowType.Header, DataControlRowState.Insert);
            var Header = (row.Cells.Cast<tablecell>()
                .Select(x => GetHeaderText(x.Text, iCount, PivotLevel)))
                .GroupBy(x => x);

            foreach (var v in Header)
            {
                TableHeaderCell cell = new TableHeaderCell();
                cell.Text = v.Key.Substring(v.Key.LastIndexOf(_Separator) + 1);
                cell.ColumnSpan = v.Count();
                oGridViewRow.Cells.Add(cell);
            }
            gv.Controls[0].Controls.AddAt(row.RowIndex, oGridViewRow);
        }
        row.Visible = false;
    }
      该函数为每个PivotLevel创建新行,和相应的合并。 PivotLevel这里是列上的轴完成的数量。
      Header从一个数组中得到所有的列值,对GetHeaderText()返回的重复的值进行分组,根据HeaderText的重复数量,设置新创建的单元格的ColumnSpan属性。将单元格添加到GridViewRow中。最后添加GridViewRow到GridView。GetHeaderText() 函数根据PivotLevel返回header text.
      例如,假设一个透视表,完成三个ColumnField: Company, Department, 和Year。GridView的初始化时候,头部将拥有Company.Department。PivotLevel 1是Year。 GetHeaderText()将返回Company。对于PivotLevel 2,GetHeaderText()将返回Company.Department。对于PivotLevel 3,GetHeaderText()将返回Company.Department.Year,等等...
      以下是GridView的截图,包含三个层次透视数据:
    艾伟_转载:透视Datatable_聚合函数_11
      代码帮助您合并你想要的格式GridView的头单元格。对于初学者,在ExcelLayer.GetDataTable()方法将是一个从Excel表中的中获得数据例子。
      目前,代码只在DataTable中透视数据。该代码将得到加强,将来透视IListSource或ICollection派生的任何对象。