SQLServer获取Excel中所有Sheet 收藏

?
E盘根目录新建一个Excel文件aa.xls后测试如下代码

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
*/

?

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jinjazz/archive/2009/08/31/4503535.aspx

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!