SQLServer读取及导⼊Excel数据
⼀、引⾔
使⽤SQL Server的OPENROWSET及OPENDATASOURCE函数，可以像查询数据表⼀样来读取Excel数据。但是，要想让这两个函数能正常运⾏，可不是那么容易，假如没理解或没配置好的话，⼀路的报错会让你怀疑⼈⽣。
⼆、配置
2.1、组件安装
要想使⽤OPENROWSET及OPENDATASOURCE函数来读取Excel数据，⾸先要在⽬标的SQL Server主机上安装AccessDatabaseEngine组件。
1）换句话说：假如要操作的数据库是在本地的，那我在本地安装AccessDatabaseEngine即可；假如要操作的数据库安装在远程的服务器上，那么需在远程的服务器上安装AccessDatabaseEngine。
2）需要说明的是，读取Excel数据，只需安装AccessDatabaseEngine，并不⼀定要安装Office。
3）依⽬标的SQL Server主机的操作系统位数，来对应安装AccessDatabaseEngine版本。本处Excel是2013版本(.xlsx)，需安装Microsoft Access Database Engine 2010 Redistributable。
2.2、服务配置
在⽬标的SQL Server主机上，Win+R调出运⾏，输⼊services.msc调出服务。将SQL Server (MSSQLSERVER)、SQL Full-text Filter Daemon Launcher (MSSQLSERVER)两个服务的登录⾝份，改为本地系统账户。
2.3、参数配置
在⽬标的SQL Server上打开查询分析器，执⾏以下语句：
--1、开启导⼊功能（查看参数：exec sp_configure）
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--2、允许在进程中使⽤ACE.OLEDB.12.0
exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
--3、允许动态参数
exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
2.3.1、开启导⼊功能对应的系统界⾯：
2.3.2、允许在进程中使⽤ACE.OLEDB.12.0及允许动态参数对应的系统界⾯：
三、测试
3.1、测试语句
在⽬标的SQL Server上打开查询分析器，执⾏以下语句：
--1、使⽤查询分析器查询EXCEL
-
-注意1：若连接的是本机的数据库，E:\EDI\年度返利费⽤表.xlsx指的是本机的⽂件路径。
--注意2：若连接的是远程的数据库，E:\EDI\年度返利费⽤表.xlsx指的是远程服务器的⽂件路径，可使⽤映射的⽅式将⽂件拷到远程服务器。
SELECT*FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;DATABASE=E:\EDI\年度返利费⽤表.xlsx',[Sheet1$])
SELECT*FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;DATABASE=E:\EDI\年度返利费⽤表.xlsx','SELECT * FROM [Sheet1$]')
SELECT*FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Extended Properties="Excel 12.0;HDR=YES;IMEX=2";Data Source="E:\EDI\年度返利费⽤表.xlsx"')...[Sheet1$]
1）特别需要注意的是⽂件路径，请特别留意上⾯代码注释的注意1及注意2。
2）OPENROWSET及OPENDATASOURCE函数，实现的功能是⼀样的，只不过写法有点不⼀样⽽已。
3）连接数据库的账号，服务器⾓⾊需为sysadmin。
3.2、测试结果
执⾏结果如下：
四、案例
能在查询分析上读取Excel数据之后，意味着能在代码上来读取Excel数据了。下⾯通过⼀个⽐较简单的VBA代码，将【年度返利费⽤表.xlsx】写⼊到远程数据库的
BRC_AnnualRebateFee表中。
4.1、数据表建⽴
CREATE TABLE[dbo].[BRC_AnnualRebateFee](
[客户编号][CHAR](10) NOT NULL,
[品号][CHAR](20) NOT NULL,
[年度返利费⽤][NUMERIC](16, 2) NULL,
CONSTRAINT[PK_BRC_AnnualRebateFee]PRIMARY KEY CLUSTERED
(
[客户编号]ASC,
[品号]ASC
)WITH (PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON) ON[PRIMARY]
) ON[PRIMARY]
4.2、⽂件拷贝
将【年度返利费⽤表.xlsx】拷贝到远程服务器下的E:\EDI⽂件夹下。
4.3、VBA程序
1）在本地打开Excel2013，另存为xlsm格式。
2）点击"⽂件"->"选项"->"⾃定义功能区"，勾选"开发⼯具"。
3）点击"开发⼯具"->"插⼊"->"命令按钮(ActiveX 控件)"。
4）点击"设计模式"，然后双击命令按钮进⼊代码页。
5）点击"⼯具"->"引⽤"，勾选"Microsoft ActiveX Data Objects 2.0"，然后点击"确定"。
6）命令按钮代码如下：
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'变量定义
Dim cn As ADODB.Connection, cmd As New ADODB.Command
Dim strCn As String, strSql As String
'数据库连接
strCn = "Provider=SQLOLEDB;Data Source=erpserver;Initial Catalog=TEST;User Id=edi;Password=edi;"
Set cn = New ADODB.Connection
cn.Open strCn
If cn.State <> adStateOpen Then
cn.Close
MsgBox"数据连接失败。", vbOKOnly, "提⽰"
Exit Sub
End If
'命令对象赋初始值
With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = ""
.CommandTimeout = 0
End With
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''excel连接sql数据库教程
'读取年度返利费⽤表
strSql = "TRUNCATE TABLE BRC_AnnualRebateFee"
cmd.CommandText = strSql
cmd.Execute
strPath = "E:\EDI\年度返利费⽤表.xlsx"
strSql = "INSERT INTO BRC_AnnualRebateFee (客户编号，品号，年度返利费⽤) SELECT 客户编号，品号，年度返利费⽤ FROM OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;Database=" & strPath & "','SELECT * FRO 'strSql = "INSERT INTO BRC_AnnualRebateFee (客户编号，品号，年度返利费⽤) SELECT 客户编号，品号，年度返利费⽤ FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=2;Database=" & strPath & "')...[Sheet1$    cmd.CommandText = strSql
cmd.Execute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'关闭连接
cn.Close
Set cn = Nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''