Using C# to generate excel documents in the application, and even providing a direct download function, is a relatively common application. Here I record several methods I encountered, and there are some things that need attention:
a) First define a data class:
code
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) The simplest method: generate excel files directly by piecing together HTML strings. The advantages are: the code is very convenient and can work without any office interface extension; the disadvantage is: the cells covered are limited, although it can be set through an intermediate method , but there is still a problem of incorrect format when opening, which does not affect normal browsing and editing. However, for some applications that need to operate excel files through code, you may encounter problems, such as when reading content through 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 an 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) General interface method: mainly generate excel files by calling the interface component Microsoft.Office.Interop.Excel provided by Office. Its characteristic is that it can eliminate the problem of incomplete coverage of content cells, and it can also eliminate the problem of abnormal opening prompt format. , but for the case of operating files through OleDB, it must be written in a relatively strict way, otherwise there will be problems with format abnormalities during the operation. The following is the standard writing method:
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)
twenty one {
22 Console.Write(ex.Message);
twenty three }
twenty four
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
-