Export (0) Print
Expand All
Expand Minimize

Bringing SQL Server 2005 Data to Excel 2007 Using Visual Studio 2005 Tools for the Office System SE

Office 2007

Summary: Learn how to create a Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office system application add-in for Microsoft Office Excel 2007 that can work with data from a Microsoft SQL Server 2005 database. See how to hook up the add-in to the 2007 Microsoft Office Fluent Ribbon and a custom task pane. (23 printed pages)

From an end-user perspective, Microsoft Office Excel 2007 provides enormous power and flexibility when it comes to analyzing and manipulating data. The user can enter this data manually or by importing it from text files. Another popular feature is the ability to use dynamic data features of Excel to load data from a live Microsoft SQL Server database. For example, in Excel 2007, click the Excel 2007 Data tab and then click From Other Sources. A list of five different data sources is displayed from which to import data as shown in Figure 1.

Figure 1. The options on From Other Sources menu

The options on From Other Source menu

Although it is useful for more advanced users, it is often not wise to allow inexperienced users to have direct access to your SQL Server. In addition, because of business rules or possible regulatory requirements, it may not even be legal. In this case, you need to build a custom user interface (UI) that allows users to get the data they need into Excel 2007 and, where applicable, send updates back to the server.

This article shows how to create an application add-in for Excel 2007 that allows you to pull data from the SQL Server 2005 AdventureWorks database and put it into the active Excel workbook. In addition, you can, where appropriate, send updates back to SQL Server 2005. The add-in takes advantage of new application-level task pane support and Microsoft Office Fluent Ribbon support.

To work through the scenarios in this article, you need a copy of Microsoft Visual Studio 2005 that supports Visual Studio 2005 Tools for Office SE. You also need to install Visual Studio 2005 Tools for Office SE, Excel 2007, and SQL Server 2005—any edition works as long as it has a copy of the AdventureWorks sample database installed.

If you do not have the AdventureWorks sample database, you can download it from the SQL Server 2005 Samples and Sample Databases.

NoteNote

Although it is possible to write Microsoft Visual Basic and Microsoft Visual C# in a similar fashion, the code written in this article generally uses built-in language constructs—such as the Visual Basic MsgBox and WithEvents features—instead of the Microsoft .NET Framework alternatives.

The AdventureWorks database has a rich schema with many different objects. This example uses the stored procedure uspUpdateEmployeePersonalInfo to update employee data. However, you need a stored procedure to provide you with a list of existing data to review and modify. To make this easy, you can create the stored procedure manually with the following Transact-SQL (T-SQL) statements.

[T-SQL]
CREATE PROCEDURE HumanResources.GetEmployeePersonalInfo 

AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here.
  SELECT
    e.EmployeeID, c.Title, c.FirstName, c.MiddleName, c.LastName, 
    c.Suffix, e.Title AS JobTitle, c.Phone, c.EmailAddress,
    e.NationalIDNumber, e.MaritalStatus, e.Gender, e.BirthDate
  FROM
    HumanResources.Employee AS e 
INNER JOIN
    Person.Contact AS c 
ON 
    c.ContactID = e.ContactID 
INNER JOIN
    HumanResources.EmployeeAddress AS ea 
ON 
    e.EmployeeID = ea.EmployeeID
END
GO

There are a number of ways to create a stored procedure from T-SQL statements. Links to three of these techniques are provided in Additional Resources.

After you update the AdventureWorks database, you are ready to create an Excel 2007 Add-in project.

In the following procedures, you create the Excel 2007 add-in project.

To create the Excel 2007 project

  1. Start Visual Studio 2005.

    NoteNote

    This article assumes that you installed Visual Studio 2005 and are using the default General Development settings. If you are using the Visual Basic Developer settings, for example, the menu items are slightly different. You may need to adjust the instructions here for your specific environment.

  2. On the File menu, click New Project.

  3. In the New Project dialog box, in the Project types pane, click Visual Basic.

  4. Expand the Office node, and then select the 2007 Add-ins node.

  5. In the Templates pane of the dialog box, click the Excel Add-in template.

  6. In the Name field, type ExcelData, and then select a location to save the project.

  7. In the Solution Name field, type VSTOSEData. Ensure that there is a check mark next to Create directory for solution, and then click OK.

    Visual Studio 2005 creates the solution with an Excel 2007 application add-in project and a Windows Installer Setup project.

  8. On the Window menu, click Close All Documents.

At this point, the add-in does not do much. In the following sections, you add the UI, the data access logic, and connect everything to Excel 2007. The first thing you need to do is create an application-level task pane. The task pane gathers configuration data from the user to connect to the database.

Application task panes are a great way to gather information from the user without the need to produce a modal dialog box. The first step you need to take is to build the UI using a Windows Forms User Control. After you create the user control, you only need to add a few lines of code to make it available to Excel 2007.

To add a custom task pane

  1. In Visual Studio 2005, in Solution Explorer, right-click the ExcelData node, point to Add, and then click User Control.

  2. In the Add New Item dialog box, in the Name field, type Configuration, and then click Add.

    Visual Studio 2005 sets the necessary references to the assemblies your solution needs and opens the user control’s designer.

    The database to use for this solution is the SQL Server 2005 AdventureWorks sample database. The AdventureWorks database contains data for Adventure Works Cycles, a fictitious large, multinational manufacturing company. The user control provides a place for the user to set configuration options for the add-in including the name of the SQL Server 2005 instance to use.

  3. From the Toolbox in the Common Components tab, drag a Label control, a Textbox control, and a Button control onto the design surface, and position the controls as shown in Figure 2. If you do not see the Toolbox, click the View menu, and then click Toolbox.

    Figure 2. Configuration user control with added controls

    Configuration user control with added controls
  4. Set the Text property of the Label control to SQL Server Instance.

  5. Set the Name property of the Textbox control to txtSQLServer.

  6. Set the Name property of the Button control to btnSave, and set its Text property to Save.

  7. Additionally, set the Enabled property of the Button control to False.

  8. On the File menu, click Save All.

  9. In Solution Explorer, right-click the ExcelData node, and then click Properties.

  10. In the Properties window, click the Settings tab.

  11. In the Settings grid, change the Name column to SQLServerInstance.

  12. Change the Value column to Unknown.

  13. On the File menu, click Save All to save your changes.

  14. On the File menu, click Close to close the Properties window.

  15. In the user control designer, select the text box.

  16. In the Properties pane, select the Events icon.

    Figure 3. Events icon in the Properties pane

    Events icon in the Properties pane
  17. Select the Click event, and then double-click it to generate an event handler and open the code editor.

  18. Add the following code to the body of the Click event handler.

    If Me.txtSQLServer.Text IsNot Nothing AndAlso _
       Me.txtSQLServer.Text.Length > 0 Then
    
       My.Settings.SQLServerInstance = Me.txtSQLServer.Text
    End If
    
    
  19. Below the Public Class Configuration statement, add the following class field.

    Private loading As Boolean = True
    
    
  20. Select the txtSQLServer text box.

  21. In the Properties pane, double-click the TextChanged event, and add the following code to the body of the event.

    If loading Then Exit Sub
    ' Enable the command button if there is text in the control.
    Me.btnSave.Enabled = (Me.txtSQLServer.Text IsNot Nothing _
      AndAlso Me.txtSQLServer.Text.Length > 0)
    
    
  22. Next, in the Properties pane for the Configuration user control, double-click Load to add an event handler, and add the following code to the body of the event handler.

    If Not My.Settings.SQLServerInstance = "Unknown" Then
      Me.txtSQLServer.Text = My.Settings.SQLServerInstance
    End If
    loading = False
    
    
  23. On the File menu, click Save All to save your changes.

  24. On the Window menu, click Close All Documents.

    With the user control created and code added, you need to have the add-in register the control so that the control is displayed in an application task pane.

  25. In Solution Explorer, right-click the ThisAddin.vb node, and then click View Code.

  26. On the View menu, click Code to display the code window.

  27. Modify the ThisAddIn_Startup event handler to look like the following.

    Private Sub ThisAddIn_Startup( _
      ByVal sender As Object, ByVal e As System.EventArgs) _
      Handles Me.Startup
    
      ' Start of Visual Studio 2005 Tools for Office SE-generated code
      Me.Application = CType( _
        Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap( _
        GetType(Excel.Application), Me.Application), _
        Excel.Application)
      ' End of Visual Studio 2005 Tools for Office SE-generated code
      Dim ctl As New Configuration
      Dim tp As Microsoft.Office.Tools.CustomTaskPane
      tp = Me.CustomTaskPanes.Add(ctl, "Configure")
      tp.DockPosition = _
      Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight
      tp.Visible = True
    End Sub
    
    
  28. On the File menu, click Save All to save your work.

  29. On the Build menu, click Build Solution and ensure the project compiles with no errors.

  30. On the Debug menu, click Start Debugging to test the solution. Excel is started.

  31. Verify that the task pane is displayed in Excel, and then close Excel and return to Visual Studio 2005.

  32. On the Window menu, click Close All Documents.

As you saw when you tested the add-in, the task pane is displayed when Excel starts. However, you also need to provide a way to turn the task pane on and off so that it is available only as needed, such as when the user needs to set the SQL Server 2005 instance name. The 2007 Microsoft Office Fluent Ribbon allows you to add UI controls to perform this and other tasks. The following procedures add a toggle button to display and hide the task pane and buttons for Load, Refresh, and Save operations.

To customize the Office Fluent Ribbon

  1. Right-click the ExcelData node, point to Add, and then click New Item.

  2. In the Add New Item dialog box, click Ribbon Support, and then click Add.

    Visual Studio adds two files to the project: Ribbon1.xml contains XML markup to modify the Office Fluent Ribbon and Ribbon1.vb contains the code that adds functionality to the customized Ribbon.

  3. Double-click Ribbon1.xml to open the code window.

  4. Replace the existing XML content with the following.

    <customUI 
      xmlns="http://schemas.microsoft.com/office/2006/01/customui" 
      onLoad="OnLoad">
      <ribbon>
        <tabs>
          <tab id="AdventureWorks" label="Adventure Works">
            <group id="Data"
                   label="Data">
              <button id="btnLoad" label="Load" 
                      screentip="Load Employee Data" size="large" 
                      imageMso="GetExternalDataFromOtherSources" 
                      onAction="OnLoadData" enabled="false" />
              <button id="btnRefresh" label="Refresh" 
                      screentip="Refresh Employee Data" size="large" 
                      imageMso="DataRefreshAll"
                      onAction="OnRefresh" enabled="false" />
              <button id="btnSave" label="Save" 
                      screentip="Save Employee Data" size="large" 
                      imageMso="DatabaseSqlServer" 
                      onAction="OnSave" enabled="false" />
            </group>
            <group id="Configuration"
                   label="Configuration">
              <toggleButton id="tbTaskPane" label="Configure Task Pane"
                screentip="Configure the Adventure Works Data Add-in."
                size="large"
                imageMso="ServerProperties"
                onAction="OnToggleTaskPane" />
            </group>
          </tab>
        </tabs>
      </ribbon>
    </customUI>
    
    

    This markup adds a new tab titled Adventure Works to the Office Fluent Ribbon. The new tab contains two groups, titled Data and Configuration, containing three buttons and a single toggle button, respectively. These buttons trigger callback procedures, which is the way that the Office Fluent Ribbon communicates with your code.

  5. On the Window menu, click Close All Documents. Click Yes in the save confirmation dialog box.

  6. In Solution Explorer, right-click the Ribbon1.xml node, and click Exclude from Project.

  7. In Solution Explorer, right-click the ExcelData node, and then click Properties.

  8. In Project Designer, select the Resources tab.

  9. Click the arrow beside the Add Resource button, and click Add Existing File as shown in Figure 4.

    Figure 4. Adding a resource file to the project

    Adding a resource file to the project
  10. In the Add existing file to resources dialog box, navigate to the folder that contains the ExcelData add-in project file. Select Ribbon1.xml, and then click Open.

  11. On the File menu, click Save All to save your changes.

  12. On the File menu, click Close to close the Properties window.

  13. Double-click the Ribbon1.vb node and replace the entire contents of the file with the following code.

    Imports System
    Imports System.Collections.Generic
    Imports System.Diagnostics
    Imports System.IO
    Imports System.Text
    Imports System.Reflection
    Imports System.Runtime.InteropServices
    Imports System.Windows.Forms
    Imports Office = Microsoft.Office.Core
    
    Partial Public Class ThisAddIn
      Private ribbon As Ribbon1
    
      Protected Overrides Function RequestService( _
        ByVal serviceGuid As Guid) As Object
    
        If serviceGuid = _
          GetType(Office.IRibbonExtensibility).GUID Then
    
          If ribbon Is Nothing Then
            ribbon = New Ribbon1()
          End If
    
          Return ribbon
        End If
    
        Return MyBase.RequestService(serviceGuid)
      End Function
    End Class
    
    <ComVisible(True)> _
      Public Class Ribbon1
      Implements Office.IRibbonExtensibility
    
      Private ribbon As Office.IRibbonUI
    
      Public Sub New()
      End Sub
    
      Public Function GetCustomUI( _
        ByVal ribbonID As String) As String _
        Implements Office.IRibbonExtensibility.GetCustomUI
    
        Return My.Resources.Ribbon1
      End Function
    
    #Region "Ribbon Callbacks"
    
      Public Sub OnLoad(ByVal ribbonUI As Office.IRibbonUI)
        Me.ribbon = ribbonUI
      End Sub
    
      Public Sub OnToggleTaskPane( _
        ByVal control As Office.IRibbonControl, _
        ByVal isPressed As Boolean)
    
      End Sub
    
    #End Region
    End Class
    
    

    This code removes some sample code and comments from the original code. In addition, it replaces the code to load the custom XML with simpler code that uses the new resource management features available to applications based on the Microsoft .NET Framework 2.0.

  14. Double-click the ThisAddin.vb node to open the code window.

  15. Cut the following lines of code from the ThisAddIn_Startup event procedure, and make them class fields instead by pasting the lines after the public class ThisAddIn statement.

    Dim ctl As New Configuration
    Dim tp As Microsoft.Office.Tools.CustomTaskPane
    
    
  16. Change the Dim commands to Private.

  17. On the File menu, click Save All to save your changes.

  18. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

  19. In the Ribbon1.vb code window, add the following class field for the Ribbon1 class.

    Public TaskPane As Microsoft.Office.Tools.CustomTaskPane
    
    
  20. In the callback procedure OnToggleTaskPane, add the following code.

    If TaskPane IsNot Nothing Then
      TaskPane.Visible = isPressed
    End If
    
    
  21. In the ThisAddin.vb code window, replace the last line of code from the ThisAddIn_Startup event handler as follows.

    Replace:

    tp.Visible = True
    
    

    With:

    Me.ribbon.TaskPane = tp
    
    

    The line assigns the task pane reference to the Ribbon class TaskPane property.

  22. On the File menu, click Save All to save your work.

  23. On the Build menu, click Build Solution and ensure the project compiles with no errors.

  24. On the Debug menu, click Start Debugging to test the solution.

  25. In Excel, select the new Adventure Works tab to see the new UI as shown in Figure 5.

    Figure 5. The Adventure Works tab

    The Adventure Works tab
  26. Click Configure Task Pane to display the task pane. Click the toggle button again to close the task pane.

  27. Exit Excel, and return to Visual Studio 2005.

  28. On the Window menu, click Close All Documents.

At this point in the project, only the toggle button is enabled and working. The other three buttons on the Adventure Works tab are disabled by default. Next, you want to ensure that these buttons are only enabled in the proper context. For example, you want the Load button enabled as long as the SQL Server 2005 instance name is set. The Refresh button should only be enabled if data is loaded and the sheet with the data is the current active sheet. Finally, you want the Save button enabled if the user changes the data and the sheet with the data is the active sheet.

Unlike CommandBar buttons used in earlier versions of Microsoft Office, you cannot change the button’s enabled state directly. Instead, the Office Fluent Ribbon must call your code to check the state of the button. In this next section, you add the code to enable the Load button. You also add code that enables the other buttons after the data is loaded. Finally, you add code to set the state of the toggle button if the user closes the task pane by using the task pane’s Close button (the X in the upper-right corner of the pane).

To set the state of the toggle button

  1. In Solution Explorer, double-click the Ribbon1.vb node to open the code window.

  2. In the Ribbon Callbacks region, add the following method.

    Public Function TaskPane_getPressed( _
       ByVal control As Office.IRibbonControl) As Boolean
    
       Return Me.TaskPane.Visible
    End Function
    
    

    To ensure that the Office Fluent Ribbon calls this procedure, you must bind the callback procedure to the toggle button and add code that indicates to the Office Fluent Ribbon to check the state of the button. First, you modify the XML for the toggle button. Second, you provide an event handler for the VisibleChanged event of the task pane. In this event handler, the code invalidates the toggle button, which causes the Office Fluent Ribbon to call the callback procedure you just added.

  3. Double-click the Ribbon1.xml node, and add the following attribute to the tbTaskPane toggle button.

    getPressed ="TaskPane_getPressed"
    
    
  4. Next, add the following method to the Ribbon1 class in the Ribbon1.vb node.

    Public Sub Refresh(ByVal controlID As String)
       Me.ribbon.InvalidateControl(controlID)
    End Sub
    
    
  5. Next, add the following event handler to the ThisAddIn class.

    Public Sub OnVisibleChanged( _
     ByVal sender As Object, ByVal e As EventArgs)
    
       Me.ribbon.Refresh("tbTaskPane")
    End Sub
    
    
  6. Finally, add the following line of code as the last line in the ThisAddIn_Startup event handler.

    AddHandler tp.VisibleChanged, AddressOf Me.OnVisibleChanged
    
    
  7. On the File menu, click Save All to save your work.

  8. On the Build menu, click Build Solution and ensure the project compiles with no errors.

  9. On the Debug menu, click Start Debugging to test the solution. Excel starts and a workbook is displayed.

  10. In Excel, click the Adventure Works tab.

  11. Click the Configure Task Pane toggle button to display the task pane.

  12. Close the task pane by using the X button in the upper-right corner of the pane. Notice how the toggle button’s state changes.

  13. Close Excel, and return to Visual Studio 2005.

To control the enabled state of the Load button, you must make modifications to the add-in similar to those in the previous procedures. In addition, you must modify the user control to communicate whether the state of the add-in configuration is valid; in other words, whether or not a server instance name is set.

To validate the Load button

  1. Double-click the Ribbon1.xml node and replace the enabled attribute of the btnLoad button with the following attribute.

    getEnabled="btnLoad_getEnabled"
    
    
  2. Next, add the following class field to the Ribbon1 class in the Ribbon1.vb file.

    Public configOK As Boolean = False
    
    
  3. Then add the following callback procedure to the Ribbon1 class.

    Public Function btnLoad_getEnabled( _
       ByVal control As Office.IRibbonControl) As Boolean
    
       Return configOK
    End Function
    
    
  4. On the File menu, click Save All to save your work.

  5. In Solution Explorer, right-click the Configuration.vb node, and then click View Code to open the code window.

  6. Add the following class-level event declaration to the Configuration class.

    Public Event ConfigChanged As System.EventHandler
    
    
  7. Add the following btnSave_Click event handler to trigger the ConfigChanged event when the SQLServerInstance setting is changed. The modified procedure should appear as follows.

    Private Sub btnSave_Click( _
      ByVal sender As System.Object, ByVal e As System.EventArgs) _
      Handles btnSave.Click
    
       If Me.txtSQLServer.Text IsNot Nothing AndAlso _
          Me.txtSQLServer.Text.Length > 0 Then
    
          My.Settings.SQLServerInstance = Me.txtSQLServer.Text
          RaiseEvent ConfigChanged(Me, EventArgs.Empty)
       End If
    End Sub
    
    
  8. Next, add the following event handler to the ThisAddIn class in the ThisAddIn.vb file.

    Public Sub OnConfigChanged( _
     ByVal sender As Object, ByVal e As EventArgs)
    
       Me.ribbon.configOK = True
       Me.ribbon.Refresh("btnLoad")
    End Sub
    
    
  9. Add the following lines of code at the end of the existing ThisAddIn_Startup event handler.

    AddHandler ctl.ConfigChanged, AddressOf Me.OnConfigChanged
    If Not My.Settings.SQLServerInstance = "Unknown" Then
      Me.ribbon.configOK = True
      Me.ribbon.Refresh("btnLoad")
    End If
    
    
  10. On the File menu, click Save All to save your work.

  11. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

  12. On the Debug menu, click Start Debugging to test the solution. Excel starts.

  13. In Excel, select the Adventure Works tab.

  14. Click Configure Task Pane to display the task pane.

  15. In the SQL Server Instance text box, type a valid server name, and then click Save. Notice that this enables Load.

  16. Exit Excel, and return to Visual Studio 2005.

  17. On the Window menu, click Close All Documents.

When the user clicks the Load button, you want the add-in to access the SQL Server 2005 instance specified by the user, execute the custom stored procedure GetEmployeePersonalInfo, gather the resulting data into a dataset specified in the System.Data.DataSet namespace, and load the data into a table in the current worksheet. The code in the following procedure does just that.

To configure the Load button

  1. In Solution Explorer, double-click the ThisAddIn.vb node to open the code window.

  2. Add the following code to the ThisAddIn class.

    ' Put in the class declarations section.
    Private employeeData As DataSet
    Private employeeWS As Excel.Worksheet
    Private rngList As Excel.Range
    
    Friend Sub LoadData()
       Try
         Me.Application.ScreenUpdating = False
    
         Dim connectionString As String = String.Format( _
           "Server={0};Database=AdventureWorks;" & _
           "Trusted_Connection=True;", My.Settings.SQLServerInstance)
    
         Dim sp As String = "HumanResources.GetEmployeePersonalInfo"
         Using con As New SqlConnection(connectionString)
           Using cmd As New SqlCommand(sp, con)
             cmd.CommandType = CommandType.StoredProcedure
             Dim sda As New SqlDataAdapter(cmd)
            employeeData = New DataSet
             sda.Fill(employeeData)
    
             Dim dt As DataTable = employeeData.Tables(0)
    
             Dim endPoint As String = Me.Application.ConvertFormula( _
               "R" & dt.Rows.Count + 1 & "C" & dt.Columns.Count, _
               Excel.XlReferenceStyle.xlR1C1, _
               Excel.XlReferenceStyle.xlA1, _
               Excel.XlReferenceType.xlRelative)
    
             Dim strRange As String = "A1:" & endPoint
    
             employeeWS = DirectCast(Me.Application.Sheets.Add(), _
               Excel.Worksheet)
             employeeWS.Name = "Employees"
    
             rngList = _ 
               DirectCast(employeeWS.Range(strRange), Excel.Range)
             rngList.Value2 = Me.ConvertDataTableToArray(dt)
    
             Dim lo As Excel.ListObject
             lo = employeeWS.ListObjects.Add( _
               Excel.XlListObjectSourceType.xlSrcRange, _
               rngList, , Excel.XlYesNoGuess.xlYes)
             lo.Name = "Employees"
    
             rngList.Columns.AutoFit()
    
             employeeWS.Activate()
    
            ' AddHandler employeeWS.Change, _
            '  AddressOf Me.employeeWS_Change
    
           End Using
         End Using
       Catch ex As Exception
         MsgBox("An unexpected error occured." & vbCrLf & _
           ex.Message, MsgBoxStyle.Exclamation, ex.Source)
       Finally
         Me.Application.ScreenUpdating = True
       End Try
    End Sub
    
    
    Private Function ConvertDataTableToArray( _
       ByVal dt As DataTable) As Object(,)
    
       Dim c As Integer = 0
       Dim data(dt.Rows.Count, dt.Columns.Count - 1) As Object
    
       For Each col As DataColumn In dt.Columns
         data(0, c) = col.ColumnName
         c += 1
       Next
    
       Dim dr As DataRow
       For i As Integer = 0 To dt.Rows.Count - 1
         dr = dt.Rows(i)
         For j As Integer = 0 To dt.Columns.Count - 1
           data(i + 1, j) = dr(j)
         Next j
       Next i
    
       Return data
    End Function
    
    

    This code connects to the AdventureWorks database on the server specified by the user. It executes the GetEmployeePersonalInfo stored procedures and loads the data into a dataset. After loading, the code creates an Excel worksheet and an Excel Range that contains all the data, including an extra row for header data. It then uses a helper method, the ConvertDataTableToArray method, to repackage the data into a matrix array of Objects. The code puts the array into the Range and then wraps the Range in a ListObject object, which creates a Table object that Excel formats and adds the Auto Filter column headers. Finally, the code auto sizes the columns to fit the data and activates the new worksheet.

  3. Next, connect the LoadData method to a Click event handler for the Load button.

  4. In Solution Explorer, double-click the Ribbon1.vb node.

    In the code window, add the following class field for the Ribbon1 class.

    Public Parent As ExcelData.ThisAddIn
    
    
  5. Add the following callback procedure to the Ribbon1 class.

    Public Sub OnLoadData(ByVal control As Office.IRibbonControl)
       Parent.LoadData()
    End Sub
    
    
  6. Add the following line of code at the end of the ThisAddIn_Startup event handler.

    Me.ribbon.Parent = Me
    
    
  7. Add the following namespace reference above the Public Class ThisAddIn statement.

    Imports System.Data.SqlClient
    
    
  8. On the File menu, click Save All to save your work.

  9. On the Build menu, click Build Solution and ensure that the project compiles with no errors.

  10. On the Debug menu, click Start Debugging to test the solution.

  11. In Excel, select the Adventure Works tab.

  12. Click Configure Task Pane to deploy that task pane.

  13. Type the name of your server in the SQL Server Instance text box on the Task Pane.

  14. After the Load button is enabled, click it. The result should appear as shown in Figure 6.

    Figure 6. The Employees data is loaded from the custom add-in

    Employee data is loaded from the custom add-in
  15. Exit Excel without saving the workbook, and return to Visual Studio 2005.

  16. In Visual Studio 2005, on the Window menu, click Close All Documents.

To save changes back to the database, you must tell the add-in what data the user changed. In the following procedures, you add an event handler that captures the Worksheet_Changed event. The event handler causes the add-in to update the in-memory dataset with changes as they occur. After the first changes occur, the add-in enables the Save button on the Office Fluent Ribbon. Finally, when the user clicks Save, the add-in posts the changes back to SQL Server by using the existing uspUpdateEmployeePersonalInfo stored procedure.

To save changes to the database

  1. First, double-click the Ribbon1.xml node, and replace the enabled attribute for the btnSave button with the following attribute.

    getEnabled="btnSave_getEnabled"
    
    
  2. In Solution Explorer, double-click the Ribbon1.vb node to open the code window.

  3. Add the following code class field in the Ribbon1 class.

    Public dataChanged As Boolean = False
    
    
  4. Add the following callback procedure to the Ribbon1 class.

    Public Function btnSave_getEnabled( _
     ByVal control As Office.IRibbonControl) As Boolean
    
       Return dataChanged
    End Function
    
    
  5. Double-click the ThisAddin.vb node to open the code window and add the following code. This code handles the worksheet Change event and saves the data back to the server.

    Private Sub employeeWS_Change( _
      ByVal Target As Microsoft.Office.Interop.Excel.Range)
    
       If Me.Application.Intersect(rngList, Target) IsNot Nothing Then
         If Target.Row > 1 Then
           Dim intRow As Integer = Target.Row - 2
           Dim intColumn As Integer = Target.Column - 1
    
           Dim dt As DataTable = employeeData.Tables(0)
           dt.Rows(intRow)(intColumn) = Target.Value
           Me.ribbon.dataChanged = True
           Me.ribbon.Refresh("btnSave")
         End If
       End If
    
    End Sub
    
    Friend Sub SaveData()
       Try
         Dim dv As New DataView(employeeData.Tables(0))
         dv.RowStateFilter = DataViewRowState.ModifiedCurrent
         Dim connectionString As String = String.Format( _
           "Server={0};Database=AdventureWorks;" & _
           "Trusted_Connection=True;", My.Settings.SQLServerInstance)
    
         Dim sp As String = "HumanResources.uspUpdateEmployeePersonalInfo"
         
         Using con As New SqlConnection(connectionString)
           Using cmd As New SqlCommand(sp, con)
             cmd.CommandType = CommandType.StoredProcedure
    
             cmd.Parameters.Add(BuildPrm("RETURN_VALUE", _
               SqlDbType.Int, DbType.Int32, _
               ParameterDirection.ReturnValue, 4))
    
             cmd.Parameters.Add(BuildPrm("@EmployeeID", _
               SqlDbType.Int, DbType.Int32, _
               ParameterDirection.Input, 4))
    
             cmd.Parameters.Add(BuildPrm("@NationalIDNumber", _
               SqlDbType.NVarChar, DbType.String, _
               ParameterDirection.Input, 15))
    
             cmd.Parameters.Add(BuildPrm("@BirthDate", _
               SqlDbType.DateTime, DbType.Date, _
               ParameterDirection.Input, 8))
    
             cmd.Parameters.Add(BuildPrm("@MaritalStatus", _
               SqlDbType.NChar, DbType.String, _
               ParameterDirection.Input, 1))
    
             cmd.Parameters.Add(BuildPrm("@Gender", _
               SqlDbType.NChar, DbType.String, _
               ParameterDirection.Input, 1))
    
             con.Open()
    
             For Each dvr As DataRowView In dv
    
               cmd.Parameters.Item(1).Value = dvr(0)
               cmd.Parameters.Item(2).Value = dvr(9)
               cmd.Parameters.Item(3).Value = dvr(12)
               cmd.Parameters.Item(4).Value = dvr(10)
               cmd.Parameters.Item(5).Value = dvr(11)
    
               cmd.ExecuteNonQuery()
             Next
           End Using
         End Using
    
         MsgBox("Changes were saved to the server.", _
           MsgBoxStyle.Information, "Save")
    
         Me.ribbon.dataChanged = False
         Me.ribbon.Refresh("btnSave")
       Catch ex As Exception
         MsgBox("An unexpected error occured." & vbCrLf & _
                 ex.Message, MsgBoxStyle.Exclamation, ex.Source)
       End Try
    End Sub
    
    Private Function BuildPrm(ByVal Name As String, _
      ByVal SType As SqlDbType, ByVal Type As DbType, _
      ByVal Direction As ParameterDirection, ByVal Size As Integer) _
      As SqlParameter
    
       Dim prm As New SqlParameter(Name, SType)
       With prm
         .DbType = Type
         .Direction = Direction
         .Size = Size
       End With
    
       Return prm
    End Function
    
    
  6. In the existing LoadData procedure, find the following two lines of commented code. Uncomment each statement by removing the apostrophe.

    ' AddHandler employeeWS.Change, _
    '  AddressOf Me.employeeWS_Change
    
    
  7. In the Ribbon1.vb code window, add the following code to the Ribbon1 class.

    Public Sub OnSave(ByVal control As Office.IRibbonControl)
       Parent.SaveData()
    End Sub
    
    
  8. On the File menu, click Save All to save your work.

  9. On the Build menu, click Build Solution and ensure the project compiles with no errors.

  10. On the Debug menu, click Start Debugging to test the solution.

  11. Click the Adventure Works tab, and then click Configure Task Pane.

  12. Type the name of your server in the text box, and click Save on the task pane.

  13. On the tab, click Load to load the data into the worksheet.

  14. Modify any of the five columns listed in the SaveData method (NationalIDNumber, BirthDate, MaritalStatus, or Gender, but excluding the EmployeeID column), and then click Save on the tab to save the change back to the database.

  15. You can verify the change by inspecting the HumanResources.Employee table in the AdventureWorks database in the SQL Server Management Studio.

  16. When you finish testing the project, exit Excel and Visual Studio 2005.

Visual Studio 2005 Tools for Office SE and Excel 2007 provide a rich development platform for building data-enabled solutions. With just a little bit of code and the Office Fluent Ribbon, it is easy to enable your users to use the rich analysis features and effective UI of Excel 2007 with a SQL Server 2005 database and stored procedures.

Brian A. Randell is a senior consultant with MCW Technologies, LLC. Brian spends his time teaching technologies based on the Microsoft .NET Framework to developers and exploring new and emerging technologies. He also helps clients worldwide with system design and implementation. Brian is a Microsoft MVP. You can reach him from his blog.

Show:
© 2014 Microsoft