1、需要注意的是导入前需要打开sp_configure，执行以下代码即可

ALTER PROCEDURE [dbo].[Openconfigure]

AS

BEGIN

exec sp_configure 'show advanced options',1 

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1 

reconfigure

END

2、另外还需要将SQL Full-text Filter Daemon Launcher和MSSQLSERVER登录帐户改为LocalSyatem

3、最后执行下面sql语句

INSERT INTO TableTest(fName ,fSize ,fColor ,fBarcode)

SELECT F1,F2,F3,F4 FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Excel 12.0;HDR=NO;DATABASE=C:\furniture.xlsx',sheet1$)

大功告成

 

---下面是转别人的。

SELECT *

FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\A.xls;User ID=admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]

 

select   *   from  

openrowset('microsoft.jet.oledb.4.0',

'Excel 8.0;HDR=YES;DATABASE=\C:\A.xls',[sheet1$])  

 

导入远端sql

insert OPENDATASOURCE(

         'SQLOLEDB',

         'Data Source=zxp;User ID=sa;Password='

         ).pubs.dbo.tablea(aa,bb,cc)

SELECT aaa,bbb,ccc

FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\A.xls;User ID=admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]