//这里加添加一个excel对象的包装器。就是添加一个引用
using system;
using system.drawing;
using system.collections;
using system.componentmodel;
using system.windows.forms;
namespace exceltest
{
/// <summary>
/// form3 的摘要说明。
/// </summary>
public class form3 : system.windows.forms.form
{
private system.windows.forms.button button1;
private system.windows.forms.combobox combobox1;
/// <summary>
/// 必需的设计器变量。
/// </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()
{
//
// windows 窗体设计器支持所必需的
//
initializecomponent();
//
// todo: 在 initializecomponent 调用后添加任何构造函数代码
//
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.dispose();
}
}
base.dispose( disposing );
}
#region windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </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 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 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 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, 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 100 rows 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 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(
"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, 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 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 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
}
}
