解析SQLServer获取Excel中所有Sheet的方法
时间:2024-01-18 15:11:26
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
*/
标签:SQLServer,Excel,Sheet
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python读入mnist二进制图像文件并显示实例
2023-04-23 16:40:44
Vue实例中生命周期created和mounted的区别详解
2024-04-29 13:08:15
![](https://img.aspxhome.com/file/2023/9/132919_0s.png)
Linux下编译安装Mysql 5.5的简单步骤
2024-01-27 13:33:03
Python常用数字处理基本操作汇总
2021-01-06 12:56:28
python 命令行传参方法总结
2021-06-04 20:51:00
Python反爬虫伪装浏览器进行爬虫
2023-07-05 22:33:38
![](https://img.aspxhome.com/file/2023/6/109536_0s.jpg)
用Dreamweaver MX设计各种网页鼠标样式
2008-10-04 10:18:00
WIn10+Anaconda环境下安装PyTorch(避坑指南)
2023-06-20 03:24:04
![](https://img.aspxhome.com/file/2023/6/105566_0s.png)
Vue前端判断数据对象是否为空的实例
2024-05-02 16:53:14
![](https://img.aspxhome.com/file/2023/4/130154_0s.jpg)
Python字符串格式化
2023-08-19 20:27:44
mysql 查询数据库中的存储过程与函数的语句
2024-01-26 17:30:35
Python 爬虫之超链接 url中含有中文出错及解决办法
2023-07-27 00:25:06
Python正则表达式匹配ip地址实例
2023-06-13 15:06:12
CentOS7安装MySQL 8.0.26的过程
2024-01-20 08:28:01
![](https://img.aspxhome.com/file/2023/7/107917_0s.png)
Vue实现web分页组件详解
2024-04-30 10:39:27
![](https://img.aspxhome.com/file/2023/2/130102_0s.gif)
MySQL自定义序列数的实现方式
2024-01-14 00:09:50
MYSQL中binlog优化的一些思考汇总
2024-01-23 01:58:25
![](https://img.aspxhome.com/file/2023/3/96493_0s.jpg)
asp中最新新闻显示new图片的实现代码
2012-11-30 20:31:42
YOLOv5部署到web端详细过程(flask+js简单易懂)
2023-03-28 07:00:17
![](https://img.aspxhome.com/file/2023/7/123827_0s.png)
编写python程序的90条建议
2022-04-30 00:36:22