创建一个函数:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
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
--查询出的字段名包括id、sheetname