C#如何通过oledb操作Excel-创新互联
小编给大家分享一下C#如何通过oledb操作Excel,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!
成都创新互联公司坚持“要么做到,要么别承诺”的工作理念,服务领域包括:成都网站制作、成都网站设计、企业官网、英文网站、手机端网站、网站推广等服务,满足客户于互联网时代的鄂伦春网站设计、移动媒体设计的需求,帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴!整理文档,搜刮出一个C# 通过 oledb 操作Excel实例代码,稍微整理精简一下做下分享。
public string GetConnectionString() { Dictionaryprops = 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:\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 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; }
以上是“C#如何通过oledb操作Excel”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!
当前题目:C#如何通过oledb操作Excel-创新互联
本文地址:http://scyanting.com/article/ddhohi.html