告別ASPNET操作EXCEL的煩惱(總結(jié)篇)

1、Copy From 告別ASP.NET操作EXCEL的煩惱(總結(jié)篇)公元19XX年前，關(guān)于EXCEL的操作就如滔滔江水，連綿不絕，真正操作EXCEL我也是從去年下半年開始的，有些比較復(fù)雜的年度報表之類的，做起來也有點費力，不過還是都能畫出來了，關(guān)于EXCEL的報表導(dǎo)出，考慮到導(dǎo)出耗時的問題我主要采用AJAX來做的，分別捕捉幾個起止狀態(tài)，給客戶端提示3個狀態(tài)：正在檢索數(shù)據(jù)。-準備導(dǎo)出數(shù)據(jù)。(只是從數(shù)據(jù)庫成功取出，還沒有讀寫excel文件)-正在讀寫文件-導(dǎo)出數(shù)據(jù)成功，當然如果哪一過程出錯，都有對應(yīng)的提示，只所以想到寫這篇文章，主要是因為今年有個系統(tǒng)的部分EXCEL的操作也讓我做，順便結(jié)

2、合之前操作EXCEL的經(jīng)驗作一下總結(jié)，可能也算不上什么，對于絕大多數(shù)來說也沒什么技術(shù)含量，網(wǎng)上一搜一大把，但我想還是有必要總結(jié)一下，至少能給園子里的新手些許幫助，OK,Let's Go.   一. 程序操作EXCEL的應(yīng)用主要還是在統(tǒng)計報表方面，您可能會考慮讀EXCEL模板，也可能會考慮沒必要讀模板，其實讀不讀模板都能達到一樣的效果，看實際情況而用了。       1. 讀模板的話，首先模板存放在某個路徑下，根據(jù)模板把從數(shù)據(jù)庫里取出的數(shù)據(jù)寫回EXCEL然后生成一個新的EXCEL存放都另一個路徑以供

3、下載，模板不變。          我這里的EXCEL操作主要是在VS2005里的，VS2003也可以的，不過沒怎么研究03里的操作(文章最后我會把05，03的示例下載地址貼上）vs05中操作EXCEL直接引用.NET自帶的COM組件，添加后項目的bin目錄下會自動出現(xiàn)Interop.Excel.dll這個DLL（需安裝office2003 excel，下面的說明及示例都是基于office2003的，版本不同調(diào)用可能會不一樣）頁面的命名空間引用 using Excel;下面是調(diào)用模板的一段代碼 

4、1#region 使用模板導(dǎo)出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方法里的參數(shù)就是模板的路徑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);/關(guān)閉Excel進程4142                    43break;44#endregion效

24、果如下：       2. 不讀模板的話，調(diào)用的時候其實會繼承一個空白模板，然后寫入數(shù)據(jù)，程序畫表頭，最終達到一樣的效果，程序如下： 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方法里的參數(shù)就相當于繼承了一個空模板(暫這樣理解吧)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 ="導(dǎo)出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);/黑色連續(xù)邊框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);/設(shè)置邊框顏色，不然打印預(yù)覽，會非常不雅觀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 ="導(dǎo)出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);/關(guān)閉Excel進程6162                    63break;6465#endregion效果如下：以上我給了兩個最簡單的操作說明，下面詳細說一下對于一些稍微復(fù)雜的報表的生成處理  

60、60;   二. 對于復(fù)雜的EXCEL報表的生成處理，無非是縱向合并相同的數(shù)據(jù)行及嵌套縱向合并等一些操作，下面就幾個具有針對性的報表作下說明.            1.要生成相對復(fù)雜的EXCEL表，在從數(shù)據(jù)庫取數(shù)據(jù)時，要注意先按照合理的要求排好序，有時候可能order by后面要跟好幾個字段，而且這幾個字段誰先誰后也要注意，因為這些會直接影響報表呈現(xiàn)的效果，比如你的EXCEL表要按月份統(tǒng)計國內(nèi)外的項目，顯示出來的時候要多個項目相同的人連續(xù)，那么排序就可能要這樣or

61、der by 月份，項目類別，用戶ID，項目ID（這是寫好的視圖，基于視圖來檢索的），這個排序的字段順序就不能變了，變了的話就不太好生成想要的形式了，如下圖：這個也是動態(tài)畫的，用了個簡單的模板，模板就一個表頭，沒多大意義，除非表頭很復(fù)雜而且在列表中不需要重畫，考慮模板就比較好，向上面那個一月份國際的和其它月份的都是需要重畫表頭的。至于合并，如果不是嵌套的合并，我們可以在向模板循環(huán)寫數(shù)據(jù)的時候直接控制，比如下面一個簡單的寫法： 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() +"個項目)"/合并后的單元格內(nèi)容合并單元格的時候也要注意一個問題，就是合并的單元格

88、必須是為空的，不然在執(zhí)行合并時，會提示“合并后的單元格的值將丟失”，具體不這樣提示的，大致是這個意思，一般我們合并都單元格相同的內(nèi)容，在合并前我們先保存那個值，再清空后合并，上面的代碼中把worksheet.Cell5+rowid,1這里系列的單元格的值空出來了，沒寫數(shù)據(jù)，而且最后合并了再寫值，避免了去循環(huán)清空。     2.嵌套的合并向上面那樣做可能控制比較麻煩，而且思路可能很混亂，我們可以考慮先循環(huán)填充所有的數(shù)據(jù)，在循環(huán)出來要合并的列，比如像下面的這張表先循環(huán)填充數(shù)據(jù)，如下： 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                            下面合并前三列相同內(nèi)容的單元： 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/跳出循環(huán)后合并最后一個招標項目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/退出循環(huán)時合并最后一個項目的標段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);/關(guān)閉Excel進程29/DownLoad(save_path);30/Page_Close();當然，上面的操作中會進行好幾次循環(huán)，在性能方面不太可取，園子里的兄弟也許會有更好的方法，小弟不吝賜教了下面我們看下幾個效果圖：（注意：這里提示的導(dǎo)出數(shù)據(jù)是指從數(shù)據(jù)庫成功取出數(shù)據(jù)，還沒有操作EXCEL對象，剛開始已經(jīng)說過了，當然這個提示文字換成其它的也可以）整個過程采用AJAX提示的，一來不刷新，二來導(dǎo)出時間比較長的話，可以給客戶一個良好的體驗效果，否可，用戶一點導(dǎo)出按鈕，半天沒反應(yīng)也沒提示，客戶就覺得怎么這么慢的，是不是你們程序有問題，指責一

145、大堆，有了這么些交互提示信息，讓客戶多等幾分鐘也能承受。     3.生成的表格包含多個sheet的操作，比如下面一種情況繪制這張表的要求是根據(jù)選擇某年的幾月到幾月，生成這個幾個月的一個綜合情況的sheet，然后分別生成這幾個月的單獨的sheet表，生成上面表的模板，包含兩個sheet ，一個綜合月份的sheet和一個單獨月份的sheet，因為單獨月份的sheet表現(xiàn)形式都是一樣的，我們可以根據(jù)選擇的月份個數(shù)Copy幾個sheet就可以了 1 Workbooks workbooks = app.Workb

146、ooks; 2 3                            _Workbook workbook = workbooks.Add(template_path +"招標單位年度招標情況逐月統(tǒng)計表.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);/月統(tǒng)計工作薄Yearsheet的操作就不說了，和前面幾個一樣操作，關(guān)鍵是月份的sheet的生成，其實就是循環(huán)操作get_Item(

152、i)，代碼如下 1/每月詳細統(tǒng)計/ 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