整理文档，搜刮出一个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://HdhCmsTestcnblogs测试数据/lizikang2013/p/6930833.html?utm_source=tuicool&utm_medium=referral
dy("nrwz");
查看更多关于C# 通过 oledb 操作Excel实例代码的详细内容...