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
C#操作Excel，套用模板并对数据进行分页
本文介绍了一种使用Excel模板进行数据分页导出的方法，包括如何根据数据量拆分到多个工作表中，适用于大量数据的高效处理。
扫一扫
到【灌水乐园】发言
