Excel程式:使用Oledb(C#)

  首先須引用System.Data.OleDb，接著是定義變數部分，如下:

   1: OleDbConnection OleDBConn_Excel = new OleDbConnection();

   2: OleDbCommand OleDBCmd_Excel = new OleDbCommand();

   3: OleDBCmd_Excel.CommandType = CommandType.Text;

   4: OleDBCmd_Excel.Connection = OleDBConn_Excel;

  第一部份是要連線Connect的部份，如下:

   1: private bool ConnectExcelDB(string ExcelFilePath, bool isFirstRowHeader, out string RetDesc)

   2: {

   3:     string header = isFirstRowHeader ? "Yes" : "No";

   4:     //Microsoft.ACE.OLEDB.12.0                

   5:     OleDBConn_Excel.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;DataSource=" +        ExcelFilePath +   ";Extended Properties=\"Excel 12.0;HDR=" + header + "\"";

   6:     try

   7:     {

   8:         if (OleDBConn_Excel.State == ConnectionState.Open)

   9:             OleDBConn_Excel.Close();

  10:         OleDBConn_Excel.Open();

  11:         RetDesc = string.Empty;

  12:         return true;

  13:     }

  14:     catch (OleDbException ex)

  15:     {

  16:         RetDesc = ex.Message;

  17:         return false;

  18:     }

  19: }

先解釋一下上面的傳入變數，ExcelFilePath，顧名思義就是Excel檔案所在路徑；isFirstRowHeader變數則是用於設定Excel檔案Sheet中的資料列第一列是否為欄位名稱，若設定為true，表示第一列當作欄位名稱，反則無；RetDesc則是回傳字串，若出現Error則會帶ErrorMessage回傳，反則屬空字串，之後的Function皆會使用到這個變數，因此爾後遇到不再多加解釋。利用Microsoft.ACE.OLEDB.12.0將OledbConn_Excel打開。 接著是先示範一下Connect成功後做一個DataReader的部份，如下: 

   1: /// <summary>

   2: /// 取得ExcelDB DataReader物件

   3: /// </summary>

   4: /// <param name="SqlStr">Sql語句</param>

   5: /// <param name="RetDesc">錯誤回傳訊息</param>

   6: /// <returns>回傳OleDBDataReader物件</returns>

   7: public OleDbDataReader GetExcelDBDataReader(string SqlStr, out string RetDesc)

   8: {

   9:     if (mDBType != DBType.Excel)

  10:     {

  11:         RetDesc = "DB Type is not Excel"; 

  12:         return null;

  13:     }

  14:     //假如未建立連線則先建立

  15:     RetDesc = string.Empty;

  16:     if (OleDBConn_Excel == null)

  17:         if (!ConnectExcelDB(mExcelFilePath,misFirstRowHeader, out RetDesc))

  18:             return null;

  19:     try 

  20:     {

  21:         //假如連線中斷，則先Open之

  22:         if (OleDBConn_Excel.State == ConnectionState.Closed)

  23:             OleDBConn_Excel.Open();

  24:         OleDBCmd_Excel.CommandText = SqlStr;

  25:         return OleDBCmd_Excel.ExecuteReader();

  26:         }

  27:     catch (Exception ex)

  28:     {

  29:         RetDesc = ex.Message;

  30:         return null;

  31:     }

  32: }

是否為null，若是則使用上面Func:ConnectExcelDB連線，最後面將傳入的Sqlstr指定至OleDBCmd_Excel之CommandText，最後回傳ExcuteReader之結果。最後我們做事情要有始有終，連線打開了記得要關閉它，DisconnectFunction如下:

   1: private void CloseExcelDB()

   2: {

   3:     try

   4:     {

   5:         if (OleDBConn_Excel.State == ConnectionState.Open)

   6:             OleDBConn_Excel.Close();

   7:         

   8:         OleDBConn_Excel.Dispose();  //釋放資源 

   9:         }

  10:     catch (Exception ex)

  11:     { 

  12:         throw new Exception(ex.Message);

  13:     }

  14: }

我們來做一下實例吧，使用上面這些Function，我使用的範例如下:



接著如下圖拉出一個Button以及Label，Button用意是我們把讀取Excel的程式撰寫在click事件中，接著將結果秀在Label上面。



接著是著手寫Button_Click事件，如下:

   1: private void btnRead_Click(object sender, EventArgs e)

   2: {

   3:     string path = Application.StartupPath + @"\TestExcel.xlsx";

   4:     string Errmsg = "";

   5:     if (ConnectExcelDB(path,false,out Errmsg))

   6:     {

   7:         //Read:OleDbDataReader

   8:         string sqlstr = "SELECT * FROM [Sheet1$B2:B2] ";

   9:         OleDbDataReader excelreader = GetExcelDBDataReader(sqlstr, out Errmsg);

  10:         if (Errmsg == "")

  11:         {

  12:             if (excelreader.Read())

  13:             {

  14:                 label1.Text = excelreader[0].ToString();

  15:             }

  16:             excelreader.Close();

  17:         }

  18:         else

  19:             MessageBox.Show(Errmsg);

  20:         CloseExcelDB();

  21:     }

  22:     else

  23:         MessageBox.Show(Errmsg);

  24: } 

上面的語法就是我要撈出Sheet1當中的B2位置的值，執行結果如下: