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_oagetpropert