1using System;
2using System.IO;
3using System.Data;
4using System.Reflection;
5using System.Diagnostics;
6using cfg = System.Configuration;
7//using Excel;
8
9namespace ExcelHelperTest
10 /// 功能说明：套用模板输出Excel，并对数据进行分页
13 /// 作 者：Lingyun_k
14 /// 创建日期：2005-7-12
15 /// </summary>
16 public class ExcelHelper
17 protected string templetFile = null;
19 protected string outputFile = null;
20 protected object missing = Missing.Value;
21
22 /// 构造函数，需指定模板文件和输出文件完整路径
24 /// </summary>
25 /// <param name="templetFilePath">Excel模板文件路径</param>
26 /// <param name="outputFilePath">输出Excel文件路径</param>
27 public ExcelHelper(string templetFilePath,string outputFilePath)
28 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 /// 将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 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 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
79 }
80
81 for(int i=1;i<=sheetCount;i++)
83 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 for(int k=0;k<colCount;k++)
99 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 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 throw e;
137 }
138 finally
139 Process[] myProcesses;
141 DateTime startTime;
142 myProcesses = Process.GetProcessesByName("Excel");
143
144 //得不到Excel进程ID，暂时只能判断进程启动时间
145 foreach(Process myProcess in myProcesses)
146 startTime = myProcess.StartTime;
148
149 if(startTime > beforeTime && startTime < afterTime)
150 myProcess.Kill();
152 }
153 }
154 }
155
156 }
157
158
159 /// 获取WorkSheet数量
161 /// </summary>
162 /// <param name="rowCount">记录总行数</param>
163 /// <param name="rows">每WorkSheet行数</param>
164
2using System.IO;
3using System.Data;
4using System.Reflection;
5using System.Diagnostics;
6using cfg = System.Configuration;
7//using Excel;
8
9namespace ExcelHelperTest
10 /// 功能说明：套用模板输出Excel，并对数据进行分页
13 /// 作 者：Lingyun_k
14 /// 创建日期：2005-7-12
15 /// </summary>
16 public class ExcelHelper
17 protected string templetFile = null;
19 protected string outputFile = null;
20 protected object missing = Missing.Value;
21
22 /// 构造函数，需指定模板文件和输出文件完整路径
24 /// </summary>
25 /// <param name="templetFilePath">Excel模板文件路径</param>
26 /// <param name="outputFilePath">输出Excel文件路径</param>
27 public ExcelHelper(string templetFilePath,string outputFilePath)
28 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 /// 将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 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 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
79 }
80
81 for(int i=1;i<=sheetCount;i++)
83 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 for(int k=0;k<colCount;k++)
99 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 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 throw e;
137 }
138 finally
139 Process[] myProcesses;
141 DateTime startTime;
142 myProcesses = Process.GetProcessesByName("Excel");
143
144 //得不到Excel进程ID，暂时只能判断进程启动时间
145 foreach(Process myProcess in myProcesses)
146 startTime = myProcess.StartTime;
148
149 if(startTime > beforeTime && startTime < afterTime)
150 myProcess.Kill();
152 }
153 }
154 }
155
156 }
157
158
159 /// 获取WorkSheet数量
161 /// </summary>
162 /// <param name="rowCount">记录总行数</param>
163 /// <param name="rows">每WorkSheet行数</param>
164
请发表评论