频道分类

delphi ADOConnection1 连接excel表格读取数据

作者:admin 来源: 日期:2020/3/31 18:21:25 人气: 标签:

 
uses ComObj;

procedure TForm1.Button6Click(Sender: TObject);
var
  Excel: OLEVariant;
  ExcelVersion: string;
  filename:string;
begin

filename:=ExtractFilePath(ParamStr(0))+'工作表.xlsx';
   try
      Excel := CreateOLEObject('EXCEL.Application');
      ExcelVersion := Excel.version;
   finally
      Excel.Quit;
      Excel := UnAssigned;
   end;
  ADOConnection1.Close;

  if ExcelVersion = '11.0' then
    //Excel2003及早期的版本
     ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+
                            filename+';Extended Properties=excel 8.0;'+
                            'Persist Security Info=false;'
  else //Excel2007及以后的版本
     ADOConnection1.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+
                            filename+';Extended Properties=excel 12.0;'+
                             'Persist Security Info=True';//注意不能为false


    ADOConnection1.LoginPrompt:=false;
    ADOConnection1.Connected:=true;
    ADOQuery1.Connection:=ADOConnection1;
    ADOQuery1.close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.add('select * from [sheet1$]');
    ADOQuery1.Active:=true;


end;



Delphi Excel写入 ADOConnection
var
  sFileName: string;
begin
  sFileName := ExtractFilePath(Application.ExeName) + 'Book1.xls';
  con1.Connected := False;
  con1.ConnectionString:= 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=' + sFileName + ';Extended Properties=Excel 8.0;Persist Security Info=True';;
  con1.Connected := true;
  con1.Execute('create table Test(name varchar(20))');
  con1.Execute('insert into Test values (''AName'')');
end;