導(dǎo)讀:1建站知識本篇文章是對SQLServer獲取Excel中所有Sheet的方法進行了詳細的分析介紹，需要的朋友參考下公司網(wǎng)站建設(shè)網(wǎng)站建設(shè)公司。

E盤根目錄新建一個Excel文件aa.xls后測試如下代碼

復(fù)制代碼 代碼如下:

use tempdb go if (網(wǎng)站建設(shè)多少錢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 , seo網(wǎng)站優(yōu)化@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 =網(wǎng)站seo優(yōu)化 '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' )/*--測試結(jié)果 3 Sheet3 2 Sheet2 1 Sheet1 */

關(guān)鍵詞標簽: 標題 標簽 SQL