用Excel展示SQL Server中的数据 (II): 宏与自动化

samsungsamsung

　　在上一篇文章中，我介绍了如何将SQL Server中的数据导入Excel。其中，建立数据连接的过程是手工完成的，偶尔为之，可以接受，如果要反复操作，就不胜其烦。本文将介绍如何使用宏来自动化数据导入。
　　在本文中，我使用的是Excel 2007，所介绍的方法同样适用于Excel 2010。为了使用宏，需要在Excel 2007中显示Developer Tab。操作方法是在Excel选项对话框中，选中Show Developer tab in the Ribbon。

　　1. 用宏理解Excel
　　为了自动化数据导入过程，需要知晓Excel在该过程中执行了哪些操作。这可以通过录制宏来捕获。在Developer Tab下，点击Record Macro，将宏命名为import_data_from_sqlserver。


　　按照上一篇文章中的方法建立数据库链接。不同之处在于，在Import Data话框中，点击Properties；在Connection Properties对话框中，将Command type修改为SQL，将Command text修改为select * from test.Stat。然后连续点击OK，完成数据导入。


　　在Developer Tab下点击Stop Recording，结束宏的录制。

　　然后点击Developer Tab的Visual Basic（或按下快捷键Alt+F11），打开的Microsoft Visual Basic视窗。在Project窗口中，双击Modules下的Module1，打开代码编辑窗口。

　　在代码编辑窗口中，可见宏import_data_from_sql_server的实现。
   

     　　Sub import_data_from_sqlserver()         
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _           
        "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhostsqlexpress;Use Procedure for Prepa" _           
        , "re=1;Auto Translate=True;Packet Size=4096;Workstation ID=LIANGSHI;Use Encryption for Data=False;Tag with column collation when " _           
        , "possible=False;Initial Catalog=bug_db"), Destination:=Range("$A$1")). _           
        QueryTable           
        .CommandType = xlCmdSql           
        .CommandText = Array("select * from test.Stat")           
        .RowNumbers = False           
        .FillAdjacentFormulas = False           
        .PreserveFormatting = True           
        .RefreshOnFileOpen = False           
        .BackgroundQuery = True           
        .RefreshStyle = xlInsertDeleteCells           
        .SavePassword = False           
        .SaveData = True           
        .AdjustColumnWidth = True           
        .RefreshPeriod = 0           
        .PreserveColumnInfo = True           
        .SourceConnectionFile = _           
        "C:Usersliangshi.000DocumentsMy Data Sourcesmytest.odc"           
        .ListObject.DisplayName = "Table_mytest"           
        .Refresh BackgroundQuery:=False           
    End With           
End Sub
   

    　　灰色代码在当前数据薄的ListObjects容器中增加了一个对象，利用Source参数指定了OLEDB数据源，并提供了数据库连接字符串，利用Destination参数指定了数据存放于何处。黄色代码设置新增对象的QueryTable属性。比较重要的设置是，设定QueryTable.CommandType为xlCmdSql（SQL查询），设定QueryTable.CommandText为待运行的SQL查询字符串，最后调用QueryTable.Refresh函数以执行查询。
　　红色代码指定了数据库服务器、数据库和SQL查询。只要能修改它们就可以在指定数据库上运行指定查询，以获得我们需要的数据。这就是本文方法的技术基础。只是宏import_data_from_sqlserver有一个缺点：它向固定区域（$A$1）插入对象，因此不能反复运行。为了避免运行时错误，我的策略是：不重复建立数据库连接，而是修改已有连接，从而获得新的查询结果。
　　2. Xlsm文件
　　从这里可以下载Book1.xlsm文件，它包含本文即将介绍的所有元素。该文件以xlsm后缀名，是因为这是一个包含宏的Excel文件。在打开它时，需要授权给宏（Macro）和数据连接（Data Connection）。


　　你如果厌倦了安全警告，可以将你的桌面加入“安全路径”。那么从桌面打开的xlsm文件，将不显示安全警告。


　　3. 界面：表格和按钮
　　Book1.xlsm的布局如下图所示。

   

     

• 左上角是一个表格，其中单元格B2对应数据库服务器，单元格B3对应数据库，单元格B4对应SQL查询，它们的格式都是文本（Text）。这里使用表格是为了美观，普通的单元格也不影响功能。       
  
• 左下角是一个按钮。它的插入方法是：Developer → Insert → Button。右击该按钮，可以进入编辑模式：调整大小、调整位置、修改名称、设定宏等。       
  
• 右下角是一个表格，它是导入的SQL Server的数据。选中该表格中的任意单元格（图中是单元格C5），点击Design Tab下的Properties，弹出External Data Properties对话框，点击Connection Properties按钮可以查看连接的详细属性。该连接是我手工建立的，其名称是my_database_connection，通过SQL查询从数据库中获得数据。    
  

   

　　4. 实现逻辑：宏
　　按下快捷键Alt+F11，打开的Microsoft Visual Basic视窗。在Module1中可见宏RefreshDataConnection。它首先修改数据连接my_database_connection，主要修改内容是用单元格B2、B3和B4的值替换原有的数据库服务器、数据库和SQL查询。然后，它调用数据连接my_database_connection的Refresh函数，以获得新的数据。
   

     　　Sub RefreshDataConnection()         
    With ActiveWorkbook.Connections("my_database_connection").OLEDBConnection           
        .BackgroundQuery = True           
        .CommandText = Array(" " & Range("B4").Value & " ")           
        .CommandType = xlCmdSql           
        .Connection = Array("OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" _           
        , "Initial Catalog=" & Range("B3").Value & ";Data Source=" & Range("B2").Value & ";" _           
        , "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LIANGSHI;Use Encryption for Data=False;Tag with " _           
        , "column collation when possible=False")           
        .RefreshOnFileOpen = False           
        .SavePassword = False           
        .SourceConnectionFile = ""           
        .SourceDataFile = ""           
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated           
        .AlwaysUseConnectionFile = False           
    End With           
    ActiveWorkbook.Connections("my_database_connection").Refresh           
End Sub
   

    　　按钮Refresh对应的宏就是RefreshDataConnection，这是通过编辑按钮的属性来设置的。


　　这样每次点击按钮Refresh就可以获得最新的数据。例如，将查询修改为SELECT * FROM test.Stat，并单击Refresh，可得视图Stat的查询结果。

　　为了方便操作，我还在Sheet1中增加了宏Worksheet_Change，以响应Worksheet_Change事件。当Sheet1的内容发生变更时，Excel会调用宏Worksheet_Change。它会检查被修改对象（Target）的单元格个数（Count）是否是1。如果是1，则进一步检查被修改单元格是否是B4（即Row=4且Column=2），即SQL查询是否被修改。如果SQL查询被修改，则调用宏RefreshDataConnection以导入数据。有了这个宏，修改完SQL查询，不用点击Refresh，Excel就会自动加载新数据，非常方便。

　　值得一提的是，导入的数据表格是可以拷贝到其他数据薄的。被拷贝的内容包含已导入的数据和相关的数据库连接。这意味着，在编写完一个SQL查询后，可以将被修改的数据连接“另存”到另一个数据薄中，以待未来使用，而这一切都可以用拷贝和粘贴来完成。
　　5. 小结
　　本文展示了一个宏的应用实例，并可以得出以下启示。
   

     

• 利用宏可以了解Excel的实现机制，这有利于更好地（手工或自动地）操作Excel。       
  
• 编写宏的一般方法是将手工操作录制为宏，然后对录制的宏进行修改。       
  
• Excel数据薄是一个可编程的GUI：每一个单元格可以看做一个输入框，可以插入按钮等控件以响应用户输入，其内建多种事件可以激发事件处理程序。       
  
• 对于许多任务，你可以用Excel来完成，而不必编写传统意义上的程序。你可以编写ASP.NET网站和Silverlight应用来提供用户界面，那很了不起。但是，你的时间是有限的，而你的客户也不喜欢网页上缓慢的输入。利用Excel的宏录制，你可以在几分钟内完成一个GUI程序，并提供用户最爱的Excel风格的输入。       
  
• 宏对于VSTO的一个优点是，它完全内建于Excel。你可以在秘书和前台的笔记本电脑上进行开发和调试，而那上面绝对不会有Visual Studio。此外，相比二进制的VSTO插件，可以轻松查看并修改的宏，可以满足一些程序员个性化定制的需要。    
  

    　　编程并不局限于C#和Visual Studio。Excel也提供了便捷的编程环境，运用得当，会事半功倍。