告别ASPNET操作EXCEL的烦恼(总结篇)

1、Copy From 告别ASP.NET操作EXCEL的烦恼(总结篇)公元19XX年前，关于EXCEL的操作就如滔滔江水，连绵不绝，真正操作EXCEL我也是从去年下半年开始的，有些比较复杂的年度报表之类的，做起来也有点费力，不过还是都能画出来了，关于EXCEL的报表导出，考虑到导出耗时的问题我主要采用AJAX来做的，分别捕捉几个起止状态，给客户端提示3个状态：正在检索数据。-准备导出数据。(只是从数据库成功取出，还没有读写excel文件)-正在读写文件-导出数据成功，当然如果哪一过程出错，都有对应的提示，只所以想到写这篇文章，主要是因为今年有个系统的部分EXCEL的操作也让我做，顺便结

2、合之前操作EXCEL的经验作一下总结，可能也算不上什么，对于绝大多数来说也没什么技术含量，网上一搜一大把，但我想还是有必要总结一下，至少能给园子里的新手些许帮助，OK,Let's Go.   一. 程序操作EXCEL的应用主要还是在统计报表方面，您可能会考虑读EXCEL模板，也可能会考虑没必要读模板，其实读不读模板都能达到一样的效果，看实际情况而用了。       1. 读模板的话，首先模板存放在某个路径下，根据模板把从数据库里取出的数据写回EXCEL然后生成一个新的EXCEL存放都另一个路径以供

3、下载，模板不变。          我这里的EXCEL操作主要是在VS2005里的，VS2003也可以的，不过没怎么研究03里的操作(文章最后我会把05，03的示例下载地址贴上）vs05中操作EXCEL直接引用.NET自带的COM组件，添加后项目的bin目录下会自动出现Interop.Excel.dll这个DLL（需安装office2003 excel，下面的说明及示例都是基于office2003的，版本不同调用可能会不一样）页面的命名空间引用 using Excel;下面是调用模板的一段代码 

4、1#region 使用模板导出Excel表 2case"ReportByTemp": 3                     4 5                &

5、#160;       DataView dv = Cache"ReportByTemp" as DataView; 6/建立一个Excel.Application的新进程 7                      

6、  Excel.Application app =new Excel.Application(); 8if (app =null) 9                        10return;11     

7、60;                  12                        app.Visible =false;13   

8、;                     app.UserControl =true;14                        

9、Workbooks workbooks = app.Workbooks;15                        _Workbook workbook = workbooks.Add(template_path +"EXCEL测试模板.xls");/这里的Ad

10、d方法里的参数就是模板的路径16                        Sheets sheets = workbook.Worksheets;17              

11、60;         _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);/模板只有一个sheet表18if (worksheet =null)19                     &

12、#160;  20return;21                        2223int rowNum =0;24for (int i =0; i < span> dv.Count; i+)25   

13、;                     26                            row

14、Num = i +1;27                            worksheet.Cells3+ i, 1 = rowNum;28        &#

15、160;                   worksheet.Cells3+ i, 2 = dvi.Row0.ToString();29                   &

16、#160;        worksheet.Cells3+ i, 3 = dvi.Row1.ToString();3031                            excelOpera

17、te.SetBold(worksheet, worksheet.Cells3+ i, 1, worksheet.Cells3+ i, 1); /黑体32                            excelOperate.SetHAlig

18、nCenter(worksheet, worksheet.Cells3+ i, 1, worksheet.Cells3+ i, 3);/居中33                            worksheet.get_Range(worksheet.

19、Cells3+ i, 1, worksheet.Cells3+ i, 3).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);3435                       

20、0;3637                        tick = DateTime.Now.Ticks.ToString();38                 

21、;       save_path = temp_path +""+ tick +".xls"39                        workbook.SaveAs(save_path,&

22、#160;Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);40         &#

23、160;              excelOperate.Dispose(worksheet, workbook, app);/关闭Excel进程4142                    43break;44#endregion效

24、果如下：       2. 不读模板的话，调用的时候其实会继承一个空白模板，然后写入数据，程序画表头，最终达到一样的效果，程序如下： 1#region 不使用模板生成Excel表 2case"ReportByNone": 3                    

25、60;4 5                        DataView dv = Cache"ReportByNone" as DataView; 6/建立一个Excel.Application的新进程 7    

26、                    Excel.Application app =new Excel.Application(); 8if (app =null) 9             &#

27、160;          10return;11                        12             &

28、#160;          app.Visible =false;13                        app.UserControl =true;14      

29、                  Workbooks workbooks = app.Workbooks;15                       &

30、#160;_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);/这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧)16                        Sheets sheets = work

31、book.Worksheets;17                        _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);18if (worksheet =null)19      

32、;                  20return;21                        2223     &#

33、160;                  worksheet.get_Range(worksheet.Cells1, 1, worksheet.Cells1, 3).Merge(Missing.Value); /横向合并24             

34、;           worksheet.get_Range(worksheet.Cells1, 1, worksheet.Cells1, 1).Value2 ="导出EXCEL测试一"25                    

35、;    excelOperate.SetBold(worksheet, worksheet.Cells1, 1, worksheet.Cells1, 1); /黑体26                        excelOperate.SetHAlignCent

36、er(worksheet, worksheet.Cells1, 1, worksheet.Cells1, 1);/居中27                        excelOperate.SetBgColor(worksheet, worksheet.Cells1, 1, work

37、sheet.Cells1, 1, System.Drawing.Color.Red);/背景色28                        excelOperate.SetFontSize(worksheet, worksheet.Cells1, 1, worksheet.Cells1, 1,

38、 16);/字体大小29                        excelOperate.SetRowHeight(worksheet, worksheet.Cells1, 1, worksheet.Cells1, 1, 32.25);/行高30    

39、;                    worksheet.get_Range(worksheet.Cells1, 1, worksheet.Cells1, 1).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);/黑色连续边框3132

40、                        worksheet.Cells2, 1 ="序号"33                  &

41、#160;     worksheet.Cells2, 2 ="公司"34                        worksheet.Cells2, 3 ="部门"35    

42、60;                   excelOperate.SetBold(worksheet, worksheet.Cells2, 1, worksheet.Cells2, 3); /黑体36             

43、           worksheet.get_Range(worksheet.Cells2, 1, worksheet.Cells2, 3).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);37          

44、0;             excelOperate.SetHAlignRight(worksheet, worksheet.Cells2, 1, worksheet.Cells2, 3);38                   

45、60;    excelOperate.SetBgColor(worksheet, worksheet.Cells2, 1, worksheet.Cells2, 3, System.Drawing.Color.Silver);/背景色39int rowNum =0;40for (int i =0; i < span> dv.Count; i+)41     

46、;                   42                            rowNum =&#

47、160;i +1;43                            worksheet.Cells3+ i, 1 = rowNum;44          &#

48、160;                 worksheet.Cells3+ i, 2 = dvi.Row0.ToString();45                     &

49、#160;      worksheet.Cells3+ i, 3 = dvi.Row1.ToString();4647                            excelOperate.SetBold(w

50、orksheet, worksheet.Cells3+ i, 1, worksheet.Cells3+ i, 1); /黑体48                            excelOperate.SetHAlignCenter(work

51、sheet, worksheet.Cells3+ i, 1, worksheet.Cells3+ i, 3);/居中49                            worksheet.get_Range(worksheet.Cells3+ 

52、;i, 1, worksheet.Cells3+ i, 3).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);/设置边框颜色，不然打印预览，会非常不雅观5051                      &

53、#160; 52                        excelOperate.SetColumnWidth(worksheet, "A", 10);53            &

54、#160;           excelOperate.SetColumnWidth(worksheet, "B", 20);54                        excelOperate.Se

55、tColumnWidth(worksheet, "C", 20);55                        worksheet.Name ="导出EXCEL测试一"5657         

56、               tick = DateTime.Now.Ticks.ToString();58                        save_path =&

57、#160;temp_path +""+ tick +".xls"59                        workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value,&

58、#160;Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);60                   &#

59、160;    excelOperate.Dispose(worksheet, workbook, app);/关闭Excel进程6162                    63break;6465#endregion效果如下：以上我给了两个最简单的操作说明，下面详细说一下对于一些稍微复杂的报表的生成处理  

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

61、der by 月份，项目类别，用户ID，项目ID（这是写好的视图，基于视图来检索的），这个排序的字段顺序就不能变了，变了的话就不太好生成想要的形式了，如下图：这个也是动态画的，用了个简单的模板，模板就一个表头，没多大意义，除非表头很复杂而且在列表中不需要重画，考虑模板就比较好，向上面那个一月份国际的和其它月份的都是需要重画表头的。至于合并，如果不是嵌套的合并，我们可以在向模板循环写数据的时候直接控制，比如下面一个简单的写法： 1for (i =0; i < span> table.Rows.Count; i+)&

62、#160;2                             3                    

63、;            bidName = table.Rowsindex"BIDNAME".ToString(); 4if (table.Rowsi"BIDNAME".ToString() = bidName) 5            

64、0;                    6                             

65、60;      projNum+; 7                                    worksheet.Cells5+ i,

66、0;2 = table.Rowsi"PROJNO" 8                                    worksheet.Cells5+ i, 3

67、 = table.Rowsi"PROJNAME" 9                                    worksheet.Cells5+ i, 4&

68、#160;= table.Rowsi"STAT_DATE"10                                    worksheet.Cells5+ i, 5 

69、= table.Rowsi"PROJTYPE"11                                    worksheet.Cells5+ i, 6 = 

70、;table.Rowsi"CONTENT"12                                    worksheet.Cells5+ i, 7 = table.

71、Rowsi"OPENDT"13                                    worksheet.Cells5+ i, 8 = table.Rowsi&qu

72、ot;OPENADDRESS"14                                    worksheet.Cells5+ i, 9 = table.Rowsi"

73、REV_DATE"15                                    worksheet.Cells5+ i, 10 = table.Rowsi"BID_U

74、NIT"16                                    worksheet.Cells5+ i, 11 = table.Rowsi"AGT_AMOUNT

75、"17                                    worksheet.Cells5+ i, 12 = table.Rowsi"CURRENCY"

76、; +":"+ table.Rowsi"BIDSER_AMOUNT"18                                    worksheet.Cells5+&

77、#160;i, 13 = table.Rowsi"SENDDATE"19                                    worksheet.Cells5+ 

78、i, 14 = table.Rowsi"CURRENCY" +":"+ table.Rowsi"BIDPRICE"20                               

79、     worksheet.Cells5+ i, 15 = table.Rowsi"BOOKAMOUNT"21                               

80、60;    worksheet.Cells5+ i, 16 = table.Rowsi"CURRENCY" +":"+ table.Rowsi"BAIL_AMOUNT"22                      &

81、#160;             worksheet.Cells5+ i, 17 = table.Rowsi"USERNAME"23                       

82、             worksheet.Cells5+ i, 18 = table.Rowsi"SECOND_USER"24                       &#

83、160;            worksheet.Cells5+ i, 19 =""25                            &#

84、160;       worksheet.get_Range(worksheet.Cells5+ i, 1, worksheet.Cells5+ i, 19).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);26continue;27         &#

85、160;                      2829                          

86、0;     worksheet.get_Range(worksheet.Cells5+ rowid, 1, worksheet.Cells5+ i -1, 1).Merge(Missing.Value); /将第一列按投标单位合并30                    &#

87、160;           worksheet.get_Range(worksheet.Cells5+ rowid, 1, worksheet.Cells5+ rowid, 1).Value2 = bidName +"("+ projNum.ToString() +"个项目)"/合并后的单元格内容合并单元格的时候也要注意一个问题，就是合并的单元格

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

89、wnum =0; 2string ProjNo ="" 3for (i =0; i < span> table.Rows.Count; i+) 4                         &

90、#160;   5                                ProjNo = table.Rowsindex"PROJNO".ToString(); 6if (tabl

91、e.Rowsi"PROJNO".ToString() = ProjNo) 7                                 8      &#

92、160;                             wksheet.Cells3+ i, 1 = rownum +1; 9         

93、60;                          wksheet.Cells3+ i, 2 ="'"+ table.Rowsi"PROJNO"   /加上单引号保证以0开头的字符原样输出10  

94、                                  wksheet.Cells3+ i, 3 ="'"+ table.Rowsi"PROJNAME"11&#

95、160;                                   wksheet.Cells3+ i, 4 ="'"+ table.Rowsi"PA_NAME&

96、quot;12                                    wksheet.Cells3+ i, 5 ="'"+ table.Rowsi"

97、;BIDER_NAME"13                                    wksheet.Cells3+ i, 6 = table.Rowsi"BAIL_

98、AMOUNT"14                                    wksheet.Cells3+ i, 7 = table.Rowsi"NOT_BACK&q

99、uot;15                                    wksheet.get_Range(wksheet.Cells3+ i, 1, wksheet.Cells3+ i,

100、 7).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);16continue;17                               &

101、#160;1819                                index = i;20             

102、;                   rownum+;21                             &

103、#160;  i-;2223                            下面合并前三列相同内容的单元： 1/合并前三列操作 2int m =1, rowid =3, k; 3string projN

104、ame ="" 4for (k =3; k < span> i +2; k+) 5                             6if (Convert.T

105、oInt32(wksheet.get_Range(wksheet.Cellsk, 1, wksheet.Cellsk, 1).Value2) = m) 7                                &

106、#160;8                                    ProjNo = wksheet.get_Range(wksheet.Cellsk, 2, wksheet.Cell

107、sk, 2).Value2.ToString(); 9                                    projName = wksheet.get_Range(wksheet.

108、Cellsk, 3, wksheet.Cellsk, 3).Value2.ToString();10                                    wksheet.get_Range(w

109、ksheet.Cellsk, 1, wksheet.Cellsk, 1).Value2 =""11                                    wkshe

110、et.get_Range(wksheet.Cellsk, 2, wksheet.Cellsk, 2).Value2 =""12                                  

111、60; wksheet.get_Range(wksheet.Cellsk, 3, wksheet.Cellsk, 3).Value2 =""13continue;14                              &#

112、160; 15                                wksheet.get_Range(wksheet.Cellsrowid, 1, wksheet.Cellsk -1, 1).Merge(Missing

113、.Value);16                                wksheet.get_Range(wksheet.Cellsrowid, 1, wksheet.Cellsrowid, 1).Value2 = 

114、m;1718                                wksheet.get_Range(wksheet.Cellsrowid, 2, wksheet.Cellsk -1, 2).Merge(Missing.Value

115、);19                                wksheet.get_Range(wksheet.Cellsrowid, 2, wksheet.Cellsrowid, 2).Value2 ="'&

116、quot;+ ProjNo;2021                                wksheet.get_Range(wksheet.Cellsrowid, 3, wksheet.Cellsk -1, 3).Me

117、rge(Missing.Value);22                                wksheet.get_Range(wksheet.Cellsrowid, 3, wksheet.Cellsrowid, 3).Value2&#

118、160;="'"+ projName;2324                                m+;25          &

119、#160;                     rowid = k;26                        

120、60;       k-;27                            28/跳出循环后合并最后一个招标项目2930          

121、                  wksheet.get_Range(wksheet.Cellsrowid, 1, wksheet.Cellsk -1, 1).Merge(Missing.Value);31              &#

122、160;             wksheet.get_Range(wksheet.Cellsrowid, 1, wksheet.Cellsrowid, 1).Value2 = m;3233                   

123、         wksheet.get_Range(wksheet.Cellsrowid, 2, wksheet.Cellsk -1, 2).Merge(Missing.Value);34                       &#

124、160;    wksheet.get_Range(wksheet.Cellsrowid, 2, wksheet.Cellsrowid, 2).Value2 ="'"+ ProjNo;3536                        &

125、#160;   wksheet.get_Range(wksheet.Cellsrowid, 3, wksheet.Cellsk -1, 3).Merge(Missing.Value);37                            wks

126、heet.get_Range(wksheet.Cellsrowid, 3, wksheet.Cellsrowid, 3).Value2 ="'"+ projName;下面合并标段列 1/合并标段列 2 3                        

127、    index =0; rowid =3; /重置变量 4string pa_name =string.Empty; /标段名称 5for (k =3; k < span> i +2; k+) 6               

128、;              7                                pa_name = ta

129、ble.Rowsindex"PA_NAME".ToString(); 8if (wksheet.get_Range(wksheet.Cellsk, 4, wksheet.Cellsk, 4).Value2.ToString() = pa_name) 9                    

130、0;           10                                    wksheet.get_Ra

131、nge(wksheet.Cellsk, 4, wksheet.Cellsk, 4).Value2 =""11continue;12                                13  

132、;                              wksheet.get_Range(wksheet.Cellsrowid, 4, wksheet.Cellsk -1, 4).Merge(Missing.Value);14  &

133、#160;                             wksheet.get_Range(wksheet.Cellsrowid, 4, wksheet.Cellsrowid, 4).Value2 ="'"+ pa_na

134、me;15                                index = k -3;16            &

135、#160;                   rowid = k;17                          

136、60;     k-;1819                            20/退出循环时合并最后一个项目的标段21            

137、;                wksheet.get_Range(wksheet.Cellsrowid, 4, wksheet.Cellsk -1, 4).Merge(Missing.Value);22                &

138、#160;           wksheet.get_Range(wksheet.Cellsrowid, 4, wksheet.Cellsrowid, 4).Value2 ="'"+ pa_name;23                 &

139、#160;          tick = DateTime.Now.ToString("yyyyMMddhhmmss");24                           

140、0;save_path = temp_path +""+ tick +"保证金收退情况表.xls"25                            Session"BailBackID" =

141、 tick +"保证金收退情况表.xls"26                            Session"_BailBack" ="true"27      

142、                      workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,

143、 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);28                            excelOperate.Dispose(workshee

144、t, workbook, app);/关闭Excel进程29/DownLoad(save_path);30/Page_Close();当然，上面的操作中会进行好几次循环，在性能方面不太可取，园子里的兄弟也许会有更好的方法，小弟不吝赐教了下面我们看下几个效果图：（注意：这里提示的导出数据是指从数据库成功取出数据，还没有操作EXCEL对象，刚开始已经说过了，当然这个提示文字换成其它的也可以）整个过程采用AJAX提示的，一来不刷新，二来导出时间比较长的话，可以给客户一个良好的体验效果，否可，用户一点导出按钮，半天没反应也没提示，客户就觉得怎么这么慢的，是不是你们程序有问题，指责一

145、大堆，有了这么些交互提示信息，让客户多等几分钟也能承受。     3.生成的表格包含多个sheet的操作，比如下面一种情况绘制这张表的要求是根据选择某年的几月到几月，生成这个几个月的一个综合情况的sheet，然后分别生成这几个月的单独的sheet表，生成上面表的模板，包含两个sheet ，一个综合月份的sheet和一个单独月份的sheet，因为单独月份的sheet表现形式都是一样的，我们可以根据选择的月份个数Copy几个sheet就可以了 1 Workbooks workbooks = app.Workb

146、ooks; 2 3                            _Workbook workbook = workbooks.Add(template_path +"招标单位年度招标情况逐月统计表.xls"); 4

147、60;                           Sheets sheets = workbook.Worksheets; 5             

148、;               _Worksheet Yearsheet = (_Worksheet)sheets.get_Item(1); 6                      &

149、#160;     _Worksheet worksheet = (_Worksheet)sheets.get_Item(2); 7if (worksheet =null) 8                         &#

150、160;   9return;10                            11for (int i =1; i < span> monthCount; i+)12  

151、60;                             worksheet.Copy(Missing.Value, workbook.Worksheets2);/月统计工作薄Yearsheet的操作就不说了，和前面几个一样操作，关键是月份的sheet的生成，其实就是循环操作get_Item(

152、i)，代码如下 1/每月详细统计/ 2 3int item_id =2; 4                            rowNum =0; book_Amount =0; index =0;

153、60;5                            bid_Amount ="" bidser_Amount ="" agent_Amount =0;/清空变量 6   

154、0;                        _Worksheet ws =null; 7for (int i =0; i < span> tableMM.Rows.Count; i+) 8