ASP.NET操作EXCEL

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

此文转自（http://www.cnblogs.com/peaceli/archive/2008/04/13/1151520.html ，特此申明，因最近在项目中要用到Excel单元格合并，在此感谢）

公元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表
2                 case "ReportByTemp":
3                     {
4
5                         DataView dv = Cache["ReportByTemp"] as DataView;
6                         //建立一个Excel.Application的新进程
7                         Excel.Application app = new Excel.Application();
8                         if (app == null)
9                         {
10                             return;
11                         }
12                         app.Visible = false;
13                         app.UserControl = true;
14                         Workbooks workbooks = app.Workbooks;
15                         _Workbook workbook = workbooks.Add(template_path + "\\EXCEL测试模板.xls");//这里的Add方法里的参数就是模板的路径
16                         Sheets sheets = workbook.Worksheets;
17                         _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//模板只有一个sheet表
18                         if (worksheet == null)
19                         {
20                             return;
21                         }
22
23                         int rowNum = 0;
24                         for (int i = 0; i < dv.Count; i++)
25                         {
26                             rowNum = i + 1;
27                             worksheet.Cells[3 + i, 1] = rowNum;
28                             worksheet.Cells[3 + i, 2] = dv[i].Row[0].ToString();
29                             worksheet.Cells[3 + i, 3] = dv[i].Row[1].ToString();
30
31                             excelOperate.SetBold(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 1]); //黑体
32                             excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]);//居中
33                             worksheet.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
37                         tick = DateTime.Now.Ticks.ToString();
38                         save_path = temp_path + "\\" + tick + ".xls";
39                         workbook.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);
40                         excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
41
42                     }
43                     break;
44                 #endregion

效果如下：

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

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

效果如下：

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

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

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

1  for (i = 0; i < table.Rows.Count; i++)
2                             {
3                                 bidName = table.Rows[index]["BIDNAME"].ToString();
4                                 if (table.Rows[i]["BIDNAME"].ToString() == bidName)
5                                 {
6                                     projNum++;
7                                     worksheet.Cells[5 + i, 2] = table.Rows[i]["PROJNO"];
8                                     worksheet.Cells[5 + i, 3] = table.Rows[i]["PROJNAME"];
9                                     worksheet.Cells[5 + i, 4] = table.Rows[i]["STAT_DATE"];
10                                     worksheet.Cells[5 + i, 5] = table.Rows[i]["PROJTYPE"];
11                                     worksheet.Cells[5 + i, 6] = table.Rows[i]["CONTENT"];
12                                     worksheet.Cells[5 + i, 7] = table.Rows[i]["OPENDT"];
13                                     worksheet.Cells[5 + i, 8] = table.Rows[i]["OPENADDRESS"];
14                                     worksheet.Cells[5 + i, 9] = table.Rows[i]["REV_DATE"];
15                                     worksheet.Cells[5 + i, 10] = table.Rows[i]["BID_UNIT"];
16                                     worksheet.Cells[5 + i, 11] = table.Rows[i]["AGT_AMOUNT"];
17                                     worksheet.Cells[5 + i, 12] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDSER_AMOUNT"];
18                                     worksheet.Cells[5 + i, 13] = table.Rows[i]["SENDDATE"];
19                                     worksheet.Cells[5 + i, 14] = table.Rows[i]["CURRENCY"] + ":" + table.Rows[i]["BIDPRICE"];
20                                     worksheet.Cells[5 + i,