C#通过oledb操作Excel实例代码-木庄网络博客

本文摘自PHP中文网，作者黄舟，侵删。

本篇文章主要介绍了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;
    }