Sql操作Excel

2003版

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:\test.xls";Extended properties=Excel 8.0')...[Sheet2$]

 

SELECT  columnName1,columnName3
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:\test.xls";Extended properties=Excel 8.0')...[Sheet2$]

 

2007和2010版本:

 SELECT *
 FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0',
 'Data Source="c:\book1.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0')...[Sheet1$]

 

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\book1.xlsx', 'SELECT * FROM [Sheet1$A1:D100]')

 

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\book1.xlsx', 'SELECT * FROM [Sheet1$]')

 

mssql05中,excel路径不支持中文路径 ,你懂滴