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