uses ComObj ; var varApp : OleVariant ; varSheet : OleVariant ;function ExportTableToExcel( queryName : TQuery ) : Boolean ; var i,j : Integer ; begin Result:=False ; if queryName.Active=False then begin ShowMessage('还未有结果数据!'); Exit ; end; try varSheet:=CreateOleObject('Excel.Sheet'); except ShowMessage('未发现安装了 Excel !'); Exit ; end; varApp:=varSheet.Application ; varSheet:=varApp.ActiveSheet ; queryName.First ; if not queryName.eof then begin queryName.DisableControls ; for i:=0 to queryName.FieldCount-1 do begin varSheet.Cells[1,i+1].Value:=queryName.Fields[i].FieldName ; //修正Excel 中数字字符自动变为数字问题 if queryName.Fields[i].DataType=ftString then begin varSheet.Columns[i+1].NumberFormatLocal:= '@'; end; end; queryName.First ; j:=2 ; while not queryName.Eof do begin for i:=0 to queryName.FieldCount-1 do begin if not queryName.Fields[i].IsNull then varSheet.Cells[j,i+1]:=queryName.Fields[i].DisplayText ; end; j:=j+1 ; queryName.Next ; end; queryName.EnableControls ; end; varSheet.Cells.Columns.AutoFit; varApp.Visible:=true; Result:=True ; end;
use activeX //头文件中引用activex单元 lcid:integer; //声明窗体级私有变量 加入TExcelApplication,Texcelworkbook,Texcelworksheet控件function ChangeToExcel(.... var i,j: Integer; MyArray:variant; M,N:INTEGER; FLD:STRING; begin lcid := GetUserDefaultLCID; eapp.Disconnect; eapp.Connect; eapp.Visible[0]:=True; eWB.ConnectTo(eapp.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid)); eWS.ConnectTo(ewb.Worksheets[1] as _Worksheet); q_result.first; q_result.DisableControls; IF q_result.FieldCount>26 THEN BEGIN M:= q_result.FieldCount DIV 26; N:= q_result.FieldCount MOD 26; IF N>0 THEN FLD:= chr(M+64)+chr(N+64) ELSE FLD:= chr(M-1+64)+'Z'; END ELSE FLD:=chr(q_result.FieldCount+64); MyArray:=VarArrayCreate([0,(q_result.FieldCount-1)],varVariant); for i:=0 to q_result.Fieldcount-1 do begin MyArray[i]:= DBgrideh2.Columns[i].Title.Caption ; end; eWS.Range['A1', (FLD+'1')].Value := MyArray; for i:=2 to q_result.RecordCount+1 do begin for j:=0 to q_result.FieldCount-1 do begin MyArray[j]:= q_result.Fields[j].Value ; end; eWS.Range['A'+intTostr(i), (FLD+intTostr(i))].Value := MyArray; q_result.Next; end; q_result.EnableControls; end; //这段代码是以显式的方法把数据转入Excel,需要隐式转入,只需设Texcelapplication.visible:=false; 至于保存成文件,调用Texcelworksheet.saveas()方法即可
[email protected]
按着一个例程改的,也注意到资源的释放,但好像数据是导入了,我在没有关闭excel的情况下
又再导入新的数据到另外一个sheet,但那有如何做?
ComObj ;
var
varApp : OleVariant ;
varSheet : OleVariant ;function ExportTableToExcel( queryName : TQuery ) : Boolean ;
var
i,j : Integer ;
begin
Result:=False ;
if queryName.Active=False then
begin
ShowMessage('还未有结果数据!');
Exit ;
end;
try
varSheet:=CreateOleObject('Excel.Sheet');
except
ShowMessage('未发现安装了 Excel !');
Exit ;
end;
varApp:=varSheet.Application ;
varSheet:=varApp.ActiveSheet ;
queryName.First ;
if not queryName.eof then
begin
queryName.DisableControls ;
for i:=0 to queryName.FieldCount-1 do
begin
varSheet.Cells[1,i+1].Value:=queryName.Fields[i].FieldName ;
//修正Excel 中数字字符自动变为数字问题
if queryName.Fields[i].DataType=ftString then
begin
varSheet.Columns[i+1].NumberFormatLocal:= '@';
end;
end;
queryName.First ;
j:=2 ;
while not queryName.Eof do
begin
for i:=0 to queryName.FieldCount-1 do
begin
if not queryName.Fields[i].IsNull then
varSheet.Cells[j,i+1]:=queryName.Fields[i].DisplayText ;
end;
j:=j+1 ;
queryName.Next ;
end;
queryName.EnableControls ;
end;
varSheet.Cells.Columns.AutoFit; varApp.Visible:=true;
Result:=True ;
end;
begin
ExportTableToExcel( query1) ;
ExportTableToExcel( query2) ;
ExportTableToExcel( table1) ;
end;
lcid:integer; //声明窗体级私有变量
加入TExcelApplication,Texcelworkbook,Texcelworksheet控件function ChangeToExcel(....
var i,j: Integer;
MyArray:variant;
M,N:INTEGER;
FLD:STRING;
begin
lcid := GetUserDefaultLCID;
eapp.Disconnect;
eapp.Connect;
eapp.Visible[0]:=True;
eWB.ConnectTo(eapp.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid));
eWS.ConnectTo(ewb.Worksheets[1] as _Worksheet);
q_result.first;
q_result.DisableControls;
IF q_result.FieldCount>26 THEN
BEGIN
M:= q_result.FieldCount DIV 26;
N:= q_result.FieldCount MOD 26;
IF N>0 THEN FLD:= chr(M+64)+chr(N+64)
ELSE FLD:= chr(M-1+64)+'Z';
END
ELSE FLD:=chr(q_result.FieldCount+64);
MyArray:=VarArrayCreate([0,(q_result.FieldCount-1)],varVariant);
for i:=0 to q_result.Fieldcount-1 do
begin
MyArray[i]:= DBgrideh2.Columns[i].Title.Caption ;
end;
eWS.Range['A1', (FLD+'1')].Value := MyArray;
for i:=2 to q_result.RecordCount+1 do
begin
for j:=0 to q_result.FieldCount-1 do
begin
MyArray[j]:= q_result.Fields[j].Value ;
end;
eWS.Range['A'+intTostr(i), (FLD+intTostr(i))].Value := MyArray;
q_result.Next;
end;
q_result.EnableControls;
end;
//这段代码是以显式的方法把数据转入Excel,需要隐式转入,只需设Texcelapplication.visible:=false;
至于保存成文件,调用Texcelworksheet.saveas()方法即可