如何用OpenDataSource或者OPENROWSET访问excel中第一个工作表？

Question

我们知道，用以下的方法可以访问account文件里面的sheet1工作表，我想知道有没有办法在不清楚第一个工作表的具体名字的情况下，用OpenDataSource或者OPENROWSET来访问里面的数据？SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1
SELECT *
FROM OPENROWSET
('MICROSOFT.JET.OLEDB.4.0','EXCEL 8.0;DATABASE=c:\Finance\account.xls',sheet1$)

Answer 1

没用过！不知道不动态SQL可以不，你自己试试吧！
指出表名不行吗？

Answer 2

表名有的,是里面工作表名不固定,因为是从别的系统中导出的数据,工作表名是动态变化的,然后用这个数据导入到数据库的时候,需要改工作表名,不然就无法用OpenDataSource或者OPENROWSET来访问数据了,不知道有无可能直接读第一个工作表的数据

Answer 3

use tempdb
go
if(object_id('udf_getExcelTableNames') is not null)
drop function dbo.udf_getExcelTableNames
go
create function udf_getExcelTableNames(@filename varchar(1000))
returns @t table(id int,name varchar(255))
as
begin
declare   
@error int,@obj int,@c int,@sheetname varchar(255) ,@sheetstring varchar(255)
   
exec @error=sp_oacreate 'Excel.Application',@obj  out  
exec @error=sp_oamethod @obj,'Workbooks.Open',@c out,@filename 
exec @error=sp_oagetproperty @obj,'ActiveWorkbook.Sheets.Count',@c   out 
while(@c>0)
begin
set @sheetstring='ActiveWorkbook.Sheets('+ltrim(@c)+').Name'
exec @error=sp_oagetproperty @obj,@sheetstring,@sheetname   out
insert into @t select @c,@sheetname 
set @c=@c-1
end
exec   @error=sp_oadestroy   @obj  
return
end
goselect * from dbo.udf_getExcelTableNames('e:\aa.xls')/*--测试结果
3 Sheet3
2 Sheet2
1 Sheet1
*/

Answer 4

http://msdn.microsoft.com/zh-cn/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

Answer 5

客户端程序获取参考
http://blog.csdn.net/jinjazz/archive/2008/05/13/2441635.aspx

Answer 6

试了,遇到一个问题,在sql2000上运行可以得到结果,同一台机器,在sql2005上得到的确是空表,不知道是什么原因!

Answer 7

在    exec @error=sp_oacreate 'Excel.Application',@obj  out  后面添加语句: insert into @t
select @obj,'sp_oacreate'
return执行后select * from dbo.udf_getExcelTableNames('D:\附属资料\001.xls')在sql2000中返回
id         name
---------------------------------
16711422 sp_oacreate在sql2005中返回
id          name
----------- ------------------
NULL        sp_oacreate很奇怪,因为是在同一台机器上,现在也分析不出原因

Answer 8

后台的EXECEL.EXE关不掉,sp_oadestroy不起作用,怎么搞?