复制代码 代码如下:

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