1
using System;
2
using System.IO;
3
using System.Data;
4
using System.Reflection;
5
using System.Diagnostics;
6
using cfg = System.Configuration;
7
// using Excel;
8
9
namespace 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
将源DataTable数据写入Excel #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
将二维数组数据写入Excel #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
最近给公司做个项目,要求每天全自动的根据一份Excel模版(带图片)把数据库中的数据自动添加进入相关cell中,并要求不同产品生成不同文件,同一产品不同型号生成不同Sheets...还要自动把当天生成的这些Excel文件自动邮件给指定客人...
恩恩,这就是自动化...
用了两个星期做完...下面说下关键的处理,给同样要做的朋友一些帮助:
1.要自动化,就需要用ini文件
加入:
[DllImport("kernel32")]
private static extern long WritePrivateProfileString(string section,string key,string val,string filePath);
[DllImport("kernel32")]
private static extern int GetPrivateProfileString(string section,string key,string def,StringBuilder retVal,int size,string filePath);
另外要加个Timer1,程序启动的时候读ini文件,确定是否需要哪些自动化,对Timer1的延时需要也在ini文件中设定:
private void Form1_Load(object sender, System.EventArgs e)
{
ini_read("Setup",7);
ini_read("Parameter",55);
ini_read("Email",9);
if (this.checkBox1.Checked ==true) //判断是否"Setup"中设定了自动执行
{
timer1.Interval =int.Parse(this.textBox1.text.ToString ());
timer1.Start();
}
}
ini_read(string strName,int intSum)是我自己的读ini方法
2.根据条件生成DataSet....这个简单不写了
3.邦定到一个dataGrid1上
4.建立Excel文件
得到产品名同型号名,用DataView来实现:
DataView dv=new DataView ();
dv=((DataSet)dataGrid1.DataSource).Tables [0].DefaultView ;
调用生成一个Excel文件的方法:
public void GetTemplate(string strFileName,DataView dv,int intdvParameterS,int intdvParameterE,int intSheetTotal)
GetTemplate(strProductID,dv,(intEnd-intSheetTotal),intEnd,intSheetTotal+1);
解释下,strFileName就是一个产品的名,也是要做为文件名称的,intdvParameterS是dv中这个产品开始的index,intdvParameterE是dv中这个产品的最后一个index,intSheetTotal就是dv中这个产品的总数
在GetTemplate中要添加模版,拷贝模版给多个Sheet,保存文件:
调用放在/Template文件夹下的template.xls文件,
Microsoft.Office.Interop.Excel.Application objEa;
objEa.Workbooks.Add(((System.Environment.CurrentDirectory.Replace(" //",""))+""+"Template//template.xls "));
生成intSheetTotal个Sheet....
拷贝模版到Sheet
Microsoft.Office.Interop .Excel .Worksheet objEs;
objEs.Copy (objEa.Workbooks[1].Worksheets[1] ,Missing.Value );
用数组添加值吧,主要用for作循环
Microsoft.Office .Interop .Excel .Range[] objEr=new Microsoft.Office .Interop .Excel .Range[30];
objEr[i]=objEs.get_Range(strPara[i],Missing.Value);
objEr[i].Value2 =dv[intdvParameterS+k-1].Row[strField[i]].ToString();
strField[i]是ini中设定的数据库中字段
strPara[i]是ini中设定的Excel中位置,比如strField[0]="ProductID",strPara[0]="C22"
...
string strName=strFileName;
string strFolder=this.textBox2.Text+" //"+strName+".xls "; //textBox2是文件夹路径
this.listBox1.Items .Add(strFolder);//这是把这个Excel文件作为邮件附件添加进附件List中处理
objEa.Workbooks[1].SaveCopyAs(strFolder);
好了,循环添加完了后,保存就行了了,这样一个产品名称的Excel文件都完成了,里面有多个不同型号的Sheet,如果查询出来的是多个产品,那么就循环调用GetTemplate()就可以了
5.其他都简单
发邮件用
using System.Web.Mail ;
附件读刚才那个listBox1就可以了
文件夹操作用
string strPath;
strPath=this.textBox2 .Text ;
System.IO.Directory.Delete(strPath,true);
Directory.CreateDirectory (strPath);