C # example of interaction with Excel

Source: Internet
Author: User

// 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

}
}

Related Article

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.