sql server访问Excel97-2003 Excel2007

接口可供选择：Microsoft.Jet.OLEDB.4.0（简称Jet引擎）和Microsoft.ACE.OLEDB.12.0（简称ACE引擎）。

Jet 引擎可访问Office 97-2003，但不能访问Office 2007。而ACE 引擎既可访问Office 2007，也可访问Office 97-2003。

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

--1.Excel导入到sql server
select * into sqz from
OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:\data.xls','select*from [data$]')

--2.Excel导入到sql server
select * into sqz from
OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:\data.xls', [data$])

--3.Excel导入到sql server
select * into sqz from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes;IMEX=1;Database=D:\data.xls')...[data$]

--4.Excel导入到sql server
select * into sqz from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=D:\data.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[data$]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--ACE引擎连接 Excel 97-2003
select * from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xls','select * from [data$]')

select * from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xls', [data$])

select * from
OpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xls')...[data$]

select * from
OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:\data.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[data$]

--ACE引擎连接 Excel 2007
select * from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xlsx', 'select * from [data$]')

select * from
OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xlsx', [data$])

select * from
OpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\data.xlsx')...[data$]

select * from
OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=D:\data.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[data$]