Sql Server 导入Excel及调用存储过程导入Excel

环境：sqlserver2008 64位，office2010 64位，AccessDatabaseEngine.exe 下载地址：点击打开链接

--查询Excel数据
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/test.xlsx', 'select * from [Sheet1$]')    
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/test.xlsx', [Sheet1$])    
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/test.xlsx')...[Sheet1$]    
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/test.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]  

--插入到testTable表
INSERT INTO testTable (id,name)select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=C:\Users\ZL\Desktop\test.xlsx', 'select * from [Sheet1$]')

--创建存储过程  
USE [npiot_20171218]  
GO  
/****** Object:  StoredProcedure [dbo].[spExcelIn]    Script Date: 03/07/2018 15:43:52 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE PROCEDURE [dbo].[spExcelIn]  
 @ExcelPath varchar(1000),  
 @TableName varchar(100)=NULL,  
 @TableField varchar(1000)=NULL  
as  
begin  
 set nocount on  
 declare @strSql varchar(4000)  
 SET @ExcelPath = '''Microsoft.ACE.OLEDB.12.0'',''Data Source="' + @ExcelPath + '";User ID=Admin;Password=;Extended properties=Excel 8.0'''   
 SET @strSql = 'insert into ' +@TableName+' ('+@TableField+') select * from opendatasource('+@ExcelPath+')...Sheet1$'  
 EXEC(@strSql)   
 set nocount off  
end  
[sql] view plain copy
--执行存储过程  
USE [npiot_20171218]  
GO  
DECLARE @return_value int  
EXEC    @return_value = [dbo].[spExcelIn]  
        @ExcelPath= N'C:\Users\ZL\Desktop\test.xlsx',  
        @TableName = N'testTable',  
        @TableField = N'id,name'  
SELECT  'Return Value' = @return_value  
GO  

/*mybatis调用存储过程  */
controller:  
String field = "id,name";  
String tableName="testTable";  
String path = "C:\Users\ZL\Desktop\test.xlsx";  
service.spExcelIn(path,field,tableName);

service:  
    public void spExcelIn(String path, String field, String tableName){  
        Map<String, Object> inputMap = new HashMap<String, Object>();  
        inputMap.put("ExcelPath", path);  
        inputMap.put("TableName", tableName);  
        inputMap.put("TableField", field);  
        spareDiffApplySqlMapper.writeToInner(inputMap);  
    }  
mapper.java:  
Integer spExcelIn(Map<String, Object> inputMap);  

<select id="spExcelIn" parameterType="java.util.Map" statementType="CALLABLE">  
    <![CDATA[ 
      {call spExcelIn (#{ExcelPath,jdbcType=VARCHAR},#{TableName,mode=IN,jdbcType=VARCHAR},#{TableField,mode=IN,jdbcType=VARCHAR})} 
    ]]>  
</select>  

异常处理

1.任务管理器-服务-SQL SERVER 服务-属性-登录-本地系统账户

2.开启远程查询支持
    exec sp_configure 'show advanced options' ,1
    reconfigure
    exec sp_configure 'Ad Hoc Distributed Queries',1

    reconfigure

3.