把数据库里的数据分组统计后显示在execl里.如下所示.
在数据库里有如下数据:Dog
然后我们根据狗状态和性别分组统计金钱,得到如下数据:设这下表名是DogTotal
这个继续放在数据库里.但是我们要生成的EXECL如下图所示.
在这里,我们得到是第二个表用Linq to sql生成类.得到的数据就是db.Total.
我大致模拟一下上面过程.如下代码.
Code
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>满足如下条件根据二个项分组.能通用.意思我不知道里
面的属性.不知怎么说,看第一篇里详细些.如下代码.
Code
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行转列.
