Ever wondered what the best way to generate reports is, without needing to write additional programs? Unleash the potential of Microsoft Office 2007 to generate and display information from a variety of database sources right in the user's own copy of Office.
In my last article, "Using Visual Studio Tools for Office in Visual Studio 2008," you had a look at the new features of Visual Studio Tools for Office (VSTO) that are shipped along with Visual Studio 2008. You also read a brief discussion on the previous versions of VSTO and went through a walkthrough with the new version to create an "Actions Pane." With this article, you will see a brand new feature of VSTO 2008 that allows the developer to integrate his components directly with the Ribbon interface. You will see what the Ribbon interface is all about and why it is new and special to Office 2007.
For years, people have been used to the two custom toolbars located at the top of Word, Excel, Access, and PowerPoint. Outlook is the only application in Office 2007 that does not have the Ribbon interface. In previous releases of Microsoft Office applications, people used a system of menus, toolbars, task panes, and dialog boxes to get their work done. This system worked well when the applications had a limited number of commands. Now that the programs do so much more, the menus and toolbars system does not work as well. Too many program features are too hard for many users to find.
Microsoft adds new features to each release of Office and makes it more sophisticated and complex than the previous version. With the drawback as explained above, Microsoft had to rethink its strategy when working on this version of Office. So, it completely revamped the entire look and functionality of Office (which was released in late 2006 and named Office 2007). It brought the concept of the Ribbon in Office 2007.
In the Office Fluent UI, the traditional menus and toolbars have been replaced by the Ribbon—a device that presents commands organized into a set of tabs. The tabs on the Ribbon display the commands that are most relevant for each of the task areas in the applications. For example, in Office Word 2007, the tabs group commands for activities such as inserting objects like pictures and tables, doing page layout, working with references, doing mailings, and reviewing. The Home tab provides easy access to the most frequently used commands. Office Excel 2007 has a similar set of tabs that make sense for spreadsheet work, including tabs for working with formulas, managing data, and reviewing. These tabs simplify accessing application features because they organize the commands in a way that corresponds directly to the tasks people perform in these applications. At the time of publishing Office 2007, Microsoft included some enhancements in VSTO and named it VSTO 2003 SE. This included some functionality to enable the programmer to add his own functionality to the Ribbon Interface. But, this wasn't easy for the average developer; he needed some special skills to get a firm understanding of the whole picture.
Revamped Features of VSTO in Visual Studio 2008
With the release of Visual Studio 2008, Microsoft revamped the features in VSTO and released a new version VSTO 3; it is an integral part of Visual Studio 2008 and gives developers a new powerful tool to develop applications directly for the Ribbon interface with the simple ability of dragging and dropping user controls.
Here, you will walk through an example of developing your custom control and displaying it on the Ribbon Interface. This will normally appear in the last tab of the application, namely Add-in. Before you start coding, see why your application will end up in the Add-in Tab. When Microsoft was working on the design of Office 2007, it didn't want to break legacy applications and custom add-ins written for previous version of Office. So, it made a rule that all custom add-ins and custom controls would appear on the last tab of every Office application and that would have its own house—the Add-in Tab. So, when you have Office 2007 open and want to access a specific function written specially for you, you need to look no where except for the Add-in's tab. Isn't that cool enough?
What you will need for this walkthrough: Office 2007 and Visual Studio 2008 (minimum will be the Professional Edition and higher). Make sure that you install all the features, including Visual Studio Tools for Office 3.0.
When you finish your application, it will look like Figure 1. The document loads with the customer names from the "AdventureWorks" database.
Figure 1: The toolbar with your custom application inside the Ribbon.
When you select a particular customer's name from the drop-down list box and click the Show Details button located right below the drop-down list box, the details of purchase made by that customer will appear in the Excel worksheet. The final layout will look like the content of Figure 2.
Figure 2: The desired output after selecting a customer's name and clicking the button.
This brings back memories of what you used to develop earlier with Windows Forms or Web Forms. The user opens up a Windows application, selects a particular combination of data from the toolbar, and views the data in a grid. With the release of VSTO 3, developers can build that feature right into Office 2007 and simplify things when creating custom reports for business organizations. The organization wouldn't need sophisticated network servers or web applications to host their business applications.
Now that you have seen what your custom add-in will look like, it is time to get down to business and write code for this. Fire up Visual Studio 2008 and choose New Project. Select Office 2007 from the Projects tab, and Excel 2007 Add-in from the Templates tab. See Figure 3. Make sure that the .NET Framework 3.5 is selected from drop-down list box at the top. This is one of the new features in Visual Studio 2008 that allows you to target different versions of the .NET Framework.
Figure 3: Select the Excel Add-in from the dialog box.
Now, you need to include the Ribbon Designer to add your functionality. Right-click the project in Solution Explorer and choose New Item. Select Ribbon (Visual Designer) from the pop-up window, as shown in Figure 4.
Figure 4: Select the Ribbon (Visual Designer) from the dialog box.
Give it a name and press Enter. This will open the Ribbon Interface in Design view where you can add your custom controls and so forth, and write code to generate the business logic. In the middle, the Ribbon designer has replaced the custom Windows Form and the tool box now has a separate tab for the Office Controls, as seen in Figure 5. This enables the developer to drag and drop various .NET Controls on the Ribbon designer just like they would do on a Windows or Web form.
Figure 5: The Ribbon designer template opens inside Visual Studio 2008.
You will see a default group box on the Ribbon. This is where controls can be dragged and placed. Additional group box controls can be placed on the Ribbon. Now, drag and drop a drop-down list box and a button so that it looks like Figure 6.
Figure 6: Place the drop-down list box and button one below the other.
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.
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.
- Open a connection to the SQL Server database.
- Create a DataReader and use a query to bring the names of the customers from the table Customers.
- Fill the DataReader with the data brought by this query.
- Populate the drop-down list box with the DataReader.
- 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.
- 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()
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;
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,
//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;
item = new RibbonDropDownItem();
item.Label = EmpReader.GetString(1);
item.Tag = EmpReader.GetSqlInt32(0).ToString();
Parse this code to see what it does. At the beginning of the function, create a new SQLCommand to fetch data from the Customers table. Define the command text in a SQL statement and then create a DataReader to execute the SQLCommand using the ExecuteReader() method. Because you are not making any changes to the table, it is better to get this data in a read-only format; this also improves performance. Now, you need to populate the drop-down list box in VSTO, so you use the DataReader to iterate through the records and then populate the drop-down list box in a loop. Note that you do not need to look for the beginning and end of the records because this is handled by the DataReader.
Finally, you need to write code for the clicked event of the button. The code is found below.
private void RCustomerLookup_Load(object sender,
//we load the data from the database.
private void button1_Click(object sender,
//Get the active worksheet
int row = 10;
//we extract the id of the employee
String selempid = Custddlb.SelectedItem.Tag.ToString();
String selempname = Custddlb.SelectedItem.Label;
//we display the data from the sales order in the Excel sheet;
//the query for this is here
SqlCommand sqlcmdOrders = new SqlCommand();
sqlcmdOrders.CommandText = "select A.contactid, A.salesorderid,
B.firstname + ' ' + B.middlename + ' ' +
B.lastname as CustName, " +
"A.subTotal, A.taxamt, A.freight, A.totaldue " +
"from sales.salesorderheader A, person.contact B " +
"where A.contactid = B.contactid " +
"and A.contactid = '" + selempid + "'" +
" order by 3";
sqlcmdOrders.Connection = sqlconn;
SqlDataReader OrdersReader = sqlcmdOrders.ExecuteReader();
//layout of the data on the Excel sheet
activeWorksheet.Cells[4, 2] = "Customer ID";
activeWorksheet.Cells[5, 2] = "Customer Name";
activeWorksheet.Cells[9, 3] = "S. No";
activeWorksheet.Cells[9, 4] = "Sales Order ID";
activeWorksheet.Cells[9, 5] = "Amount";
activeWorksheet.Cells[9, 6] = "Tax";
activeWorksheet.Cells[9, 7] = "Freight";
activeWorksheet.Cells[9, 8] = "Total";
int currentrow = 1;
activeWorksheet.Cells[4, 3] = selempid;
activeWorksheet.Cells[5, 3] = selempname;
activeWorksheet.Cells[row, 3] = currentrow;
activeWorksheet.Cells[row, 4] = OrdersReader.GetValue(1);
activeWorksheet.Cells[row, 5] = OrdersReader.GetValue(3);
activeWorksheet.Cells[row, 6] = OrdersReader.GetValue(4);
activeWorksheet.Cells[row, 7] = OrdersReader.GetValue(5);
activeWorksheet.Cells[row, 8] = OrdersReader.GetValue(6);
Parse this code and see what it does. If you look carefully, you will see that it is similar to what you did earlier with the DataReader except that now you are displaying the data in particular cells of the Excel file instead of populating other controls.
Now, when the user runs the application, he will get the desired output, as shown in Figure 8.
Figure 8: The final details of the orders for the selected customer.
Of course, there are many more things that could be done with this code to make the data more compelling and display more statistics to the reader. But, this is out of the scope of this article because the main point was to show how VSTO 3 helps to write applications like this.
You have worked through a very interesting example of how VSTO 3 has changed the way developers will now write applications for Office 2007 to take advantage of the Ribbon Interface and create compelling applications for users of Office 2007.
About the Author
M. Rajesh works as a Technical Expert in a leading software company in Chennai, India. He has around 14 years of expertise in the Microsoft platform and has worked since the days of MS-DOS and FoxPro. He was an MVP (Microsoft Valuable Professional) in .NET, Windows Shell, and Tablet PC for the period 2002-06. Now, he focuses on the Windows Mobile Platform and is working on mission-critical operations for a leading provider in New York City. His favorite pastimes include reading blogs, learning about new technology, and he is an avid F1 fan.