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
*/
您可能感興趣的文章:- 隨機提取Access/SqlServer數據庫中的10條記錄的SQL語句
- 如何在 Access 2003 和 Access 2002 中創建 DSN 的連接到 SQLServer 對鏈接表
- ACCESS轉SQLSERVER數據庫的注意事項
- Access轉SqlServer的注意事項
- asp.net 數據庫備份還原(sqlserver+access)
- SQL 隨機查詢 包括(sqlserver,mysql,access等)
- Excel導入Sqlserver數據庫腳本
- ASP將Excel數據導入到SQLServer的實現代碼
- ADO.NET 連接數據庫字符串小結(Oracle、SqlServer、Access、ODBC)
- 將ACCESS數據庫遷移到SQLSERVER數據庫兩種方法(圖文詳解)
- 將excel高效導入sqlserver的可行方法
- SQL SERVER 2008 64位系統無法導入ACCESS/EXCEL怎么辦
