在我昨天的文章《在SQL中读取和使用Excel数据》的评论中，@足彩沙龙 朋友提出，想要我详细说一下OPENROWSET中各个参数的含义。今天我就详细说说SQLServer神奇好用的行集函数。

SQLServer的行集函数

SQLServer中有5个行集函数：

• OPENDATASOURCE('驱动引擎', '数据源')
• OPENJSON('Json字符串', 其它参数)
• OPENROWSET('驱动引擎', '数据源', 其它参数)
• OPENQUERY(链接服务器,查询脚本)
• OPENXML('XML字符串',其它参数)

这五个行集函数写法不同，但大多可实现共同目的。比如要读取Excel，我们既可以使用OpenDataSource、也可以使用OpenRowSet，甚至可以先创建一个链接服务器，再用OpenQuery读取，殊途同归。

其中，从外部Excel文件需要用到的，主要是OpenDataSource、OpenRowSet和OpenQuery，今天我们重点说说前两个。

其中的驱动引擎含义为：

注册为用于访问数据源的 OLE DB 访问接口的 PROGID 的名称。 的数据类型为 char，并且没有默认值。

说的通俗些，就是一个用来表示驱动引擎的字符串了。比如，我们要读取Excel，就主要用到'Microsoft.Jet.OLEDB.4.0'（简称Jet引擎）或'Microsoft.ACE.OLEDB.12.0'（简称ACE引擎）两种引擎。

要查看我们的数据支持的驱动引擎，可执行：

EXEC master..xp_enum_oledb_providers 

在SQL管理器中也可以查看：

Jet 引擎可以访问 Office 97-2003，但不能访问 Office 2007及以后。ACE 引擎是随 Office 2007 一起发布的数据库连接组件，既可以访问 Office 2007，也可以访问 Office 97-2003。

ACE引擎可以访问正在打开的 Excel 文件，而 Microsoft.Jet.OLEDB.4.0 是不可以的。

OpenDataSource和OpenRowSet两者之间有什么区别呢？

其实很容易理解，OpenDataSource返回的像整个Excel文件，您还需要继续指定它对应的Sheet；而OpenRowSet返回的直接是某个Sheet。

比如，我们要读取一个Excel文件“销售数据”里面的一个Sheet“销售表”，针对不同的Excel版本，实现方法分别如下：

如何读取Excel97~2003

1、使用OpenDataSource和Jet引擎

select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 
'Data Source=C:\销售数据.xls;Extended Properties=EXCEL 5.0')...[销售表$]; 

2、使用OpenRowSet和Jet引擎

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0; HDR=Yes; IMEX=1; Database=C:\销售数据.xls', [销售表$]);

数据源中的HDR、IMEX的含义这里有必要说一下：

HDR=Yes，第一行是标题，做字段名。

HDR=NO，第一行是数据。

系统默认的是YES

IMEX 有三种模式：

0—输入（写入）模式

1—输出（读取）模式

2—完全（读取、写入）模式

3、使用OpenDataSource和ACE引擎

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=Yes; IMEX=1; Database=C:\销售数据.xls')...[销售表$];

4、使用OpenRowSet和ACE引擎

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=Yes; IMEX=1; Database=c:\销售数据.xls', [销售表$]);

如何读取Excel2007及以后

1、使用OpenDataSource和ACE引擎

select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=Yes; IMEX=1; Database=C:\销售数据.xlsx')...[销售表$];

2、使用OpenRowSet和ACE引擎

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=Yes; IMEX=1; Database=C:\销售数据.xlsx', [销售表$]);

希望对您有所帮助！