C#后台创建Excel文件的那点事儿

来源：互联网发布：域名备案需要多久编辑：程序博客网时间：2024/05/28 11:50

使用C#在应用中生成excel文档，甚至再提供直接下载功能，算是一个比较常用的应用，这里记录下自己遇到的几种方法，还有一些需要注意的地方：

a) 首先定义一个数据类：

代码

    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文件，优点是：代码十分方便，无需任何的office接口扩展即可工作；不足为：覆盖的单元格有限，虽然可以通过中间方法来设置，但仍存在打开时提示格式不正确的问题，随不影响正常浏览及编辑，但对于一些需要通过代码操作excel文件的应用，可能会遇到问题，比如通过OleDB来读取内容的时候等。

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) 通用接口方法：主要通过调用Office提供的接口组件Microsoft.Office.Interop.Excel，来生成excel文件，其特点是可以消除内容单元格覆盖不全的问题，而且也可以消除打开提示格式异常的问题，但针对通过OleDB来操作文件的情况，还必须按照一种比较严格的方式进行编写才成，否则就会出现操作过程中格式异常的问题，下面为标准的写法：

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.XlSaveAsAccessMode.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