Example of C # interaction with Excel

Source: Internet
Author: User
Tags range tostring visual studio
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

}
}





Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.