ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

3.添加下列引用

4.在Form1.h中插入以下代码，详细见代码分析与注释

pragma once  
 
 
namespace Yincheng {  
 
    using namespace System;  
    using namespace System::ComponentModel;  
    using namespace System::Collections;  
    using namespace System::Windows::Forms;  
    using namespace System::Data;  
    using namespace System::Drawing;  
 
 
    using namespace System::Data::OleDb;  
    using namespace System::Reflection;  
 
    /// <summary>  
    /// Form1 摘要  
    ///  
    /// 警告: 如果更改此类的名称，则需要更改  
    ///          与此类所依赖的所有 .resx 文件关联的托管资源编译器工具的  
    ///          “资源文件名”属性。否则，  
    ///          设计器将不能与此窗体的关联  
    ///          本地化资源正确交互。  
    /// </summary>  
    public ref class Form1 : public System::Windows::Forms::Form  
    {  
    public:  
        Form1(void)  
        {  
            InitializeComponent();  
            //  
            //TODO: 在此处添加构造函数代码  
            //  
        }  
 
    protected:  
        /// <summary>  
        /// 清理所有正在使用的资源。  
        /// </summary>  
        ~Form1()  
        {  
            if (components)  
            {  
                delete components;  
            }  
        }  
    private: System::Data::DataSet^  dataSet1;  
    protected:   
    private: System::Data::OleDb::OleDbCommand^  oleDbCommand1;  
    private: System::Data::OleDb::OleDbConnection^  oleDbConnection1;  
    private: System::Data::OleDb::OleDbCommand^  oleDbSelectCommand1;  
    private: System::Data::OleDb::OleDbCommand^  oleDbInsertCommand1;  
    private: System::Data::OleDb::OleDbCommand^  oleDbUpdateCommand1;  
    private: System::Data::OleDb::OleDbCommand^  oleDbDeleteCommand1;  
    private: System::Data::OleDb::OleDbDataAdapter^  oleDbDataAdapter1;  
    private: System::Windows::Forms::Label^  label1;  
    private: System::Windows::Forms::TextBox^  textBox1;  
    private: System::Windows::Forms::Button^  button1;  
    private: System::Windows::Forms::Button^  button2;  
    private: System::Windows::Forms::DataGridView^  dataGridView1;  
 
    private:  
        /// <summary>  
        /// 必需的设计器变量。  
        /// </summary>  
        System::ComponentModel::Container ^components; 
 
#pragma region Windows Form Designer generated code  
        /// <summary>  
        /// 设计器支持所需的方法 - 不要  
        /// 使用代码编辑器修改此方法的内容。  
        /// </summary>  
        void InitializeComponent(void)  
        {  
            System::ComponentModel::ComponentResourceManager^  resources = (gcnew System::ComponentModel::ComponentResourceManager(Form1::typeid));  
            this->dataSet1 = (gcnew System::Data::DataSet());  
            this->oleDbCommand1 = (gcnew System::Data::OleDb::OleDbCommand());  
            this->oleDbConnection1 = (gcnew System::Data::OleDb::OleDbConnection());  
            this->oleDbSelectCommand1 = (gcnew System::Data::OleDb::OleDbCommand());  
            this->oleDbInsertCommand1 = (gcnew System::Data::OleDb::OleDbCommand());  
            this->oleDbUpdateCommand1 = (gcnew System::Data::OleDb::OleDbCommand());  
            this->oleDbDeleteCommand1 = (gcnew System::Data::OleDb::OleDbCommand());  
            this->oleDbDataAdapter1 = (gcnew System::Data::OleDb::OleDbDataAdapter());  
            this->label1 = (gcnew System::Windows::Forms::Label());  
            this->textBox1 = (gcnew System::Windows::Forms::TextBox());  
            this->button1 = (gcnew System::Windows::Forms::Button());  
            this->button2 = (gcnew System::Windows::Forms::Button());  
            this->dataGridView1 = (gcnew System::Windows::Forms::DataGridView());  
            (cli::safe_cast<System::ComponentModel::ISupportInitialize^  >(this->dataSet1))->BeginInit();  
            (cli::safe_cast<System::ComponentModel::ISupportInitialize^  >(this->dataGridView1))->BeginInit();  
            this->SuspendLayout();  
            //   
            // dataSet1  
            //   
            this->dataSet1->DataSetName = L"NewDataSet";  
            //   
            // oleDbDataAdapter1  
            //   
            this->oleDbDataAdapter1->DeleteCommand = this->oleDbDeleteCommand1;  
            this->oleDbDataAdapter1->InsertCommand = this->oleDbInsertCommand1;  
            this->oleDbDataAdapter1->SelectCommand = this->oleDbSelectCommand1;  
            this->oleDbDataAdapter1->UpdateCommand = this->oleDbUpdateCommand1;  
            //   
            // label1  
            //   
            this->label1->AutoSize = true;  
            this->label1->ForeColor = System::Drawing::Color::Coral;  
            this->label1->Location = System::Drawing::Point(129, 38);  
            this->label1->Name = L"label1";  
            this->label1->Size = System::Drawing::Size(83, 12);  
            this->label1->TabIndex = 0;  
            this->label1->Text = L"SQL查询语句：";  
            //   
            // textBox1  
            //   
            this->textBox1->Location = System::Drawing::Point(208, 34);  
            this->textBox1->Name = L"textBox1";  
            this->textBox1->Size = System::Drawing::Size(145, 21);  
            this->textBox1->TabIndex = 1;  
            this->textBox1->Text = L"Select * From 客户";  
            //   
            // button1  
            //   
            this->button1->Location = System::Drawing::Point(357, 32);  
            this->button1->Name = L"button1";  
            this->button1->Size = System::Drawing::Size(75, 23);  
            this->button1->TabIndex = 2;  
            this->button1->Text = L"显示数据";  
            this->button1->UseVisualStyleBackColor = true;  
            this->button1->Click += gcnew System::EventHandler(this, &Form1::button1_Click);  
            //   
            // button2  
            //   
            this->button2->Location = System::Drawing::Point(432, 32);  
            this->button2->Name = L"button2";  
            this->button2->Size = System::Drawing::Size(80, 23);  
            this->button2->TabIndex = 3;  
            this->button2->Text = L"导出Excel表";  
            this->button2->UseVisualStyleBackColor = true;  
            this->button2->Click += gcnew System::EventHandler(this, &Form1::button2_Click);  
            //   
            // dataGridView1  
            //   
            this->dataGridView1->Anchor = static_cast<System::Windows::Forms::AnchorStyles>((((System::Windows::Forms::AnchorStyles::Top | System::Windows::Forms::AnchorStyles::Bottom)   
                | System::Windows::Forms::AnchorStyles::Left)   
                | System::Windows::Forms::AnchorStyles::Right));  
            this->dataGridView1->ColumnHeadersHeightSizeMode = System::Windows::Forms::DataGridViewColumnHeadersHeightSizeMode::AutoSize;  
            this->dataGridView1->Location = System::Drawing::Point(35, 58);  
            this->dataGridView1->Name = L"dataGridView1";  
            this->dataGridView1->RowTemplate->Height = 23;  
            this->dataGridView1->Size = System::Drawing::Size(601, 332);  
            this->dataGridView1->TabIndex = 4;  
            //   
            // Form1  
            //   
            this->AutoScaleDimensions = System::Drawing::SizeF(6, 12);  
            this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;  
            this->BackColor = System::Drawing::SystemColors::ActiveCaptionText;  
            this->BackgroundImage = (cli::safe_cast<System::Drawing::Image^  >(resources->GetObject(L"$this.BackgroundImage")));  
            this->ClientSize = System::Drawing::Size(674, 414);  
            this->Controls->Add(this->dataGridView1);  
            this->Controls->Add(this->button2);  
            this->Controls->Add(this->button1);  
            this->Controls->Add(this->textBox1);  
            this->Controls->Add(this->label1);  
            this->Name = L"Form1";  
            this->StartPosition = System::Windows::Forms::FormStartPosition::CenterScreen;  
            this->Text = L"CSDN技术专家尹成-演示使用自动化导出Excel文件";  
            (cli::safe_cast<System::ComponentModel::ISupportInitialize^  >(this->dataSet1))->EndInit();  
            (cli::safe_cast<System::ComponentModel::ISupportInitialize^  >(this->dataGridView1))->EndInit();  
            this->ResumeLayout(false);  
            this->PerformLayout();  
 
        } 
#pragma endregion  
    //显示数据  
    private: System::Void button1_Click(System::Object^  sender, System::EventArgs^  e) {  
         try 
         {  
            this->dataSet1 = gcnew DataSet();  
            if (this->oleDbConnection1->State == ConnectionState::Open)  
            this->oleDbConnection1->Close();   
            this->oleDbConnection1->ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Application::StartupPath+"\\Yincheng.mdb";  
            this->oleDbConnection1->Open();  
            String^ MySQL = this->textBox1->Text;  
            this->oleDbCommand1->CommandText=MySQL;  
            this->oleDbCommand1->Connection=this->oleDbConnection1;  
            this->oleDbDataAdapter1->SelectCommand=this->oleDbCommand1;  
            this->oleDbDataAdapter1->Fill(this->dataSet1);  
            this->dataGridView1->DataSource =this->dataSet1->Tables[0];  
         }  
         catch (Exception^ MyEx)  
         {  
             MessageBox::Show(MyEx->Message, "信息提示", MessageBoxButtons::OK, MessageBoxIcon::Information);  
         }  
    }  
    //导出Excel表  
private: System::Void button2_Click(System::Object^  sender, System::EventArgs^  e) {  
             Excel::ApplicationClass^ MyExcel;  
             Excel::Workbooks^ MyWorkBooks;  
             Excel::Workbook^ MyWorkBook;  
             Excel::Worksheet^ MyWorkSheet;  
             Excel::Range^ MyRange;  
             array<System::Object^,2>^ MyData=gcnew array<System::Object^,2>(5000,30);  
             OleDbDataReader^ MyReader;  
             DataColumn^ MyColumn;  
             int Count,i,j;  
             try 
             {  
                 MyExcel = gcnew Excel::ApplicationClass();  
                 MyExcel->Visible = true;        
                 MyWorkBooks=MyExcel->Workbooks;  
                 MyWorkBook=MyWorkBooks->Add(Missing::Value);  
                 MyWorkSheet=(Excel::Worksheet^)MyWorkBook->Worksheets[1];  
                 switch(this->dataSet1->Tables[0]->Columns->Count + 64)  
                 {  
                    case 64:  
                         MyRange=MyWorkSheet->Range["A1","A1"];  
                        break;  
                    case 65:  
                        MyRange=MyWorkSheet->Range["A1","B1"];  
                        break;  
                    case 66:  
                        MyRange=MyWorkSheet->Range["A1","C1"];  
                        break;  
                    case 67:  
                        MyRange=MyWorkSheet->Range["A1","D1"];  
                        break;  
                    case 68:  
                        MyRange=MyWorkSheet->Range["A1","E1"];  
                        break;  
                    case 69:  
                        MyRange=MyWorkSheet->Range["A1","F1"];  
                        break;  
                    case 70:  
                        MyRange=MyWorkSheet->Range["A1","G1"];  
                        break;  
                    case 71:  
                        MyRange=MyWorkSheet->Range["A1","H1"];  
                        break;  
                    case 72:  
                        MyRange=MyWorkSheet->Range["A1","I1"];  
                        break;  
                    case 73:  
                        MyRange=MyWorkSheet->Range["A1","J1"];  
                        break;  
                    case 74:  
                        MyRange=MyWorkSheet->Range["A1","K1"];  
                        break;  
                    case 75:  
                        MyRange=MyWorkSheet->Range["A1","L1"];  
                        break;  
                    case 76:  
                        MyRange=MyWorkSheet->Range["A1","M1"];  
                        break;  
                    case 77:  
                        MyRange=MyWorkSheet->Range["A1","N1"];  
                        break;  
                    case 78:  
                        MyRange=MyWorkSheet->Range["A1","O1"];  
                        break;  
                    case 79:  
                        MyRange=MyWorkSheet->Range["A1","P1"];  
                        break;  
                    case 80:  
                        MyRange=MyWorkSheet->Range["A1","Q1"];  
                        break;  
                    case 81:  
                        MyRange=MyWorkSheet->Range["A1","R1"];  
                        break;  
                    case 82:  
                        MyRange=MyWorkSheet->Range["A1","S1"];  
                        break;  
                    case 83:  
                        MyRange=MyWorkSheet->Range["A1","T1"];  
                        break;  
                    case 84:  
                        MyRange=MyWorkSheet->Range["A1","U1"];  
                        break;  
                    case 85:  
                        MyRange=MyWorkSheet->Range["A1","V1"];  
                        break;  
                    case 86:  
                        MyRange=MyWorkSheet->Range["A1","W1"];  
                        break;  
                    case 87:  
                        MyRange=MyWorkSheet->Range["A1","X1"];  
                        break;  
                    case 88:  
                        MyRange=MyWorkSheet->Range["A1","Y1"];  
                        break;  
                    case 89:  
                        MyRange=MyWorkSheet->Range["A1","Z1"];  
                        break;  
                 }                     
                 MyReader=oleDbCommand1->ExecuteReader();  
                 Count=0;  
                 for each(DataColumn^ MyNewColumn in this->dataSet1->Tables[0]->Columns)  
                 {  
                    MyData[0,Count]=MyNewColumn->ColumnName;  
                    Count=Count+1;  
                 }  
                //设标题为黑体字     
                 MyWorkSheet->Range[MyWorkSheet->Cells[1,1],MyWorkSheet->Cells[1,Count]]->Font->Name="黑体";  
                //标题字体加粗    
                MyWorkSheet->Range[MyWorkSheet->Cells[1,1],MyWorkSheet->Cells[1,Count]]->Font->Bold=true;  
                //设表格边框样式     
                MyWorkSheet->Range[MyWorkSheet->Cells[1,1],MyWorkSheet->Cells[1,Count]]->Borders->LineStyle=1;  
                j=1;  
                while(MyReader->Read())  
                {  
                    for(i=0;i< MyReader->FieldCount;i++)  
                    {  
                        MyData[j,i]=MyReader[i]->ToString();  
                    }  
                    j++;  
                }  
                MyReader->Close();  
                MyRange=MyRange->Resize[this->dataSet1->Tables[0]->Rows->Count,this->dataSet1->Tables[0]->Columns->Count];  
                MyRange->Value2=MyData;  
                MyRange->EntireColumn->AutoFit();  
            }  
            catch (Exception^ MyEx)  
            {  
                MessageBox::Show(MyEx->Message, "信息提示", MessageBoxButtons::OK, MessageBoxIcon::Information);  
            }  
         }  
};  
} 

5.启动调试运行如下：

点击”导出Excel表“按钮启动Excel，显示如下，其中自动插入了一个程序中的表格及数据：

成功的演示了使用自动化导出Excel文件的方法，希望读者和前面的用剪贴板导出Excel文件的方法加以区别，实际应用还需读者自行研究揣摩，以实现更为复杂高效的功能。