解析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

  



      
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 

*/
 
，