asp.net(C#)套用模板操作Excel
当需要输出带大量公式的Excel文档的时候，在代码里写公式就太累了。
用设计好的Excel模板，复制一下，往里面添加数据比较省事。
模板
导出文件：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
Bind();
}
private void Bind()
{
//模板文件
string TempletFileName = Server.MapPath("template/") + "template.xlsx";
//导出文件
string ReportFileName = Server.MapPath("xls/") + "out.xlsx";
string strTempletFile = Path.GetFileName(TempletFileName);
//将模板文件复制到输出文件
FileInfo mode = new FileInfo(TempletFileName);
mode.CopyTo(ReportFileName, true);
//打开excel
object missing = Missing.Value;
Application app = null;
Workbook wb = null;
Worksheet ws = null;
Range r = null;
//
app = new Microsoft.Office.Interop.Excel.Application();
wb = app.Workbooks.Open(ReportFileName, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
app.Visible = true;
//得到WorkSheet对象
ws = (Worksheet)wb.Worksheets.get_Item(1);
//添加或修改WorkSheet里的数据
ws.Cells[1, 1] = "100";
ws.Cells[2, 1] = "100";
ws.Cells[2, 2] = "100";
//代码里写个公式
r = (Range)ws.Cells[2, 3];
r.Formula = "=A2*B2";
//输出Excel文件并退出
wb.Save();
wb.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
ws = null;
wb = null;
app = null;
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
Bind();
}
private void Bind()
{
//模板文件
string TempletFileName = Server.MapPath("template/") + "template.xlsx";
//导出文件
string ReportFileName = Server.MapPath("xls/") + "out.xlsx";
string strTempletFile = Path.GetFileName(TempletFileName);
//将模板文件复制到输出文件
FileInfo mode = new FileInfo(TempletFileName);
mode.CopyTo(ReportFileName, true);
//打开excel
object missing = Missing.Value;
Application app = null;
Workbook wb = null;
Worksheet ws = null;
Range r = null;
//
app = new Microsoft.Office.Interop.Excel.Application();
wb = app.Workbooks.Open(ReportFileName, false, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
app.Visible = true;
//得到WorkSheet对象
ws = (Worksheet)wb.Worksheets.get_Item(1);
//添加或修改WorkSheet里的数据
ws.Cells[1, 1] = "100";
ws.Cells[2, 1] = "100";
ws.Cells[2, 2] = "100";
//代码里写个公式
r = (Range)ws.Cells[2, 3];
r.Formula = "=A2*B2";
//输出Excel文件并退出
wb.Save();
wb.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
ws = null;
wb = null;
app = null;
}
}
参考：
C#操作Excel，套用模板并对数据进行分页
1
using System;
2using System.IO;
3using System.Data;
4using System.Reflection;
5using System.Diagnostics;
6using cfg = System.Configuration;
7//using Excel;
8
9namespace ExcelHelperTest
10
{
11
/// <summary>
12
/// 功能说明：套用模板输出Excel，并对数据进行分页
13 /// 作 者：Lingyun_k
14 /// 创建日期：2005-7-12
15
/// </summary>
16 public class ExcelHelper
17 {
18 protected string templetFile = null;
19 protected string outputFile = null;
20 protected object missing = Missing.Value;
21
22 /// <summary>
23 /// 构造函数，需指定模板文件和输出文件完整路径
24 /// </summary>
25 /// <param name="templetFilePath">Excel模板文件路径</param>
26 /// <param name="outputFilePath">输出Excel文件路径</param>
27 public ExcelHelper(string templetFilePath,string outputFilePath)
28 {
29 if(templetFilePath == null)
30 throw new Exception("Excel模板文件路径不能为空！");
31
32 if(outputFilePath == null)
33 throw new Exception("输出Excel文件路径不能为空！");
34
35 if(!File.Exists(templetFilePath))
36 throw new Exception("指定路径的Excel模板文件不存在！");
37
38 this.templetFile = templetFilePath;
39 this.outputFile = outputFilePath;
40
41 }
42
43 /// <summary>
44 /// 将DataTable数据写入Excel文件（套用模板并分页）
45 /// </summary>
46 /// <param name="dt">DataTable</param>
47 /// <param name="rows">每个WorkSheet写入多少行数据</param>
48 /// <param name="top">行索引</param>
49 /// <param name="left">列索引</param>
50 /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2
”</param>
51 public void DataTableToExcel(DataTable dt,int rows,int top,int left,string sheetPrefixName)
52 {
53 int rowCount = dt.Rows.Count; //源DataTable行数
54 int colCount = dt.Columns.Count; //源DataTable列数
55 int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
56 DateTime beforeTime;
57 DateTime afterTime;
58
59 if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
60 sheetPrefixName = "Sheet";
61
62 //创建一个Application对象并使其可见
63 beforeTime = DateTime.Now;
64 Excel.Application app = new Excel.ApplicationClass();
65 app.Visible = true;
66 afterTime = DateTime.Now;
67
68 //打开模板文件，得到WorkBook对象
69 Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
70 missing,missing,missing,missing,missing,missing,missing);
71
72 //得到WorkSheet对象
73 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
74
75 //复制sheetCount-1个WorkSheet对象
76 for(int i=1;i<sheetCount;i++)
77 {
78 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
79 }
80
81 #region 将源DataTable数据写入Excel
82 for(int i=1;i<=sheetCount;i++)
83 {
84 int startRow = (i - 1) * rows; //记录起始行索引
85 int endRow = i * rows; //记录结束行索引
86
87 //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
88 if(i == sheetCount)
89 endRow = rowCount;
90
91 //获取要写入数据的WorkSheet对象，并重命名
92 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
93 sheet.Name = sheetPrefixName + "-" + i.ToString();
94
95 //将dt中的数据写入WorkSheet
96 for(int j=0;j<endRow-startRow;j++)
97 {
98 for(int k=0;k<colCount;k++)
99 {
100 sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
101 }
102 }
103
104 //写文本框数据
105 Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
106 Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
107 Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
108
109 txtAuthor.Text = "KLY.NET的Blog";
110 txtDate.Text = DateTime.Now.ToShortDateString();
111 txtVersion.Text = "1.0.0.0";
112 }
113 #endregion
114
115 //输出Excel文件并退出
116 try
117 {
118 workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
119 workBook.Close(null,null,null);
120 app.Workbooks.Close();
121 app.Application.Quit();
122 app.Quit();
123
124 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
125 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
126 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
127
128 workSheet=null;
129 workBook=null;
130 app=null;
131
132 GC.Collect();
133 }
134 catch(Exception e)
135 {
136 throw e;
137 }
138 finally
139 {
140 Process[] myProcesses;
141 DateTime startTime;
142 myProcesses = Process.GetProcessesByName("Excel");
143
144 //得不到Excel进程ID，暂时只能判断进程启动时间
145 foreach(Process myProcess in myProcesses)
146 {
147 startTime = myProcess.StartTime;
148
149 if(startTime > beforeTime && startTime < afterTime)
150 {
151 myProcess.Kill();
152 }
153 }
154 }
155
156 }
157
158
159 /// <summary>
160 /// 获取WorkSheet数量
161 /// </summary>
162 /// <param name="rowCount">记录总行数</param>
163 /// <param name="rows">每WorkSheet行数</param>
164 private int GetSheetCount(int rowCount,int rows)
165 {
166 int n = rowCount % rows; //余数
167
168 if(n == 0)
169 return rowCount / rows;
170 else
171 return Convert.ToInt32(rowCount / rows) + 1;
172 }
173
174
175 /// <summary>
176 /// 将二维数组数据写入Excel文件（套用模板并分页）
177 /// </summary>
178 /// <param name="arr">二维数组</param>
179 /// <param name="rows">每个WorkSheet写入多少行数据</param>
180 /// <param name="top">行索引</param>
181 /// <param name="left">列索引</param>
182 /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2”</param>
183 public void ArrayToExcel(string[,] arr,int rows,int top,int left,string sheetPrefixName)
184 {
185 int rowCount = arr.GetLength(0); //二维数组行数（一维长度）
186 int colCount = arr.GetLength(1); //二维数据列数（二维长度）
187 int sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数
188 DateTime beforeTime;
189 DateTime afterTime;
190
191 if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
192 sheetPrefixName = "Sheet";
193
194 //创建一个Application对象并使其可见
195 beforeTime = DateTime.Now;
196 Excel.Application app = new Excel.ApplicationClass();
197 app.Visible = true;
198 afterTime = DateTime.Now;
199
200 //打开模板文件，得到WorkBook对象
201 Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
202 missing,missing,missing,missing,missing,missing,missing);
203
204 //得到WorkSheet对象
205 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
206
207 //复制sheetCount-1个WorkSheet对象
208 for(int i=1;i<sheetCount;i++)
209 {
210 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
211 }
212
213 #region 将二维数组数据写入Excel
214 for(int i=1;i<=sheetCount;i++)
215 {
216 int startRow = (i - 1) * rows; //记录起始行索引
217 int endRow = i * rows; //记录结束行索引
218
219 //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
220 if(i == sheetCount)
221 endRow = rowCount;
222
223 //获取要写入数据的WorkSheet对象，并重命名
224 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
225 sheet.Name = sheetPrefixName + "-" + i.ToString();
226
227 //将二维数组中的数据写入WorkSheet
228 for(int j=0;j<endRow-startRow;j++)
229 {
230 for(int k=0;k<colCount;k++)
231 {
232 sheet.Cells[top + j,left + k] = arr[startRow + j,k];
233 }
234 }
235
236 Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
237 Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
238 Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
239
240 txtAuthor.Text = "KLY.NET的Blog";
241 txtDate.Text = DateTime.Now.ToShortDateString();
242 txtVersion.Text = "1.0.0.0";
243 }
244 #endregion
245
246 //输出Excel文件并退出
247 try
248 {
249 workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
250 workBook.Close(null,null,null);
251 app.Workbooks.Close();
252 app.Application.Quit();
253 app.Quit();
254
255 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
256 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
257 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
258
259 workSheet=null;
260 workBook=null;
261 app=null;
262
263 GC.Collect();
264 }
265 catch(Exception e)
266 {
267 throw e;
268 }
269 finally
270 {
271 Process[] myProcesses;
272 DateTime startTime;
273 myProcesses = Process.GetProcessesByName("Excel");
274
275 //得不到Excel进程ID，暂时只能判断进程启动时间
276 foreach(Process myProcess in myProcesses)
277 {
278 startTime = myProcess.StartTime;
279
280 if(startTime > beforeTime && startTime < afterTime)
281 {
282 myProcess.Kill();
283 }
284 }
285 }
286
287 }
288 }
289
}
290
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
转载于:https://www.cnblogs.com/jipeng/archive/2013/04/21/3034227.html