procedure TForm1.EXCEL2Click(Sender: TObject); var eclApp,WorkBook:Variant;//声明为OLE Automation 对象 xlsFileName:string; i,j,n:integer; begin if SaveDialog1.Execute then begin xlsFileName:=SaveDialog1.FileName; if fileexists(SaveDialog1.FileName) then DeleteFile(SaveDialog1.FileName); try //创建OLE对象Excel Application与 WorkBook eclApp:=CreateOleObject('Excel.Application'); WorkBook:=CreateOleobject('Excel.Sheet'); except ShowMessage('您的机器里未安装Microsoft Excel。'); Exit; end; try Screen.Cursor:=crHourGlass; workBook:=eclApp.workBooks.Add; i:=1; //EXECL表行号 n:=0;//query字段N序号 Query1.First; j:=1; for n:=0 to Query1.FieldCount -1 do begin eclApp.Cells(i,j):=Query1.fields[n].DisplayLabel; j:=j+1; end; i:=2; //EXECL表行号 n:=0;//query字段N序号 while not Query1.Eof do begin j:=1;//EXECL表列号 for n:=0 to Query1.FieldCount -1 do begin eclApp.Cells(i,j):=Query1.fields[n].AsString; j:=j+1; end; Query1.Next; i:=i+1; end; WorkBook.SaveAs(xlsFileName); Application.MessageBox('操作在没有警告的情况下正常结束!','完成',mb_ok+mb_iconinformation); WorkBook.close; eclApp.Quit;//退出Excel Application eclApp:=Unassigned;//释放VARIANT变量 Screen.Cursor:=crdefault; except ShowMessage('不能正确操作Excel文件。可能是该文件已被其他程序打开,或系统错误。'); WorkBook.close; eclApp.Quit; //释放VARIANT变量 eclApp:=Unassigned; end; END; END;
//注意:下面的方法必须包含 ComObj, Excel97 单元 //----------------------------------------------------------- // if toExcel = false, export dbgrid contents to the Clipboard // if toExcel = true, export dbgrid to Microsoft Excel procedure ExportDBGrid(toExcel: Boolean); var bm: TBook; col, row: Integer; sline: String; mem: TMemo; ExcelApp: Variant; begin Screen.Cursor := crHourglass; DBGrid1.DataSource.DataSet.DisableControls; bm := DBGrid1.DataSource.DataSet.GetBook; DBGrid1.DataSource.DataSet.First;
// create the Excel object if toExcel then begin ExcelApp := CreateOleObject('Excel.Application'); ExcelApp.WorkBooks.Add(xlWBatWorkSheet); ExcelApp.WorkBooks[1].WorkSheets[1].Name := 'Grid Data'; end;
// First we send the data to a memo // works faster than doing it directly to Excel mem := TMemo.Create(Self); mem.Visible := false; mem.Parent := MainForm; mem.Clear; sline := '';
// add the info for the column names for col := 0 to DBGrid1.FieldCount-1 do sline := sline + DBGrid1.Fields[col].DisplayLabel + #9; mem.Lines.Add(sline);
// get the data into the memo for row := 0 to DBGrid1.DataSource.DataSet.RecordCount-1 do begin sline := ''; for col := 0 to DBGrid1.FieldCount-1 do sline := sline + DBGrid1.Fields[col].AsString + #9; mem.Lines.Add(sline); DBGrid1.DataSource.DataSet.Next; end;
// we copy the data to the clipboard mem.SelectAll; mem.CopyToClipboard;
// if needed, send it to Excel // if not, we already have it in the clipboard if toExcel then begin ExcelApp.Workbooks[1].WorkSheets['Grid Data'].Paste; ExcelApp.Visible := true; end;
var
eclApp,WorkBook:Variant;//声明为OLE Automation 对象
xlsFileName:string;
i,j,n:integer;
begin
if SaveDialog1.Execute then
begin
xlsFileName:=SaveDialog1.FileName;
if fileexists(SaveDialog1.FileName) then DeleteFile(SaveDialog1.FileName);
try
//创建OLE对象Excel Application与 WorkBook
eclApp:=CreateOleObject('Excel.Application');
WorkBook:=CreateOleobject('Excel.Sheet');
except
ShowMessage('您的机器里未安装Microsoft Excel。');
Exit;
end;
try Screen.Cursor:=crHourGlass;
workBook:=eclApp.workBooks.Add;
i:=1; //EXECL表行号
n:=0;//query字段N序号
Query1.First;
j:=1;
for n:=0 to Query1.FieldCount -1 do
begin
eclApp.Cells(i,j):=Query1.fields[n].DisplayLabel;
j:=j+1;
end;
i:=2; //EXECL表行号
n:=0;//query字段N序号
while not Query1.Eof do begin
j:=1;//EXECL表列号
for n:=0 to Query1.FieldCount -1 do
begin
eclApp.Cells(i,j):=Query1.fields[n].AsString;
j:=j+1;
end;
Query1.Next;
i:=i+1;
end; WorkBook.SaveAs(xlsFileName);
Application.MessageBox('操作在没有警告的情况下正常结束!','完成',mb_ok+mb_iconinformation);
WorkBook.close;
eclApp.Quit;//退出Excel Application
eclApp:=Unassigned;//释放VARIANT变量
Screen.Cursor:=crdefault;
except
ShowMessage('不能正确操作Excel文件。可能是该文件已被其他程序打开,或系统错误。');
WorkBook.close;
eclApp.Quit;
//释放VARIANT变量
eclApp:=Unassigned;
end;
END;
END;
//-----------------------------------------------------------
// if toExcel = false, export dbgrid contents to the Clipboard
// if toExcel = true, export dbgrid to Microsoft Excel
procedure ExportDBGrid(toExcel: Boolean);
var
bm: TBook;
col, row: Integer;
sline: String;
mem: TMemo;
ExcelApp: Variant;
begin
Screen.Cursor := crHourglass;
DBGrid1.DataSource.DataSet.DisableControls;
bm := DBGrid1.DataSource.DataSet.GetBook;
DBGrid1.DataSource.DataSet.First;
// create the Excel object
if toExcel then
begin
ExcelApp := CreateOleObject('Excel.Application');
ExcelApp.WorkBooks.Add(xlWBatWorkSheet);
ExcelApp.WorkBooks[1].WorkSheets[1].Name := 'Grid Data';
end;
// First we send the data to a memo
// works faster than doing it directly to Excel
mem := TMemo.Create(Self);
mem.Visible := false;
mem.Parent := MainForm;
mem.Clear;
sline := '';
// add the info for the column names
for col := 0 to DBGrid1.FieldCount-1 do
sline := sline + DBGrid1.Fields[col].DisplayLabel + #9;
mem.Lines.Add(sline);
// get the data into the memo
for row := 0 to DBGrid1.DataSource.DataSet.RecordCount-1 do
begin
sline := '';
for col := 0 to DBGrid1.FieldCount-1 do
sline := sline + DBGrid1.Fields[col].AsString + #9;
mem.Lines.Add(sline);
DBGrid1.DataSource.DataSet.Next;
end;
// we copy the data to the clipboard
mem.SelectAll;
mem.CopyToClipboard;
// if needed, send it to Excel
// if not, we already have it in the clipboard
if toExcel then
begin
ExcelApp.Workbooks[1].WorkSheets['Grid Data'].Paste;
ExcelApp.Visible := true;
end;
FreeAndNil(mem);
// FreeAndNil(ExcelApp);
DBGrid1.DataSource.DataSet.GotoBook(bm);
DBGrid1.DataSource.DataSet.FreeBook(bm);
DBGrid1.DataSource.DataSet.EnableControls;
Screen.Cursor := crDefault;
end;