讀寫Excel檔案

先要引用這些命名空間. 不明白的地方可以在msdn中找到.

using System.Data.OleDb;
using Excel;
using System.Reflection; // For Missing.Value and BindingFlags

/// <summary>
  /// 讀取Excel表格
  /// </summary>
  /// <param name="path">excel檔案全名</param>
  /// <param name="id">放到DataSet中的表名</param>
  public void readexcel(string path,string id)
  {
   try
   {
    string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " +
     path + " ; Extended Properties=Excel 8.0" ;
    OleDbConnection myConn = new OleDbConnection ( strCon ) ;
    string strCom = " SELECT * FROM [sheet1$]";
    myConn.Open ( ) ;
    OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
    myCommand.Fill (this.dataSet1,id) ;
    myConn.Close ( ) ;
    int ii=this.dataSet1.Tables[id].Rows.Count;
    }
   catch(System.Exception op)
   {
    MessageBox.Show("發生錯誤 "+op.Message);
   }
  } 

     首先將excel.exe copy 到 ../Microsoft Visual Studio .NET 2003/SDK/v1.1/Bin目錄下,利用.net 中帶的工具在命令提示字元下執行tlbimp excel.exe.這樣就不會因為你的Excel是xp或2000的不同要去找不同的*.olb檔案，還有一點就是因為在2000以後的版本中沒有了excel9.olb這個檔案了。通過執行tlbimp excel.exe後我們會得到excel.dll檔案。在工程中引用這個檔案就可以了.

/// <summary>
  /// 寫入並儲存Excel檔案
  /// </summary>
  /// <param name="path">儲存檔案的全檔案名稱</param>
  /// <param name="id">DataSet中的表名</param>
  private void wexcel2(string path ,string id)
  {
   int count = this.dataSet1.Tables[id].Rows.Count;
   int column = this.dataSet1.Tables[id].Columns.Count;

   Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
   // Make Excel Visible
   excelApp.Visible = false;
   Excel.Workbook workbook =
    excelApp.Workbooks.Open(fileName,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing);
  
   excelApp.Cells[1,1] = this.dataSet1.Tables[id].Rows[xx].ItemArray[0];

//.................................

//添加寫表的語句.xx,yy,x,y這幾個是示意性的變數.
   excelApp.Cells[x,y] = this.dataSet1.Tables[id].Rows[yy].ItemArray[5];
     
   string tname = "c://test.xls";
   workbook.SaveAs(tname,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
    Excel.XlSaveAsAccessMode.xlNoChange,
    Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
   
   try
   {
    workbook.Saved = true;
    excelApp.UserControl = false;
    excelApp.Quit();
   }
   catch (System.Exception op)//(COMException)
   {
    MessageBox.Show(op.Message +" /n User closed Excel manually, so we don't have to do that");
   }    
  }