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

 

}

}

C # example of interaction with Excel