Create an excelfile aa.xls in the einventory directory and test the following code:
Copy codeThe Code is 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 (idint, 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 Result
3 Sheet3
2 Sheet2
1 Sheet1
*/