procedure DbGridEHTO_Excel(DbGridEH:TDbGridEH);
var ExcelApp: Variant;
i,j,row,column:integer;
begin
if DbGridEH.Columns.Count=0 then exit;
//创建应用程序
if not quizmsg('输出到Excel在数据量大时可能需要较长时间,是否继续?') then exit;
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Add;
ExcelApp.WorkSheets[ 'Sheet1' ].Activate;//设置第1个工作表为活动工作表
ExcelApp.ActiveSheet.Rows[1].Font.Size:=9;
ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
row:=1;
column:=1;
for j:= 0 to DbGridEH.DataSource.DataSet.FieldCount-1 do
begin
ExcelApp.Cells[row,column].Value:=DbGridEH.DataSource.DataSet.Fields[j].DisplayLabel;
column:=column+1;
end;
row:=2;
while (Not DbGridEH.DataSource.DataSet.Eof) and (Not DbGridEH.DataSource.DataSet.IsEmpty) do
begin
column:=1;
for i:=1 to DbGridEH.DataSource.DataSet.FieldCount do
begin
ExcelApp.Cells[row,column].Value:=DbGridEH.DataSource.DataSet .fields[i-1].AsString;
column:=column+1;
end;
DbGridEH.DataSource.DataSet .Next;
row:=row+1;
end;
try
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
ExcelApp:= Unassigned;
Application.MessageBox('Excel文件导出成功!','成功',MB_OK);
except
ExcelApp:= Unassigned;
end;
end;
把数据库的一个个表取出来导到EXCEL表格不就可以了
数据集导出代码:
function TForm1.S_IsFileInUse(FileName : string ) : boolean;
var
HFileRes : HFILE;
begin
Result := false;
if not FileExists(FileName) then
exit;
HFileRes := CreateFile(pchar(FileName), GENERIC_READ or GENERIC_WRITE,0, nil, OPEN_EXISTING,FILE_ATTRIBUTE_NORMAL, 0);
Result := (HFileRes = INVALID_HANDLE_VALUE);
if not Result then
CloseHandle(HFileRes);
end;
procedure TForm1.suiButton13Click(Sender: TObject);
Var
ExcelApp:Variant;
SaveDialog1: TSaveDialog;
i,j,row,column:integer;
begin
with Public_DM.ClientDataSet1 do begin
querycount:=RecordCount;
close;open;
if Public_DM.ClientDataSet1.IsEmpty then
begin
ShowMessage('没有数据需要存盘!');//test
Exit;
end;
SaveDialog1:= TSaveDialog.Create(nil);
SaveDialog1.Filter := 'Excel 文件 (*.xls)|*.xls';
SaveDialog1.Title:='确定另存为excel的文件名';
if savedialog1.Execute Then
begin
while S_IsFileInUse(savedialog1.FileName) do
begin
case Application.MessageBox(PChar('无法存盘,'+string(ExtractFileName(savedialog1.FileName))+'正在使用中'), '请确认', MB_ICONQuestion+MB_ABORTRETRYIGNORE+MB_DEFBUTTON2) of
IDAbort:
begin
SaveDialog1.Free;
Exit;
end;
IDRetry:
begin
continue;
end;
IDIgnore:
begin
if Not savedialog1.Execute then break;
end;
end;
end;
end
else
begin
SaveDialog1.Free;
exit;
end;//if
try
ExcelApp:=CreateOleObject('Excel.Application');//首先创建 Excel 对象,使用ComObj
except
Application.Messagebox('Excel没有安装!','Hello',MB_ICONERROR + mb_Ok);
Abort;
end;//end try
try
ExcelApp.Visible := False;//显示当前窗口
ExcelApp.Caption := '应用程序调用 Microsoft Excel';//更改 Excel 标题栏
ExcelApp.WorkBooks.Add;//添加新工作簿:
ExcelApp.WorkSheets[ 'Sheet1' ].Activate;//设置第1个工作表为活动工作表
ExcelApp.ActiveSheet.Rows[1].Font.Size:=10;
ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
row:=1;
column:=1;
for j:= 0 to Public_DM.ClientDataSet1.FieldCount-1 do
begin
ExcelApp.Cells[row,column].Value:=Public_DM.ClientDataSet1.Fields[j].DisplayLabel;
column:=column+1;
end;
row:=2;
while (Not Public_DM.ClientDataSet1 .Eof) and (Not Public_DM.ClientDataSet1 .IsEmpty) do
begin
column:=1;
for i:=1 to Public_DM.ClientDataSet1 .FieldCount do
begin
ExcelApp.Cells[row,column].Value:=Public_DM.ClientDataSet1 .fields[i-1].AsString;
column:=column+1;
end;
Public_DM.ClientDataSet1 .Next;
row:=row+1;
end;
if Not S_IsFileInUse(savedialog1.FileName) then
try
ExcelApp.ActiveWorkBook.SaveAs(savedialog1.filename);
except
SaveDialog1.Free;
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
ExcelApp:= Unassigned;
exit;
end;
SaveDialog1.Free;
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
ExcelApp:= Unassigned;
Application.MessageBox('Excel文件导出成功!','成功',MB_OK);
except
SaveDialog1.Free;
ExcelApp:= Unassigned;
end;
end;
end;
type
TForm1 = class(TForm)
Button1: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
var
ex,wb:Variant;
begin
//更多内容请参考Microsoft VBA帮助(关于Excel的VBA帮助在Office安装目录下
//文件名为VBAXLx.CHM(x为版本号,office2000为9,officexp为10))