1 将数据库表导出到Excel，并生成文件（C#实现）[转载]
2
3
4 需添加项目引用：
5
6 1. .NET->System.Data.OracleClient.dll
7
8 2. COM->Microsoft Excel 11.0 Object Library
9
10
11
12 代码如下：
13
14 using System;
15 using System.IO;
16 using System.Data;
17 using System.Reflection;
18 using System.Diagnostics;
19 using System.Configuration;
20 using System.Collections;
21 using Excel;
22
23 namespace ProtoType
24 {
25 ///
26 /// 套用模板输出Excel，生成xls文件和html文件
27 /// Author: Liu Wen
28 /// Date Created: 2006-8
29 ///
30 public class ExportExcel
31 {
32 #region variable member
33 protected string templateFile = null;
34 protected string excelFile = null;
35 protected string htmlFile = null;
36 protected object missing = Missing.Value;
37 Excel.ApplicationClass app;
38 Excel.Workbook book;
39 Excel.Worksheet sheet;
40 Excel.Range range;
41 private DateTime beforeTime; //Excel启动之前时间
42 private DateTime afterTime; //Excel启动之后时间
43 #endregion
44
45 ///
46 /// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径
47 ///
48 /// Excel模板文件路径
49 /// Excel输出文件路径
50 /// Html输出文件路径
51 public ExportExcel(string templateFile, string excelFile, string htmlFile)
52 {
53 if(templateFile == null)
54 throw new Exception("Excel模板文件路径不能为空！");
55
56 if(excelFile == null)
57 throw new Exception("Excel输出文件路径不能为空！");
58
59 if(htmlFile == null)
60 throw new Exception("Html输出文件路径不能为空！");
61
62 if(!File.Exists(templateFile))
63 throw new Exception("指定路径的Excel模板文件不存在！");
64
65 this.templateFile = templateFile;
66 this.excelFile = excelFile;
67 this.htmlFile = htmlFile;
68
69 //创建一个Application对象
70 beforeTime = DateTime.Now;
71 app = new ApplicationClass();
72 //app.Visible = true;
73 afterTime = DateTime.Now;
74
75 //打开模板文件，得到WorkBook对象
76 try
77 {
78 book = app.Workbooks.Open(templateFile,missing,missing,missing,missing,missing,
79 missing,missing,missing,missing,missing,missing,missing,missing,missing);
80 }
81 catch (Exception e)
82 {
83 throw e;
84 }
85 //得到WorkSheet对象
86 sheet = (Excel.Worksheet)book.Sheets.get_Item(1);
87 }
88
89 ///
90 /// 将DataTable数据导出到Excel（可动态插入行）
91 ///
92 /// DataTable
93 /// 插入行的索引
94 /// 插入列的索引
95 public void DataTableToExcel(System.Data.DataTable dt, int rowIndex, int colIndex)
96 {
97 //range = sheet.get_Range("A7", missing);
98 //range.Value2 = "raogerrr";
99 //string str = range.Text.ToString();
100
101 int rowCount = dt.Rows.Count; //DataTable行数
102 int colCount = dt.Columns.Count; //DataTable列数
103 int iRow;
104 int iCol;
105
106 //将数据导出到相应的单元格
107 for (iRow = 0; iRow < rowCount; iRow++)
108 {
109 //插入新行
110 this.InsertRows(sheet, iRow+rowIndex);
111 //填充当前行
112 for (iCol = 0; iCol < colCount; iCol++)
113 {
114 sheet.Cells[iRow+rowIndex, iCol+colIndex] = dt.Rows[iRow][iCol].ToString();
115 }
116 }
117 this.DeleteRows(sheet, rowCount+rowIndex);
118
119 //Excel.QueryTables qts = sheet.QueryTables;
120 //Excel.QueryTable qt = qts.Add(,,);
121 //qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
122 //qt.Refresh();
123 }
124
125 ///
126 /// 将DataTable数据导出到Excel（可动态插入行）
127 ///
128 /// DataTable
129 /// 插入数据的起始单元格
130 public void DataTableToExcel(System.Data.DataTable dt, string cellID)
131 {
132 int rowIndex = sheet.get_Range(cellID, missing).Row;
133 int colIndex = sheet.get_Range(cellID, missing).Column;
134 int rowCount = dt.Rows.Count; //DataTable行数
135 int colCount = dt.Columns.Count; //DataTable列数
136 int iRow;
137 int iCol;
138
139 //利用二维数组批量写入
140 string[,] array = new string[rowCount,colCount];
141 for (iRow = 0; iRow < rowCount; iRow++)
142 {
143 for (iCol = 0; iCol < colCount; iCol++)
144 {
145 array[iRow,iCol] = dt.Rows[iRow][iCol].ToString();
146 }
147 }
148
149 for (iRow = 0; iRow < rowCount; iRow++)
150 {
151 this.InsertRows(sheet, iRow+rowIndex);
152 }
153 this.DeleteRows(sheet, rowCount+rowIndex);
154
155 range = sheet.get_Range(cellID, missing);
156 range = range.get_Resize(rowCount, colCount);
157 range.Value2 = array;
158 }
159
160 ///
161 /// 将DataTable数据导出到Excel（固定）
162 ///
163 /// DataTable
164 /// 插入数据的起始单元格
165 public void DataTableToExcel2(System.Data.DataTable dt, string cellID)
166 {
167 int rowCount = dt.Rows.Count; //DataTable行数
168 int colCount = dt.Columns.Count; //DataTable列数
169 int iRow;
170 int iCol;
171
172 //利用二维数组批量写入
173 string[,] array = new string[rowCount,colCount];
174 for (iRow = 0; iRow < rowCount; iRow++)
175 {
176 for (iCol = 0; iCol < colCount; iCol++)
177 {
178 array[iRow,iCol] = dt.Rows[iRow][iCol].ToString();
179 }
180 }
181
182 range = sheet.get_Range(cellID, missing);
183 range = range.get_Resize(rowCount, colCount);
184 range.Value2 = array;
185 }
186
187 ///
188 /// 输出生成的Excel, Html文件
189 ///
190 public void OutputFile()
191 {
192 if (this.excelFile == null)
193 throw new Exception("没有指定Excel输出文件路径！");
194 if (this.htmlFile == null)
195 throw new Exception("没有指定Htmll输出文件路径！");
196 try
197 {
198 book.SaveAs(excelFile, missing, missing, missing, missing, missing,
199 Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing);
200
201 book.SaveAs(htmlFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing,
202 Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
203 }
204 catch (Exception e)
205 {
206 throw e;
207 }
208 finally
209 {
210 this.Dispose();
211 }
212 }
213
214 ///
215 /// 在工作表中插入行，并调整其他行以留出空间
216 ///
217 /// 当前工作表
218 /// 欲插入的行索引
219 private void InsertRows(Excel.Worksheet sheet, int rowIndex)
220 {
221 Range r = (Excel.Range)sheet.Rows[rowIndex, missing];
222
223 //object Range.Insert(object shift, object copyorigin);
224 //shift: Variant类型，可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一：
225 //xlShiftToRight 或 xlShiftDown。如果省略该参数，Microsoft Excel 将根据区域形状确定调整方式。
226 r.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
227 }
228
229 ///
230 /// 在工作表中删除行
231 ///
232 /// 当前工作表
233 /// 欲删除的行索引
234 private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
235 {
236 Range r = (Range)sheet.Rows[rowIndex, missing];
237
238 r.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
239 }
240
241 ///
242 /// 退出Excel，并且释放调用的COM资源
243 ///
244 private void Dispose()
245 {
246 book.Close(missing, missing, missing);
247 app.Workbooks.Close();
248 app.Quit();
249
250 if(range != null)
251 {
252 System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
253 range = null;
254 }
255 if(sheet != null)
256 {
257 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
258 sheet = null;
259 }
260 if(book != null)
261 {
262 System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
263 book = null;
264 }
265 if(app != null)
266 {
267 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
268 app = null;
269 }
270
271 GC.Collect();
272 this.KillExcelProcess();
273 }
274
275 ///
276 /// 结束Excel进程
277 ///
278 private void KillExcelProcess()
279 {
280 DateTime startTime;
281 Process[] processes = Process.GetProcessesByName("Excel");
282
283 //得不到Excel进程ID，暂时只能判断进程启动时间
284 foreach (Process process in processes)
285 {
286 startTime = process.StartTime;
287 if(startTime > beforeTime && startTime < afterTime)
288 process.Kill();
289 }
290 }
291
292 }
293 }
