Asp.net操作Excel更轻松
今天先介绍一个关于导出数据的例子，以Excel为模板。直接进入正题了。
1.操作Excel的动态链接库
2.建立操作动态链接库的共通类，方便调用。（ExcelHelper）
具体如下：
View Code
1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Linq;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.HtmlControls;
9 using System.Web.UI.WebControls;
10 using System.Web.UI.WebControls.WebParts;
11 using System.Xml.Linq;
12 using System.IO;
13 using System.Reflection;
14 using System.Diagnostics;
15 using System.Collections;
16
17 ///<summary>
18 ///ExcelHelper 的摘要说明
19 ///</summary>
20 public class ExcelHelper
21 {
22 private string reportModelPath = null;
23 private string outPutFilePath = null;
24 private object missing = Missing.Value;
25 Excel.Application app;
26 Excel.Workbook workBook;
27 Excel.Worksheet workSheet;
28 Excel.Range range;
29
30 ///<summary>
31 /// 获取或设置报表模板路径
32 ///</summary>
33 public string ReportModelPath
34 {
35 get { return reportModelPath; }
36 set { reportModelPath = value; }
37 }
38
39 ///<summary>
40 /// 获取或设置输出路径
41 ///</summary>
42 public string OutPutFilePath
43 {
44 get { return outPutFilePath; }
45 set { outPutFilePath = value; }
46 }
47
48
49 public ExcelHelper()
50 {
51 //
52 //TODO: 在此处添加构造函数逻辑
53 //
54 }
55
56 ///<summary>
57 /// 带参ExcelHelper构造函数
58 ///</summary>
59 ///<param name="reportModelPath">报表模板路径</param>
60 ///<param name="outPutFilePath">输出路径</param>
61 public ExcelHelper(string reportModelPath, string outPutFilePath)
62 {
63 //路径验证
64 if (null == reportModelPath || ("").Equals(reportModelPath))
65 throw new Exception("报表模板路径不能为空！");
66 if (null == outPutFilePath || ("").Equals(outPutFilePath))
67 throw new Exception("输出路径不能为空！");
68 if (!File.Exists(reportModelPath))
69 throw new Exception("报表模板路径不存在！");
70
71 //设置路径值
72 this.ReportModelPath = reportModelPath;
73 this.OutPutFilePath = outPutFilePath;
74
75 //创建一个应用程序对象
76 app = new Excel.ApplicationClass();
77
78 //打开模板文件，获取WorkBook对象
79 workBook = app.Workbooks.Open(reportModelPath, missing, missing, missing, missing, missing, missing,
80 missing, missing, missing, missing, missing, missing);
81
82 //得到WorkSheet对象
83 workSheet = workBook.Sheets.get_Item(1) as Excel.Worksheet;
84 }
85
86 ///<summary>
87 /// 给单元格设值
88 ///</summary>
89 ///<param name="rowIndex">行索引</param>
90 ///<param name="colIndex">列索引</param>
91 ///<param name="content">填充的内容</param>
92 public void SetCells(int rowIndex,int colIndex,object content)
93 {
94 if (null != content)
95 {
96 content = content.ToString();
97 }
98 else
99 {
100 content = string.Empty;
101 }
102
103 try
104 {
105 workSheet.Cells[rowIndex, colIndex] = content;
106 }
107 catch
108 {
109 GC();
110 throw new Exception("向单元格[" + rowIndex + "," + colIndex + "]写数据出错！");
111 }
112 }
113
114 ///<summary>
115 /// 保存文件
116 ///</summary>
117 public void SaveFile()
118 {
119 try
120 {
121 workBook.SaveAs(outPutFilePath, missing, missing, missing, missing, missing,
122 Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
123 }
124 catch
125 {
126 throw new Exception("保存至文件失败！");
127 }
128 finally
129 {
130 Dispose();
131 }
132 }
133
134 ///<summary>
135 /// 垃圾回收处理
136 ///</summary>
137 protected void GC()
138 {
139 if (null != app)
140 {
141 int generation = 0;
142 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
143
144 generation = System.GC.GetGeneration(app);
145 System.GC.Collect(generation);
146 app = null;
147 missing = null;
148 }
149 }
150
151 ///<summary>
152 /// 释放资源
153 ///</summary>
154 protected void Dispose()
155 {
156 workBook.Close(null, null, null);
157 app.Workbooks.Close();
158 app.Quit();
159
160 if (null != workSheet)
161 {
162 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
163 workSheet = null;
164 }
165 if (workBook != null)
166 {
167 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
168 workBook = null;
169 }
170 if (app != null)
171 {
172 int generation = 0;
173 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
174 generation = System.GC.GetGeneration(app);
175 System.GC.Collect(generation);
176 app = null;
177 missing = null;
178 }
179 }
180 }
通过ExcelHelper类提供的SetCells()和SaveFile()方法可以给Excel单元格赋值并保存到临时文件夹内。仅供参考。
3.调用
因为这里需要用到导出模板，所以需要先建立模板。具体如下：、
View Code
1 ///<summary>
2 /// 导出数据
3 ///</summary>
4 protected void Export_Data()
5 {
6 int ii = 0;
7 //取得报表模板文件路径
8 string reportModelPath = HttpContext.Current.Server.MapPath("ReportModel/导出订单模板.csv");
9 //导出报表文件名
10 fileName = string.Format("{0}-{1}{2}.csv", "导出订单明细", DateTime.Now.ToString("yyyyMMdd"), GetRndNum(3));
11 //导出文件路径
12 string outPutFilePath = HttpContext.Current.Server.MapPath("Temp_Down/" + fileName);
13 //创建Excel对象
14 ExcelHelper excel = new ExcelHelper(reportModelPath, outPutFilePath);
15
16 SqlDataReader sdr = Get_Data();
17 while (sdr.Read())
18 {
19 ii++;
20 excel.SetCells(1 + ii, 1, ii);
21 excel.SetCells(1 + ii, 2, sdr["C_Name"]);
22 excel.SetCells(1 + ii, 3, sdr["C_Mtel"]);
23 excel.SetCells(1 + ii, 4, sdr["C_Tel"]);
24 excel.SetCells(1 + ii, 5, sdr["C_Province"]);
25 excel.SetCells(1 + ii, 6, sdr["C_Address"]);
26 excel.SetCells(1 + ii, 7, sdr["C_Postcode"]);
27 }
28 sdr.Close();
29 excel.SaveFile();
30 }
关于导出就简单写到这，另外下一节讲介绍如何通过这个类库上传Excel文件。
--------------------------------------------欢迎交流，转载请注明出处---------------------------------------------------------------------
