March 3, 2021
Hot Topics:

Visual Studio Tools for Office 2007: How the Ribbon Helps Make Your Document a Dynamic Application

  • By M. Rajesh
  • Send Email »
  • More Articles »

Set the properties for the drop-down list box and the button as follows:

  • Control: Drop-Down List Box
  • Name: Custddlb
  • Label: Customer Name
  • Control: Button
  • Name: button1
  • Label: Show Details

When you are done with this, your screen should look like Figure 7.

Click here for a larger image.

Figure 7: The layout after renaming the controls and adding the caption.

Writing Code to Make the Document Work

Now, it is time to write code for the load event of the application (when the Excel document opens and for the clicked event of the button, after the user selects a name from the drop-down list box and clicks the "Show Details" button).

The logic for this function is similar to what you are already familiar with in Windows Forms and ADO.NET 2.0. The steps are summarized below.

  1. Open a connection to the SQL Server database.
  2. Create a DataReader and use a query to bring the names of the customers from the table Customers.
  3. Fill the DataReader with the data brought by this query.
  4. Populate the drop-down list box with the DataReader.
  5. When the user clicks "Show Details" the button, create another DataReader. This time, use this to pull data from a combination of two tables so that the matching orders are displayed for that particular customer.
  6. Display the data on the Excel sheet.

Now, you will have to do the following actions, in order, to get the desired output. To begin with, you will update the "ThisAddIn.cs" file that was created earlier.

In the "ThisAddin.cs" file, enter the following code at the bottom of the page so that you can instantiate the worksheet.

public Excel.Worksheet GetActiveWorksheet()
      return (Excel.Worksheet)Application.ActiveSheet;

The preceding code is necessary for the application to instantiate a new instance of the worksheet every time the document is opened. Normally, when you open a document, it opens with the first sheet; this is the active sheet.

Now, you will write the remaining code in the Ribbon Designer "RCustomerLookup.cs" file.

Add the following references at the top of the page.

using Excel  = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

Add this code at the beginning of the class. This will be used throughout the application; therefore, it needs to be defined at the global event. Ensure that it points to the AdventureWorks Database in SQL Server 2005 and you have permission to read from this database.

SqlConnection sqlconn =
   new SqlConnection("Integrated Security=SSPI;
                      Persist Security Info=False;
                      Initial Catalog=AdventureWorks;
                      Data Source=YourComputerName\\sqlexpress;

Add the following code to the Ribbon Load Event. This method can be found by double-clicking the Ribbon Designer. This is similar to the Form Load Event, except that the Ribbon is the Form in this add-in.

private void RCustomerLookup_Load(object sender,
   RibbonUIEventArgs e)
   //we load the data from the database.

Here, you declare a function to pull the Customer names from the AdventureWorks database and then populate the drop-down list box. The code for this GetCustomerNames() function is defined below:

private void GetCustomerNames()
      //we connect to the database
      SqlCommand sqlcmdEmp  = new SqlCommand();
      sqlcmdEmp.CommandText = "select contactid, firstname + ' ' +
                               middlename + ' ' +
                               lastname from person.contact
                               WHERE firstname + ' ' +
                               middlename + ' ' +
                               lastname is not null and firstname +
                               ' ' + middlename + ' ' +
                               lastname like 'And%'";
      sqlcmdEmp.Connection  = sqlconn;

      SqlDataReader EmpReader = sqlcmdEmp.ExecuteReader();

      //Set up the data binding.
      RibbonDropDownItem item = new RibbonDropDownItem();
      item.Label = "- Customer Name -";
      item.Tag   = String.Empty;

      while (EmpReader.Read())
         item       = new RibbonDropDownItem();
         item.Label = EmpReader.GetString(1);
         item.Tag   = EmpReader.GetSqlInt32(0).ToString();

Page 3 of 5

This article was originally published on July 11, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date