相关文章
ASP源码
PHP源码
.NET源码
JSP源码
使用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 = st***g.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
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("http://ww***.org/TR/REC-html40">
10 // title columns.
11 sb***pend(st***g.Format("Event_CourseId UserId Attended {0}
12 // content columns.
13 foreach (DataContent data in arrDatas)
14 sb***pend(st***g.Format("{0} {1} {2} {3}
15 // add extension rows.
16 sb***pend(GetExRows(50, 33));
17 // last part.
18 sb***pend("");
19 // response the result as a excel file.
20 Re***nse.ContentType = "ap***cation/vnd.ms-excel";
21 Re***nse.AddHeader("Content-Disposition", "attachment; fi***ame=DataList.xls;filetype=excel");
22 Re***nse.ContentEncoding = En***ing.GetEncoding("utf-8");
23 Re***nse.Write(sb***String());
24 Re***nse.Flush();
25 Re***nse.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***pend("");
33 return sb***String();
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***pend(st***g.Format("{0} ", GetExCells(colNum)));
41 return sb***String();
42 }
c) 通用接口方法：主要通过调用Office提供的接口组件Mi***soft.Office.Interop.Excel，来生成excel文件，其特点是可以消除内容单元格覆盖不全的问题，而且也可以消除打开提示格式异常的问题，但针对通过OleDB来操作文件的情况，还必须按照一种比较严格的方式进行编写才成，否则就会出现操作过程中格式异常的问题，下面为标准的写法：
1 private void Test4()
2 {
3 string filePath = @"C:Test.xls";
4 List
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 = Sy***m.Reflection.Missing.Value;
11 Ex***.Application objExcel = null;
12 Ex***.Workbooks objBooks = null;
13 Ex***.Workbook objBook = null;
14 try
15 {
16 try
17 {
18 objExcel = new Ex***.Application();
19 }
20 catch (Exception ex)
21 {
22 Co***le.Write(ex***ssage);
23 }
24
25 objBooks = (Ex***.Workbooks)objEx***.Workbooks;
26 objBook = (Ex***.Workbook)(ob***oks.Add(objOpt));
27 // Add data to cells of the first worksheet in the new workbook.
28 Ex***.Sheets objSheets = (Ex***.Sheets)ob***ok.Worksheets;
29 Ex***.Worksheet objSheet = (Ex***.Worksheet)(ob***eets.get_Item(1));
30 ob***eet.Name = "DataList";
31 ob***eet.Cells[1, 1] = "Name";
32 ob***eet.Cells[1, 2] = "Address";
33 ob***eet.Cells[1, 3] = "Phone";
34 Ex***.Range objRange = ob***eet.get_Range(ob***eet.Cells[1, 1], ob***eet.Cells[1, 3]);
35 //***Range.Font.Bold = true;
36 ob***nge.Font.Size = 18;
37 ob***nge.Interior.ColorIndex = 37;
38
39 for (int i = 1; i < 4; i++)
40 {
41 objRange = ob***eet.get_Range(ob***eet.Cells[2, i], ob***eet.Cells[2 + ar***tas.Count, i]);
42 ob***nge.NumberFormatLocal = "@";
43 for (int j = 0; j < ar***tas.Count; j++)
44 ob***eet.Cells[2 + j, i] = arrDatas[j].GetContentByIndex(i);
45 }
46 ob***eet.Columns.AutoFit();
47 if (Sy***m.IO.File.Exists(filePath))
48 Sy***m.IO.File.Delete(filePath);
49 // Save the file.
50 ob***ok.SaveAs(filePath, Ex***.XlFileFormat.xlWorkbookNormal, objOpt, objOpt,
51 objOpt, objOpt, Ex***.XlSaveAsAccessMode.xlNoChange,
52 objOpt, objOpt, objOpt, objOpt, objOpt);
53 ob***ok.Close(false, objOpt, objOpt);
54 ob***cel.Quit();
55 objExcel = null;
56 GC***llect();
57 // Response the file to client.
58 string downloadFileName = st***g.Format("{0:yyyyMMdd}", Da***ime.Now) + "-" +
59 th***Page.User.Identity.Name + "-D***List.xls";
60 Re***nse.ContentType = "ap***cation/vnd.ms-excel";
61 Re***nse.AddHeader("Content-Disposition", "attachment; filename=" + downloadFileName + ";filetype=excel");
62 Re***nse.ContentEncoding = En***ing.GetEncoding("utf-8");
63 Re***nse.WriteFile(filePath);
64 Re***nse.Flush();
65
66 if (Sy***m.IO.File.Exists(filePath))
67 Sy***m.IO.File.Delete(filePath);
68 Re***nse.End();
69 }
70 catch (Exception ex)
71 {
72 if (objExcel != null)
73 {
74 try
75 {
76 foreach (Ex***.Workbook wb in objEx***.Workbooks)
77 {
78 if (wb != null)
79 {
80 try
81 {
82 wb.Saved = true;
83 }
84 catch { }
85 }
86 }
87 objEx***.Workbooks.Close();
88 ob***cel.Quit();
89 objExcel = null;
90 GC***llect();
91 }
92 catch { }
93 }
94 }
95 }
96
-
