function ADOQueryToExcel(tempADOQuery:TADOQuery;p_DlgTitle:string=''):boolean;
var
eclApp,WorkBook:Variant;
i,j:integer;
sFileName:string;
tempSaveDialog: TSaveDialog;
begin
//init
result:=false;
tempSaveDialog:=TSaveDialog.Create(nil);
if p_DlgTitle<>'' then
tempSaveDialog.Title:=p_DlgTitle+'另存为';
try
//创建OLE对象Excel Application与 WorkBook
eclApp:=CreateOleObject('Excel.Application');
WorkBook:=CreateOleobject('Excel.Sheet');
Except
ShowMessage('您的机器里未安装Microsoft Excel!');
Exit;
end;
workBook:=eclApp.workBooks.Add;
eclApp.Visible:=false;
//save
tempSaveDialog.Filter:='Excel(*.xls)|*.xls';
if tempSaveDialog.Execute then
begin
sFileName:=tempSaveDialog.FileName;
//取出数据到Excel
with tempADOQuery do
begin
//show field name
for i:=0 to Fields.Count-1 do
eclApp.cells[1,i+1]:=Fields.Fields.DisplayLabel;
//show data
if not Recordset.BOF then
Recordset.MoveFirst;
i:=1;
//for i:=1 to Recordset.RecordCount do
while not Recordset.EOF do
begin
for j:=0 to Fields.Count-1 do
eclApp.cells[i+1,j+1]:=Recordset.Fields.Item[j].Value;
Recordset.MoveNext;
i:=i+1;
end;
end;
try
WorkBook.saveas(sFileName);
result:=true;
Except
ShowMessage('保存为Microsoft Excel文件时出错!');
end;
end;
//关闭对象
WorkBook.saved:=true;
WorkBook.close;
eclApp.Quit;
tempSaveDialog.Free;
end;
function ExcelToADOQuery: TADOQuery;
var
eclApp,WorkBook:Variant;
tempOpenDialog: TOpenDialog;
tempADOQuery: TADOQuery;
sFileName:string;
sSheetName:string;
begin
//init
Result:=nil;
tempOpenDialog:=TOpenDialog.Create(nil);
tempADOQuery:=TADOQuery.Create(nil);
tempOpenDialog.Filter:='Excel文件格式(*.xls)|*.xls';
//open file
if tempOpenDialog.Execute then
begin
sFileName:=tempOpenDialog.FileName;
//取sheet的数据表结构
try
//创建OLE对象Excel Application与 WorkBook
eclApp:=CreateOleObject('Excel.Application');
WorkBook:=eclApp.Workbooks.Open(sFileName);
Except
ShowMessage('您的机器里未安装Microsoft Excel或所选择的文件格式出错!');
Exit;
end;
sSheetName:=Workbook.Sheets[1].Name;
//生成ADOQuery
WorkBook.saved:=true;
WorkBook.close;
eclApp.Quit;
try
with tempADOQuery do
begin
ConnectionString:='Provider = Microsoft.Jet.OLEDB.4.0;Data Source='+sFileName+';Extended Properties=Excel 8.0;';
SQL.Clear;
SQL.Add('select * from ['+sSheetName+'$]');
Open;
end;
except
ShowMessage('打开Excel文件失败!');
Abort;
end;
Result:=tempADOQuery;
end;
tempOpenDialog.Free;
end;
