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;
    }

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