纯代码实现报表功能，让我们告别控件控

在asp.net中通常情况下，我们实现对数据报表的查看，都会使用报表控件来完成对数据的统计操作，然而有些时候我们却不想被控件所定的规格束缚住，因此我们想了另一种方式：首先来看看其中的一种效果如图1：


其主要功能实现的是一段sql语句(这里使用了存储过程):

View Code

CREATE Proc [dbo].[Compact_MonthCrossoverFormByEmp]
@bTime datetime,
@eTime dateTime
As
Select OrgId,OrgName,EmployeId, EmployeName, [1] as Day1,[2] as Day2,[3] as Day3,[4] as Day4,[5] as Day5,[6] as Day6,[7] as Day7,[8] as Day8,[9] as Day9,[10] as Day10,[11] as Day11,[12] as Day12,[13] as Day13,[14] as Day14,[15] as Day15,[16] as Day16,[17] as Day17,[18] as Day18,[19] as Day19,[20] as Day20,[21] as Day21,[22] as Day22,[23] as Day23,[24] as Day24,[25] as Day25,[26] as Day26,[27] as Day27,[28] as Day28,[29] as Day29,[30] as Day30,[31] as Day31
From(
  Select  O.OrgId,O.OrgName, E.EmployeId, E.Name As EmployeName, DatePart(day, C.CreateTime) as TimePoint,C.MoneyAmount as Amount
  From  Employe E Inner join Organ   O On O.OrgId=E.OrgId Inner join Compact C On C.EmployeId=E.EmployeId
  Where C.Status=2 And C.AuthType=1 And C.MoneyAmount>10 And C.CreateTime>=@bTime And C.CreateTime<@eTime
) as ds

Pivot(
    Count(amount)
    FOR TimePoint  In ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
   ) as Pvt
Order By EmployeId

，其中关于Pivot的用法，详解请看遗忘海岸的SQL查询之 Pivot 详解。 上述操作已经把主要的功能实现了，接下来就是如何显示的问题了，我们这里是配合GridView来绑定，然后我们需要将一个月中多余的天数移除掉（比如5月有31天，6月是30天）,我们立刻可以想到的是使用GridView.Columns.RemoveAt(int index)循环移除多余的列，一切好像都在顺利的按照我们所想的实现着，可是问题还是来了。。。当我们点击“图1”的“总计”排序时，数据已然无法正常显示，很是苦恼，在网上查了一些资料，将列隐藏掉是个不错的选择，主要代码如下： View Code

System.Globalization.Calendar calendar = new TaiwanCalendar();
            var daysInMonth= calendar.GetDaysInMonth(Year, Month);
            var hideColumns=gridViewEx.Columns.Count - 4 - daysInMonth; //4:序号，编号，名称，汇总四列
            while (hideColumns-- > 0)
            {
                gridViewEx.Columns[gridViewEx.Columns.Count - hideColumns-1].Visible = false;
            }

接下来还是让我们看看它如何一步步实现绑定的吧!
(1)与ODS绑定方法：
　　  /// <summary>
        /// 按员工分组，的月统计
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public List<CompactMonthCrossoverFormRow> QueryByEmp(int year, int month, string sortExpress)
        {
            Calendar calendar = new TaiwanCalendar();
            var daysInMonth = calendar.GetDaysInMonth(year, month);
            var bTime = new DateTime(year, month, 1);
            var eTime = bTime.AddMonths(1);
            var ds = OB.R<CompactManager>().MonthCrossoverFormByEmp(bTime, eTime);//调用其上介绍过的存储过程
            var list = DBH.SetModelProperty<CompactMonthCrossoverFormRow>("*", ds.Tables[0]);

            list.ForEach(ent =>
            {
                ent.EffectiveDay = daysInMonth;
                ent.SumAndSetTotal(0, (total, v) => total += v);
            });//根据daysInMonth来设置显示的列数

            #region 排序处理（将设置的列排序）
            if (!string.IsNullOrEmpty(sortExpress))
            {
                bool isAsc = true;
                if (sortExpress.ToUpper().IndexOf("DESC") > 0) isAsc = false;
                var field = sortExpress.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries)[0];
                var rtyInfo = typeof(CompactMonthCrossoverFormRow).GetProperty(field);
                if (isAsc)
                {
                    list = list.OrderBy(ent => rtyInfo.GetValue(ent, null)).ToList();
                }
                else
                {
                    list = list.OrderByDescending(ent => rtyInfo.GetValue(ent, null)).ToList();
                }
            }
            #endregion

            var sumRow = CrossoverForm.CreateMonthSumRow<CompactMonthCrossoverFormRow, int, int>(list, (a, b) => a += b);
            sumRow.EmployeName = "汇总";
            sumRow.SumAndSetTotal(0, (a, b) => a + b);
            list.Add(sumRow);

            return list;
        }
附带主要的几个文件:SalesPerformance.rar