SQL 读取Excel

xsmscb

　　一、数据库读取Excel文件
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=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)。