数据库与EXECL 相联系(200分)

利用DELPHI技术，将数据库的查询结果放入EXECLS的设定单元内，即放入预计好报表表头的
EXECLE工作表内，如按你的操作可完成，我送200分。

var
FExcelApp,FWorkBook: Variant;
begin
try
FExcelApp := CreateOleObject('Excel.Application');
except on E:Exceptiondo
begin
Application.MessageBox('不能初始化Microsoft Excel, 您的计算机上需要安装Microsoft Excel 才能执行本功能,请您检查是否安装',
'错误;', MB_OK+MB_ICONERROR);
Exit;
end;
end;

FExcelApp.visible := False;
FExcelApp.DisplayAlerts := False;
FWorkBook := FExcelApp[APP].WorkBooks.Add('xxx.xls);
FExcelApp[APP].Cells[1,1] := ADODataset['aaa'];
...................
end

rocedure TForm1.Button1Click(Sender: TObject);
var ado1:tadoquery;
i,j:integer;
begin
try app1.Connect;
except
messagedlg('系统没有安装WORD',mterror,[mbok],0);
abort;
end;
ado1:=tadoquery.create(self);ado1.Connection:=adoconn;
try ado1.Close;ado1.SQL.Clear;ado1.SQL.Add('select * from table1');
ado1.Open;
if not ado1.Eof then
begin
app1.Workbooks.Add(null,0);
ework.ConnectTo(app1.workbooks[1]);
wsheet1.ConnectTo(ework.Sheets[1] as _worksheet);
for i:=0 to ado1.FieldCount-1do
begin
wsheet1.Cells.Item[1,i+1]:=ado1.Fields.FieldName;
end;
j:=2;
while not ado1.Eofdo
begin
for i:=0 to ado1.FieldCount-1do
begin
wsheet1.Cells.Item[j,i+1]:=ado1.Fields.AsString;
end;
j:=j+1;ado1.Next;
end;
ework.SaveCopyAs('c:/1.xls');
ework.Close(false);
app1.Disconnect;
app1.quit;
end;
finally ado1.free;
end;
end;

[]

type
xlapp: TExcelApplication;
procedure TForm1.BitBtn1Click(Sender: TObject);
var
tmp:string;
begin

dc(tmp);
end;
procedure TForm1.dc(v_mx: string);
var
ksh, ksl, j: integer;
VL_Str: string;
VL_MBWJM, vl_bbmc, vl_bblx: string;
begin
if Table.Active = false then
table.active := true;
Vl_MBWJM := TableMBWJM.AsString;
Vl_BBMC := TableBBMC.AsString;
Vl_BBLX := TableBBLX.AsString;
xlapp.Connect;
xlapp.Workbooks.Add(GetCurrentDir + '/Templete/' + Vl_MBWJM, 0);
VL_BH := v_title + Vl_BBMC;
xlapp.Cells.Item[1, 1] := VL_BH;
xlapp.Cells.Item[2, 1] := V_mx;
//取开始行.开始列
ksh := tableksh.AsInteger;
ksl := tableksl.AsInteger;
Query.First;
while not Query.Eofdo
begin
for j := ksl to Query.FieldCount + ksl - 1do
xlapp.Cells.Item[ksh, j] := query.Fields[J - ksl].AsString;
ksh := ksh + 1;
Query.Next;
end;
xlapp.Visible[0] := True;
xlapp.Disconnect;
end;
试试看..

你查查我以前答的,包你满意!基本上你对EXCEL要什么控制都可以实现.若你看了后还不太
清楚,再贴上来问我.

the most simple way is adopt advanced string grid, only one method, SaveToXls(filename)

老兄:你的转换成功了吗，能否将你的方法详细转告于我，我只有100分可送了。

老兄：你的转换成功了吗，能否将你的方法详细告诉我，我只有100分可送了！

bitbtn3.Enabled:=false;
statusbar1.Panels[1].Text:='';
statusbar1.Panels.Items[0].Text:='正在連接Excel………';
Application.ProcessMessages;
try
ExcelApplication1.Connect;
Except
statusbar1.Panels.Items[0].Text:='Excel連接失敗,請檢查Excel是否有安裝...';
ComboBox1.SetFocus;
bitbtn1.Enabled:=true;
bitbtn2.Enabled:=true;
bitbtn3.Enabled:=true;
exit
end;
ExcelApplication1.Visible[0]:=FALSE;
ExcelApplication1.Workbooks.Add(xlWBATWorksheet,0);
statusbar1.Panels.Items[0].Text:='正在設定Excel格式………';
statusbar1.Refresh;
asheet:=ExcelApplication1.ActiveSheet;
asheet.pagesetup.orientation:=xlLandscape;
//設置頁面方向
asheet.pagesetup.leftMargin:=Excelapplication1.InchesToPoints(0.006220440945);
// 設置
asheet.pagesetup.rightMargin:=Excelapplication1.InchesToPoints(0.0000787401575e-02);
// 頁面
asheet.pagesetup.topMargin:=Excelapplication1.InchesToPoints(0.236220472440945);
// 邊距
asheet.pagesetup.bottomMargin:=Excelapplication1.InchesToPoints(0.35748031496063);
//
Excelapplication1.ActiveWindow.Zoom:=100;
asheet.name:='物料庫存';
asheet.cells[1,2]:='XXXX實業股份有限公司物料庫存報表';
asheet.range[asheet.cells[1,1],asheet.cells[1,10]].font.size:=18;
asheet.range[asheet.cells[1,1],asheet.cells[1,10]].font.bold:=true;
asheet.range[asheet.cells[1,1],asheet.cells[1,10]].font.NAME:='標楷體';
asheet.range[asheet.cells[1,2],asheet.cells[1,12]].Merge;
asheet.cells[1,2].Horizontalalignment:=xlcenter;
asheet.cells[2,1]:='列印日期:';
asheet.cells[2,1].Horizontalalignment:=xlleft;
//欄位內容居左
asheet.cells[2,2].NumberFormatLocal:='yyyy-mm-dd';//設置日期顯示格式
asheet.cells[2,2]:=datetostr(date);
asheet.Range[asheet.cells[2,2],asheet.cells[2,3]].Merge;
asheet.cells[2,2].Horizontalalignment:=xlleft;
//欄位內容居中,下同
asheet.cells[3,1]:='物料編號';
asheet.cells[3,1].Horizontalalignment:=xlcenter;
asheet.cells[3,2]:='物料名稱';
asheet.cells[3,2].Horizontalalignment:=xlcenter;
asheet.range[asheet.cells[3,2],asheet.cells[3,3]].Merge;
//合并
asheet.cells[3,4]:='單位';
asheet.cells[3,4].Horizontalalignment:=xlcenter;
asheet.cells[3,5]:='上月結存';
asheet.cells[3,5].Horizontalalignment:=xlcenter;
asheet.cells[3,6]:='入庫數量';
asheet.cells[3,6].Horizontalalignment:=xlcenter;
asheet.cells[3,7]:='出庫數量';
asheet.cells[3,7].Horizontalalignment:=xlcenter;
asheet.cells[3,8]:='結存數量';
asheet.cells[3,8].Horizontalalignment:=xlcenter;
asheet.cells[3,9]:='單價';
asheet.cells[3,10]:='幣別';
asheet.cells[3,11]:='合 計';
asheet.cells[3,11].Horizontalalignment:=xlcenter;
asheet.cells[3,12]:='庫存原因';
asheet.cells[3,12].Horizontalalignment:=xlcenter;
asheet.cells[3,13]:='異動日期';
asheet.cells[3,13].Horizontalalignment:=xlcenter;
asheet.cells[3,14]:='廠 商';
asheet.cells[3,14].Horizontalalignment:=xlcenter;
asheet.cells[3,15]:='型 體';
asheet.cells[3,15].Horizontalalignment:=xlcenter;
asheet.range[asheet.cells[3,15],asheet.cells[3,16]].Merge;
//合并
statusbar1.Panels.Items[0].Text:='正在匯入資料………';
ClientDataSet1.First;
i:=4;
J:=0;
Gauge1.Visible:=true;
Gauge1.MinValue:=0;
Gauge1.Progress:=0;
Gauge1.MaxValue:=ClientDataSet1.RecordCount;
While Not ClientDataSet1.Eofdo
begin
asheet.cells[i,1]:=''''+ClientDataSet1.FieldByName('ano').AsString+'-'+ClientDataSet1.FieldByName('bno').AsString;
asheet.cells[i,2]:=''''+ClientDataSet1.FieldByName('mat_na').AsString;
asheet.cells[i,4]:=''''+ClientDataSet1.FieldByName('unit').AsString;
asheet.cells[i,5]:=ClientDataSet1.FieldByName('LAST_BALANCE').AsString;
asheet.cells[i,6]:=ClientDataSet1.FieldByName('iqty').AsString;
asheet.cells[i,7]:=ClientDataSet1.FieldByName('oqty').AsString;
asheet.cells[i,8]:=ClientDataSet1.FieldByName('tqty').Asstring;
asheet.cells[i,9]:=ClientDataSet1.FieldByName('price').AsString;
asheet.cells[i,10]:=ClientDataSet1.FieldByName('exch' ).AsString;
asheet.cells[i,12]:=ClientDataSet1.FieldByName('rname').AsString;
if ClientDataSet1.FieldByName('idate').AsString>ClientDataSet1.FieldByName('odate').AsString then
asheet.cells[i,13]:=ClientDataSet1.FieldByName('idate').AsString
else
asheet.cells[i,13]:=ClientDataSet1.FieldByName('odate').AsString ;
asheet.cells[i,14]:=ClientDataSet1.FieldByName('SNAMES' ).AsString;
asheet.cells[i,15]:=''''+ClientDataSet1.FieldByName('artic_no').AsString;
asheet.range[asheet.cells[I,2],asheet.cells[I,3]].Merge;
//合并
asheet.range[asheet.cells[I,15],asheet.cells[I,16]].Merge;
//合并
inc(j);
// Statusbar1.Panels.Items[1].Text:='[第'+INTTOSTR(J)+'筆 /共'+IntToStr(ClientDataSet1.RecordCount)+'筆]';
Application.ProcessMessages;
ClientDataSet1.Next;
inc(i);
Gauge1.Progress:=j;
end;

Statusbar1.Panels.Items[0].Text:='資料轉檔成功,正在完成字體及框線設置……';
Application.ProcessMessages;
statusbar1.Panels.Items[1].Text:='';
asheet.cells[i,2].columnwidth:=16;
asheet.cells[i,4].columnwidth:=2.5;
asheet.cells[i,4].Horizontalalignment:=xlcenter;
asheet.cells[i,5].columnwidth:=10.5;
asheet.cells[i,9].columnwidth:=4.7;
asheet.cells[i,10].columnwidth:=3;
asheet.range[asheet.cells[2,1],asheet.cells[I,20]].font.size:=9;
asheet.Range[asheet.cells[3,1],asheet.cells[i-1,16]].borders.LineStyle:=xlContinuous;
//畫框線
ExcelApplication1.Visible[0]:=true;
ExcelApplication1.Disconnect;
bitbtn1.Enabled:=true;
bitbtn2.Enabled:=true;
bitbtn3.Enabled:=true;
ComboBox1.Text:='';
ComboBox2.Text:='';
dateedit1.Date:=date;
dateedit2.Date:=date;
Statusbar1.Panels.Items[0].Text:='全部工作結束';
statusbar1.Panels.Items[1].Text:=' 總共有[ '+IntToStr(j)+' ]筆資料匯入';
Gauge1.Visible:=FALSE;
ClientdataSet1.Close;

利用控件：Developer ExpressQuantumGrid或XLSReadWrite，特点速度快！适合大量数据。

这个是我以前打印查询数据的一个通用程序，动态创建输入参数
调用SQL语句完成查询－> 导出数据到EXCEL
//****************************************************
//* 该模块用于统计实现报表功能，利用EXCEL的强大功能，*
//* 巧妙的实现了求行和和求列和 *
//****************************************************
unit U_New_Report;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,ExtCtrls, Buttons,Excel2000, OleServer,ComCtrls;
type
tarray = array of TLabeledEdit;
TF_new_Report = class(TForm)
Panel1: TPanel;
B_close: TSpeedButton;
B_Ok: TSpeedButton;
procedure FormCreate(Sender: TObject);
procedure B_closeClick(Sender: TObject);
procedure B_OkClick(Sender: TObject);
private
{ Private declarations }
AEdit:tarray;
public
{ Public declarations }
Procedure Print_Excel(RepID:string);//打印EXCEL模本
end;

var
F_new_Report: TF_new_Report;
implementation
Uses Umain,PublicPro;
{$R *.dfm}
procedure TF_new_Report.FormCreate(Sender: TObject);
var
tempSQL,PreField,ECaption,EName:string;
tempPos,num,XLeft,XTop:integer;
ParaStr,Menu_name:string;
begin
Num:=0;
XLeft:=100;
XTop:=50;
//ShowMess(NowReportID);
//1、根据配置的报表信息，生成输入的编辑框，供用户输入
if NowReportID<>'0' then
begin
with Fmain.Q1do
begin
close;
sql.Clear;
tempSQL:='select * from syspz_report where ReportID='+NowReportID;
sql.Add(tempSQL);
open;
ParaStr :=FieldByName('ParaStr').AsString;
menu_name:=FieldByName('Menu_Name').AsString;
end;
//with
end;
//if
caption:=Menu_Name;
//显示窗体标题
//2、动态生成输入框
while paraStr<>''do
begin
tempPos :=pos(';',ParaStr);
PreField:=copy(ParaStr,1,TempPos-1);
//当前参数
ECaption:=copy(PreField,1,pos(',',PreField)-1);
//编辑框的名称
EName :=copy(PreField,Pos(',',PreField)+1,Length(PreField)-1);//编辑框的name
//*生成编辑框
Inc(Num);
SetLength(AEdit, Num);
if not Assigned(AEdit[Num-1]) then
begin
AEdit[Num-1] := TLabeledEdit.Create(Self);
AEdit[Num-1].Parent := Panel1;
AEdit[Num-1].Width := 150;
AEdit[Num-1].Left := XLeft ;
AEdit[Num-1].Top := XTop + 20 * Num;
AEdit[Num-1].Visible:= True;
AEdit[Num-1].EditLabel.caption :=ECaption + '：';
AEdit[Num-1].Name :=EName;
AEdit[Num-1].LabelPosition:=lpLeft;
AEdit[Num-1].Text:='';
end;
//********
paraStr:=copy(paraStr,tempPos+1,length(ParaStr));
end;
//while
end;

procedure TF_new_Report.B_closeClick(Sender: TObject);
begin
close;
end;

procedure TF_new_Report.B_OkClick(Sender: TObject);
begin
//如果用户没有填写数据，给出出错提示
Print_Excel(NowReportID);
end;

//SumRow 格式说明: A=Sum(B#+C#)
//利用替换函数，替换#为列号。巧妙的利用EXCEL的求和功能
procedure TF_new_Report.Print_Excel(RepID:string);
var
eaImport : TExcelApplication;//
ewbImport : TExcelWorkbook;
// v
sheet : TExcelWorksheet;
//sheet
I,J,k,IrowCount,tempPos:Integer;
//循环变量
tempSQL,tempStr,PreStr:string;
//临时变量
sqlStr,FileName,FieldStr,SumRow,SumCol:string;
//ColStr:表示哪列要求和
StartCol,StartRow,ColStr:integer;
SourcePath,PrintDate:string;
begin
SourcePath:=EXTRACTFILEPATH(PARAMSTR(0)) + DirPath;
//根据报表号找出SQL语句
with Fmain.Q1do
begin
close;
sql.Clear;
tempSQL:='select * from syspz_Report where reportId=' + RepId;
sql.Add(tempSQL);
open;
IRowCount:=RecordCount;
if IRowCount=0 then
begin
ShowMess('没有该报表的记录，请确认是否添加了该报表');
exit;
end;
SqlStr :=fieldByName('SqLStr').asstring;
FieldStr:=FieldByName('FieldStr').asstring;
FileName:=SourcePath + FieldByName('Path').AsString;
StartCol:=FieldByName('StartCol').AsInteger;
StartRow:=FieldByName('StartRow').AsInteger;
SumRow :=FieldByName('SumRow').AsString;
tempStr :=FieldByName('SumCol').AsString;
SumCol :=copy(tempStr,pos('(',tempStr)+1,length(tempStr)-5);
//获取SUM(B:C中括号的内容
end;

//往EXCEL写入数据
with Fmain.Q1do
begin
close;
sql.Clear;
//根据参数给SQL语句赋值
parameters.Clear;
sql.Add(SqlStr);
for i:=0 to high(AEdit)do
begin
parameters.ParamByName(AEdit.Name).Value:=AEdit.Text;
//parameters.Value:=trim(AEdit.Text);
//*********************
end;
showmessage(sql.Text);
Open;
IrowCount:=RecordCount;
if IRowCount=0 then
begin
ShowMess('该报表现在暂时没有你要的数据');
exit;
end;

//打开已存在的报表，插入数据
eaImport := TExcelApplication.Create(Application);
try
eaImport.ConnectKind := ckNewInstance;
eaImport.Connect;
except
showmess('请先安装 Excel ！');
eaImport.Free;
Exit;
end;
try
eaImport.Workbooks.Open(FileName,null,null,null,null,null,null,null,null,null,null,null,null,0);
eaImport.Visible[1] := true;
except
eaImport.Disconnect;
eaImport.Quit;
eaImport.Free;
showmess('无法打开文件！');
Exit;
end;
try
ewbImport := TExcelWorkbook.Create(Application);
sheet := TExcelWorksheet.Create(Application);
Fmain.SBar.panels[0].text:='正在读取数据...';
ewbImport.ConnectTo(eaImport.Workbooks[1]);
sheet.ConnectTo(ewbImport.Worksheets[1] as _Worksheet);
//打印页面设置
sheet.PageSetup.CenterFooter:=' 注：第 &amp;P 页（总共&amp;N页）';
sheet.PageSetup.TopMargin:=30;
sheet.PageSetup.BottomMargin:=30;
sheet.PageSetup.LeftMargin:=30;
sheet.PageSetup.RightMargin:=30;
sheet.PageSetup.CenterHorizontally:=true;//是否水平居中
//sheet.PageSetup.CenterVertically:=true;//是否垂直居中
sheet.PageSetup.Orientation:=2;
//1:竖打2：横打
sheet.PageSetup.Draft:=false;
//非草稿模式
//显示报表打印时间
PrintDate:=FormatDateTime('yyyymmdd',date);
sheet.Cells.Item[2,2]:=copy(PrintDate,1,4) + '年'
+copy(PrintDate,5,2) + '月'
+copy(PrintDate,7,2) + '日';
first;
//用循环取字符串的字段，然后插入到EXCEL表里
For i:=0 to IRowCount-1do
begin
j:=0;
k:=0;
tempStr:=FieldStr;
//*********************************************************************
//*取要显示的字符串的格式：FIELD_A,FIELD_B, * *
//* FIELD_A:表示SQL语句字段名称 *
//* , ：表示字符名称分隔符号 *
//*********************************************************************
while tempStr<>''do
begin
tempPos:=pos(',',tempStr);
PreStr:=copy(tempStr,1,TempPos-1);
sheet.cells.Item[StartRow+i,StartCol+j]:=FieldByname(PreStr).asstring;
tempStr:=copy(tempStr,tempPos+1,length(tempStr));
inc(j);
end;
//while
//*********************************************************************
//*加入统计字段，使用EXCEL自带的统计功能可以实现大部分功能 20020403 *
//*统计行和字符串格式： A=SUM(B#+C#-D#);E=SUM(F#+G#-H#);
*
//* A :表示求和结果显示的列 *
//* =SUM(B#+C#-D#) :表示EXCEL里求和的表达式 *
//* # :表示列号 *
//* ;
:表示各个求和表达式的分隔符号 *
//*********************************************************************
tempStr:=SumRow;
while tempStr<>''do
begin
tempPos:=pos(';',tempStr);
PreStr :=copy(tempStr,1,TempPos-1);
//获取当前求行和的字符串
ColStr :=Trans_ColNum(copy(PreStr,1,1));
//利用函数返回字母的ASCII码
sheet.Cells.Item[StartRow+i,ColStr]:=replacing(copy(preStr,2,length(PreStr)-1),IntToStr(StartRow+i));
tempStr:=copy(tempStr,tempPos+1,length(tempStr));
inc(k);
end;
//while
next;
end;
//for
//*********************************************************************
//*加入统计字段，使用EXCEL自带的统计功能可以实现大部分功能 20020403 *
//*统计行和字符串格式： sum(A;B;C *
//* A :表示要求和的列 *
//* ;
:表示各个求和列的分隔符号 *
//*********************************************************************
tempStr:=SumCol;
sheet.Cells.Item[StartRow+IRowCount,1]:='合计：';
while tempStr<>''do
begin
tempPos:=pos(';',tempStr);
PreStr :=copy(tempStr,1,TempPos-1);
//获取当前求行和的字符串
sheet.Cells.Item[StartRow + IRowCount , Trans_ColNum(PreStr)]:='=sum(' + PreStr + IntToStr(StartRow) + ':' + PreStr + IntToStr(StartRow+IRowCount-1) + ')';
tempStr:=copy(tempStr,tempPos+1,length(tempStr));
end;
//while
//**************
//给数据加上边框
sheet.Range[sheet.Cells.Item[StartRow,StartCol],sheet.Cells.Item[StartRow+IRowCount,StartCol+J+k-1]].Borders.LineStyle :=1;
{sheet.Range[sheet.Cells.Item[4,4],sheet.Cells.Item[6,6]].Borders.LineStyle :=2;
//2到8都可以}
sheet.Disconnect;
ewbImport.Disconnect;
eaImport.Disconnect;
eaImport.Quit;
sheet.Free;
ewbImport.Free;
eaImport.Free;
Fmain.SBar.panels[0].text:='数据读取完毕。';
except
eaImport.Disconnect;
eaImport.Quit;
eaImport.Free;
showmess('文件读取错误！');
end;
//try
end;
//with
end;

end.

没时间仔细看，只好委屈各位大虾了，平均分赃吧，呵呵