作一个Excel exporter的组件
在博客园搜索了一下,导出Excel方法。
感觉没有什么价值的东西(收回这句,我又重新用关键字"excel xml导出"搜了一下,发现一些好东西分享)
无非是把datagrid存成excel,或者是建立一个模板,导出一个excel(只讲讲如何用.net来操作excel,网上到处都是)。
.net与excel的结合应用,我自己也写过三篇文章。
a universal class to complete import data from an excel file into a database (垃圾文章)
关于excel在asp.net中使用 (10分给7.5分;实际上是如何建立通用的可配置的,导入Excel数据的解决方案)
利用接口灵活实现Excel导入/到出数据功能 (垃圾文章)
这里言归正传,我下面说说如何建立一个灵活的、可配置的导出数据到Excel的解决方案。
告诉我,你是不是会经常碰到要导出这样的数据格式
![](/images/cnblogs_com/king_astar/excel/aboutexcel_1.jpg)
描述一下我们要干什么:
1.支持多表头定义。
2.固定的某格填入固定值
3.有一个数据区
4.某列某行可能要根据某些数据条件插入不同的背景,图片,备注。
甚至还有要求这样的格式
难道我们要每次用代码写,而不能只修改一下配置文件就完成么?
![](/images/cnblogs_com/king_astar/excel/aboutexcel_2.jpg)
这都是没有问题的,只要你明白我的思路和发挥一下自己的想像能力就可以作到扩展。
对,扩展,这就是我要强调的内容。
很多的文章的代码,无法帮你扩展,而只能让你去拷贝,这不是我们想要的。
来让我们加快速度来认识一下根据上面要求,扩展的xml文件
![](/images/cnblogs_com/king_astar/excel/aboutexcel_3.jpg)
abcd四个地方描述我们的excel会以那种方式,哪个sheet,哪些固定格,哪些列输出哪些内容。
对它建立了数据导出到Excel的对应关系。
cssClass是描述样式类
DBToExceLRelation 是描述总的数据对应关系类(它可能包含多个sheet对应关系)
FixedItem是固定格类
RelationItem是数据区对应类(它是一个可递归类)
SheetMapRelation这是一个excelsheet类(它包含FixedItem,RelationItem,cssClass)
如下图:
![](/images/cnblogs_com/king_astar/excel/aboutexcel_4.jpg)
接着来看看我们的导出接口
1
using System;
2
using System.Data;
3![](/Images/OutliningIndicators/None.gif)
4
using excelExporter.Configs;
5
using Excel;
6![](/Images/OutliningIndicators/None.gif)
7![](/Images/OutliningIndicators/None.gif)
8
namespace excelExporter.Exporter
9
{
10
public delegate void OnDataBindHandler(Excel.Range excelRange,DataRow row,String ColumnName);
11
/// <summary>
12
/// IConvertor 的摘要说明。
13
/// </summary>
14
public interface IExporter
15
{
16![](/Images/OutliningIndicators/None.gif)
17
String Execute(System.Data.DataSet ds,DBToExceLRelation dbExcelRealtion,String filePath);
18![](/Images/OutliningIndicators/None.gif)
19
20
event OnDataBindHandler OnDataBind;
21
22![](/Images/OutliningIndicators/None.gif)
23
}
24
}
25![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
注意我们申明了一个事件,这样我们就可以处理特殊问题了。
如这些问题
某列某行可能要根据某些数据条件插入不同的背景,图片,备注。
下面是创建excel的一部分代码,这里完成了创建固定格,创建表头,创建数据区的。
1
protected virtual void CreateExcel()
2
{
3![](/Images/OutliningIndicators/InBlock.gif)
4![](/Images/OutliningIndicators/InBlock.gif)
5
try
6
{
7
app = new Excel.ApplicationClass();
8
}
9
catch(System.Exception ex)
10
{
11
throw new Exception("无法实例化Excel对象;<br/>details:<hr size=1/>"+ex.Message);
12
}
13
app.DisplayAlerts = false;
14![](/Images/OutliningIndicators/InBlock.gif)
15
// //app.UserControl = true;
16
wbs = app.Workbooks;
17
int NowSheet =0;
18
try
19
{
20
21
//wbc = wbs.Open(_filepath,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt);
22
wbc = wbs.Add(_objOpt);
23
for(int sheetIndex=0;sheetIndex<this._dbrelation.sheets.Length;sheetIndex++)
24
{
25![](/Images/OutliningIndicators/InBlock.gif)
26
SheetMapRelation sheetmapR = _dbrelation.sheets[sheetIndex];
27
NowSheet = sheetIndex+1;
28
29
if(sheetmapR.SheetName!="")
30
{
31
wbc.Windows[NowSheet].Zoom = sheetmapR.WindowZomm;
32
//wbc.Windows[NowSheet].
33
ws= (Excel.Worksheet)wbc.Worksheets.get_Item(NowSheet);
34
ws.Name = sheetmapR.SheetName;
35
if(Convert.ToBoolean(ws.Cells.Select()))
36
{
37
38
Excel.Range allrange = (Excel.Range)wbc.Windows[NowSheet].Selection;
39
allrange.Font.ColorIndex = _dbrelation.DataCss.FontColorIndex;
40
allrange.Font.Name = _dbrelation.DataCss.FontName;
41
allrange.Font.Size = _dbrelation.DataCss.FontSize;
42
43
}
44![](/Images/OutliningIndicators/InBlock.gif)
45
46![](/Images/OutliningIndicators/InBlock.gif)
47
firstRange = ws.get_Range(sheetmapR.DataStartRange.Trim().ToUpper(),this._objOpt);
48
//取消全部选中
49
firstRange.Select();
50![](/Images/OutliningIndicators/InBlock.gif)
51![](/Images/OutliningIndicators/InBlock.gif)
52
///创建固定格
53
CreateFixedRange(sheetmapR.FixedItems);
54
///创建表头
55
CreateTitle(sheetmapR.Items,sheetmapR.MaxDepth,firstRange,0);
56![](/Images/OutliningIndicators/InBlock.gif)
57
Excel.Range datastartrange = firstRange.get_Offset(sheetmapR.MaxDepth,_objOpt);
58
if(_ds!=null&&_ds.Tables[sheetIndex]!=null)
59
CreateDataRange(datastartrange,sheetmapR,_ds.Tables[sheetIndex]);///创建数据区
60
}
61
}
62![](/Images/OutliningIndicators/InBlock.gif)
63
_fileName = System.DateTime.Now.ToString("yy_MM_dd-hh_mm_ss_ff")+".xls";
64
String tempName = _filepath+"/" +_fileName;
65
wbc.SaveAs(tempName,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,Excel.XlSaveAsAccessMode.xlExclusive,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt);
66![](/Images/OutliningIndicators/InBlock.gif)
67
68
}
69
catch(System.Exception ex)
70
{
71
throw new Exception("无法正确创建Excel文件;<br/>details:<hr size=1/>"+"创建第 "+NowSheet.ToString() +" sheet:"+ex.Message);
72
}
73
finally
74
{
75
this.Dispose();
76
}
77
}
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/InBlock.gif)
15
![](/Images/OutliningIndicators/InBlock.gif)
16
![](/Images/OutliningIndicators/InBlock.gif)
17
![](/Images/OutliningIndicators/InBlock.gif)
18
![](/Images/OutliningIndicators/InBlock.gif)
19
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
20
![](/Images/OutliningIndicators/InBlock.gif)
21
![](/Images/OutliningIndicators/InBlock.gif)
22
![](/Images/OutliningIndicators/InBlock.gif)
23
![](/Images/OutliningIndicators/InBlock.gif)
24
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
25
![](/Images/OutliningIndicators/InBlock.gif)
26
![](/Images/OutliningIndicators/InBlock.gif)
27
![](/Images/OutliningIndicators/InBlock.gif)
28
![](/Images/OutliningIndicators/InBlock.gif)
29
![](/Images/OutliningIndicators/InBlock.gif)
30
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
31
![](/Images/OutliningIndicators/InBlock.gif)
32
![](/Images/OutliningIndicators/InBlock.gif)
33
![](/Images/OutliningIndicators/InBlock.gif)
34
![](/Images/OutliningIndicators/InBlock.gif)
35
![](/Images/OutliningIndicators/InBlock.gif)
36
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
37
![](/Images/OutliningIndicators/InBlock.gif)
38
![](/Images/OutliningIndicators/InBlock.gif)
39
![](/Images/OutliningIndicators/InBlock.gif)
40
![](/Images/OutliningIndicators/InBlock.gif)
41
![](/Images/OutliningIndicators/InBlock.gif)
42
![](/Images/OutliningIndicators/InBlock.gif)
43
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
44
![](/Images/OutliningIndicators/InBlock.gif)
45
![](/Images/OutliningIndicators/InBlock.gif)
46
![](/Images/OutliningIndicators/InBlock.gif)
47
![](/Images/OutliningIndicators/InBlock.gif)
48
![](/Images/OutliningIndicators/InBlock.gif)
49
![](/Images/OutliningIndicators/InBlock.gif)
50
![](/Images/OutliningIndicators/InBlock.gif)
51
![](/Images/OutliningIndicators/InBlock.gif)
52
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
53
![](/Images/OutliningIndicators/InBlock.gif)
54
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
55
![](/Images/OutliningIndicators/InBlock.gif)
56
![](/Images/OutliningIndicators/InBlock.gif)
57
![](/Images/OutliningIndicators/InBlock.gif)
58
![](/Images/OutliningIndicators/InBlock.gif)
59
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
60
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
61
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
62
![](/Images/OutliningIndicators/InBlock.gif)
63
![](/Images/OutliningIndicators/InBlock.gif)
64
![](/Images/OutliningIndicators/InBlock.gif)
65
![](/Images/OutliningIndicators/InBlock.gif)
66
![](/Images/OutliningIndicators/InBlock.gif)
67
![](/Images/OutliningIndicators/InBlock.gif)
68
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
69
![](/Images/OutliningIndicators/InBlock.gif)
70
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
71
![](/Images/OutliningIndicators/InBlock.gif)
72
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
73
![](/Images/OutliningIndicators/InBlock.gif)
74
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
75
![](/Images/OutliningIndicators/InBlock.gif)
76
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
77
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
其中创建多表头是一个难点,我已经在blog上谈过几次,
这篇文章说了说基础:多表头的两个东西,宽度,深度。
http://www.cnblogs.com/king_astar/archive/2005/05/11/153071.html
最后调用如下,
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)