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