把数据库里的数据分组统计后显示在execl里.如下所示.
在数据库里有如下数据:Dog
然后我们根据狗状态和性别分组统计金钱,得到如下数据:设这下表名是DogTotal
![](https://images.cnblogs.com/cnblogs_com/zhouxin/2009-09-25_134438.png)
这个继续放在数据库里.但是我们要生成的EXECL如下图所示.
![](https://images.cnblogs.com/cnblogs_com/zhouxin/2009-09-25_134502.png)
在这里,我们得到是第二个表用Linq to sql生成类.得到的数据就是db.Total.
我大致模拟一下上面过程.如下代码.
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
public class Dog
2![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
3![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public int ID
{ get; set; }
4![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public string Status
{ get; set; }
5![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public float Amount
{ get; set; }
6![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public string Sex
{ get; set; }
7
public List<DogTotal> Total(List<Dog> dogs)
8![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
9
return List<DogTotal> dogtotal = (from dog in dogs
10
group dog by new
11![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
12
dog.Sex,
13
dog.Status
14
} into g
15
select new DogTotal
16![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
27![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public string Status
{ get; set; }
28![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public float TotalAmount
{ get; set; }
29![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public string Sex
{ get; set; }
30
}
而我要实现的功能如下,知道分组后的数据,如我得到List<DogTotal>满足如下条件根据二个项分组.能通用.意思我不知道里
面的属性.不知怎么说,看第一篇里详细些.如下代码.
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
public class GroupExecl<T,U> where T : IEnumerable<U>
2![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
3
private readonly Type item = null;
4
private T total = default(T);
5
public GroupExecl(T t)
6![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
18
get
19![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
20
return top;
21
}
22
set
23![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
24
if (value < 1 || value > 65536)
25
throw new Exception("超过最大行数.");
26
top = value;
27
}
28
}
29
public int Left
30![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
31
get
32![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
33
return left;
34
}
35
set
36![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
37
if (value < 1 || value > 65536)
38
throw new Exception("超过最大行数.");
39
left = value;
40
}
41
}
42
public string Row
43![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
44
set
45![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
52
set
53![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
61
set
62![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
63
show = item.GetProperty(value);
64
}
65
}
66![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public bool IsRowOrderBy
{ get; set; }
67![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public bool IsColumnOrderBy
{ get; set; }
68![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
public string TopText
{ get; set; }
69
public void FillExel(Worksheet wsheet)
70![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
84
ntop++;
85
wsheet.Cells[ntop, left] = irow;
86
nleft = left;
87
foreach (string icolumn in columns)
88![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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行转列.