在SQL Server中获取Excel工作表名称 [英] Get Excel sheet name In SQL Server

问题描述

如何使用SQL Sever 2005在Excel文件中获取工作表名称?

How to get the sheet names in an Excel file using SQL Sever 2005?

请注意:

• 没有前端(C#，VB，PHP等)；
• 我正试图仅使用SQL Server 2005来获取工作表名称.

谢谢.

推荐答案

您不能.有两种从Excel读取数据的途径.一种是 COM/OLE自动化路由，它使您可以在工作簿中的工作表中进行枚举.这需要TSQL不会使用的程序语言.我什至不认为如果允许CLR方法混入，您将能够访问Office库，因为它们不在BCL列表中.

You can't. There are two routes to reading data from Excel. One is the COM/OLE automation route which would allow you to enumerate through worksheets in a workbook. That requires a procedural language which TSQL won't do. I don't even think if you allowed CLR methods into the mix, you'd be able to access the Office libraries as they aren't on the BCL list.

在这种情况下，第二种方法是通过openquery使用 Jet驱动程序，但作为设置的一部分，您需要明确定义要访问的文件和工作表.您可以放弃列出工作表名称，但是即使如此，Excel仍无法尽最大可能公开有关工作表的元数据.

The second route would be to use the Jet driver via openquery in this case but as part of the setup, you need to explicitly define the file and worksheet to access. You can forgo listing the worksheet name but even then, Excel does not expose metadata about worksheets to the best of my divination.

如果有人知道另一种方法，但是我已经以多种方式解决了这个问题，那么我将删除此答案，但我没有得出一个不能归结为上述两种方法之一的答案.

I'll delete this answer if someone knows another way but having sliced this problem in a number of ways, I haven't come up with an answer that doesn't boil down to one of the two aforementioned approaches.

这篇关于在SQL Server中获取Excel工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！