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);