下边函数返回一个记录集,这个记录集中包含的就是从excel中读入的数据 Function ExcelToADOQuery: TADOQuery; var eclApp,WorkBook:Variant; tempOpenDialog: TOpenDialog; tempADOQuery:TADOQuery; sFileName:string; sSheetName:string; i:integer; begin Result:=nil; tempOpenDialog:=TOpenDialog.Create(nil); tempOpenDialog.Filter:='Excel文件格式(*.xls)|*.xls'; if tempOpenDialog.Execute then begin sFileName:=tempOpenDialog.FileName; If tempADOQuery<>nil Then tempADOQuery.Free; tempADOQuery:=TADOQuery.Create(nil); try if VarIsEmpty(eclApp) then eclApp:=CreateOleObject('Excel.Application'); WorkBook:=eclApp.Workbooks.Open(sFileName); Except on e: exception do begin MessageBox(Application.handle,PChar('导入过程中发生错误!'+#13+'错误信息:'#10 + e.message),'错误',MB_OK+MB_ICONERROR); Exit; end; end; sSheetName:=Workbook.Sheets[1].Name; WorkBook.saved:=true; WorkBook.close; eclApp.Quit; WorkBook := Unassigned; eclApp := Unassigned; 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; For i:=0 To tempADOQuery.Fields.Count-1 Do tempADOQuery.Fields.Fields[i].DisplayWidth :=10; except on e: exception do begin MessageBox(Application.handle,PChar('导入过程中发生错误!'+#13+'错误信息:'#10 + e.message),'错误',MB_OK+MB_ICONERROR); Abort; end; end; end; Result:=tempADOQuery; tempOpenDialog.Free;end;
Function ExcelToADOQuery: TADOQuery;
var
eclApp,WorkBook:Variant;
tempOpenDialog: TOpenDialog;
tempADOQuery:TADOQuery;
sFileName:string;
sSheetName:string;
i:integer;
begin
Result:=nil;
tempOpenDialog:=TOpenDialog.Create(nil);
tempOpenDialog.Filter:='Excel文件格式(*.xls)|*.xls';
if tempOpenDialog.Execute then
begin
sFileName:=tempOpenDialog.FileName;
If tempADOQuery<>nil Then tempADOQuery.Free;
tempADOQuery:=TADOQuery.Create(nil);
try
if VarIsEmpty(eclApp) then
eclApp:=CreateOleObject('Excel.Application');
WorkBook:=eclApp.Workbooks.Open(sFileName);
Except on e: exception do
begin
MessageBox(Application.handle,PChar('导入过程中发生错误!'+#13+'错误信息:'#10 + e.message),'错误',MB_OK+MB_ICONERROR);
Exit;
end;
end;
sSheetName:=Workbook.Sheets[1].Name; WorkBook.saved:=true;
WorkBook.close;
eclApp.Quit;
WorkBook := Unassigned;
eclApp := Unassigned;
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;
For i:=0 To tempADOQuery.Fields.Count-1 Do
tempADOQuery.Fields.Fields[i].DisplayWidth :=10;
except on e: exception do
begin
MessageBox(Application.handle,PChar('导入过程中发生错误!'+#13+'错误信息:'#10 + e.message),'错误',MB_OK+MB_ICONERROR);
Abort;
end;
end;
end;
Result:=tempADOQuery;
tempOpenDialog.Free;end;