在SQL Server中获取Excel表格名称

Question

如何使用SQL Server 2005获取Excel文件中的工作表名称？

请注意：

• 没有前端（C＃，VB，PHP等）；
• 我正在尝试仅使用SQL Server 2005获取工作表名称。

谢谢。

- Temp

Answer 1

至少有两种方法可以做到这一点。我承认，目前我没有一种简单的方法来在SQL Server 2005中检查这个，只有2008有。

1：创建一个链接服务器并使用sp_tables_ex和/或sp_columns_ex：

-- Get table (worksheet) or column (field) listings from an excel spreadsheet

-- SET THESE!
declare @linkedServerName sysname = 'TempExcelSpreadsheet'
declare @excelFileUrl nvarchar(1000) = 'c:\MySpreadsheet.xls'
-- /SET

-- Remove existing linked server (if necessary)
if exists(select null from sys.servers where name = @linkedServerName) begin
    exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

-- Add the linked server
-- ACE 12.0 seems to work for both xsl and xslx, though some might prefer the older JET provider
exec sp_addlinkedserver
    @server = @linkedServerName,
    @srvproduct = 'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = @excelFileUrl,
    @provstr = 'Excel 12.0;HDR=Yes'

-- Grab the current user to use as a remote login
declare @suser_sname nvarchar(256) = suser_sname()

-- Add the current user as a login
exec sp_addlinkedsrvlogin
    @rmtsrvname = @linkedServerName,
    @useself = 'false',
    @locallogin = @suser_sname,
    @rmtuser = null,
    @rmtpassword = null

-- Return the table/column info
exec sp_tables_ex @linkedServerName
exec sp_columns_ex @linkedServerName

-- Remove temp linked server
if exists(select null from sys.servers where name = @linkedServerName) begin
    exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

我在这里找到了这个想法的灵感。

2: 可以按照这里的说明使用Ole自动化程序。 我自己没有尝试过。

- Tim Lehner

Answer 2

不行。从Excel读取数据有两种方法。一种是COM/OLE自动化路线，可以枚举工作簿中的工作表。这需要过程性语言，而TSQL无法实现。即使允许CLR方法混合使用，我认为也无法访问Office库，因为它们不在BCL列表中。

第二种方法是通过openquery使用Jet驱动程序，但在设置的一部分中，您需要明确定义要访问的文件和工作表。您可以放弃列出工作表名称，但即使这样，Excel也不会公开有关工作表的元数据，根据我的推测。

如果有人知道其他方法，我会删除此答案，但是在多种方式中切片这个问题后，我没有想到不归结于上述两种方法之一的答案。

- billinkc

---

我正在考虑CLR方法...尽管我仍需要进行进一步的学习和研究。谢谢。 - Temp

@billinkc，这显然是旧的，但我认为我可能碰巧发现了一种或两种方法。 - Tim Lehner

Answer 3

仅补充选项2 - Ole自动化程序，因为Tim的答案中的链接现在已经失效。您可以使用以下代码来完成此操作：

declare @FilePath varchar(max) = '[Excel File].xlsx'
declare @ConnectionString varchar(max) = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+@FilePath+';Extended Properties=''Excel 12.0;HDR=Yes'''
declare @r int, @objConnection int, @objRecordSet int
          exec @r = sp_OACreate 'ADODB.Connection', @objConnection output
if @r = 0 exec @r = sp_OAMethod @objConnection, 'Open', null, @connectionstring
if @r = 0 exec @r = sp_OAMethod @objConnection, 'OpenSchema', @objRecordSet output, 20
if @r = 0 exec @r = sp_OAGetProperty @objRecordSet, 'GetRows'

- Brenton