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

在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

转载于:https://www.cnblogs.com/zlr-2217/archive/2011/06/07/2074107.html