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(KeyValuePair propinprops) { 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; }
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。