C # manipulating Excel instance code through OLE DB

This article mainly introduces C # through OLE DB operation of Excel instance code, small series feel very good, and now share to everyone, but also for everyone to do a reference. Let's take a look at it with a little knitting.

Organize your documents, and loot a C # to manipulate Excel instance code through OLE DB, and sort it out a little bit to share.

public string getconnectionstring () {dictionary<string, string> props = new dictionary<string, String&gt       ;();      Xlsx-excel, 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 = newOleDbCommand (); 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; }