Source Viewer: adonet-excel.cs
Font Size:
1
2
3
4
5
6
7
adonet-excel.cs
AutoExcelImport.cs
AutomateExcel.cs
CopyData-SqlToExcel-TwoOledbConnections.cs
ExtractToExcel.cs
ExtractToExcel2.cs
..
// adonet-excel.cs // // insert into MS Excel via ADO.NET // // refs: // http://support.microsoft.com/?kbid=316934 // http://msdn.microsoft.com/library/en-us/odbc/htm/odbcjetsdk_43.asp // // // Tue, 16 Sep 2003 13:29 // public class TestAdoNetExcel { System.Data.OleDb.OleDbConnection conn; System.Random r= new System.Random(); private void CreateTable() { System.Console.WriteLine("Create..."); string strSql = "CREATE TABLE SampleTable ( Ix NUMBER, CustName char(255), Stamp datetime )"; System.Data.OleDb.OleDbCommand cmd= new System.Data.OleDb.OleDbCommand(strSql, conn); try { conn.Open(); cmd.ExecuteNonQuery(); } catch (System.Exception e2){ if (!e2.Message.Trim().EndsWith("already exists.")) System.Console.WriteLine("Error while creating. " + e2); else System.Console.WriteLine("Table already exists..."); } finally { conn.Close(); } } private void Insert() { System.Console.WriteLine("Insert..."); string strSql = "insert into [SampleTable] ([ix],[CustName],[Stamp]) values(@p1,@p2,@p3)"; System.Data.OleDb.OleDbCommand cmd= new System.Data.OleDb.OleDbCommand(strSql, conn); cmd.Parameters.Add("@p1", System.Data.OleDb.OleDbType.Numeric).Value = r.Next(42); cmd.Parameters.Add("@p2", System.Data.OleDb.OleDbType.VarChar).Value = "Some text"; cmd.Parameters.Add("@p3", System.Data.OleDb.OleDbType.Date).Value = System.DateTime.Now; try { conn.Open(); cmd.ExecuteNonQuery(); } catch (System.Exception e2){ System.Console.WriteLine("Error while inserting. " + e2); } finally { conn.Close(); } } public void Run() { const string Filename= "adonet-excel.xls"; //const string Filename= "Book1.xls"; const string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Filename + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;\""; // FIRSTROWHASNAMES=1;READONLY=false\" System.Console.WriteLine("ADO.NET -> XLS..."); //if (System.IO.File.Exists(Filename)) //System.IO.File.Delete(Filename); try { conn = new System.Data.OleDb.OleDbConnection(strConnect); CreateTable(); int N= r.Next(5)+2; for(int i=0; i < N; i++) Insert(); } catch (System.Exception ex) { System.Console.WriteLine("Exception: " + ex.Message+ "\n " + ex.StackTrace); } System.Console.WriteLine("launching Excel..."); System.Diagnostics.Process.Start(Filename); } public static void Main() { TestAdoNetExcel test= new TestAdoNetExcel(); test.Run(); } }
The srcview page has been enjoyed 291584 times since 18 September 2003