sql动态读取Excel多个分页

最新推荐文章于 2024-11-22 10:11:53 发布

chinaly

最新推荐文章于 2024-11-22 10:11:53 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

文章标签： excel sql insert table c office

本文链接：https://blog.youkuaiyun.com/chinaly/article/details/4962786

这篇博客介绍了一种SQL方法，用于动态读取指定文件夹下所有Excel文件的多个工作表数据，并将其插入到数据库表中。通过创建一个SQL函数`udf_getExcelTableNames`获取Excel文件的工作表名称，然后使用`OPENROWSET`配合`OPENXML`读取数据，实现了对Excel数据的批量导入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 ))

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