Linq,企业类库,EXECL生成,Execl chart的一些基本操作记录.(二)

 把数据库里的数据分组统计后显示在execl里.如下所示.
在数据库里有如下数据:Dog

 
然后我们根据狗状态和性别分组统计金钱,得到如下数据:设这下表名是DogTotal

这个继续放在数据库里.但是我们要生成的EXECL如下图所示.

在这里,我们得到是第二个表用Linq to sql生成类.得到的数据就是db.Total.
我大致模拟一下上面过程.如下代码.

 1    public class Dog
 2    {
 3        public int ID { get; set; }
 4        public string Status { get; set; }
 5        public float Amount { get; set; }
 6        public string Sex { get; set; }
 7        public List<DogTotal> Total(List<Dog> dogs)
 8        {
 9            return List<DogTotal> dogtotal = (from dog in dogs
10                                       group dog by new
11                                       {
12                                           dog.Sex,
13                                           dog.Status
14                                       } into g
15                                       select new DogTotal
16                                       {
17                                           Status = g.Key.Sex,
18                                           Sex = g.Key.Sex,
19                                           TotalAmount = g.Sum(p => p.Amount)
20                                       }).ToList<DogTotal>();
21        }
22        
23    }
24    
25    public class DogTotal
26    {
27        public string Status { get; set; }
28        public float TotalAmount { get; set; }
29        public string Sex { get; set; }
30    }

而我要实现的功能如下,知道分组后的数据,如我得到List<DogTotal>满足如下条件根据二个项分组.能通用.意思我不知道里
面的属性.不知怎么说,看第一篇里详细些.如下代码.

  1public class GroupExecl<T,U> where T : IEnumerable<U>
  2    {
  3        private readonly Type item = null;
  4        private T total = default(T);
  5        public GroupExecl(T t)
  6        {
  7            item = typeof(U);
  8            total = t;
  9        }
 10        private PropertyInfo row = null;
 11        private PropertyInfo column = null;
 12        private List<string> columns = null;
 13        private List<string> rows = null;
 14        private int top = 1;
 15        private int left = 1;
 16        public int Top
 17        {
 18            get
 19            {
 20                return top;
 21            }
 22            set
 23            {
 24                if (value < 1 || value > 65536)
 25                    throw new Exception("超过最大行数.");
 26                top = value;
 27            }
 28        }
 29        public int Left
 30        {
 31            get
 32            {
 33                return left;
 34            }
 35            set
 36            {
 37                if (value < 1 || value > 65536)
 38                    throw new Exception("超过最大行数.");
 39                left = value;
 40            }
 41        }
 42        public string Row
 43        {
 44            set
 45            {
 46                row = item.GetProperty(value);
 47                rows = total.Select((U p) => row.GetValue(p, null).ToString()).Distinct().ToList<string>();
 48            }
 49        }
 50        public string Column
 51        {
 52            set
 53            {
 54                column = item.GetProperty(value);
 55                columns = total.Select((U p) => column.GetValue(p, null).ToString()).Distinct().ToList<string>();
 56            }
 57        }
 58        private PropertyInfo show = null;
 59        public string Show
 60        {
 61            set
 62            {
 63                show = item.GetProperty(value);
 64            }
 65        }
 66        public bool IsRowOrderBy { get; set; }
 67        public bool IsColumnOrderBy { get; set; }
 68        public string TopText { get; set; }
 69        public void FillExel(Worksheet wsheet)
 70        {
 71            if(!string.IsNullOrEmpty(TopText))
 72                wsheet.get_Range(wsheet.Cells[top, left], wsheet.Cells[top, left]).Value2 = TopText;
 73            int ntop = top; int nleft = left;
 74            if (!IsRowOrderBy)
 75                rows.OrderByDescending(p => p);
 76            else
 77                rows.OrderBy(p => p);
 78            if (!IsColumnOrderBy)
 79                columns.OrderByDescending(p => p);
 80            else
 81                columns.OrderBy(p => p);
 82            foreach (string irow in rows)
 83            {
 84                ntop++;
 85                wsheet.Cells[ntop, left] = irow;
 86                nleft = left;
 87                foreach (string icolumn in columns)
 88                {
 89                    nleft++;
 90                    wsheet.Cells[top, nleft] = icolumn;
 91                    Func<string,string,bool> where = (p1,p2) => p1 == irow && p2 == icolumn;       
 92                    var am = total.Where( p => where((row.GetValue(p,null)??string.Empty).ToString(),(column.GetValue(p,null)??string.Empty).ToString()));
 93                    string s = string.Empty;
 94                    if (am != null && am.Count() > 0)
 95                    {
 96                        object o = show.GetValue(am.First(), null);
 97                        if (o != null)
 98                            s = o.ToString();
 99                    }
100                    wsheet.Cells[ntop, nleft] = s;
101                }
102            }
103            wsheet.get_Range(wsheet.Cells[top, left], wsheet.Cells[ntop, nleft]).Borders.LineStyle = 1; 
104        }        
105    }

想得到生成上面的execl,只需要如下:
            GroupExecl<IEnumerable<DogTotal>, DogTotal> ex = new GroupExecl<IEnumerable<DogTotal>, DogTotal>(db.DogTotal);
            ex.Row = "Status";
            ex.Column = "Sex";
            ex.Top = 4;
            ex.Left = 1;
            ex.Show = "TotalAmount";
            ex.FillExel(wsheet); 
在其中利用反射取值和泛形来完成通用性设计.

上面类还多有BUG,希望各位大大们能帮忙改一下.谢谢.

 

SQL相关通用行转列.可以看我的另一篇随笔.

SQL行转列.