建立一個函數：
代碼
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 錯誤號碼
union all select @src as 錯誤源
union all select @desc as 錯誤描述
lb_re:
return
end
go
在查詢分析器中可以直接調用：
select * from f_sheetname('c:\a.xls')
--查詢出的欄位名包括id、sheetname
