What you need: Visual Studio 2005 Tools for Office SQL Server Word 2003

I never cease to be amazed at how much time and effort organizations put into preparing spreadsheets and documents to handle basic business functions like accounts receivable and invoicing. Much of this time is spent on repetitive tasks that could be easily automated. Smart documents have been around for a while and were introduced to address this exact problem. Unfortunately, smart documents have historically been difficult to create and maintain because developers either had to use a VBA environment or develop in Visual studio without a graphical designer. Under Visual Studio 2005, however, we have a new .NET development paradigm that makes smart documents significantly easier to build and deploy using the Visual Studio 2005 Tools for Office (VSTO). In this article, I'll create a simple smart document that helps a services company create an invoice from data in a time and billing system. I'll accomplish this by first creating a database in SQL Server for time and billing information. Then I'll create the smart document in Visual Studio 2005.

Figure 1. The Timecard Database

Before I could get started on the smart document, I needed a data source. Unfortunately, there are no readily-available test databases with time and billing information. Therefore, I had to create my own. I used SQL Server to create a simple database named Timecard with the schema shown in Figure 1. I exported the database to a Microsoft Office Access file for download with this article.

The smart document itself is created entirely inside of Visual Studio 2005. In Visual Studio 2005 Tools for Office, you will see a node named Office in the New Project dialog that allows you to create projects based on Word or Excel documents. Figure 2 shows the New Project dialog with the Office node selected.

Figure 2. The New Project Dialog

For this solution, I created a new project based on a Word document. When you create this project, you'll notice that Visual Studio hosts the entire Word application within the design environment. This is an incredibly powerful way to create smart documents because Word becomes just another interface like a Windows form or ASP.NET page. Furthermore, Visual Studio merges the Word menu items with the development environment so you have complete access to all the functionality of Word while developing the solution. We'll take advantage of this in our solution.

Creating the XML Schema
My strategy for creating the invoice is to use an XML schema to define fields of interest within the Word document. Once I define these areas using XML elements, I will be able to utilize them to manipulate the data in the document. Therefore, I must create an XML schema in Visual Studio and attach it to my smart document.

Within Visual Studio, I selected to add a new XML schema item to the project. Within the schema, I created elements for the customer's name, the project name, and the tasks that are part of the project. These elements will allow me to place all the necessary information in the invoice. Figure 3 shows the definition of the elements in my schema, and the schema file is available as part of the download for this article.

Figure 3. The XML Schema

Once you have defined an XML schema, it needs to be attached to the Word document using the following steps.

1. Select the Word document in the design environment.
2. Select Tools>Microsoft Office Word Tools>Templates and Add-Ins from the Visual Studio menu.
3. In the Templates and Add-Ins dialog, select to add a new schema to the document and navigate to the XML schema you created.

Take note of the URI for your schema because you'll need this information later. Figure 4 shows the Templates and Add-Ins dialog.

Once you attach the XML Schema to the document, you may define nodes within the document using the XML Structure view in the Task Pane. At this point, you can make your invoice appear any way you want it using all the features of Word. I simply downloaded a template from the Microsoft Office Online Web site and added the XML elements as shown in Figure 5.

Figure 4. The Templates and Add-Ins Dialog

Coding the Document
Once you have the document created, you can write code for it using the code window associated with the document. My plan is to create a set of controls in the Task Pane of Word that can be used to view timecard information and insert it in the invoice. When the document is opened, I will read the information from the Timecard database and present it in a series of lists. The user can select the customer, project, and task item from these lists and insert the appropriate information in the document. Figure 6 shows the completed document in action.

Placing controls in the Task Pane is done programmatically by defining controls and adding them to the ActionsPane object of the document. For my project, I defined ComboBox controls for the customer, project, and tasks lists. I defined a Button control to insert the task items into the invoice. In the Startup event for the document, I set the properties for the controls and added them to the task pane.

Figure 5. XML Elements Defined in the Document

Another key element of my solution involves using the document offline. I utilize a public DataSet object to hold all of the project information that will appear in the lists. When I declared the DataSet, I did so using the [Cached()] attribute. This attribute allows cached data to be used when the Timecard database is not available. In code, I simply check to see if the DataSet is null. If not, the cached data is used; otherwise the data needs to be initialized from the source.

When the user selects a customer from the list, the project list updates with related projects for that customer. Similarly, when the user selects a project, the task list updates with tasks from the project. When the user pushes the button, the selected information inserts into the document.

Figure 6. The Final Document

Each time the user inserts an item, I want to create a new line in the invoice. Therefore, I use an XPath expression to return the nodes and the document and help me find a blank line in the invoice where a new item can be inserted. The Word object model supports an XMLNode object that is similar to the one found in the .NET System.Xml namespace. I use the SelectNodes and SelectSingleNodes methods, which both require the URI of the XML Schema as an argument. That's why you needed to make note of it earlier.

Once I have identified an empty node in the invoice, it is a simple matter to set the Text property of the node to insert the new information. I then run a simple calculation to total up the extended price for each line item in the invoice. Now you have a smart document that can easily create an invoice without copying and pasting from the time and billing system. Listing 1 shows the complete code for the project.

Creating smart documents under Visual Studio 2005 Tools for Office is now as familiar as creating any other type of project. You can utilize .NET code as well as external elements like controls and schemas to create smart documents. This should make these solutions much easier to deploy and maintain than they have been in the past.

Scot Hillier is an independent consultant focused on creating solutions for Information Workers with SharePoint, Office, and related .NET technologies. He is the author of 8 books on Microsoft technologies including 'Microsoft SharePoint: Building Office 2003 Solutions' and 'Advanced Windows SharePoint Services.' When not writing about technology, Scot can often be found presenting to audiences ranging from developers to C-level executives. For information on Scot's Microsoft books, visit www.sharepointstuff.com.