C # interact with Excel

[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 "Uses", "use the the clipboard", "use the clipboard", "Create a Delimited Text File That Excel Can Parse Into Rows and Column", "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.Application ();. 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", "Tax"}; 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 rows and add it to // the worksheet 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 ", out objRecAff, (int) ADODB.CommandTypeEnum.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

// Transfer the recordset to the worksheet starting at cell A2 m_objRange = m_objSheet.get_Range ( "A2", m_objOpt);. M_objRange.CopyFromRecordset (objRS, m_objOpt, m_objOpt);. // Save the workbook and quit Excel m_objBook.SaveAs ( Application.StartupPath "//Book3.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 ();

// close the recordset and connection objrs.close (); objconn.close ();

}

private void Automation_QueryTable () {// 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)) ;

// Create a QueryTable that starts 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_objQryTables = m_objSheet.QueryTables; m_objQryTable = (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.xlinsertirerows; 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 / tbpterdate / 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 columns in the recordset 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 ());} Else if (ObjReader.getDataTypename (i) == "dbtype_date") {sw.write (ObjReader.getdatetime (i) .tostring ("d"));} else if (ObjReader.GetDataTypename (i) == "dbtype_wvarchar" ) {Sw.write (I));}}} (i <5) sw.write ("/ 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 (); 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_objOpt, 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_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;Extended Properties = Excel 8.0;"); Objconn ();

// 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 ();

// close the connection ();}

// End Class

}