sql动态读取Excel多个分页

 

sql Code

 

--先创建一个方法,动态读取一个文件夹下面所有的Excle文件

 

--sql function

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER function [dbo].[udf_getExcelTableNames] (@filename varchar (1000 ))

returns @t table (id int , name varchar (255 ))

as

begin

declare

@error int , @obj int , @c int , @sheetname varchar (255 ) , @sheetstring varchar (255 )

 

exec @error = sp_oacreate 'Excel.Application' , @obj out

exec @error = sp_oamethod @obj , 'Workbooks.Open' , @c out , @filename

exec @error = sp_oagetproperty @obj , 'ActiveWorkbook.Sheets.Count' , @c out

while (@c > 0 )

begin

set @sheetstring = 'ActiveWorkbook.Sheets(' + ltrim (@c )+ ').Name'

exec @error = sp_oagetproperty @obj , @sheetstring , @sheetname out

insert into @t select @c , @sheetname

set @c = @c - 1

end

exec @error = sp_oadestroy @obj

return

end

 

---

 

--并支持读取一个Excel文件有多个分页文件名

--读取多个Excel文件的数据全部保存到表#Result中

 

create table #Result
(
 A1 varchar(10),
 B1 varchar(10),
 C1 varchar(10),
 D1 varchar(10)
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
 create table #t(fname varchar(260),depth int,isf bit)
 insert into #t exec master..xp_dirtree 'c:/test',1,1
 

 --注意,我用的是office2005,Excel后缀名为.xlsx
 declare tb cursor for select fn='c:/test/'+fname from #t where isf=1 and fname like '%.xlsx'  --取.xls文件(EXCEL)
 declare @fn varchar(8000)
 open tb
 fetch next from tb into @fn   --循环路径c:/test/文件夹下面所有的Excel文件名
 while @@fetch_status=0
 begin

  declare @sql varchar(2000)

  set @sql='insert into #Result(A1,B1,C1,D1)
      select A1,B1,C1,D1 from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE='+@fn+''+''','

  declare ta cursor for select ta=name+'$' from dbo.udf_getExcelTableNames (@fn)
 
  open ta
  declare @ta varchar(255)
  fetch next from ta into @ta  --循环Excel文件下面多个Excel分页
  while @@fetch_status=0
  begin
   set @fn=@sql+@ta+')'
 
   exec(@fn)

fetch next from ta into @ta

end

close ta
deallocate ta
    fetch next from tb into @fn
end
select * from #Result
close tb
deallocate tb
drop table #t
drop table #Result

 

--end

 

c:/test/下面Excel文件格式:

 

A1         B1       C1      D1

A01       B01     C01    D01

A02       B02     C02    D02

A03       B03     C03    D03