使用 OpenRowSet 和 OpenDataSource 访问 Excel 97.docx

使用 OpenRowSet 和 OpenDataSource 访问 Excel 97.docx

《使用 OpenRowSet 和 OpenDataSource 访问 Excel 97.docx》由会员分享，可在线阅读，更多相关《使用 OpenRowSet 和 OpenDataSource 访问 Excel 97.docx（13页珍藏版）》请在冰豆网上搜索。

使用OpenRowSet和OpenDataSource访问Excel97

使用OpenRowSet和OpenDataSource访问Excel97-2007

测试文件：

D:

\97-2003.xls和D:

\2007.xlsx，两个文件的内容是一模一样的。

测试环境：

SQLServer2000/2005。

∙接口类型

∙语法一览

∙注册表设置

∙单一数据类型列的类型解析

∙混合数据类型列的自然解析

∙混合数据类型列的强制解析——IMEX=1

∙如何解决NULL值问题

∙SQLServer2000中的列顺序问题

∙如何访问隐藏的Sheet

∙如何访问非常规命名的Sheet

接口类型

有两种接口可供选择：

Microsoft.Jet.OLEDB.4.0（以下简称Jet引擎）和Microsoft.ACE.OLEDB.12.0（以下简称ACE引擎）。

Jet引擎大家都很熟悉，可以访问Office97-2003，但不能访问Office2007。

ACE引擎是随Office2007一起发布的数据库连接组件，既可以访问Office2007，也可以访问Office97-2003。

另外：

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

Microsoft.ACE.OLEDB.12.0安装文件：

语法一览

使用Jet引擎或ACE引擎访问，在语法上没有什么的区别。

viewplaincopytoclipboardprint?

1.--> Jet 引擎访问 Excel 97-2003   

2.select * from OpenRowSet（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:

\97-2003.xls', 'select * from [Sheet1$]'）  

3.select * from OpenRowSet（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:

\97-2003.xls', [Sheet1$]）  

4.select * from OpenDataSource（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:

\97-2003.xls'）...[Sheet1$]  

5.select * from OpenDataSource（'Microsoft.Jet.OLEDB.4.0', 'Data Source=D:

\97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"'）...[Sheet1$]  

6.  

7.--> ACE 引擎访问 Excel 97-2003   

8.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:

\97-2003.xls', 'select * from [Sheet1$]'）  

9.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:

\97-2003.xls', [Sheet1$]）  

10.select * from OpenDataSource（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:

\97-2003.xls'）...[Sheet1$]  

11.select * from OpenDataSource（'Microsoft.ACE.OLEDB.12.0', 'Data Source=D:

\97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"'）...[Sheet1$]  

12.  

13.--> ACE 引擎访问 Excel 2007   

14.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:

\2007.xlsx', 'select * from [Sheet1$]'）  

15.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:

\2007.xlsx', [Sheet1$]）  

16.select * from OpenDataSource（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:

\2007.xlsx'）...[Sheet1$]  

17.select * from OpenDataSource（'Microsoft.ACE.OLEDB.12.0', 'Data Source=D:

\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"'）...[Sheet1$]  

Excel2007工作簿文件的扩展名是：

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?

1.use tempdb  

2.go  

3.  

4.select * into #type from OpenRowSet（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:

\97-2003.xls', 'select * from [Sheet1$]'）  

5.  

6.select  

7.  a.name,  

8.  date_type = b.name + case a.xusertype when 231 then '（'+ltrim（a.length/2）+'）' else '' end  

9.from  

10.  syscolumns a inner join systypes b on a.xusertype = b.xusertype  

11.where  

12.  a.id = object_id（'#type'）  

13.  

14./*  

15.name     date_type  

16.-------- -------------   

17.longtext ntext  

18.text     nvarchar（255）  

19.datetime datetime  

20.date     datetime  

21.time     datetime  

22.money    money  

23.float    float  

24.numeric  float  

25.integer  float  

26.*/  

27.  

28.drop table #type  

数据类型解析总结

∙文本：

长度<=255，解析为nvarchar（255），长度>255，解析为ntext。

∙数值：

货币解析为money，其它均解析为float。

∙时间：

datetime。

混合数据类型列的自然解析

相对于使用IMEX=1的强制解析，不使用IMEX=1，称为自然解析。

下图是Sheet2的内容：

对于混合数据类型列的自然解析，ACE引擎和Jet有细节上的区别，先看测试：

viewplaincopytoclipboardprint?

1.select * from OpenRowSet（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:

\97-2003.xls', 'select * from [Sheet2$]'）  

2./*  

3.id    describe num>str  num=str  num

4.----- -------- -------- -------- --------   

5.1     sampling 1        1        NULL  

6.2     sampling 2        2        NULL  

7.3     sampling 3        3        NULL  

8.4     sampling 4        4        A  

9.5     sampling 5        NULL     B  

10.6     sampling NULL     NULL     C  

11.7     sampling NULL     NULL     D  

12.8     sampling NULL     NULL     E  

13.9     others   1        2        NULL  

14.10    others   NULL     NULL     <  

15.----- -------- ------- --------- --------   

16.float nvarchar float   float     nvarchar  <-- 解析的数据类型   

17.*/  

18.  

19.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:

\97-2003.xls', 'select * from [Sheet2$]'）  

20.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:

\2007.xlsx', 'select * from [Sheet2$]'）  

21./*  

22.id    describe num>str  num=str  num

23.----- -------- -------- -------- --------   

24.1     sampling 1        1        1  

25.2     sampling 2        2        2  

26.3     sampling 3        3        3  

27.4     sampling 4        4        A  

28.5     sampling 5        A        B  

29.6     sampling NULL     B        C  

30.7     sampling NULL     C        D  

31.8     sampling NULL     D        E  

32.9     others   1        2        3  

33.10    others   NULL     =        <  

34.----- -------- -------- -------- --------   

35.float nvarchar float    nvarchar nvarchar  <-- 解析的数据类型   

36.*/  

相同地方

∙取样行里数值型多于文本型，解析为float数值。

∙取样行里数值型少于文本型，解析为nvarchar/ntext文本。

∙当解析为float数值时，文本类型显示为NULL，这点毫无疑问。

相异地方

∙取样行里数值型等于文本型，Jet引擎解析为float数值，数值优先，ACE引擎解析为nvarchar/ntext文本，文本优先。

∙当解析为nvarchar/ntext文本时，Jet引擎将非文本数据显示为NULL，ACE引擎正确显示。

混合数据类型列的强制解析——IMEX=1

使用IMEX=1选参之后，只要取样数据里是混合数据类型的列，一律强制解析为nvarchar/ntext文本。

当然，IMEX=1对单一数据类型列的解析是不影响的。

viewplaincopytoclipboardprint?

1.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:

\97-2003.xls', 'select * from [Sheet2$]'）  

2.select * from OpenRowSet（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:

\97-2003.xls', 'select * from [Sheet2$]'）  

3.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:

\2007.xlsx', 'select * from [Sheet2$]'）  

4./*  

5.id    describe num>str  num=str  num

6.----- -------- -------- -------- --------   

7.1     sampling 1        1        1  

8.2     sampling 2        2        2  

9.3     sampling 3        3        3  

10.4     sampling 4        4        A  

11.5     sampling 5        A        B  

12.6     sampling A        B        C  

13.7     sampling B        C        D  

14.8     sampling C        D        E  

15.9     others   1        2        3  

16.10    others   >        =        <  

17.----- -------- -------- -------- --------   

18.float nvarchar nvarchar nvarchar nvarchar  <-- 解析的数据类型   

19.*/  

最后一列（num

在数据解析的细节方面，ACE引擎的表现优于Jet引擎。

在前面提到的文本优先问题、非文本数据的NULL值问题，ACE引擎的解析更合理。

如何解决NULL值问题

前8行（取样行）是混合数据类型的列，使用IMEX=1选参解决。

前8行是文本，8行之外有非文本的数据，使用ACE引擎解决。

前8行是数值，8行之外又非数值的数据：

∙将前8行其中一行的单元格式数字设置为文本（如果还不行，可能要手工重写该单元格，以应用文本格式，不记得是Office97还是2000存在这个问题了）；

∙修改注册表中的TypeGuessRows（注册表设置），增加取样行数，或设置为0全部解析。

目的只有一个，让取样行变成混合数据类型的列，然后使用IMEX=1选参解决。

SQLServer2000中的列顺序问题

这是SQLServer2000行集函数OpenRowSet和OpenDataSource本身的问题，与访问接口引擎无关，也与Excel版本无关。

SQLServer2005的OpenRowSet和OpenDataSource不存在这个问题。

上图是Sheet3的内容，连接到SQLServer2000测试看看是什么问题：

viewplaincopytoclipboardprint?

1.--> HDR=Yes   

2.select * from OpenRowSet（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:

\97-2003.xls', [Sheet3$]）  

3.select * from OpenDataSource（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:

\97-2003.xls'）...[Sheet3$]  

4.select * from OpenDataSource（'Microsoft.ACE.OLEDB.12.0', 'Data Source=D:

\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes"'）...[Sheet3$]  

5./*  

6.A   B   C   D   E   F   G   H   I   J  

7.--- --- --- --- --- --- --- --- --- ---   

8.C10 C9  C8  C7  C6  C5  C4  C3  C2  C1  

9.*/  

10.  

11.--> HDR=No   

12.select * from OpenRowSet（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:

\97-2003.xls', [Sheet3$]）  

13.select * from OpenDataSource（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:

\97-2003.xls'）...[Sheet3$]  

14.select * from OpenDataSource（'Microsoft.ACE.OLEDB.12.0', 'Data Source=D:

\2007.xlsx;Extended Properties="Excel 12.0;HDR=No"'）...[Sheet3$]  

15./*  

16.F1  F10 F2  F3  F4  F5  F6  F7  F8  F9  

17.--- --- --- --- --- --- --- --- --- ---   

18.J   A   I   H   G   F   E   D   C   B  

19.C1  C10 C2  C3  C4  C5  C6  C7  C8  C9  

20.*/  

返回结果集的列顺序，是按照列名排序，并不是Excel表的列顺序。

HDR=No貌似正确，但仔细一看，仍然是按列名排序的。

OpenRowSet（query）

OpenRowSet（query）可以解决这个列顺序的问题，包括后面的访问隐藏的Sheet或非常规命名的Sheet，都可以用 OpenRowSet（query）解决。

viewplaincopytoclipboardprint?

1.--> HDR=Yes   

2.select * from OpenRowSet（'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:

\97-2003.xls', 'select * from [Sheet3$]'）  

3.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:

\97-2003.xls', 'select * from [Sheet3$]'）  

4.select * from OpenRowSet（'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:

\2007.xlsx'