Create a function:
Code
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_sheetname] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_sheetname]
Go
Create Function f_sheetname (
@ Excelfname nvarchar (260)
) Returns @ Re table (ID int identity (1, 1), sheetname nvarchar (100 ))
As
Begin
Declare @ err int, @ SRC varchar (255), @ DESC varchar (255)
Declare @ OBJ int, @ icount int, @ sheetname varchar (200)
Exec @ err = sp_oacreate 'excel. application', @ OBJ out
If @ err <> 0 goto lb_err
Exec @ err = sp_oamethod @ OBJ, 'workbooks. open'
, @ Icount out
, @ Excelfname
If @ err <> 0 goto lb_err
Exec @ err = sp_oagetproperty @ OBJ, 'activeworkbook. Sheets. count', @ icount out
If @ err <> 0 goto lb_err
While @ icount> 0
Begin
Set @ src = 'activeworkbook. Sheets ('+ Cast (@ icount as varchar) +'). name'
Exec @ err = sp_oagetproperty @ OBJ, @ SRC, @ sheetname out
If @ err <> 0 goto lb_err
Insert @ Re values (@ sheetname)
Set @ icount =@ icount-1
End
Exec @ err = sp_oadestroy @ OBJ
Goto lb_re
Lb_err:
Exec sp_oageterrorinfo 0, @ SRC out, @ DESC out
Insert @ Re
Select cast (@ err as varbinary (4) as error code
Union all select @ SRC as error source
Union all select @ DESC as error description
Lb_re:
Return
End
Go
In the query analyzer, you can directly call:
Select * From f_sheetname ('C: \ a.xls ')
-- The queried field names include ID and sheetname.
