e盘根目录新建一个excel文件aa.xls后测试如下代码
复制代码 代码如下:
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
go
select * from dbo .udf_getexceltablenames (‘e:/aa.xls’ )
/*–测试结果
3 sheet3
2 sheet2
1 sheet1
*/