Resolve SQL Server to get all the sheet in Excel

E Packing directory Create a new Excel file Aa.xls and test the following code

Copy Code code as follows:

Use tempdb
Go
if (object_id (' udf_getexceltablenames ') are NOT null)
Drop function dbo. udf_getexce Ltablenames
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), @sh eetstring 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. UD F_getexceltablenames (' E:/aa.xls ')
/*--test Results
3 Sheet3
2 Sheet2
1 Sheet1
*/