解析SQLServer获取Excel中所有Sheet的方法
前端之家收集整理的这篇文章主要介绍了
解析SQLServer获取Excel中所有Sheet的方法，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
E盘根目录新建一个Excel文件aa.xls后测试如下代码
<div class="codetitle"><a style="CURSOR: pointer" data="14410" class="copybut" id="copybut14410" onclick="doCopy('code14410')"> 代码如下:
<div class="codebody" id="code14410">
use tempdb
go
if (object_id ('udf_getExcelTableNames' ) is not null )
drop function dbo .udf_getExcelTableNames
go
create function 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
go
select
from dbo .udf_getExcelTableNames ('e:/aa.xls' )
/--测试结果
3 Sheet3
2 Sheet2
1 Sheet1
*/
