请教如何把查询的结果复制到Excel中

时间:2021-12-12 04:30:18
请教高手一个问题,我所编制的程序中有好多个TQuery控件,每个查询的结果都有与之相对应的DGGrid控件,可以看到结果,这些结果都是String和时间数据类型的,没有复杂的数据如image数据类型等。现在的问题是我想打印这些结果,而报表的设计又非常复杂,况且查询结果中的有好多字段,我想把查询的结果复制到Excel中,这样就可以打印选择想要打印的字段记录了,这个问题非常急,请高手给予解答!

4 个解决方案

#1


请参阅www.trackon.net/bcblist/resource.asp里我翻译和原创的两篇文章。

#2


这里要用到OLE1

查找关于BCB读写Excel的文章!

#3


这是一个老问题,不过我贴出一段代码(我前天写的):
(你也以使用EXCEL的类型库来写)
//---------------------------------------------------------------------------

#ifndef MainH
#define MainH
//---------------------------------------------------------------------------
#include <Classes.hpp>
#include <Controls.hpp>
#include <StdCtrls.hpp>
#include <Forms.hpp>
#include <utilcls.h>
#include <math.h>

#include "CCEdit.h"
#include <Db.hpp>
#include <DBGrids.hpp>
#include <DBTables.hpp>
#include <Grids.hpp>
#include <ADODB.hpp>
#define  PG OlePropertyGet
#define  PS OlePropertySet
#define  FN OleFunction
#define  PR OleProcedure
//---------------------------------------------------------------------------
class TForm1 : public TForm
{
__published: // IDE-managed Components
        TButton *Button1;
        TButton *Button2;
        TCCEdit *CCEdit1;
        TButton *Button3;
        TDBGrid *DBGrid1;
        TDataSource *DataSource1;
        TDatabase *MyDB;
        TButton *Button4;
        TQuery *MyQuery;
        TLabel *Label1;
        TEdit *Edit1;
        TEdit *Edit2;
        TEdit *Edit3;
        TEdit *Edit4;
        TEdit *Edit5;
        TLabel *Label2;
        TLabel *Label3;
        TLabel *Label4;
        TLabel *Label5;
        TLabel *Label6;
        TButton *Button5;
        void __fastcall Button1Click(TObject *Sender);
        void __fastcall Button4Click(TObject *Sender);
        void __fastcall Button2Click(TObject *Sender);
        void __fastcall Button3Click(TObject *Sender);
        void __fastcall Button5Click(TObject *Sender);
private: // User declarations
public: // User declarations
        __fastcall TForm1(TComponent* Owner);
protected:
        virtual void __fastcall WndProc(TMessage &Message);

};
//---------------------------------------------------------------------------
extern PACKAGE TForm1 *Form1;
//---------------------------------------------------------------------------
#endif

.cpp File
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Main.h"
#include "utilcls.h"

//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "CCEdit"
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::WndProc(TMessage &Message)
{
        if(Message.Msg==WM_SYSCOMMAND)
        {
                if(Message.WParam==SC_CLOSE || Message.WParam==SC_SCREENSAVE)
                {
                        Message.WParam=0;
                }
        }
        TForm::WndProc(Message);
}
void __fastcall TForm1::Button1Click(TObject *Sender)
{
        Variant V,WB1,SH1;
        V=Variant::CreateObject("Excel.Application");
        V.PS("Visible",true);
        V.PG("WorkBooks").PR("Open","c:\\Chenbin\\BCB\\Excel\\Book1.xls");
        WB1=V.PG("ActiveWorkBook");
        SH1=WB1.PG("ActiveSheet");
        CCEdit1->Text=SH1.PG("Cells",1,1).PG("Value");
        for(int i=1;i<=10;i++)
        {
                for(int j=1;j<=10;j++)
                {
                        //AnsiString &S=IntToStr(i*100+j);
                        wchar_t* oleStr = SysAllocString(L"Hello");



                        SH1.PG("Cells",i,j).PS("Value",Variant(oleStr));
                }
        }
        V.PR("Save");
        //V.FN("Close");
        V.PR("Quit");
        V=Unassigned;
        WB1=Unassigned;
        SH1=Unassigned;
        Sys
        ShowMessage("Excel has been opened by BCB");
        SysFreeString(oleStr);



}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button4Click(TObject *Sender)
{

       // Button5->Enabled=false;
        MyDB->Connected=true;
        MyQuery->Close();
        MyQuery->SQL->Clear();
        MyQuery->SQL->Add("insert into Excel2000(Name,Gender,Age,Nationality,Address) Values(:Name,:Gender,:Age,:Nationality,:Address)");
        MyQuery->ParamByName("Name")->AsString=Edit1->Text;
        MyQuery->ParamByName("Gender")->AsString=Edit2->Text;
        MyQuery->ParamByName("Age")->AsInteger=StrToInt(Edit3->Text);
        MyQuery->ParamByName("Nationality")->AsString=Edit4->Text;
        MyQuery->ParamByName("Address")->AsString=Edit5->Text;
        MyQuery->ExecSQL();
        MyQuery->Close();
        MyQuery->SQL->Clear();
        MyQuery->SQL->Add("select  * from Excel2000");
        MyQuery->Open();


}
//---------------------------------------------------------------------------



void __fastcall TForm1::Button2Click(TObject *Sender)
{
        int button;
        button=Application->MessageBox("Do you really want to exit thisform?","Confirmation",MB_YESNO+MB_ICONQUESTION);
        if(button==IDYES)
        {
                Application->Terminate();
        }




}
//---------------------------------------------------------------------------


void __fastcall TForm1::Button3Click(TObject *Sender)
{
        MyDB->Connected=true;
        MyQuery->Close();
        MyQuery->SQL->Clear();
        MyQuery->SQL->Add("select * from Excel2000");
        MyQuery->Open();
       //Button5->Enabled=false;
        
        




}
//---------------------------------------------------------------------------


void __fastcall TForm1::Button5Click(TObject *Sender)
{
       /* Button3->Enabled=false;
        Button4->Enabled=false;
        ADOConnection->Connected=true;
        ADOQuery->Close();
        ADOQuery->SQL->Clear();
        ADOQuery->SQL->Add("select * from Excel2000");
        ADOQuery->Open();
                               */
                               
        



        



}
//---------------------------------------------------------------------------
我这里面有包括将EXCEL 作为一张表操作及向EXCEL表格随机写入数据。


#4


很高兴大家的帮助,直接采用OLE的方法调用EXEL是可以的,我认为比较麻烦,我的意思是有没有简便的方法直接把数据COPY到EXCEL中,我认为这是DBGrid控件作的不是很好,按理说它应该直接可以把选择的结果按ctr+c键时复制到剪贴板中,然后到EXCEL中粘贴就可以了,我现在的思路是把选择的数据利用Tclipboard()把选择的数据(当然是多行的)复制到剪贴板中。

#1


请参阅www.trackon.net/bcblist/resource.asp里我翻译和原创的两篇文章。

#2


这里要用到OLE1

查找关于BCB读写Excel的文章!

#3


这是一个老问题,不过我贴出一段代码(我前天写的):
(你也以使用EXCEL的类型库来写)
//---------------------------------------------------------------------------

#ifndef MainH
#define MainH
//---------------------------------------------------------------------------
#include <Classes.hpp>
#include <Controls.hpp>
#include <StdCtrls.hpp>
#include <Forms.hpp>
#include <utilcls.h>
#include <math.h>

#include "CCEdit.h"
#include <Db.hpp>
#include <DBGrids.hpp>
#include <DBTables.hpp>
#include <Grids.hpp>
#include <ADODB.hpp>
#define  PG OlePropertyGet
#define  PS OlePropertySet
#define  FN OleFunction
#define  PR OleProcedure
//---------------------------------------------------------------------------
class TForm1 : public TForm
{
__published: // IDE-managed Components
        TButton *Button1;
        TButton *Button2;
        TCCEdit *CCEdit1;
        TButton *Button3;
        TDBGrid *DBGrid1;
        TDataSource *DataSource1;
        TDatabase *MyDB;
        TButton *Button4;
        TQuery *MyQuery;
        TLabel *Label1;
        TEdit *Edit1;
        TEdit *Edit2;
        TEdit *Edit3;
        TEdit *Edit4;
        TEdit *Edit5;
        TLabel *Label2;
        TLabel *Label3;
        TLabel *Label4;
        TLabel *Label5;
        TLabel *Label6;
        TButton *Button5;
        void __fastcall Button1Click(TObject *Sender);
        void __fastcall Button4Click(TObject *Sender);
        void __fastcall Button2Click(TObject *Sender);
        void __fastcall Button3Click(TObject *Sender);
        void __fastcall Button5Click(TObject *Sender);
private: // User declarations
public: // User declarations
        __fastcall TForm1(TComponent* Owner);
protected:
        virtual void __fastcall WndProc(TMessage &Message);

};
//---------------------------------------------------------------------------
extern PACKAGE TForm1 *Form1;
//---------------------------------------------------------------------------
#endif

.cpp File
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Main.h"
#include "utilcls.h"

//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "CCEdit"
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::WndProc(TMessage &Message)
{
        if(Message.Msg==WM_SYSCOMMAND)
        {
                if(Message.WParam==SC_CLOSE || Message.WParam==SC_SCREENSAVE)
                {
                        Message.WParam=0;
                }
        }
        TForm::WndProc(Message);
}
void __fastcall TForm1::Button1Click(TObject *Sender)
{
        Variant V,WB1,SH1;
        V=Variant::CreateObject("Excel.Application");
        V.PS("Visible",true);
        V.PG("WorkBooks").PR("Open","c:\\Chenbin\\BCB\\Excel\\Book1.xls");
        WB1=V.PG("ActiveWorkBook");
        SH1=WB1.PG("ActiveSheet");
        CCEdit1->Text=SH1.PG("Cells",1,1).PG("Value");
        for(int i=1;i<=10;i++)
        {
                for(int j=1;j<=10;j++)
                {
                        //AnsiString &S=IntToStr(i*100+j);
                        wchar_t* oleStr = SysAllocString(L"Hello");



                        SH1.PG("Cells",i,j).PS("Value",Variant(oleStr));
                }
        }
        V.PR("Save");
        //V.FN("Close");
        V.PR("Quit");
        V=Unassigned;
        WB1=Unassigned;
        SH1=Unassigned;
        Sys
        ShowMessage("Excel has been opened by BCB");
        SysFreeString(oleStr);



}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button4Click(TObject *Sender)
{

       // Button5->Enabled=false;
        MyDB->Connected=true;
        MyQuery->Close();
        MyQuery->SQL->Clear();
        MyQuery->SQL->Add("insert into Excel2000(Name,Gender,Age,Nationality,Address) Values(:Name,:Gender,:Age,:Nationality,:Address)");
        MyQuery->ParamByName("Name")->AsString=Edit1->Text;
        MyQuery->ParamByName("Gender")->AsString=Edit2->Text;
        MyQuery->ParamByName("Age")->AsInteger=StrToInt(Edit3->Text);
        MyQuery->ParamByName("Nationality")->AsString=Edit4->Text;
        MyQuery->ParamByName("Address")->AsString=Edit5->Text;
        MyQuery->ExecSQL();
        MyQuery->Close();
        MyQuery->SQL->Clear();
        MyQuery->SQL->Add("select  * from Excel2000");
        MyQuery->Open();


}
//---------------------------------------------------------------------------



void __fastcall TForm1::Button2Click(TObject *Sender)
{
        int button;
        button=Application->MessageBox("Do you really want to exit thisform?","Confirmation",MB_YESNO+MB_ICONQUESTION);
        if(button==IDYES)
        {
                Application->Terminate();
        }




}
//---------------------------------------------------------------------------


void __fastcall TForm1::Button3Click(TObject *Sender)
{
        MyDB->Connected=true;
        MyQuery->Close();
        MyQuery->SQL->Clear();
        MyQuery->SQL->Add("select * from Excel2000");
        MyQuery->Open();
       //Button5->Enabled=false;
        
        




}
//---------------------------------------------------------------------------


void __fastcall TForm1::Button5Click(TObject *Sender)
{
       /* Button3->Enabled=false;
        Button4->Enabled=false;
        ADOConnection->Connected=true;
        ADOQuery->Close();
        ADOQuery->SQL->Clear();
        ADOQuery->SQL->Add("select * from Excel2000");
        ADOQuery->Open();
                               */
                               
        



        



}
//---------------------------------------------------------------------------
我这里面有包括将EXCEL 作为一张表操作及向EXCEL表格随机写入数据。


#4


很高兴大家的帮助,直接采用OLE的方法调用EXEL是可以的,我认为比较麻烦,我的意思是有没有简便的方法直接把数据COPY到EXCEL中,我认为这是DBGrid控件作的不是很好,按理说它应该直接可以把选择的结果按ctr+c键时复制到剪贴板中,然后到EXCEL中粘贴就可以了,我现在的思路是把选择的数据利用Tclipboard()把选择的数据(当然是多行的)复制到剪贴板中。