Example of C # interaction with Excel

excel| Interaction | example//Add a wrapper for an Excel object 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 description of the FORM3.
</summary>
public class Form3:System.Windows.Forms.Form
{
Private System.Windows.Forms.Button button1;
Private System.Windows.Forms.ComboBox ComboBox1;
<summary>
The required designer variable.
</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 Forms Designer support
//
InitializeComponent ();

//
TODO: Add any constructor code after the InitializeComponent call
//
}

<summary>
Clean up all resources that are in use.
</summary>
protected override void Dispose (bool disposing)
{
if (disposing)
{
if (Components!= null)
{
Components. Dispose ();
}
}
Base. Dispose (disposing);
}

Code generated #region the Windows forms Designer
<summary>
Designer supports required methods-do not use the Code editor to modify
The contents of this method.
</summary>
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.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.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 the automation to Transfer the Data cell by cell",
"Use Automation to Transfer an Array of Data to a worksheet",
"Use Automation-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.Application ();
M_objbooks = (excel.workbooks) m_objexcel.workbooks;
M_objbook = (Excel._workbook) (M_objbooks.add (m_objopt));

ADD data to cells of the the the ' the ' the ' the ' 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, "The Name of the");
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 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 to 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<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, 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 (
"OLE DB; 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 "the" 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 The 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 (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 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_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

}
}