ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007

使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007

原创 Linux操作系统 作者:小鸟1128 时间:2009-03-04 16:06:48 0 删除 编辑

 

接口类型

有两种接口可供选择: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 引擎访问,在语法上没有什么的区别。

  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$]  

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 引擎的数据解析:

  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 选参之后,只要取样数据里是混合数据类型的列,一律强制解析为 nvarchar/ntext 文本。当然,IMEX=1 对单一数据类型列的解析是不影响的。

  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 行其中一行的单元格式数字设置为文本(如果还不行,可能要手工重写该单元格,以应用文本格式,不记得是 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 测试看看是什么问题:

  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) 解决。

  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''select * from [Sheet3$]')   
  5. /*   
  6. J   I   H   G   F   E   D   C   B   A   
  7. --- --- --- --- --- --- --- --- --- ---   
  8. C1  C2  C3  C4  C5  C6  C7  C8  C9  C10   
  9. */   
  10.   
  11. --> HDR=No   
  12. select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0''Excel 8.0;HDR=No;Database=D:\97-2003.xls''select * from [Sheet3$]')   
  13. select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0''Excel 12.0;HDR=No;Database=D:\97-2003.xls''select * from [Sheet3$]')   
  14. select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0''Excel 12.0;HDR=No;Database=D:\2007.xlsx''select * from [Sheet3$]')   
  15. /*   
  16. F1  F2  F3  F4  F5  F6  F7  F8  F9  F10   
  17. --- --- --- --- --- --- --- --- --- ---   
  18. J   I   H   G   F   E   D   C   B   A   
  19. C1  C2  C3  C4  C5  C6  C7  C8  C9  C10   
  20. */  

如何访问隐藏的 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)。 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/518723/viewspace-561399/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-14

  • 博文量
    22
  • 访问量
    50035