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 ') 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 ')
/*--test Results
3 Sheet3
2 Sheet2
1 Sheet1
*/