This is delphi function to export data from delphi to excel. We still use ComOBj to create ole object excel
from delphi.
Uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComObj, Grids, StdCtrls, ExtCtrls, DBCtrls, DBGrids, DB,
DBTables;
- XLApp := CreateOleObject(’Excel.Application’); –>command to call excel
- XlBook:=XLApp.WorkBooks.Add; –>command to add workbook in excel
- XlSheet := XlBook.worksheets.add; –> command to add worksheet in workbook
- Go to the first record on table
- create loop
- write data on cell worksheet until end of file or last record
This is full function exportab( tab: Ttable; SFile: string): Boolean;
call function :
procedure TForm1.Button3Click(Sender: TObject);
begin
exportab(table1,’export at’+ FormatDateTime(’ dddd dd mmmm yyyy hh mm ss’, Now )+’.xls’);
end;
function TForm1.exportab(tab: Ttable; SFile: string): Boolean;
const
xlCellTypeLastCell = $0000000B;
var
XLApp, XlSheet,XlBook: OLEVariant;
x, r: Integer;
begin
Result := False;
XLApp := CreateOleObject(’Excel.Application’);
try
XlBook:=XLApp.WorkBooks.Add;
XlSheet := XlBook.worksheets.add;
x:=2;
tab.First;
while not tab.Eof do
begin
for r := 1 to tab.FieldCount do
begin
if tab.Fields[r-1].DataType=ftString then
XlSheet.Cells.Item[x, r].Value:=””+tab.Fields[r-1].AsString
else
XlSheet.Cells.Item[x, r].Value:=tab.Fields[r-1].AsString
end;
tab.Next;
inc(x,1);
end;
finally
for r := 1 to tab.FieldCount do
begin
XlSheet.Cells.Item[1, r].Value:=tab.Fields[r-1].FieldName;
XlSheet.cells[1, r].Interior.ColorIndex := 39;
end;
XlSheet.range['A1:z1000'].Columns.AutoFit;
XlApp.visible:=true;
XlApp.ActiveWorkBook.SaveAs(ExtractFilePath(Application.ExeName)+SFile);
XlSheet.PrintPreview;
// XlApp.visible:=true;
// XlApp.free;
end;
end;
EmoticonEmoticon