使用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
-