|
|
本文分享三种Excel连接Sql Server数据库的方法。第一种是用Excel自带的获取外部数据功能，第二种是用Excel VBA，第三种是用专业的第三方插件
1、用Excel自带的获取外部数据功能连接Sql Server数据库
点击“数据”->“自其他来源”->“来自Sql Server”->填写登录信息->选择数据库和表->下一步->完成。此时会跳出以下对话框：
此处可以读取表中的数据，或是用Excel透视图或透视表来分析这个表
2、用Excel VBA连接Sql Server数据库
点击“工具”->“引用”添加以下引用：
Microsoft ActiveX Data Objects 2.8 Libary
Microsoft ActiveX Data Objects Records 2.8 Libary
Microsoft ADO Ext.2.8 For Dll and Security
Microsoft Forms 2.0 Object Libary
在工作簿里建一个表，我们给它取名为sys并在表中定义好数据库连接信息如下：
这样我们就可以自己配置数据库连接信息了
新建一个模块专门用来写数据库的常用操作比如我们给这个模块取名为Mdl_public，首先在这个模块中定义以下几个公共变量
Public cat As New ADOX.Catalog
Public Conn As New ADODB.Connection '定义数据链接对象 ,保存连接数据库信息；请先添加ADO引用
Public rs As New ADODB.Recordset '定义记录集对象,保存数据表
Public Strsql As String
'打开数据库连接
Public Sub OpenSql()
If Conn.State = 1 Then Conn.Close
If Conn.State = 0 Then
With ThisWorkbook.Sheets("sys")
Conn.Open "Provider=sqloledb;" & _
" Server=" & .Cells(1, 2).Value & _
";Database=" & .Cells(2, 2).Value & _
";Uid=" & .Cells(3, 2).Value & _
";Pwd=" & .Cells(4, 2).Value & ";" '定义数据库链接字符串
End With
End If
End Sub
'关闭数据库连接
Public Sub CloseConn()
rs.Close
Conn.Close
End Sub
通过以上方法就实现了Sql Server数据库连接的打开和关闭
接下来，我们要使用连接查询数据库中的表，如下：
Sub ViewTop1000Rows(TBName As String)
Strsql = "SELECT TOP 1000 * FROM " & TBName
OpenSql '打开连接
rs.Open Strsql, Conn '使用连接
Cells.Clear
Dim i As Integer
For i = 0 To rs.Fields.Count - 1 '处理查询结果
Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
Cells(2, 1).CopyFromRecordset rs
CloseConn '关闭连接
End Sub
Sub Test()
Call ViewTop1000Rows("MSreplication_options")
End Sub
这样设计看上去有些繁琐，但是当做比较大型的项目时这样处理好处很多。第一方便配置数据库，第二常用的数据库操作功能封装起来方便调用。比如查询数据库要经历三个步骤：1，打开连接（OpenSql） 2，执行查询语句（rs.Open Strsql, Conn ）3，关闭连接（CloseConn）
3、用SqlCel连接Sql Server数据库
SqlCel连接数据库非常方便。SqlCel不仅可以连接Sql Server数据库，同时也可以连接Mysql、Oracle和文件夹。点击数据库连接下的SqlServer会出现以下对话框：
设置好连接信息后点击确定即可。数据库连接上之后点击“查看所有表”即可将数据库中的所有表显示在当前工作簿中，之后点击其中任何一个表都可以对其进行各种操作，如下：
关于SqlCel的更多信息可登录以下网站查阅：
http://sqlcel.com
SqlCel软件的下载可见以下网址：
http://sqlcel.com/download/
|
|
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-12-30 22:43
楼主
