AI 助理
备案控制台
开发者社区 数据库 文章 正文

使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007

2011-09-30 991
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： source:http://blog.csdn.net/Limpire/archive/2008/06/30/2599760.aspx#E10 使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007测试文件：D:\97-2003.xls和D:\2007.xlsx，两个文件的内容是一模一样的。

source:http://blog.csdn.net/Limpire/archive/2008/06/30/2599760.aspx#E10

使用 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-cnFamilyID=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-2003select * 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-2003select * 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 2007select * 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。

Sheet1

对于单一数据类型列的类型解析，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 tempdbgoselect * 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 '' endfrom  syscolumns a inner join systypes b on a.xusertype = b.xusertypewhere  a.id = object_id('#type')/*name     date_type-------- -------------longtext ntexttext     nvarchar(255)datetime datetimedate     datetimetime     datetimemoney    moneyfloat    floatnumeric  floatinteger  float*/drop table #type数据类型解析总结

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

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

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        NULL2     sampling 2        2        NULL3     sampling 3        3        NULL4     sampling 4        4        A5     sampling 5        NULL     B6     sampling NULL     NULL     C7     sampling NULL     NULL     D8     sampling NULL     NULL     E9     others   1        2        NULL10    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        12     sampling 2        2        23     sampling 3        3        34     sampling 4        4        A5     sampling 5        A        B6     sampling NULL     B        C7     sampling NULL     C        D8     sampling NULL     D        E9     others   1        2        310    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        12     sampling 2        2        23     sampling 3        3        34     sampling 4        4        A5     sampling 5        A        B6     sampling A        B        C7     sampling B        C        D8     sampling C        D        E9     others   1        2        310    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

 

 

上图是 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=Yesselect * 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=Noselect * 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   BC1  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=Yesselect * 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=Noselect * 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   AC1  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)。

 

SQL 2005 开启OpenRowset/OpenDatasource的办法

在SQL2005中，MS为安全考虑默认阻止了OpenRowset和OpenDatasource。如果未被开启会有如下的错误提示：
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
   启用：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
    关闭：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

 

 

文章标签：
云解析DNS
混合云容灾服务
云数据库 RDS SQL Server 版
SQL
测试技术
安全
数据库连接
关键词：
访问excel
y0umer
目录
相关文章
维维cloud
|
SQL 存储 关系型数据库
通过excel的VBA 是否可以 直接访问阿里云的RDS数据库（SQL Server）
通过excel的VBA 直接访问阿里云的云数据库（SQL Server）
维维cloud
4299 0
技术小甜
|
Windows 数据库连接
VB 6.0中如何访问EXCEL 2007及EXCEL 2010
技术小甜
1420 0
y0umer
|
Windows
Microsoft Office Excel 不能访问文件 的解决办法
Microsoft Office Excel 不能访问文件“a.xls”。 可能的原因有: ? 文件名称或路径不存在。  ? 文件正被其他程序使用。  ? 您正要保存的工作簿与当前打开的工作簿同名 This solution is .
y0umer
3251 0
rstaryan
|
数据库 存储 小程序
用数据库访问的方法解决从excel大量导入数据
  要从excel里面导入数据岛程序里面，如果用一般方法 在数量很大的情况下 可能耗费很长的时间。  在网上查询了一些资料获得了一个执行效率相当高的方法，现在写下来以方便以后知识的整理。
rstaryan
928 0
y0umer
|
安全 数据安全/隐私保护
Microsoft Excel 不能访问文件“ 文件名称或路径不存在。 • 文件正被其他程序使用。 • 您正要保存的工作簿与当前打开的工作簿同名。
Microsoft Office Excel 不能访问文件“D:\WWWRoot\KOBELCOSH\WebUI\ExcelTemplate\QUOTE5.xls”。 可能的原因有: 1 文件名称或路径不存在。
y0umer
3088 0
pollyduan
|
Java 数据库连接
java直接访问excel的demo
今天一个哥们问，用jdbc能不能把excel作为数据源访问？ 考虑之前写的一个java访问access的例子，稍加修改，测试可以用。 测试例子，新建一个excel，命名为Mobile.
pollyduan
1116 0
鱼的爱情看不出泪水
|
5月前
|
Python
如何根据Excel某列数据为依据分成一个新的工作表
在处理Excel数据时，我们常需要根据列值将数据分到不同的工作表或文件中。本文通过Python和VBA两种方法实现该操作：使用Python的`pandas`库按年级拆分为多个文件，再通过VBA宏按班级生成新的工作表，帮助高效整理复杂数据。
鱼的爱情看不出泪水
347 1
兵临天下19970108016
|
5月前
|
数据采集 数据可视化 数据挖掘
用 Excel+Power Query 做电商数据分析：从 “每天加班整理数据” 到 “一键生成报表” 的配置教程
在电商运营中，数据是增长的关键驱动力。然而，传统的手工数据处理方式效率低下，耗费大量时间且易出错。本文介绍如何利用 Excel 中的 Power Query 工具，自动化完成电商数据的采集、清洗与分析，大幅提升数据处理效率。通过某美妆电商的实战案例，详细拆解从多平台数据整合到可视化报表生成的全流程，帮助电商从业者摆脱繁琐操作，聚焦业务增长，实现数据驱动的高效运营。
兵临天下19970108016
775 0
游客ssfcoszh24x4q
|
7月前
|
存储 安全 大数据
网安工程师必看！AiPy解决fscan扫描数据整理难题—多种信息快速分拣+Excel结构化存储方案
作为一名安全测试工程师，分析fscan扫描结果曾是繁琐的手动活：从海量日志中提取开放端口、漏洞信息和主机数据，耗时又易错。但现在，借助AiPy开发的GUI解析工具，只需喝杯奶茶的时间，即可将[PORT]、[SERVICE]、[VULN]、[HOST]等关键信息智能分类，并生成三份清晰的Excel报表。告别手动整理，大幅提升效率！在安全行业，工具党正碾压手动党。掌握AiPy，把时间留给真正的攻防实战！官网链接：https://www.aipyaipy.com，解锁更多用法！
游客ssfcoszh24x4q
369 0
无糖可乐嘟嘟
|
数据采集 数据可视化 数据挖掘
利用Python自动化处理Excel数据：从基础到进阶####
本文旨在为读者提供一个全面的指南，通过Python编程语言实现Excel数据的自动化处理。无论你是初学者还是有经验的开发者，本文都将帮助你掌握Pandas和openpyxl这两个强大的库，从而提升数据处理的效率和准确性。我们将从环境设置开始，逐步深入到数据读取、清洗、分析和可视化等各个环节，最终实现一个实际的自动化项目案例。 ####
无糖可乐嘟嘟
2155 10

热门文章

最新文章

  • 1
    Python 高效清理 Excel 空白行列：从原理到实战
  • 2
    Java读取、写入、处理Excel文件中的数据
  • 3
    pandas读excel类型文件报错: xlrd.biffh.XLRDError: Excel xlsx file； not supported
  • 4
    使用JavaScript和XLSX.js将数据导出为Excel文件
  • 5
    到处excel表格的数据和页面的数据不一致
  • 6
    杨老师课堂之Excel VBA 程序开发第七讲表格数据高亮显示
  • 7
    Java Poi创建与读取Excel
  • 8
    【RDS MySQL】将Excel的数据导入数据库
  • 9
    如何使用Excel管理项目？
  • 10
    【Excel操作的白月光】「EasyExcel技术专题」摒除OOM！让你的Excel操作变得更加优雅和安全
  • 1
    Python 高效清理 Excel 空白行列：从原理到实战
    404
  • 2
    Error resolving template [excel/readExcel], template might not exist or might not be accessible by
    155
  • 3
    如何将Excel表的数据导入RDS MySQL数据库？
    219
  • 4
    别再用Excel死磕了！阿里云QBI+DataV才是政企报表的正确打开方式
    275
  • 5
    公众号如何添加附传Word、Excel、Pdf、PPT文档
    608
  • 6
    (H5查看CAD)网页CAD提取图纸表格到excel
    197
  • 7
    恢复office图标，Microsoft office（Word，Excel，Powerpoint）图标变为白色恢复方法
    3968
  • 8
    用 Excel+Power Query 做电商数据分析：从 “每天加班整理数据” 到 “一键生成报表” 的配置教程
    775
  • 9
    Django实战：基于Django和openpyxl实现Excel导入导出功能
    186
  • 10
    Excel 如何利用Pivot Table将日期时间直接分类为月汇总
    974

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    安全设备篇——WAF