C# 通过 oledb 操作Excel实例代码

整理文档，搜刮出一个C# 通过 oledb 操作Excel实例代码，稍微整理精简一下做下分享。

public string GetConnectionString()
    {
      Dictionary props = new Dictionary();
 
      // XLSX - Excel 2007, 2010, 2012, 2013
      props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
      props["Extended Properties"] = "Excel 12.0 XML";
      props["Data Source"] = @"C:toolsMyExcel.xlsx";
 
      // XLS - Excel 2003 and Older
      //props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
      //props["Extended Properties"] = "Excel 8.0";
      //props["Data Source"] = "C:\MyExcel.xls";
 
      var sb = new StringBuilder();
 
      foreach (KeyValuePair prop in props)
      {
 sb.Append(prop.Key);
 sb.Append('=');
 sb.Append(prop.Value);
 sb.Append(';');
      }
 
      return sb.ToString();
    }
 
    public void WriteExcelFile()
    {
      string connectionString = GetConnectionString();
 
      using (OleDbConnection conn = new OleDbConnection(connectionString))
      {
 conn.Open();
 OleDbCommand cmd = new OleDbCommand();
 cmd.Connection = conn;
 
 cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
 cmd.ExecuteNonQuery();
 
 cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
 cmd.ExecuteNonQuery();
 
 cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";
 cmd.ExecuteNonQuery();
 
 cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";
 cmd.ExecuteNonQuery();
 
 cmd.CommandText = "UPDATe [table1] SET name = 'DDDD' WHERe id = 3;";
 cmd.ExecuteNonQuery();
 
 conn.Close();
      }
    }
 
    public DataSet ReadExcelFile()
    {
      DataSet ds = new DataSet();
 
      string connectionString = GetConnectionString();
 
      using (OleDbConnection conn = new OleDbConnection(connectionString))
      {
 conn.Open();
 OleDbCommand cmd = new OleDbCommand();
 cmd.Connection = conn;
 
 // Get all Sheets in Excel File
 DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 
 // Loop through all Sheets to get data
 foreach (DataRow dr in dtSheet.Rows)
 {
   string sheetName = dr["TABLE_NAME"].ToString();
 
   if (!sheetName.EndsWith("$"))
     continue;
 
   // Get all rows from the Sheet
   cmd.CommandText = "SELECt * FROM [" + sheetName + "]";
 
   DataTable dt = new DataTable();
   dt.TableName = sheetName;
 
   OleDbDataAdapter da = new OleDbDataAdapter(cmd);
   da.Fill(dt);
 
   ds.Tables.Add(dt);
 }
 
 cmd = null;
 conn.Close();
      }
 
      return ds;
    }

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持考高分网。