--得到Excel文件中的所有工作表名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 lberrexec @err=sp_oamethod @obj,'Workbooks.Open'
,@icount out
,'c:\csdn.xls' --*****************************你的Excel文件名
if @err<>0 goto lberrexec @err=sp_oagetproperty @obj,'ActiveWorkbook.Sheets.Count',@icount out
if @err<>0 goto lberr
select @icount
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 lberr
print @sheetname
set @icount=@icount-1
endexec @err=sp_oadestroy @obj
returnlberr:
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
declare @obj int,@icount int,@sheetname varchar(200)exec @err=sp_oacreate 'Excel.Application',@obj out
if @err<>0 goto lberrexec @err=sp_oamethod @obj,'Workbooks.Open'
,@icount out
,'c:\csdn.xls' --*****************************你的Excel文件名
if @err<>0 goto lberrexec @err=sp_oagetproperty @obj,'ActiveWorkbook.Sheets.Count',@icount out
if @err<>0 goto lberr
select @icount
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 lberr
print @sheetname
set @icount=@icount-1
endexec @err=sp_oadestroy @obj
returnlberr:
exec sp_oageterrorinfo 0,@src out,@desc out
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
drop function [dbo].[f_sheetname]
GO/*--得到Excel文件中的所有工作表名--邹建 2004.04--*//*--调用示例
select * from f_sheetname('c:\a.xls')
--*/
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
怎么回事啊?