C # interact with Excel

/// private void initializecomponent () {this.button1 = new system.windows.Forms.Button (); this.comboBox1 = new system.windows.forms.comBOBOX (); this.suspendlayout (); / / // button1 // this.button1.location = new system.drawing.point (208, 136); this.button1.name = "button1"; this.button1.size = new system.drawing.size (128, 32 This.button1.tabindex = 0; this.Button1.text = "button1"; this.button1.click = new system.eventhandler (this.button1_click); // // ComboBox1 // this.comboBOBOX1.LOCATION = New system.drawing.point (112, 40); this.comboBox1.name = "combobox1"; this.comboBox1.size = new system.drawing.size (376, 20); this.comboBox1.tabindex = 1; this. ComboBOX1.Text = "ComboBox1"; // // form3 // this.autoscalebasesize = new system.drawing.size (6, 14); this.clientsize = new system.drawing.size (544, 333); this.Controls .Add (this.comboBox1); this.controls.add (this.button1); this.name = "form3"; this.text = "form3"; this.Load = new System.EventHandler (this.Form3_Load); this.ResumeLayout (false);} #endregion [STAThread] static void Main () {Application.Run (new Form3 ());} private void Form3_Load (object sender, System.EventArgs e) {comboBox1.DropDownStyle = ComboBoxStyle.DropDownList; comboBox1.Items.AddRange (new object [] { "Use Automation to Transfer Data Cell by Cell", "Use Automation to Transfer an Array of Data to a Range ON a Worksheet, "Use Automation to Transfer An Ado Recordset to a Worksheet Range"

, "Use Automation to Create a QueryTable on a Worksheet", "Use the Clipboard", "Create a Delimited Text File that Excel Can Parse into Rows and Columns", "Transfer Data to a Worksheet Using ADO.NET"}); comboBox1 .SelectedIndex = 0; button1.Text = "! Go";} private void button1_Click (object sender, System.EventArgs e) {switch (comboBox1.SelectedIndex) {case 0: Automation_CellByCell (); break; case 1: Automation_UseArray () Break; Case 2: Automation_adorecordSet (); Break; Case 3: Automation_QueryTable (); Break; Case 4: USE_CLIPBOARD (); Break; Case 5: CREATE_TEXTFILE (); Break; Case 6: USE_ADONET (); Break;} / / Clean-up m_objFont = null; m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBooks = null; m_objBook = null; m_objExcel = null; GC.Collect ();} private void Automation_CellByCell () {// Start a new Workbook in Excel. m_objexcel = new Excel.Applica tion (); m_objBooks = (Excel.Workbooks) m_objExcel.Workbooks; m_objBook = (Excel._Workbook) (m_objBooks.Add (m_objOpt));. // Add data to cells of the first worksheet in the new workbook m_objSheets = (Excel .Sheets) m_objBook.Worksheets; m_objSheet = (Excel._Worksheet) (m_objSheets.get_Item (1)); m_objRange = m_objSheet.get_Range ( "A1", m_objOpt); m_objRange.set_Value (m_objOpt, "Last Name"); m_objRange = m_objSheet.get_Range ( "B1", m_objOpt); m_objRange.set_Value (m_objOpt, "First Name"); m_objRange = m_objSheet.get_Range ( "A2", m_objOpt); m_objRange.set_Value (m_objOpt, "Doe");

m_objRange = m_objSheet.get_Range ( "B2", m_objOpt); m_objRange.set_Value (m_objOpt, "John"); // Apply bold to cells A1:. B1 m_objRange = m_objSheet.get_Range ( "A1", "B1"); m_objFont = m_objRange.Font; m_objFont.Bold = true;. // Save the workbook and quit Excel m_objBook.SaveAs (Application.StartupPath "//Book1.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode. xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close (false, m_objOpt, m_objOpt); m_objExcel.Quit ();} private void Automation_UseArray () {// Start a new workbook in Excel m_objExcel = new Excel. .Application (); m_objBooks = (Excel.Workbooks) m_objExcel.Workbooks; m_objBook = (Excel._Workbook) (m_objBooks.Add (m_objOpt)); m_objSheets = (Excel.Sheets) m_objBook.Worksheets; m_objSheet = (Excel._Worksheet) (m_objsheets.get_item (1)); // Create An Array for the Headers and add it to cells a1: c1. Object [] objHeaders = {"ORDER ID", "Amount", "T ax "}; m_objRange = m_objSheet.get_Range (" A1 "," C1 "); m_objRange.set_Value (m_objOpt, objHeaders); m_objFont = m_objRange.Font; m_objFont.Bold = true; // Create an array with 3 columns and 100 Rowsheet Starting At Cell A2. Object [,] Objdata = New Object [100, 3]; Random RDM = New Random (INT) DATETIME.NOW.TICKS; Double NorderAmt, NTax; for (int R = 0; R <100; R ) {Objdata [r, 0] = "ORD" R.TOString ("0000"); Norderamt = rdm.next (1000); Objdata [r, 1] = Norderamt .Tostring ("c"); NTAX =

nOrderAmt * 0.07; objData [r, 2] = nTax.ToString ( "c");} m_objRange = m_objSheet.get_Range ( "A2", m_objOpt); m_objRange = m_objRange.get_Resize (100,3); m_objRange.set_Value (m_objOpt , "objData");. // Save the workbook and quit Excel m_objBook.SaveAs (Application.StartupPath "//Book2.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt , m_objOpt, m_objOpt, m_objOpt); m_objBook.Close (false, m_objOpt, m_objOpt); m_objExcel.Quit ();} private void Automation_ADORecordset () {// Create a Recordset from all the records in the Orders table ADODB.Connection objConn. = new adodb.connection (); adoDb._recordset objrs = null; objconn.Open ("provider = microsoft.jet.Oledb.4.0; data source =" m_strnorthwind ";", ",", ", 0); Objconn.cursorlocation = adodb.cursorlocationenum.aduseclient; object objrecaff; objrs = (adoDb._recordset) Objconn.execute ("Orders", ObjRecaff, (int) adoDb.comma ndTypeEnum.adCmdTable); // Start a new workbook in Excel m_objExcel = new Excel.Application. (); m_objBooks = (Excel.Workbooks) m_objExcel.Workbooks; m_objBook = (Excel._Workbook) (m_objBooks.Add (m_objOpt)); m_objSheets = (Excel.Sheets) m_objBook.Worksheets; m_objSheet = (Excel._Worksheet) (m_objSheets.get_Item (1));. // Get the Fields collection from the recordset and determine // the number of fields (or columns) System .Collections.ienumerator objfields = objrs.fields.getenumerator (); int nfields = objrs.fields.count;

// create an array for the headers and add it to the // Worksheet Starting At Cell A1. Object [] objHeaders = new object [nfields]; adodb.field objfield = null; for (int N = 0; n

(Excel._QueryTable) m_objQryTables.Add ( "OLEDB; Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" m_strNorthwind ";", m_objRange, "Select * From Orders"); m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode .xlInsertEntireRows; m_objQryTable.Refresh (false);. // Save the workbook and quit Excel m_objBook.SaveAs (Application.StartupPath "//Book4.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange , m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close (false, m_objOpt, m_objOpt); m_objExcel.Quit ();.} private void Use_Clipboard () {// Copy a string to the clipboard string sData = "FirstName / TLASTNAME / TBIRTHDATE / R / N " " BILL / TBROWN / T2 / 5/85 / R / N " " Joe / Tthomas / T1 / 1/91 "; System.Windows.Forms.Clipboard.SetDataObject (SDATA) // start a new workbook in excel. M_objexcel = new excel.application (); m_objbooks = (Excel.workBooks) m_objexcel.workbooks; m_objbook = (Excel._workbook) (m_objbooks. Add (m_objOpt)); // Paste the data starting at cell A1 m_objSheets = (Excel.Sheets) m_objBook.Worksheets; m_objSheet = (Excel._Worksheet) (m_objSheets.get_Item (1).); M_objRange = m_objSheet.get_Range ( " A1 ", m_objOpt); m_objSheet.Paste (m_objRange, false);. // Save the workbook and quit Excel m_objBook.SaveAs (Application.StartupPath " //Book5.xls ", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.xlsaveasaccessmode.xlnochange, m_objopt, m_objopt, m_objopt, m_objopt, m_objopt; m_objbook.close (false, m_objopt, m_objopt); m_objexcel.quit ();

} Private void Create_TextFile () {// Connect to the data source System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection ( "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =". M_strNorthwind ";"); objConn.Open ();. // Execute a command to retrieve all records from the Employees table System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand ( "Select * from Employees" , objConn); System.Data.OleDb.OleDbDataReader objReader; objReader = objCmd.ExecuteReader ();. // Create the FileStream and StreamWriter object to write // the recordset contents to file System.IO.FileStream fs = new System.IO .Filestream (Application.startuppath "//book6.txt", system.io.filemode.create; system.io.streamwriter sw = new system.io.StreamWriter (fs, system.text.encoding.unicode); / / Write the Field Names (Headers) as the first line in the text file. Sw.writeline (ObjReader.getname (0) "/ t" ObjReader.getname (1) "/ T " objreader.getname (2) " / t " Objreader.getname (3) " / t " Objreader.getName (4) " / t " ObjReader.getname (5)); // Write THE First Six Column in The Record To a Text File As // Tab-Delimited. While (ObjReader.Read ()) {for (INT i = 0; i <= 5; i ) {if (! Objreader.IsDBnull (i) ) {String S; s = objreader.getDataTypename (i); if (Objreader.getDataTypename (i) == "dbtype_i4") {sw.write (ObjReader.GetInt32 (i) .tostring ());} elseiff (ObjReader) .GetDataTypename (i) == "DBTYPE_DATE"

) {Sw.write (i) .tostring ("d"));} else if (ObjReader.GetDataTypename (i) == "dbtype_wvarchar") {sw.write (ObjReader.getstring (i)); }}} F ("/ t");} sw.writeline ();} sw.flush (); // Write the Buffered Data to the filestream. // Close The fileStream. Fs. Close (); // close the reader and the connection (); objconn.close (); / / ======================== =========================================================== // Optionally, Automate Excel To . open the text file and save it in the // Excel workbook format // Open the text file in Excel m_objExcel = new Excel.Application ();. m_objBooks = (Excel.Workbooks) m_objExcel.Workbooks; m_objBooks.OpenText (Application. StartupPath "//Book6.txt", Excel.XlPlatform.xlWindows, 1, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false, false, false, false, m_objOpt, m_objOpt, m_objO pt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook = m_objExcel.ActiveWorkbook;. // Save the text file in the typical workbook format and quit Excel m_objBook.SaveAs (Application.StartupPath "//Book6.xls", Excel. XLFILEFORMAT.XLWORKBOOKNORMAL, M_OBJOPT, M_OBJOPT, M_OBJOPT, M_OBJOPT, Excel.xlsaveasaccessMode.xlnochange, M_Objopt, M_Objopt, M_Objopt, M_Objopt, M_Objopt;

m_objBook.Close (false, m_objOpt, m_objOpt); m_objExcel.Quit ();.} private void Use_ADONET () {// Establish a connection to the data source System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb. OLEDBCONNECTION ("provider = microsoft.jet.Oledb.4.0; data source =" application.startuppath "//book7.xls; encel 8.0;"); objconn.open (); // add two (); // add two records to the table named 'MyTable' System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand ();. objCmd.Connection = objConn; objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" "values ('Bill', 'Brown') "; Objcmd.executenonQuery (); objcmd.commandtext =" Insert Into MyTable (FirstName) " " VALUES ('Joe', 'Thomas') "; objcmd.executenonQuery () } // end class} author BLOG:

http://blog.9cbs.net/greystar/