读取Excel（比较健全的版本）

这个版本的类库，读取EXcel没有进程。因为已经被GC清理了。下面是源码，希望有人可以提出不足以及改进。

 

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 
  5 using System.Data;
  6 using System.Data.OleDb;
  7 using System.ComponentModel;
  8 
  9 using System.Reflection;
 10 using Excel;
 11 namespace ExcelHandle
 12 {
 13     /// <summary>
 14     /// 项目名：Excel操作
 15     /// 程序员：Vseen
 16     /// 日期/时间：2008.12.16
 17     /// 联系QQ：56222777
 18     /// Mail：XinSet@vip.qq.com
 19     /// </summary>
 20     public class Excel
 21     {
 22         /// <summary>
 23         /// Excel操作。
 24         /// </summary>
 25         public Excel()
 26         {
 27             ofd.Title = "打开Excel";
 28             ofd.Multiselect = false;
 29             ofd.Filter = "Excel 2000 - Excel 2003|*.xls";
 30             ofd.AddExtension = true;
 31         }
 32         private System.Drawing.Font font = new System.Drawing.Font("隶书", 30, System.Drawing.FontStyle.Bold);
 33         private System.Drawing.Color color = System.Drawing.Color.Red;
 34         /// <summary>
 35         /// 标题格式。
 36         /// </summary>
 37         /// <param name="font">字体。</param>
 38         /// <param name="color">颜色。</param>
 39         public void SetTitleFont(System.Drawing.Font font, System.Drawing.Color color)
 40         {
 41             this.font = font;
 42             this.color = color;
 43         }
 44 
 45         #region save
 46         /// <summary>
 47         /// 保存Excel(SaveFileDialog)。
 48         /// </summary>
 49         /// <param name="dv">指定的DataGridView。</param>
 50         /// <param name="titlename">标题名称。</param>
 51         public void Save(System.Windows.Forms.DataGridView dv, string titlename)
 52         {
 53 
 54             try
 55             {
 56                 save(dv, titlename);
 57             }
 58             catch (Exception ex)
 59             {
 60                 System.Windows.Forms.MessageBox.Show("错误：\n" + ex.Message, "NIO工作室");
 61             }
 62             finally
 63             {
 64                 System.IO.File.Delete(@"c:\Keysadshjkfhjkfhjdskfhajkshf.xls");
 65                 GC.Collect();
 66             }
 67         }
 68 
 69         void save(System.Windows.Forms.DataGridView dv, string titlename)
 70         {
 71             Application excel = new Application();
 72             Workbook wBook = excel.Application.Workbooks.Add(true);
 73             Worksheet wSheet = wBook.ActiveSheet as Worksheet;
 74             object objOpt = Missing.Value;
 75 
 76             Range titleRange = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[1, dv.Columns.Count]);
 77             SetRange(titleRange, titlename, font, color);
 78             try
 79             {
 80                 for (int i = 1; i <= dv.Columns.Count; i++)
 81                 {
 82                     wSheet.Cells[2, i] = dv.Columns[i - 1].Name;
 83                 }
 84 
 85                 for (int i = 1; i < dv.Rows.Count; i++)
 86                 {
 87                     for (int j = 1; j <= dv.Columns.Count; j++)
 88                     {
 89                         wSheet.Cells[i + 2, j] = dv[j - 1, i - 1].Value;
 90                     }
 91                 }
 92 
 93                 titleRange = wSheet.get_Range(wSheet.Cells[1, 1], wSheet.Cells[wSheet.Rows.Count, wSheet.Columns.Count]);
 94                 titleRange.HorizontalAlignment = XlVAlign.xlVAlignCenter;
 95                 titleRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
 96                 excel.Save(@"c:\Keysadshjkfhjkfhjdskfhajkshf.xls");
 97             }
 98             catch (Exception ex)
 99             {
 
             }
             finally
             {
                 excel.Quit();
             }
         }
 
         private void SetRange(Range titleRange, string value, System.Drawing.Font font, System.Drawing.Color color)
         {
             titleRange.Merge(true);
             titleRange.Font.Name = font.Name;
             titleRange.Font.Size = font.Size;
             titleRange.Font.Bold = font.Bold;
             titleRange.Font.Underline = font.Underline;
             titleRange.Font.Italic = font.Italic;
             titleRange.Font.Color = System.Drawing.ColorTranslator.ToOle(color);
             titleRange.Value2 = value;
         }
         #endregion
 
         System.Windows.Forms.OpenFileDialog ofd = new System.Windows.Forms.OpenFileDialog();
 
         #region open
         /// <summary>
         /// 往指定的数据集载入Excel。
         /// </summary>
         /// <param name="mySet">数据集。</param>
         /// <param name="tablename">表名。</param>
         public void Open(DataSet mySet, string tablename)
         {
             try
             {
                 if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                     open(ofd.FileName,mySet, tablename);
             }
             catch(Exception ex)
             {
                 System.Windows.Forms.MessageBox.Show("错误：\n" + ex.Message, "NIO工作室");
             }
             finally
             {
                 GC.Collect();
             }
         }
         void open(string excelFile, DataSet mySet, string tablename)
         {
             object objOpt = Missing.Value;
             Application excel = new Application();
             try
             {
                 Workbook wBook = excel.Application.Workbooks.Open(excelFile, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt, objOpt);
                 Worksheet wSheet = wBook.ActiveSheet as Worksheet;
                 for (int i = 3; i <= wSheet.Rows.Count; i++)
                 {
                     if (((Range)wSheet.Cells[i, 1]).Value2 == null) return;
                     List<object> li = new List<object>();
 
                     for (int j = 1; j <= mySet.Tables[tablename].Columns.Count; j++)
                     {
                         object obj = ((Range)wSheet.Cells[i, j]).Value2;
 
                         if (obj != null)
                             li.Add(obj);
                         else
                             li.Add(null);
                     }
                     mySet.Tables[tablename].Rows.Add(li.ToArray());
                 }
             }
             catch (Exception ex)
             {
 
             }
             finally
             {
                 excel.Quit();
             }
         }
         #endregion
 
     }
 }