C# 通过 oledb 操作Excel实例代码
整理文档，搜刮出一个C#通过oledb操作Excel实例代码，稍微整理精简一下做下分享。
publicstringGetConnectionString()
{
Dictionaryprops=newDictionary();
//XLSX-Excel2007,2010,2012,2013
props["Provider"]="Microsoft.ACE.OLEDB.12.0;";
props["ExtendedProperties"]="Excel12.0XML";
props["DataSource"]=@"C:\tools\MyExcel.xlsx";
//XLS-Excel2003andOlder
//props["Provider"]="Microsoft.Jet.OLEDB.4.0";
//props["ExtendedProperties"]="Excel8.0";
//props["DataSource"]="C:\\MyExcel.xls";
varsb=newStringBuilder();
foreach(KeyValuePairpropinprops)
{
sb.Append(prop.Key);
sb.Append('=');
sb.Append(prop.Value);
sb.Append(';');
}
returnsb.ToString();
}
publicvoidWriteExcelFile()
{
stringconnectionString=GetConnectionString();
using(OleDbConnectionconn=newOleDbConnection(connectionString))
{
conn.Open();
OleDbCommandcmd=newOleDbCommand();
cmd.Connection=conn;
cmd.CommandText="CREATETABLE[table1](idINT,nameVARCHAR,datecolDATE);";
cmd.ExecuteNonQuery();
cmd.CommandText="INSERTINTO[table1](id,name,datecol)VALUES(1,'AAAA','2014-01-01');";
cmd.ExecuteNonQuery();
cmd.CommandText="INSERTINTO[table1](id,name,datecol)VALUES(2,'BBBB','2014-01-03');";
cmd.ExecuteNonQuery();
cmd.CommandText="INSERTINTO[table1](id,name,datecol)VALUES(3,'CCCC','2014-01-03');";
cmd.ExecuteNonQuery();
cmd.CommandText="UPDATE[table1]SETname='DDDD'WHEREid=3;";
cmd.ExecuteNonQuery();
conn.Close();
}
}
publicDataSetReadExcelFile()
{
DataSetds=newDataSet();
stringconnectionString=GetConnectionString();
using(OleDbConnectionconn=newOleDbConnection(connectionString))
{
conn.Open();
OleDbCommandcmd=newOleDbCommand();
cmd.Connection=conn;
//GetallSheetsinExcelFile
DataTabledtSheet=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
//LoopthroughallSheetstogetdata
foreach(DataRowdrindtSheet.Rows)
{
stringsheetName=dr["TABLE_NAME"].ToString();
if(!sheetName.EndsWith("$"))
continue;
//GetallrowsfromtheSheet
cmd.CommandText="SELECT*FROM["+sheetName+"]";
DataTabledt=newDataTable();
dt.TableName=sheetName;
OleDbDataAdapterda=newOleDbDataAdapter(cmd);
da.Fill(dt);
ds.Tables.Add(dt);
}
cmd=null;
conn.Close();
}
returnds;
}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。