C # example of interaction with Excel

// Add an Excel Object package here. Is to add a reference

Using system;
Using system. drawing;
Using system. collections;
Using system. componentmodel;
Using system. Windows. forms;

Namespace exceltest
{
/// <Summary>
/// Summary of form3.
/// </Summary>
Public class form3: system. Windows. Forms. Form
{
Private system. Windows. Forms. Button button1;
Private system. Windows. Forms. ComboBox combox1;
/// <Summary>
/// Required designer variables.
/// </Summary>
Private system. componentmodel. Container components = NULL;
// Excel Object references.
Private excel. Application m_objexcel = NULL;
Private excel. workbooks m_objbooks = NULL;
Private excel. _ workbook m_objbook = NULL;
Private excel. Sheets m_objsheets = NULL;
Private excel. _ worksheet m_objsheet = NULL;
Private excel. Range m_objrange = NULL;
Private excel. Font m_objfont = NULL;
Private excel. querytables m_objqrytables = NULL;
Private excel. _ querytable m_objqrytable = NULL;

// Frequenty-used variable for optional arguments.
Private object m_objopt = system. reflection. Missing. value;

// Paths used by the sample code for accessing and storing data.
Private string m_strnorthwind = @ "C:/program files/Microsoft Visual Studio/vb98/nwind. mdb ";

Public form3 ()
{
//
// Required for Windows Form Designer support
//
Initializecomponent ();

//
// Todo: add Any constructor code after initializecomponent calls
//
}

/// <Summary>
/// Clear all resources in use.
/// </Summary>
Protected override void dispose (bool disposing)
{
If (disposing)
{
If (components! = NULL)
{
Components. Dispose ();
}
}
Base. Dispose (disposing );
}

# Region code generated by Windows Form Designer
/// <Summary>
/// The designer supports the required methods-do not use the code editor to modify
/// Content of this method.
/// </Summary>
Private void initializecomponent ()
{
This. button1 = new system. Windows. Forms. Button ();
This. combox1 = 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. button#click );
//
// Combox1
//
This. combobox1.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. combox1 );
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 button#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, Excel. xlsaveasaccessmode. xlnochange,
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
// 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, Excel. xlsaveasaccessmode. xlnochange,
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
// Worksheet starting at cell A1.
Object [] objheaders = new object [nfields];
ADODB. Field objfield = NULL;
For (INT n = 0; n <nfields; n ++)
{
Objfields. movenext ();
Objfield = (ADODB. Field) objfields. Current;
Objheaders [N] = objfield. Name;
}
M_objrange = m_objsheet.get_range ("A1", m_objopt );
M_objrange = m_objrange.get_resize (1, nfields );
M_objrange.set_value (m_objopt, objheaders );
M_objfont = m_objrange.font;
M_objfont.bold = true;

// 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, Excel. xlsaveasaccessmode. xlnochange,
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. xlinsertentirerows;
M_objqrytable.refresh (false );

// Save the workbook and quit excel.
M_objbook.saveas (application. startuppath + "// book4.xls", 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, 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
// 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 (objreader. getstring (I ));
}
}
If (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.
Objreader. Close ();
Objconn. Close ();

// ================================================ ======================================
// Optionally, automate EXCEL to open the text file and save it in
// 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, 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, 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; extended properties = Excel 8.0 ;");
Objconn. open ();

// 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, lastname)" +
"Values ('Joe ', 'Thomas ')";
Objcmd. executenonquery ();

// Close the connection.
Objconn. Close ();

}

// End class

}
}