一、直接用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" ');
