`
izuoyan
  • 浏览: 8881644 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

告别ASP.NET操作EXCEL的烦恼(总结篇)——放到首页奢侈下

阅读更多

出处:http://www.cnblogs.com/peaceli/archive/2008/04/13/1151520.html

公元19XX年前,关于EXCEL的操作就如滔滔江水,连绵不绝,真正操作EXCEL我也是从去年下半年开始的,有些比较复杂的年度报表之类的,做起来也有点费力,不过还是都能画出来了,关于EXCEL的报表导出,考虑到导出耗时的问题我主要采用AJAX来做的,分别捕捉几个起止状态,给客户端提示3个状态:正在检索数据。。。---》准备导出数据。。。(只是从数据库成功取出,还没有读写excel文件)--》正在读写文件--》导出数据成功,当然如果哪一过程出错,都有对应的提示,只所以想到写这篇文章,主要是因为今年有个系统的部分EXCEL的操作也让我做,顺便结合之前操作EXCEL的经验作一下总结,可能也算不上什么,对于绝大多数来说也没什么技术含量,网上一搜一大把,但我想还是有必要总结一下,至少能给园子里的新手些许帮助,OK,Let's Go...

一. 程序操作EXCEL的应用主要还是在统计报表方面,您可能会考虑读EXCEL模板,也可能会考虑没必要读模板,其实读不读模板都能达到一样的效果,看实际情况而用了。
1. 读模板的话,首先模板存放在某个路径下,根据模板把从数据库里取出的数据写回EXCEL然后生成一个新的EXCEL存放都另一个路径以供下载,模板不变。
我这里的EXCEL操作主要是在VS2005里的,VS2003也可以的,不过没怎么研究03里的操作(文章最后我会把05的示例下载地址贴上 那个demo里之前打包忘了放了一个生成数据的文件,刚放进去了,不加也是可以运行的,还有模板文件的数据稍微过滤了下重新放了部分对照看下)vs05中操作EXCEL直接引用.NET自带的COM组件,添加后项目的bin目录下会自动出现

Interop.Excel.dll这个DLL(需安装office2003 excel,下面的说明及示例都是基于office2003的,版本不同调用可能会不一样)
页面的命名空间引用 using Excel;
下面是调用模板的一段代码

1#region使用模板导出Excel表
2case"ReportByTemp":
3{
4
5DataViewdv=Cache["ReportByTemp"]asDataView;
6//建立一个Excel.Application的新进程
7Excel.Applicationapp=newExcel.Application();
8if(app==null)
9{
10return;
11}
12app.Visible=false;
13app.UserControl=true;
14Workbooksworkbooks=app.Workbooks;
15_Workbookworkbook=workbooks.Add(template_path+"\\EXCEL测试模板.xls");//这里的Add方法里的参数就是模板的路径
16Sheetssheets=workbook.Worksheets;
17_Worksheetworksheet=(_Worksheet)sheets.get_Item(1);//模板只有一个sheet表
18if(worksheet==null)
19{
20return;
21}
22
23introwNum=0;
24for(inti=0;i<dv.Count;i++)
25{
26rowNum=i+1;
27worksheet.Cells[3+i,1]=rowNum;
28worksheet.Cells[3+i,2]=dv[i].Row[0].ToString();
29worksheet.Cells[3+i,3]=dv[i].Row[1].ToString();
30
31excelOperate.SetBold(worksheet,worksheet.Cells[3+i,1],worksheet.Cells[3+i,1]);//黑体
32excelOperate.SetHAlignCenter(worksheet,worksheet.Cells[3+i,1],worksheet.Cells[3+i,3]);//居中
33worksheet.get_Range(worksheet.Cells[3+i,1],worksheet.Cells[3+i,3]).Borders.Color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
34
35}
36
37tick=DateTime.Now.Ticks.ToString();
38save_path=temp_path+"\\"+tick+".xls";
39workbook.SaveAs(save_path,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
40excelOperate.Dispose(worksheet,workbook,app);//关闭Excel进程
41
42}
43break;
44#endregion

效果如下:


2. 不读模板的话,调用的时候其实会继承一个空白模板,然后写入数据,程序画表头,最终达到一样的效果,程序如下:

1#region不使用模板生成Excel表
2case"ReportByNone":
3{
4
5DataViewdv=Cache["ReportByNone"]asDataView;
6//建立一个Excel.Application的新进程
7Excel.Applicationapp=newExcel.Application();
8if(app==null)
9{
10return;
11}
12app.Visible=false;
13app.UserControl=true;
14Workbooksworkbooks=app.Workbooks;
15_Workbookworkbook=workbooks.Add(XlWBATemplate.xlWBATWorksheet);//这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧)
16Sheetssheets=workbook.Worksheets;
17_Worksheetworksheet=(_Worksheet)sheets.get_Item(1);
18if(worksheet==null)
19{
20return;
21}
22
23worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,3]).Merge(Missing.Value);//横向合并
24worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,1]).Value2="导出EXCEL测试一";
25excelOperate.SetBold(worksheet,worksheet.Cells[1,1],worksheet.Cells[1,1]);//黑体
26excelOperate.SetHAlignCenter(worksheet,worksheet.Cells[1,1],worksheet.Cells[1,1]);//居中
27excelOperate.SetBgColor(worksheet,worksheet.Cells[1,1],worksheet.Cells[1,1],System.Drawing.Color.Red);//背景色
28excelOperate.SetFontSize(worksheet,worksheet.Cells[1,1],worksheet.Cells[1,1],16);//字体大小
29excelOperate.SetRowHeight(worksheet,worksheet.Cells[1,1],worksheet.Cells[1,1],32.25);//行高
30worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,1]).Borders.Color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
31
32worksheet.Cells[2,1]="序号";
33worksheet.Cells[2,2]="公司";
34worksheet.Cells[2,3]="部门";
35excelOperate.SetBold(worksheet,worksheet.Cells[2,1],worksheet.Cells[2,3]);//黑体
36worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[2,3]).Borders.Color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
37excelOperate.SetHAlignRight(worksheet,worksheet.Cells[2,1],worksheet.Cells[2,3]);
38excelOperate.SetBgColor(worksheet,worksheet.Cells[2,1],worksheet.Cells[2,3],System.Drawing.Color.Silver);//背景色
39introwNum=0;
40for(inti=0;i<dv.Count;i++)
41{
42rowNum=i+1;
43worksheet.Cells[3+i,1]=rowNum;
44worksheet.Cells[3+i,2]=dv[i].Row[0].ToString();
45worksheet.Cells[3+i,3]=dv[i].Row[1].ToString();
46
47excelOperate.SetBold(worksheet,worksheet.Cells[3+i,1],worksheet.Cells[3+i,1]);//黑体
48excelOperate.SetHAlignCenter(worksheet,worksheet.Cells[3+i,1],worksheet.Cells[3+i,3]);//居中
49worksheet.get_Range(worksheet.Cells[3+i,1],worksheet.Cells[3+i,3]).Borders.Color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色,不然打印预览,会非常不雅观
50
51}
52excelOperate.SetColumnWidth(worksheet,"A",10);
53excelOperate.SetColumnWidth(worksheet,"B",20);
54excelOperate.SetColumnWidth(worksheet,"C",20);
55worksheet.Name="导出EXCEL测试一";
56
57tick=DateTime.Now.Ticks.ToString();
58save_path=temp_path+"\\"+tick+".xls";
59workbook.SaveAs(save_path,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
60excelOperate.Dispose(worksheet,workbook,app);//关闭Excel进程
61
62}
63break;
64
65#endregion

效果如下:


以上我给了两个最简单的操作说明,下面详细说一下对于一些稍微复杂的报表的生成处理

二. 对于复杂的EXCEL报表的生成处理,无非是纵向合并相同的数据行及嵌套纵向合并等一些操作,下面就几个具有针对性的报表作下说明.
1.要生成相对复杂的EXCEL表,在从数据库取数据时,要注意先按照合理的要求排好序,有时候可能order by后面要跟好几个字段,而且这几个字段谁先谁后也要注意,因为这些会直接影响报表呈现的效果,比如你的EXCEL表要按月份统计国内外的项目,显示出来的时候要多个项目相同的人连续,那么排序就可能要这样order by 月份,项目类别,用户ID,项目ID(这是写好的视图,基于视图来检索的),这个排序的字段顺序就不能变了,变了的话就不太好生成想要的形式了,如下图:

这个也是动态画的,用了个简单的模板,模板就一个表头,没多大意义,除非表头很复杂而且在列表中不需要重画,考虑模板就比较好,向上面那个一月份国际的和其它月份的都是需要重画表头的。至于合并,如果不是嵌套的合并,我们可以在向模板循环写数据的时候直接控制,比如下面一个简单的写法:

1for(i=0;i<table.Rows.Count;i++)
2{
3bidName=table.Rows[index]["BIDNAME"].ToString();
4if(table.Rows[i]["BIDNAME"].ToString()==bidName)
5{
6projNum++;
7worksheet.Cells[5+i,2]=table.Rows[i]["PROJNO"];
8worksheet.Cells[5+i,3]=table.Rows[i]["PROJNAME"];
9worksheet.Cells[5+i,4]=table.Rows[i]["STAT_DATE"];
10worksheet.Cells[5+i,5]=table.Rows[i]["PROJTYPE"];
11worksheet.Cells[5+i,6]=table.Rows[i]["CONTENT"];
12worksheet.Cells[5+i,7]=table.Rows[i]["OPENDT"];
13worksheet.Cells[5+i,8]=table.Rows[i]["OPENADDRESS"];
14worksheet.Cells[5+i,9]=table.Rows[i]["REV_DATE"];
15worksheet.Cells[5+i,10]=table.Rows[i]["BID_UNIT"];
16worksheet.Cells[5+i,11]=table.Rows[i]["AGT_AMOUNT"];
17worksheet.Cells[5+i,12]=table.Rows[i]["CURRENCY"]+":"+table.Rows[i]["BIDSER_AMOUNT"];
18worksheet.Cells[5+i,13]=table.Rows[i]["SENDDATE"];
19worksheet.Cells[5+i,14]=table.Rows[i]["CURRENCY"]+":"+table.Rows[i]["BIDPRICE"];
20worksheet.Cells[5+i,15]=table.Rows[i]["BOOKAMOUNT"];
21worksheet.Cells[5+i,16]=table.Rows[i]["CURRENCY"]+":"+table.Rows[i]["BAIL_AMOUNT"];
22worksheet.Cells[5+i,17]=table.Rows[i]["USERNAME"];
23worksheet.Cells[5+i,18]=table.Rows[i]["SECOND_USER"];
24worksheet.Cells[5+i,19]="";
25worksheet.get_Range(worksheet.Cells[5+i,1],worksheet.Cells[5+i,19]).Borders.Color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
26continue;
27}
28
29worksheet.get_Range(worksheet.Cells[5+rowid,1],worksheet.Cells[5+i-1,1]).Merge(Missing.Value);//将第一列按投标单位合并
30worksheet.get_Range(worksheet.Cells[5+rowid,1],worksheet.Cells[5+rowid,1]).Value2=bidName+"("+projNum.ToString()+"个项目)";//合并后的单元格内容

合并单元格的时候也要注意一个问题,就是合并的单元格必须是为空的,不然在执行合并时,会提示“合并后的单元格的值将丢失”,具体不这样提示的,大致是这个意思,一般我们合并都单元格相同的内容,在合并前我们先保存那个值,再清空后合并,上面的代码中把worksheet.Cell[5+rowid,1]这里系列的单元格的值空出来了,没写数据,而且最后合并了再写值,避免了去循环清空。
2.嵌套的合并向上面那样做可能控制比较麻烦,而且思路可能很混乱,我们可以考虑先循环填充所有的数据,在循环出来要合并的列,比如像下面的这张表

先循环填充数据,如下:

1intindex=0,rownum=0;
2stringProjNo="";
3for(i=0;i<table.Rows.Count;i++)
4{
5ProjNo=table.Rows[index]["PROJNO"].ToString();
6if(table.Rows[i]["PROJNO"].ToString()==ProjNo)
7{
8wksheet.Cells[3+i,1]=rownum+1;
9wksheet.Cells[3+i,2]="'"+table.Rows[i]["PROJNO"];//加上单引号保证以0开头的字符原样输出
10wksheet.Cells[3+i,3]="'"+table.Rows[i]["PROJNAME"];
11wksheet.Cells[3+i,4]="'"+table.Rows[i]["PA_NAME"];
12wksheet.Cells[3+i,5]="'"+table.Rows[i]["BIDER_NAME"];
13wksheet.Cells[3+i,6]=table.Rows[i]["BAIL_AMOUNT"];
14wksheet.Cells[3+i,7]=table.Rows[i]["NOT_BACK"];
15wksheet.get_Range(wksheet.Cells[3+i,1],wksheet.Cells[3+i,7]).Borders.Color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
16continue;
17}
18
19index=i;
20rownum++;
21i--;
22
23}

下面合并前三列相同内容的单元:

padding-right: 5px; padding-left: 4px; font-size: 13px; padding-bottom: 4px; width: 98%
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics