SQLServer获取Excel中所有Sheet

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
*/