首页 > 编程 > C# > 正文

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

2019-10-29 21:10:03
字体:
来源:转载
供稿:网友

整理文档,搜刮出一个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;    }

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


注:相关教程知识阅读请移步到c#教程频道。
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表