C#后臺創(chuàng)建Excel文件的那點事兒

2019-11-17 03:54:12

字體：大中小

供稿：網(wǎng)友

使用C#在應(yīng)用中生成Excel文檔，甚至再提供直接下載功能，算是一個比較常用的應(yīng)用，這里記錄下自己遇到的幾種方法，還有一些需要注意的地方：

    a) 首先定義一個數(shù)據(jù)類：

代碼
    public class DataContent
    {
        public string Name { set; get; }
        public string Address { set; get; }
        public string Phone { set; get; }

        public string GetContentByIndex(int index)
        {
            string Value = string.Empty;
            switch (index)
            {
                case 1:
                    Value = Name;
                    break;
                case 2:
                    Value = Address;
                    break;
                case 3:
                    Value = Phone;
                    break;
                default:
                    Value = "";
                    break;
            }
            return Value;
        }
    }

     b) 最簡方法：直接通過拼湊HTML字符串來生成excel文件，優(yōu)點是：代碼十分方便，無需任何的office接口擴展即可工作；不足為：覆蓋的單元格有限，雖然可以通過中間方法來設(shè)置，但仍存在打開時提示格式不正確的問題，隨不影響正常瀏覽及編輯，但對于一些需要通過代碼操作excel文件的應(yīng)用，可能會遇到問題，比如通過OleDB來讀取內(nèi)容的時候等。

1 Public void GetExcelDataToClient()
2 {
3             List<DataContent> arrDatas = new List<DataContent>() {
4             new DataContent() { Address="aaa", Name="aaa", Phone="aaa" },
5             new DataContent() { Address="bbb", Phone="bbb", Name="bbb" },
6             new DataContent() { Name="ccc", Phone="ccc", Address="ccc"}
7             };
8
9             StringBuilder sb = new StringBuilder("<HTML xmlns:o=/"urn:schemas-microsoft-com:office:office/" xmlns:x=/"urn:schemas-microsoft-com:office:excel/" xmlns=/"http://www.w3.org/TR/REC-html40/"><HEAD><META HTTP-EQUIV=/"Content-Type/" content=/"text/html; charset=gb_2312-80/"><TITLE></TITLE><style>td{mso-number-format:/"//@/";}</style></HEAD><BODY><TABLE border=/"1/">", 500);
10             // title columns.
11             sb.Append(string.Format("<TR><TD><B>Event_CourseId<B/></TD><TD><B>UserId</B></TD><TD><B>Attended</B></TD>{0}</TR>", GetExCells(30)));
12             // content columns.
13             foreach (DataContent data in arrDatas)
14                 sb.Append(string.Format("<TR><TD>{0}</TD><TD>{1}</TD><TD>{2}</TD>{3}</TR>", data.Name, data.Address, data.Phone, GetExCells(30)));
15             // add extension rows.
16             sb.Append(GetExRows(50, 33));
17             // last part.
18             sb.Append("</TABLE></BODY></HTML>");
19             // response the result as a excel file.
20             Response.ContentType = "application/vnd.ms-excel";
21             Response.AddHeader("Content-Disposition", "attachment; filename=DataList.xls;filetype=excel");
22             Response.ContentEncoding = Encoding.GetEncoding("utf-8");
23             Response.Write(sb.ToString());
24             Response.Flush();
25             Response.End();
26 }
27         // add the extension cells.
28         PRivate string GetExCells(int num)
29         {
30             StringBuilder sb = new StringBuilder(200);
31             for (int i = 0; i < num; i++)
32                 sb.Append("<TD></TD>");
33             return sb.ToString();
34         }
35         // add the extension rows.
36         private string GetExRows(int rowNum, int colNum)
37         {
38             StringBuilder sb = new StringBuilder(200);
39             for (int i = 0; i < rowNum; i++)
40                 sb.Append(string.Format("<TR>{0}</TR>", GetExCells(colNum)));
41             return sb.ToString();
42 }

    c) 通用接口方法：主要通過調(diào)用Office提供的接口組件Microsoft.Office.Interop.Excel，來生成excel文件，其特點是可以消除內(nèi)容單元格覆蓋不全的問題，而且也可以消除打開提示格式異常的問題，但針對通過OleDB來操作文件的情況，還必須按照一種比較嚴格的方式進行編寫才成，否則就會出現(xiàn)操作過程中格式異常的問題，下面為標準的寫法：

1 private void Test4()
2         {
3             string filePath = @"C:/Test.xls";
4             List<DataContent> arrDatas = new List<DataContent>() {
5             new DataContent() { Address="aaa", Name="aaa", Phone="aaa" },
6             new DataContent() { Address="bbb", Phone="bbb", Name="bbb" },
7             new DataContent() { Name="ccc", Phone="ccc", Address="ccc"}
8             };
9
10             object objOpt = System.Reflection.Missing.Value;
11             Excel.Application objExcel = null;
12             Excel.Workbooks objBooks = null;
13             Excel.Workbook objBook = null;
14             try
15             {
16                 try
17                 {
18                     objExcel = new Excel.Application();
19                 }
20                 catch (Exception ex)
21                 {
22                     Console.Write(ex.Message);
23                 }
24
25                 objBooks = (Excel.Workbooks)objExcel.Workbooks;
26                 objBook = (Excel.Workbook)(objBooks.Add(objOpt));
27                 // Add data to cells of the first worksheet in the new workbook.
28                 Excel.Sheets objSheets = (Excel.Sheets)objBook.Worksheets;
29                 Excel.Worksheet objSheet = (Excel.Worksheet)(objSheets.get_Item(1));
30                 objSheet.Name = "DataList";
31                 objSheet.Cells[1, 1] = "Name";
32                 objSheet.Cells[1, 2] = "Address";
33                 objSheet.Cells[1, 3] = "Phone";
34                 Excel.Range objRange = objSheet.get_Range(objSheet.Cells[1, 1], objSheet.Cells[1, 3]);
35                 //objRange.Font.Bold = true;
36                 objRange.Font.Size = 18;
37                 objRange.Interior.ColorIndex = 37;
38
39                 for (int i = 1; i < 4; i++)
40                 {
41                     objRange = objSheet.get_Range(objSheet.Cells[2, i], objSheet.Cells[2 + arrDatas.Count, i]);
42                     objRange.NumberFormatLocal = "@";
43                     for (int j = 0; j < arrDatas.Count; j++)
44                         objSheet.Cells[2 + j, i] = arrDatas[j].GetContentByIndex(i);
45                 }
46                 objSheet.Columns.AutoFit();
47                 if (System.IO.File.Exists(filePath))
48                     System.IO.File.Delete(filePath);
49                 // Save the file.
50                 objBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, objOpt, objOpt,
51                           objOpt, objOpt, Excel.XlSaveAs accessMode.xlNoChange,
52                          objOpt, objOpt, objOpt, objOpt, objOpt);
53                 objBook.Close(false, objOpt, objOpt);
54                 objExcel.Quit();
55                 objExcel = null;
56                 GC.Collect();
57                 // Response the file to client.
58                 string downloadFileName = string.Format("{0:yyyyMMdd}", DateTime.Now) + "-" +
59                                       this.Page.User.Identity.Name + "-DataList.xls";
60                 Response.ContentType = "application/vnd.ms-excel";
61                 Response.AddHeader("Content-Disposition", "attachment; filename=" + downloadFileName + ";filetype=excel");
62                 Response.ContentEncoding = Encoding.GetEncoding("utf-8");
63                 Response.WriteFile(filePath);
64                 Response.Flush();
65
66                 if (System.IO.File.Exists(filePath))
67                     System.IO.File.Delete(filePath);
68                 Response.End();
69             }
70             catch (Exception ex)
71             {
72                 if (objExcel != null)
73                 {
74                     try
75                     {
76                         foreach (Excel.Workbook wb in objExcel.Workbooks)
77                         {
78                             if (wb != null)
79                             {
80                                 try
81                                 {
82                                     wb.Saved = true;
83                                 }
84                                 catch { }
85                             }
86                         }
87                         objExcel.Workbooks.Close();
88                         objExcel.Quit();
89                         objExcel = null;
90                         GC.Collect();
91                     }
92                     catch { }
93                 }
94             }
95         }
96

上一篇：淺談.NET下的多線程和并行計算（六）線程池基礎(chǔ)下

下一篇：ASP.NET MVC分頁、ASP.NET MVC Ajax分頁組件1.0發(fā)布!