使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007

最新推荐文章于 2022-09-13 13:32:03 发布
最新推荐文章于 2022-09-13 13:32:03 发布
阅读量474 收藏
点赞数
分类专栏： 技术文章
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012804387/article/details/22064843
版权
本文详细介绍了使用OpenRowSet和OpenDataSource接口访问Excel97-2007文件的方法，包括不同版本的Excel文件访问、数据类型解析、列顺序问题、NULL值处理及访问隐藏或非常规命名的工作表。文中还提供了针对Jet引擎和ACE引擎的具体语法示例，并讨论了注册表设置、单一与混合数据类型列的解析方式以及如何解决特定问题。
摘要由CSDN通过智能技术生成

使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007
测试文件：D:\97-2003.xls和D:\2007.xlsx，两个文件的内容是一模一样的。

测试环境：SQL Server 2000 / 2005。


--------------------------------------------------------------------------------

接口类型
语法一览
注册表设置
单一数据类型列的类型解析
混合数据类型列的自然解析
混合数据类型列的强制解析——IMEX=1
如何解决 NULL 值问题
SQL Server 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 引擎访问，在语法上没有什么的区别。

view plaincopy to clipboardprint?
--> Jet 引擎访问 Excel 97-2003   
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet1$]')   
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', [Sheet1$])   
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls')...[Sheet1$]   
select * from OpenDataSource('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 * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet1$]')   
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', [Sheet1$])   
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls')...[Sheet1$]   
select * from OpenDataSource('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 * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx', 'select * from [Sheet1$]')   
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx', [Sheet1$])   
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx')...[Sheet1$]   
select * from OpenDataSource('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 * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', [Sheet1$])
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls')...[Sheet1$]
select * from OpenDataSource('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 * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls', [Sheet1$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\97-2003.xls')...[Sheet1$]
select * from OpenDataSource('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 * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx', [Sheet1$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\2007.xlsx')...[Sheet1$]
select * from OpenDataSource('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\Access Connectivity Engine\Engines\Excel

FirstRowHasNames

设置 HDR 参数的缺省行为，默认为 Yes。

ImportMixedTypes

设置混合列的强制解析类型，默认为文本 Text。

TypeGuessRows

设置用于解析数据类型的取样行数，默认取样前 8 行。如果设置为 0，将分析所有数据行，但不建议这样做，会影响引擎的性能。

注意：Excel 表数据列是单一列数据类型还是混合列数据类型列，是由取样行决定，而不是整列数据决定。


--------------------------------------------------------------------------------

单一数据类型列的类型解析
Sheet1 的内容如下图所示，涵盖了大部分 Excel 的数据类型，其中 longtext 分别有 256 个 A 和 B。

 

对于单一数据类型列的类型解析，ACE 引擎和 Jet 引擎是一样的，下面测试 Jet 引擎的数据解析：

view plaincopy to clipboardprint?
use tempdb   
go   
  
select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\97-2003.xls', 'select * from [Sheet1$]')   
  
select  
  a.name,   
  date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end  
from  
  syscolumns a inner join systypes b on a.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  
*/   
  
drop table #type  
use tempdb
go

select * into #type from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\97-2003.xls', 'select * from [Sheet1$]')

select
  a.name,
  date_type = b.name + case a.xusertype when 231 then '('+ltrim(a.length/2)+')' else '' end
from
  syscolumns a inner join systypes b on a.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
*/

drop table #type数据类型解析总结

文本：长度 <= 255，解析为 nvarchar(255)，长度 > 255，解析为 ntext。
数值：货币解析为 money，其它均解析为 float。
时间：datetime。

--------------------------------------------------------------------------------

混合数据类型列的自然解析
相对于使用 IMEX=1 的强制解析，不使用 IMEX=1，称为自然解析。下图是 Sheet2 的内容：

 

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

view plaincopy to clipboardprint?
select * from OpenRowSet('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 * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\97-2003.xls', 'select * from [Sheet2$]')   
select * from OpenRowSet('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 * from OpenRowSet('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 * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\97-2003.xls', 'select * from [Sheet2$]')
select * from OpenRowSet('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 对单一数据类型列的解析是不影响的。

view plaincopy to clipboardprint?
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet2$]')   
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet2$]')   
select * from OpenRowSet('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 * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet2$]')
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=D:\97-2003.xls', 'select * from [Sheet2$]')
select * from OpenRowSet('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 行是文本，8 行之外有非文本的数据，使用 ACE 引擎解决。

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

将前 8 行其中一行的单元格式数字设置为文本（如果还不行，可能要手工重写该单元格，以应用文本格式，不记得是 Office 97 还是 2000 存在这个问题了）；
修改注册表中的 TypeGuessRows（注册表设置），增加取样行数，或设置为 0 全部解析。
目的只有一个，让取样行变成混合数据类型的列，然后使用 IMEX=1 选参解决。


--------------------------------------------------------------------------------

SQL Server 2000 中的列顺序问题
这是 SQL Server 2000 行集函数 OpenRowSet 和 OpenDataSource 本身的问题，与访问接口引擎无关，也与 Excel 版本无关。SQL Server 2005 的 OpenRowSet 和 OpenDataSource 不存在这个问题。

 

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

view plaincopy to clipboardprint?
--> HDR=Yes   
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:\97-2003.xls', [Sheet3$])   
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\97-2003.xls')...[Sheet3$]   
select * from OpenDataSource('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 * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:\97-2003.xls', [Sheet3$])   
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\97-2003.xls')...[Sheet3$]   
select * from OpenDataSource('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 * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:\97-2003.xls', [Sheet3$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\97-2003.xls')...[Sheet3$]
select * from OpenDataSource('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 * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:\97-2003.xls', [Sheet3$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\97-2003.xls')...[Sheet3$]
select * from OpenDataSource('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) 解决。

view plaincopy to clipboardprint?
--> HDR=Yes   
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:\97-2003.xls', 'select * from [Sheet3$]')   
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\97-2003.xls', 'select * from [Sheet3$]')   
select * from OpenRowSet('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 * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:\97-2003.xls', 'select * from [Sheet3$]')   
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\97-2003.xls', 'select * from [Sheet3$]')   
select * from OpenRowSet('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 * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=D:\97-2003.xls', 'select * from [Sheet3$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;Database=D:\97-2003.xls', 'select * from [Sheet3$]')
select * from OpenRowSet('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 * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=No;Database=D:\97-2003.xls', 'select * from [Sheet3$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=No;Database=D:\97-2003.xls', 'select * from [Sheet3$]')
select * from OpenRowSet('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/Wendysuly/archive/2009/01/20/3840626.aspx

小草a小草
关注
专栏目录
【SQL Server技巧篇】如何用Transact-SQL脚本语言导入Excel表
05-22
在SQL Server环境中，当需要处理大量数据或者生成复杂的分析报告时，使用Excel可能会因为性能问题而变得不适用。此时，将Excel数据导入SQL Server数据库是一个高效的选择。本篇将详细介绍如何利用Transact-SQL脚本...
Excel讀入dataset數據丟失的問題!
蝸牛的窩窩
01-30 1049
　摘要：研究了ISAM驱动限制，分析了在.NET中采用Microsoft.Jet.OLEDB读取Excel文件产生数据丢失的原因，给出了解决此问题的方法。 　　关键词：Excel;数据访问;Ole DB;ISAM 　　中图分类号：TP 311.1文献标识码：A 文章编号：1009-3044(2007)04-10937-02 　　 　　1 引言 　　在应用程序的设计中，经常需要读取Excel数据或将
excel单个字段字数过多，导入数据库 存在截断问题的解决方法
哇咔咔的博客
05-11 6220
4、找到 TypeGuessRows 修改为 0。2、打开注册表（搜索框输入 regedit）1、关闭所有office软件。来源：微软官方技术方案。修改后，最好重启电脑。
C# 知识点记录
女孩子与计算机
02-16 169
C# 知识点 1、decimal 表示128位数据类型 同浮点型相比，decimal类型具有更高的精度和更小的范围，使其更适合于财务与货币运算。 2、动态程序集 没有被编译至主程序，而是主程序在运行时动态调用或者生成的程序集。 C#加载程序集的机制： 一个C#应用程序在遇到当前应用程序域（AppDomain）里没有的程序集时，会自动搜索与应用程序在同一目录里的所有程序集，如果没有，抛出异常。 所以如果当我们的动态程序集放到和应用程序在同一个目录里时，不会产生序列化和反序列化的问题，而一旦我们把各种功能的程序
SQL高级应用之使用SQL查询Excel表格数据的方法
09-11
接下来，使用`OPENROWSET`或`OPENDATASOURCE`函数来查询Excel文件。这里我们使用`OPENDATASOURCE`函数来演示如何查询Excel文件。 ##### 使用OPENDATASOURCE函数 `OPENDATASOURCE`函数可以通过指定的数据源连接字符...
SQL访问Excel电子表格的方法
11-03
以上介绍了两种主要的方法来使用SQL Server访问Excel文件：通过创建链接服务器和使用`OpenDataSource`函数。这两种方法各有优缺点，具体选择哪种方法取决于实际需求和个人偏好。通过这些技术，我们可以更灵活地处理...
sql server 和excel的数据导入导出
11-08
在 SQL Server 中查询 Excel 文件可以通过 `OPENROWSET` 或者 `OPENDATASOURCE` 函数来实现。这两种方法都依赖于 OLE DB 提供程序来访问 Excel 文件。 **基本语法：** ```sql SELECT * FROM OPENROWSET('Microsoft...
SQL SERVER使用OpenRowset,、OpenDataSource函数导入、导出数据到Excel 的几种方法
Dlia's blog
03-10 3305
一、如我在D盘下有D:/物件编码.xls文件，有工作表名Sheet是中文命名为‘办公用品编码’，‘零件编码’。 select*into#tempfromopendatasource('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:/物件编码.xls')...[办公用品编码$] select*into#temp...
ADO中GetCollect()函数获取EXCEL表格中完整数字的方法
weixin_34109408的博客
04-26 227
ADO是一组由微软提供的COM组件，基于面向对象思想的编程接口。它建立在COM体系结构之上，它的所有接口都是自动化接口，因此在C++、VisualBasic、Delphi等支持COM的开发语言中通过接口都可以访问到ADO。ADO对象模型非常精炼，由三个主要对象Connection、Command、Recordset和几个辅助对象组成。 我们利用ADO技术的GetCollect...
excel 读出的值为空 解决方案
libinlink的专栏
05-05 2507
明明看到Ecxel中有值，读出来的确是空值 null ，很奇怪吧。1。excel的连接字串中的扩展属性必须包含IMEX=1;2.修改注册表中的HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows的值为0。TypeGuessRows的系统默认值为8，表示系统从文件中的前8行数据中采样取得数据类型。如果前
【SQLserver】用OPENDATASOURCE函数将EXCEL数据写入SQLserver表中
人间十年
03-11 2504
使用T-SQL语句将excel表数据写入SQLserver的实现方式.
C#操作Office杂谈
LazyLion2010的专栏
03-31 648
操作两个Access数据库 从Excel导入数据 从Word导入数据
通过oledb驱动读取excel、csv数据丢失解决方案
weixin_34384557的博客
02-22 327
1、问题出现       在开发应用程序的过程中，比较常用一功能就是通过oledb驱动读取excel、csv、text等文件；而最近有客户反映，在使用短信平台（下载地址：http://www.sms1086.com/product/detail-150.html），发现出现以下问题：     还有一种情况，excel只能导入部分，而其他的导入不进去。     而其他excel或text文件，不会...
从Excel导入SQL Server时的一个错误
sizheng0320的专栏
09-14 736
用导入数据向导，将Excel文件导入到SQL Server数据库，头一回遇到这种错误： “作为源列XXX的数据对于所指定的缓冲区来讲太大”，如图所示：    查了一下，结果如下： 修改服务器的注册表：    HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel   下    双击右边的"Typ
【SQLserver】使用openrowset方法导入EXCEL表格数据
人间十年
09-13 1836
使用openrowset方法导入EXCEL表格数据
Excel 有資料，但匯入到資料庫後卻是 NULL；設定登錄機碼 TypeGuessRows、連線字串 IMEX
只是記錄
02-24 1357
Excel 有資料，但匯入到資料庫後卻是 NULL；設定登錄機碼 TypeGuessRows、連線字串 IMEX
SQL Server访问Excel：OpenRowSet与OpenDataSource方法
"这篇文章主要介绍了如何在SQL Server中使用OpenRowSet和OpenDataSource来访问Excel 97到2007版本的文件。在尝试从ASP.NET导入Excel 2007文件时，可能会遇到'Microsoft.Jet.OLEDB.12.0'提供程序未注册的错误，而本文...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值