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

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

public string GetConnectionString()
   {
     Dictionary<string, string> props = new Dictionary<string, string>();

// XLSX - Excel 2007, 2010, 2012, 2013
     props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
     props["Extended Properties"] = "Excel 12.0 XML";
     props["Data Source"] = @"C:\tools\MyExcel.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<string, string> 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;
   }

来源：http://www.cnblogs.com/lizikang2013/p/6930833.html?utm_source=tuicool&utm_medium=referral