//这里加添加一个excel对象的包装器。就是添加一个引用
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
namespace ExcelTest
{
///
/// Form3 的摘要说明。
///
public class Form3 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.ComboBox comboBox1;
///
/// 必需的设计器变量。
///
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 调用后添加任何构造函数代码
//
}
///
/// 清理所有正在使用的资源。
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows 窗体设计器生成的代码
///
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
///
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
{
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_objOp
