使用EXCEL的查询

一、直接用ADO连接Excel

直接用ADOQuery连接，其ConnectionString设为

Provider=microsoft.jet.oledb.4.0;data source=C://2.xls;Extended Properties='Excel 8.0',HDR=Yes;IMEX=1'

SQL属性：select * from [Result$] where month(addtime)>'6'

 

二、借助SQL SERVER的OpenDataSource函数

使用adoquery控件

const

    cConstr='Provider=SQLOLEDB.1;UserID=%s;Password=%s;Data Source=%s;Initial Catalog=%s';

var

    ADO:TADOConnection;

    qry:TADOQuery;

begin

    ADO:=TADOConnection.Create(nil);

    qry:=TADOQuery.Create(nil);

    ADO.Open;

    qry.Connection:=ADO;

    qry.Close;

    qry.SQL.Text:='select count(*) as cou from OpenRowSet("Microsoft.jet.oledb.4.0"),"Excel 8.0;HDR=Yes;database=C:/2.xls;Extended Properties=excel8.0;Persist Security Info=False","select * from [Result$]") where month(addtime)>"9" ';

    qry.Open;

    Showmessage(IntToStr(qry.FieldByName('cou').AsInteger)) ;

    ADO.Free;

    qry.Free;

end;

 

三、其它操作Excel的SQL语句

1.修改Excel

update OpenRowSet("Microsoft.jet.oledb.4.0","Excel 8.0;HDR=Yes;database=C:/Book1.xls;","select * from [sheet2$]") set a="erquan" where C like "%f"

 

2.导入导出

insert into OpenRowSet("microsoft.jet.oledb.4.0","Excel 8.0;hdr=yes;database=C:/book1.xls;","select * from [sheets$] ")(id,name) select id,name from serv_user

 

3.查询

select * from OpenRowSet("microsoft.jet.oledb.4.0","Excel 8.0;hdr=yes;database=C:/book1.xls;","select * from [sheets$] ") where c like "%f%"

 

4.插入数据

select * into temp From OpenRowSet("microsoft.jet.oledb.4.0","Excel 8.0;hdr=yes;database=C:/2.xls;","select * from [Results$] ") where month(addtime)>"6" ');