SQLServer获取Excel中所有Sheet

发布时间：2020-12-12 15:34:59 所属栏目：MsSql教程来源：网络整理

导读：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

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

（编辑：李大同）

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