使用OpenRowSet 和OpenDataSource 访问Excel 97-2007
测试文件：D:/97-2003.xls和D:/2007.xlsx，两个文件的内容是一模一样的。
测试环境：SQLServer 2000 / 2005。
--------------------
接口类型
语法一览
注册表设置
单一数据类型列的类型解析
混合数据类型列的自然解析
混合数据类型列的强制解析——IMEX=1
如何解决NULL 值问题
SQLServer 2000 中的列顺序问题
如何访问隐藏的Sheet
如何访问非常规命名的Sheet
---------------------
接口类型
有两种接口可供选择：Microsoft.Jet.OLEDB.4.0（以下简称Jet 引擎）和Microsoft.ACE.OLEDB.12.0（以下简称ACE 引擎）。
Jet 引擎大家都很熟悉，可以访问Office 97-2003，但不能访问Office 2007。
ACE 引擎是随Office 2007 一起发布的数据库连接组件，既可以访问Office 2007，也可以访问Office 97-2003。
另外：Microsoft.ACE.OLEDB.12.0 可以访问正在打开的Excel 文件，而Microsoft.Jet.OLEDB.4.0 是不可以的。
Microsoft.ACE.OLEDB.12.0 安装文件：
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891
----------------------
语法一览
使用Jet 引擎或ACE 引擎访问，在语法上没有什么的区别。
viewplaincopytoclipboardprint?
--> Jet 引擎访问Excel 97-2003
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls','select * from [Sheet1$]')
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls',[Sheet1$])
select*fromOpenDataSource('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
select*fromOpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=D:/97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--> ACE 引擎访问Excel 97-2003
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls','select * from [Sheet1$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls',[Sheet1$])
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:/97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--> ACE 引擎访问Excel 2007
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx','select * from [Sheet1$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx',[Sheet1$])
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx')...[Sheet1$]
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--> Jet 引擎访问Excel 97-2003
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls','select * from [Sheet1$]')
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls',[Sheet1$])
select*fromOpenDataSource('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
select*fromOpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=D:/97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--> ACE 引擎访问Excel 97-2003
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls','select * from [Sheet1$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls',[Sheet1$])
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:/97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--> ACE 引擎访问Excel 2007
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx','select * from [Sheet1$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx',[Sheet1$])
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx')...[Sheet1$]
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]Excel 2007 工作簿文件的扩展名是：xlsx
HDR=Yes/No
可选参数，指定Excel 表的第一行是否列名，缺省为Yes，可以在注册表中修改缺省的行为。
IMEX=1
可选参数，将Excel 表中混合Intermixed 数据类型的列强制解析为文本。
-----------------------------
注册表设置
Microsoft.Jet.OLEDB.4.0
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel
Microsoft.ACE.OLEDB.12.0
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Office/12.0/AccessConnectivityEngine/Engines/Excel
FirstRowHasNames
设置HDR 参数的缺省行为，默认为Yes。
ImportMixedTypes
设置混合列的强制解析类型，默认为文本Text。
TypeGuessRows
设置用于解析数据类型的取样行数，默认取样前 8 行。如果设置为 0，将分析所有数据行，但不建议这样做，会影响引擎的性能。
注意：Excel 表数据列是单一列数据类型还是混合列数据类型列，是由取样行决定，而不是整列数据决定。
------------------------------
单一数据类型列的类型解析
Sheet1 的内容如下图所示，涵盖了大部分Excel 的数据类型，其中longtext 分别有 256 个A 和B。
对于单一数据类型列的类型解析，ACE 引擎和Jet 引擎是一样的，下面测试Jet 引擎的数据解析：
viewplaincopytoclipboardprint?
usetempdb
go
select*into#typefromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:/97-2003.xls','select * from [Sheet1$]')
select
a.name,
date_type=b.name+casea.xusertypewhen 231 then'('+ltrim(a.length/2)+')'else''end
from
syscolumnsainnerjoinsystypesbona.xusertype=b.xusertype
where
a.id=object_id('#type')
/*
name date_type
-------- -------------
longtext ntext
text nvarchar(255)
datetime datetime
date datetime
time datetime
money money
float float
numeric float
integer float
*/
droptable#type
usetempdb
go
select*into#typefromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:/97-2003.xls','select * from [Sheet1$]')
select
a.name,
date_type=b.name+casea.xusertypewhen 231 then'('+ltrim(a.length/2)+')'else''end
from
syscolumnsainnerjoinsystypesbona.xusertype=b.xusertype
where
a.id=object_id('#type')
/*
name date_type
-------- -------------
longtext ntext
text nvarchar(255)
datetime datetime
date datetime
time datetime
money money
float float
numeric float
integer float
*/
droptable#type数据类型解析总结
文本：长度<= 255，解析为nvarchar(255)，长度> 255，解析为ntext。
数值：货币解析为money，其它均解析为float。
时间：datetime。
---------------------
混合数据类型列的自然解析
相对于使用IMEX=1 的强制解析，不使用IMEX=1，称为自然解析。下图是Sheet2 的内容：
对于混合数据类型列的自然解析，ACE 引擎和Jet 有细节上的区别，先看测试：
viewplaincopytoclipboardprint?
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:/97-2003.xls','select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 NULL
2 sampling 2 2 NULL
3 sampling 3 3 NULL
4 sampling 4 4 A
5 sampling 5 NULL B
6 sampling NULL NULL C
7 sampling NULL NULL D
8 sampling NULL NULL E
9 others 1 2 NULL
10 others NULL NULL <
----- -------- ------- --------- --------
float nvarchar float float nvarchar <-- 解析的数据类型
*/
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:/97-2003.xls','select * from [Sheet2$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:/2007.xlsx','select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 1
2 sampling 2 2 2
3 sampling 3 3 3
4 sampling 4 4 A
5 sampling 5 A B
6 sampling NULL B C
7 sampling NULL C D
8 sampling NULL D E
9 others 1 2 3
10 others NULL = <
----- -------- -------- -------- --------
float nvarchar float nvarchar nvarchar <-- 解析的数据类型
*/
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:/97-2003.xls','select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 NULL
2 sampling 2 2 NULL
3 sampling 3 3 NULL
4 sampling 4 4 A
5 sampling 5 NULL B
6 sampling NULL NULL C
7 sampling NULL NULL D
8 sampling NULL NULL E
9 others 1 2 NULL
10 others NULL NULL <
----- -------- ------- --------- --------
float nvarchar float float nvarchar <-- 解析的数据类型
*/
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:/97-2003.xls','select * from [Sheet2$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:/2007.xlsx','select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 1
2 sampling 2 2 2
3 sampling 3 3 3
4 sampling 4 4 A
5 sampling 5 A B
6 sampling NULL B C
7 sampling NULL C D
8 sampling NULL D E
9 others 1 2 3
10 others NULL = <
----- -------- -------- -------- --------
float nvarchar float nvarchar nvarchar <-- 解析的数据类型
*/相同地方
取样行里数值型多于文本型，解析为float 数值。
取样行里数值型少于文本型，解析为nvarchar/ntext 文本。
当解析为float 数值时，文本类型显示为NULL，这点毫无疑问。
相异地方
取样行里数值型等于文本型，Jet 引擎解析为float 数值，数值优先，ACE 引擎解析为nvarchar/ntext 文本，文本优先。
当解析为nvarchar/ntext 文本时，Jet 引擎将非文本数据显示为NULL，ACE 引擎正确显示。
--------------------------------------------------------------------------------
混合数据类型列的强制解析——IMEX=1
使用IMEX=1 选参之后，只要取样数据里是混合数据类型的列，一律强制解析为nvarchar/ntext 文本。当然，IMEX=1 对单一数据类型列的解析是不影响的。
viewplaincopytoclipboardprint?
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;IMEX=1;Database=D:/97-2003.xls','select * from [Sheet2$]')
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=D:/97-2003.xls','select * from [Sheet2$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;IMEX=1;Database=D:/2007.xlsx','select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 1
2 sampling 2 2 2
3 sampling 3 3 3
4 sampling 4 4 A
5 sampling 5 A B
6 sampling A B C
7 sampling B C D
8 sampling C D E
9 others 1 2 3
10 others > = <
----- -------- -------- -------- --------
float nvarchar nvarchar nvarchar nvarchar <-- 解析的数据类型
*/
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;IMEX=1;Database=D:/97-2003.xls','select * from [Sheet2$]')
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;IMEX=1;Database=D:/97-2003.xls','select * from [Sheet2$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;IMEX=1;Database=D:/2007.xlsx','select * from [Sheet2$]')
/*
id describe num>str num=str num<str
----- -------- -------- -------- --------
1 sampling 1 1 1
2 sampling 2 2 2
3 sampling 3 3 3
4 sampling 4 4 A
5 sampling 5 A B
6 sampling A B C
7 sampling B C D
8 sampling C D E
9 others 1 2 3
10 others > = <
----- -------- -------- -------- --------
float nvarchar nvarchar nvarchar nvarchar <-- 解析的数据类型
*/最后一列（num<str），Jet 引擎的自然解析和强制解析都解析为nvarchar(255)，但是自然解析将数值显示为NULL，强制解析却能正确显示，这是不一致的地方。
在数据解析的细节方面，ACE 引擎的表现优于Jet 引擎。在前面提到的文本优先问题、非文本数据的NULL 值问题，ACE 引擎的解析更合理。
--------------------------------------------------------------------------------
如何解决NULL 值问题
前 8 行（取样行）是混合数据类型的列，使用IMEX=1 选参解决。
前 8 行是文本，行之外有非文本的数据，使用ACE 引擎解决。
前 8 行是数值，行之外又非数值的数据：
将前 8 行其中一行的单元格式数字设置为文本（如果还不行，可能要手工重写该单元格，以应用文本格式，不记得是Office 97 还是 2000 存在这个问题了）；
修改注册表中的TypeGuessRows（注册表设置），增加取样行数，或设置为 0 全部解析。
目的只有一个，让取样行变成混合数据类型的列，然后使用IMEX=1 选参解决。
--------------------------------------------------------------------------------
SQLServer 2000 中的列顺序问题
这是SQLServer 2000 行集函数OpenRowSet 和OpenDataSource 本身的问题，与访问接口引擎无关，也与Excel 版本无关。SQLServer 2005 的OpenRowSet 和OpenDataSource 不存在这个问题。
上图是Sheet3 的内容，连接到SQLServer 2000 测试看看是什么问题：
viewplaincopytoclipboardprint?
--> HDR=Yes
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;Database=D:/97-2003.xls',[Sheet3$])
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;Database=D:/97-2003.xls')...[Sheet3$]
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"')...[Sheet3$]
/*
A B C D E F G H I J
--- --- --- --- --- --- --- --- --- ---
C10 C9 C8 C7 C6 C5 C4 C3 C2 C1
*/
--> HDR=No
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=No;Database=D:/97-2003.xls',[Sheet3$])
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=No;Database=D:/97-2003.xls')...[Sheet3$]
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=No"')...[Sheet3$]
/*
F1 F10 F2 F3 F4 F5 F6 F7 F8 F9
--- --- --- --- --- --- --- --- --- ---
J A I H G F E D C B
C1 C10 C2 C3 C4 C5 C6 C7 C8 C9
*/
--> HDR=Yes
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;Database=D:/97-2003.xls',[Sheet3$])
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;Database=D:/97-2003.xls')...[Sheet3$]
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"')...[Sheet3$]
/*
A B C D E F G H I J
--- --- --- --- --- --- --- --- --- ---
C10 C9 C8 C7 C6 C5 C4 C3 C2 C1
*/
--> HDR=No
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=No;Database=D:/97-2003.xls',[Sheet3$])
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=No;Database=D:/97-2003.xls')...[Sheet3$]
select*fromOpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=No"')...[Sheet3$]
/*
F1 F10 F2 F3 F4 F5 F6 F7 F8 F9
--- --- --- --- --- --- --- --- --- ---
J A I H G F E D C B
C1 C10 C2 C3 C4 C5 C6 C7 C8 C9
*/返回结果集的列顺序，是按照列名排序，并不是Excel 表的列顺序。HDR=No 貌似正确，但仔细一看，仍然是按列名排序的。
OpenRowSet(query)
OpenRowSet(query) 可以解决这个列顺序的问题，包括后面的访问隐藏的Sheet 或非常规命名的Sheet，都可以用OpenRowSet(query) 解决。
viewplaincopytoclipboardprint?
--> HDR=Yes
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;Database=D:/97-2003.xls','select * from [Sheet3$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;Database=D:/97-2003.xls','select * from [Sheet3$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;Database=D:/2007.xlsx','select * from [Sheet3$]')
/*
J I H G F E D C B A
--- --- --- --- --- --- --- --- --- ---
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
*/
--> HDR=No
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=No;Database=D:/97-2003.xls','select * from [Sheet3$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=No;Database=D:/97-2003.xls','select * from [Sheet3$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=No;Database=D:/2007.xlsx','select * from [Sheet3$]')
/*
F1 F2 F3 F4 F5 F6 F7 F8 F9 F10
--- --- --- --- --- --- --- --- --- ---
J I H G F E D C B A
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
*/
--> HDR=Yes
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;Database=D:/97-2003.xls','select * from [Sheet3$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;Database=D:/97-2003.xls','select * from [Sheet3$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;Database=D:/2007.xlsx','select * from [Sheet3$]')
/*
J I H G F E D C B A
--- --- --- --- --- --- --- --- --- ---
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
*/
--> HDR=No
select*fromOpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=No;Database=D:/97-2003.xls','select * from [Sheet3$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=No;Database=D:/97-2003.xls','select * from [Sheet3$]')
select*fromOpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=No;Database=D:/2007.xlsx','select * from [Sheet3$]')
/*
F1 F2 F3 F4 F5 F6 F7 F8 F9 F10
--- --- --- --- --- --- --- --- --- ---
J I H G F E D C B A
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
*/
--------------------------------------------------------------------------------
如何访问隐藏的Sheet
隐藏Sheet 的访问情况比较复杂，就不写测试过程了，归纳一下：
使用OpenRowSet(query) 肯定可以访问。
Excel 2007 任何写法都可以访问（Jet 引擎不能访问Excel 2007）。
打开的Excel 文件任何写法都可以访问（Jet 引擎不能访问打开的Excel 文件）。
--------------------------------------------------------------------------------
如何访问非常规命名的Sheet
新建一个空白的Sheet，重命名为 4 保存关闭：
使用OpenRowSet(query) 可以正常访问。
其它写法，用单引号限定名称['4$'] 可以访问。
OpenRowSet(query) 也可以使用单引号限定访问：'select * from [''4$'']'
引出最后一个问题，访问Excel 97-2003 空白的Sheet，会返回一行NULL 值，访问Excel 2007 空白的Sheet，返回空结果集，数据类型均解析为nvarchar(255)。
本文来自CSDN博客：http://blog.csdn.net/Limpire/archive/2008/06/30/2599760.aspx