学步园

创建一个函数：

 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