http://www.cnblogs.com/wangshenhe/archive/2012/05/10/2493727.html
1 /******************************************************************
2 * 创 建 人： SamWang
3 * 创建时间： 2012-3-16 9:59
4 * 描 述：
5 * 导入导出Excel通用类
6 * 版 本： V1.0
7 * 环 境： VS2005
8 ******************************************************************/
9 using System;
10 using System.Collections.Generic;
11 using System.Text;
12 using System.Windows.Forms;
13 using Excel = Microsoft.Office.Interop.Excel;
14 using System.Data;
15 using System.Drawing;
16 using System.Collections;
17 using System.Diagnostics;
18 using System.Data.OleDb;
19
20 namespace LingDang.CRM.UI.Client
21 {
22 public class ExcelIO:IDisposable
23 {
24 #region Constructors
25 private ExcelIO()
26 {
27 status = IsExistExecl() ? 0 : -1;
28 }
29
30 public static ExcelIO GetInstance()
31 {
32 //if(instance == null)
33 //{
34 // lock (syncRoot)
35 // {
36 // if(instance == null)
37 // {
38 // instance = new ExcelIO();
39 // }
40 // }
41 //}
42 //return instance;
43 return new ExcelIO();
44 }
45 #endregion
46
47 #region Fields
48 private static ExcelIO instance;
49 private static readonly object syncRoot = new object();
50 private string returnMessage;
51 private Excel.Application xlApp;
52 private Excel.Workbooks workbooks = null;
53 private Excel.Workbook workbook = null;
54 private Excel.Worksheet worksheet = null;
55 private Excel.Range range = null;
56 private int status = -1;
57 private bool disposed = false;//是否已经释放资源的标记
58 #endregion
59
60 #region Properties
61 /// <summary>
62 /// 返回信息
63 /// </summary>
64 public string ReturnMessage
65 {
66 get { return returnMessage; }
67 }
68
69 /// <summary>
70 /// 状态:0-正常，-1-失败 1-成功
71 /// </summary>
72 public int Status
73 {
74 get { return status;}
75 }
76 #endregion
77
78 #region Methods
79 /// <summary>
80 /// 判断是否安装Excel
81 /// </summary>
82 /// <returns></returns>
83 protected bool IsExistExecl()
84 {
85 try
86 {
87 xlApp = new Excel.Application();
88 if (xlApp == null)
89 {
90 returnMessage = "无法创建Excel对象，可能您的计算机未安装Excel!";
91 return false;
92 }
93 }
94 catch (Exception ex)
95 {
96 returnMessage = "请正确安装Excel！";
97 //throw ex;
98 return false;
99 }
100
101 return true;
102 }
103
104 /// <summary>
105 /// 获得保存路径
106 /// </summary>
107 /// <returns></returns>
108 public static string SaveFileDialog()
109 {
110 SaveFileDialog sfd = new SaveFileDialog();
111 sfd.DefaultExt = "xls";
112 sfd.Filter = "Excel文件(*.xls)|*.xls";
113 if (sfd.ShowDialog() == DialogResult.OK)
114 {
115 return sfd.FileName;
116 }
117 return string.Empty;
118 }
119
120 /// <summary>
121 /// 获得打开文件的路径
122 /// </summary>
123 /// <returns></returns>
124 public static string OpenFileDialog()
125 {
126 OpenFileDialog ofd = new OpenFileDialog();
127 ofd.DefaultExt = "xls";
128 ofd.Filter = "Excel文件(*.xls)|*.xls";
129 if (ofd.ShowDialog() == DialogResult.OK)
130 {
131 return ofd.FileName;
132 }
133 return string.Empty;
134 }
135
136 /// <summary>
137 /// 设置单元格边框
138 /// </summary>
139 protected void SetCellsBorderAround()
140 {
141 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
142 //if (dt.Rows.Count > 0)
143 //{
144 // range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
145 // range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
146 // range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
147 //}
148 //if (dt.Columns.Count > 1)
149 {
150 range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
151 range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
152 range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
153 }
154 }
155
156 /// <summary>
157 /// 将DataTable导出Excel
158 /// </summary>
159 /// <param name="dt">数据集</param>
160 /// <param name="saveFilePath">保存路径</param>
161 /// <param name="reportName">报表名称</param>
162 /// <returns>是否成功</returns>
163 public bool DataTableToExecl(DataTable dt, string saveFileName, string reportName)
164 {
165 //判断是否安装Excel
166 bool fileSaved = false;
167 if(status == -1) return fileSaved;
168 //判断数据集是否为null
169 if (dt == null)
170 {
171 returnMessage = "无引出数据！";
172 return false;
173 }
174 //判断保存路径是否有效
175 if (!saveFileName.Contains(":"))
176 {
177 returnMessage = "引出路径有误！请选择正确路径！";
178 return false;
179 }
180
181 //创建excel对象
182 workbooks = xlApp.Workbooks;
183 workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
184 worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
185 worksheet.Cells.Font.Size = 10;
186 worksheet.Cells.NumberFormat = "@";
187 long totalCount = dt.Rows.Count;
188 long rowRead = 0;
189 float percent = 0;
190 int rowIndex = 0;
191
192 //第一行为报表名称，如果为null则不保存该行
193 ++rowIndex;
194 worksheet.Cells[rowIndex, 1] = reportName;
195 range = (Excel.Range)worksheet.Cells[rowIndex, 1];
196 range.Font.Bold = true;
197
198 //写入字段(标题)
199 ++rowIndex;
200 for (int i = 0; i < dt.Columns.Count; i++)
201 {
202 worksheet.Cells[rowIndex,i+1] = dt.Columns[i].ColumnName;
203 range = (Excel.Range)worksheet.Cells[rowIndex, i + 1];
204
205 range.Font.Color = ColorTranslator.ToOle(Color.Blue);
206 range.Interior.Color = dt.Columns[i].Caption == "表体" ? ColorTranslator.ToOle(Color.SkyBlue) : ColorTranslator.ToOle(Color.Yellow);
207 }
208
209 //写入数据
210 ++rowIndex;
211 for (int r = 0; r < dt.Rows.Count; r++)
212 {
213 for (int i = 0; i < dt.Columns.Count; i++)
214 {
215 worksheet.Cells[r + rowIndex, i + 1] = dt.Rows[r][i].ToString();
216 }
217 rowRead++;
218 percent = ((float)(100 * rowRead)) / totalCount;
219 }
220
221 //画单元格边框
222 range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
223 this.SetCellsBorderAround();
224
225 //列宽自适应
226 range.EntireColumn.AutoFit();
227
228 //保存文件
229 if (saveFileName != "")
230 {
231 try
232 {
233 workbook.Saved = true;
234 workbook.SaveCopyAs(saveFileName);
235 fileSaved = true;
236 }
237 catch (Exception ex)
238 {
239 fileSaved = false;
240 returnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
241 }
242 }
243 else
244 {
245 fileSaved = false;
246 }
247
248 //释放Excel对应的对象（除xlApp,因为创建xlApp很花时间，所以等析构时才删除)
249 //Dispose(false);
250 Dispose();
251 return fileSaved;
252 }
253
254 /// <summary>
255 /// 导入EXCEL到DataSet
256 /// </summary>
257 /// <param name="fileName">Excel全路径文件名</param>
258 /// <returns>导入成功的DataSet</returns>
259 public DataSet ImportExcel(string fileName)
260 {
261 if (status == -1) return null;
262 //判断文件是否被其他进程使用
263 try
264 {
265 workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
266 worksheet = (Excel.Worksheet)workbook.Worksheets[1];
267 }
268 catch
269 {
270 returnMessage = "Excel文件处于打开状态，请保存关闭";
271 return null;
272 }
273
274 //获得所有Sheet名称
275 int n = workbook.Worksheets.Count;
276 string[] sheetSet = new string[n];
277 ArrayList al = new ArrayList();
278 for (int i = 0; i < n; i++)
279 {
280 sheetSet[i] = ((Excel.Worksheet)workbook.Worksheets[i+1]).Name;
281 }
282
283 //释放Excel相关对象
284 Dispose();
285
286 //把EXCEL导入到DataSet
287 DataSet ds = null;
288 //string connStr = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + fileName + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\"";
289 List<string> connStrs = new List<string>();
290 connStrs.Add("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"");
291 connStrs.Add("Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + fileName + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\"");
292 foreach (string connStr in connStrs)
293 {
294 ds = GetDataSet(connStr, sheetSet);
295 if (ds != null) break;
296 }
297 return ds;
298 }
299
300 /// <summary>
301 /// 通过olddb获得dataset
302 /// </summary>
303 /// <param name="connectionstring"></param>
304 /// <returns></returns>
305 protected DataSet GetDataSet(string connStr, string[] sheetSet)
306 {
307 DataSet ds = null;
308 using (OleDbConnection conn = new OleDbConnection(connStr))
309 {
310 try
311 {
312 conn.Open();
313 OleDbDataAdapter da;
314 ds = new DataSet();
315 for (int i = 0; i < sheetSet.Length; i++)
316 {
317 string sql = "select * from [" + sheetSet[i] + "$] ";
318 da = new OleDbDataAdapter(sql, conn);
319 da.Fill(ds, sheetSet[i]);
320 da.Dispose();
321 }
322 conn.Close();
323 conn.Dispose();
324 }
325 catch (Exception ex)
326 {
327 return null;
328 }
329 }
330 return ds;
331 }
332
333 /// <summary>
334 /// 释放Excel对应的对象资源
335 /// </summary>
336 /// <param name="isDisposeAll"></param>
337 protected virtual void Dispose(bool disposing)
338 {
339 try
340 {
341 if (!disposed)
342 {
343 if (disposing)
344 {
345 if (range != null)
346 {
347 System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
348 range = null;
349 }
350 if (worksheet != null)
351 {
352 System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
353 worksheet = null;
354 }
355 if (workbook != null)
356 {
357 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
358 workbook = null;
359 }
360 if (workbooks != null)
361 {
362 xlApp.Application.Workbooks.Close();
363 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
364 workbooks = null;
365 }
366 if (xlApp != null)
367 {
368 xlApp.Quit();
369 System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
370 }
371 int generation = GC.GetGeneration(xlApp);
372 System.GC.Collect(generation);
373 }
374
375 //非托管资源的释放
376 //KillExcel();
377 }
378 disposed = true;
379 }
380 catch (Exception e)
381 {
382 throw e;
383 }
384 }
385
386 /// <summary>
387 /// 会自动释放非托管的该类实例的相关资源
388 /// </summary>
389 public void Dispose()
390 {
391 try
392 {
393 Dispose(true);
394 //告诉垃圾回收器,资源已经被回收
395 GC.SuppressFinalize(this);
396 }
397 catch (Exception e)
398 {
399 throw e;
400 }
401 }
402
403 /// <summary>
404 /// 关闭
405 /// </summary>
406 public void Close()
407 {
408 try
409 {
410 this.Dispose();
411 }
412 catch (Exception e)
413 {
414
415 throw e;
416 }
417 }
418
419 /// <summary>
420 /// 析构函数
421 /// </summary>
422 ~ExcelIO()
423 {
424 try
425 {
426 Dispose(false);
427 }
428 catch (Exception e)
429 {
430 throw e;
431 }
432 }
433
434 /// <summary>
435 /// 关闭Execl进程(非托管资源使用)
436 /// </summary>
437 private void KillExcel()
438 {
439 try
440 {
441 Process[] ps = Process.GetProcesses();
442 foreach (Process p in ps)
443 {
444 if (p.ProcessName.ToLower().Equals("excel"))
445 {
446 //if (p.Id == ExcelID)
447 {
448 p.Kill();
449 }
450 }
451 }
452 }
453 catch (Exception ex)
454 {
455 //MessageBox.Show("ERROR " + ex.Message);
456 }
457 }
458
459 #endregion
460
461 #region Events
462
463 #endregion
464
465
466 #region IDisposable 成员
467
468
469 #endregion
470 }
471 }
