Building an Excel 2003 Invoice Application Using Visual Studio 2005 Tools for Office

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

J. Jason De Lorme, Simplesheet, Inc.

Revised: November 2005

Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Office Excel 2003

Summary: See a demonstration of an Excel 2003 invoice solution written in Microsoft Visual C# and Microsoft Visual Basic using Microsoft Visual Studio 2005 Tools for the Microsoft Office System. (19 printed pages)

Download OfficeVSTOBuildingExcelInvoices.msi.

Contents

• Scenario Overview

• Architectural Considerations in Designing the Solution

• Developing the Excel 2003 Invoice Application

• Deploying the Excel 2003 Invoice Application

• Code Security

• Additional Resources

• About the Author

Scenario Overview

As the proprietor of a small consulting firm, I live invoice to invoice. As predominant as that may seem to my well-being, preparing invoices can be one of my least favorite tasks. The reason is the amount of time it can take to compile a single month's invoices. To keep our time organized, my company uses a homegrown Web-based timesheet application. As a good citizen of the Microsoft .NET Framework, I recently added some Web services that made it possible for other business applications to share basic client, project, and time data from the timesheet application. This article presents an invoice solution using Microsoft Office Excel 2003 and built with Microsoft Visual Studio 2005 Tools for the Microsoft Office System. The demonstration uses customer, project, and timesheet XML data sources.

Background

For the purposes of this demonstration, call my company Adventure Works, Ltd. Adventure Works, Ltd is a professional services company that invoices customers on a time and materials basis. Consultants record their hourly time in a timesheet application that supports exporting the data in XML format. Adventure Works always uses Excel to create invoices. The client project manager must review the timesheet entries and determine if they are billable or not based on contract requirements. Sometimes invoice line items must be manually edited, removed, or new items added before sending the invoice to the client.

Business Rules

In analyzing potential solutions, the Adventure Works team compiled the following list of business requirements for the invoicing application.

• Client addresses and contact information must be imported from existing business applications.

• Only active projects must be imported for the client being invoiced. These projects must be imported from the existing business application.

• Consultant time entries must be imported from the timesheet system, then reviewed and edited, including adds or deletes by the client project manager.

• The project manager must be able to calculate discounts on the invoice.

• While we do not have to tax services in our state, there are certain materials that we must calculate tax on. Because this happens so rarely, Adventure Works does not have any third-party components to manage tax calculation. For these exceptions, the tax is calculated using a formula on the spreadsheet.

• The project manager requires the following fields when reviewing work performed:

  • Date

  • Hours

  • Description

  • Resource name

• The invoice that is sent to the customer must have the following fields on each line item, in the following order:

  • Hours

  • Date

  • Resource name

  • Hourly rate

  • Line item total

• Invoices are printed and mailed to the customer. The customer never receives the soft copy of the invoice.

Scenario

Currently, to invoice clients, an Adventure Works project manager uses the Web-based timesheet application to view a timesheet for each consultant and then cut and paste each field available in the timesheet into an Excel invoice workbook. Typically the previous month's invoice is used as a template for the hourly rate and customer information, but this often causes a problem when the data in the business application changes. To mitigate errors the project manager must always double-check the business application to verify the address and hourly rate information. The Excel invoice as shown in Figure 1 is printed and mailed to the client.

Figure 1. Excel invoice

Solution

Using Visual Studio 2005 Tools for Office, I created an Excel solution using managed, .NET-based code: Visual C# and Visual Basic. This solution uses XML data sources available to us from the existing business applications and eliminates the cut and paste steps. The workbook has two sheets; the invoice worksheet is the final product that is printed and delivered to the client and the Data worksheet is the user's work area. The Data worksheet allows the project manager to perform any editing required and choose which entries are applied to the invoice. When the project manager opens the Excel invoice workbook, a Document Actions task pane (actions pane) appears allowing them to choose the client and project as shown in Figure 2.

Figure 2. Client project selection actions pane

The user can choose a client from the drop-down list in the actions paneand data binding populates the customer name, address, and contact information in the invoice worksheet. Changing the selected client updates the list of available projects in the drop-down list below. Clicking Get Hours retrieves all the timesheet entries to bill for that project and takes the user to the Data worksheet as shown in Figure 3.

Figure 3. Data worksheet

The Data worksheet allows the user to edit any of the entries including adding or removing entries. When the user activates the Data worksheet, the task pane updates to disallow the user from changing the selected client. However, it still allows them to get hours for additional projects for that client. Clicking Get Hours loads hours into to the list. Clicking Add To Invoice on the Data worksheet adds the entries to the invoice worksheet and sets that as the active worksheet.

When the invoice worksheet is activated, notice that the actions pane is updated with all the functionality available on the invoice worksheet. The user can click Clear Invoice to clear the contents of all of the customer and line item cells on the invoice sheet.

System Requirements

In order to use the invoicing solution you must have the following software installed:

• Microsoft Visual Studio 2005 Tools for the Microsoft Office System

• Microsoft Office Excel 2003 or Microsoft Office Professional Edition 2003

The order in which you install the software is not important. However, installing Excel or Office Professional Edition 2003 is a prerequisite for using Visual Studio 2005 Tools for Office. When you install Visual Studio 2005 Tools for Office, the installer installs the Office Primary Interop Assemblies (PIAs) if they are not already installed.

To compile and run the Excel invoice sample file

1. Perform a complete installation of Office Professional Edition 2003 or Excel

   Note

   Only a complete installation includes the PIAs.

2. Install Visual Studio 2005 Tools for Office.

3. Download the package associated with this article, which contains the source code, an Excel workbook, and XML data files.

4. Extract those files to a directory on your local computer.

5. Open the Visual C# or Visual Basic project in Visual Studio 2005 Tools for Office by double-clicking the Invoice.sln file.

6. Press F5 to compile and run the sample.

Architectural Considerations in Designing the Solution

Using Excel has always been a natural choice for us when creating invoices, however, cut and paste was not a viable solution long-term. Adventure Works has standardized on the .NET Framework and when thinking about how to automate the invoice process, I considered several implementation options that could use our skills with the.NET Framework.

The Managed Code Solution

Writing the solution in managed code offers several advantages. The common language runtime validates code as it compiles. The validation prevents the process from performing illegal operations such as accessing memory that does not belong to it. Rogue buffer overruns and other common security exploits can cause this type of illegal operation. The managed heap is automatically subject to garbage collection. Garbage collection destroys objects when they are no longer needed or accessible, thereby reducing the amount of supporting code we need to write and transparently optimizing memory utilization for our process. Additionally, we have access to the .NET Framework class library, which includes the following technologies:

• ADO.NET

  ADO.NET is the suite of data access technologies included in the .NET Framework class libraries that provide access to relational data and XML. It is the flexible data access layer that provides consistent access to a variety of data sources in a disconnected architecture. Data sources, include SQL databases and data sources exposed through OLE DB data access technology and XML. For the sake of simplicity, the sample included with this article uses local XML files in place of what could be XML returned from Web service calls. By using ADO.NET, you can make replacing the data provider completely transparent to the rest of the application.

• ASP.NET

  ASP.NET is a set of technologies in the Microsoft .NET Framework for building Web applications and Web services. Considering the timesheet application is already a Web-based ASP.NET solution, at first I thought it might make a logical extension to add an invoicing component. The application could read data from the database and produce an invoice in HTML. When I started to think about implementing the Web solution, I was quickly reminded of the myriad of exceptions that can occur when we build an invoice. For example, we occasionally need to bill for materials in addition to time, or apply a discount, balance, or even credits. While this was easy with the Excel workbook, it would probably require a lot more work to implement in a Web application.

• Windows Forms

  Alternatively, I considered writing a Microsoft Windows Forms solution. Windows Forms is a framework for building Windows client applications that utilize the common language runtime. I conceptualized using a grid control for the invoice area and allowing the users to import the time entries and then enabling them to edit items in the grid. While this would be possible in the ASP.NET solution, the Windows Forms controls may have given me a little more flexibility.

  Taking a step back, I realized that what I was about to build was really a fancy spreadsheet, but it did not give us the flexibility we already had with Excel.

• Visual Studio 2005 Tools for Office

  The choice to use Microsoft Office 2003 Editions, and particularly Excel, was a clear one. Our users are more comfortable using applications they already know and rely on and furthermore, using Microsoft Office programs saves us a lot of code.

  Traditionally, Visual Basic for Applications (VBA) and COM add-ins have been the primary means to extend Microsoft Office programs. While I find that VBA has its place with smaller utilities and common repeatable tasks, we were looking to build a solution with managed .NET code for all the benefits I already mentioned.

  Visual Studio 2005 Tools for Office enables us to build scalable solutions using Microsoft Office 2003 as an application platform. Using Visual Studio 2005 Tools for Office, you can build a Microsoft Office Word 2003 or Excel solution written in Visual C# or Visual Basic, using the.NET Framework version 2.0. Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003 was the first time that the Microsoft Office System used the .NET runtime and provided much of the infrastructure we needed to start building reliable Microsoft Office solutions with the Microsoft .NET Framework.

Developing the Excel 2003 Invoice Application

Microsoft Visual Studio 2005 Tools for the Microsoft Office System provides excellent managed wrappers for some of the Microsoft Office object models, but we must write most of the automation for Microsoft Office programs against COM objects. You can gain access to the COM objects through .NET Interop using the PIAs.

Because Visual Studio 2005 Tools for Office supports Visual C# and Visual Basic, the choice of what language you use is up to you. Both languages are first-class programming languages that are based on the .NET Framework and they are equally powerful. Many argue that the difference is merely syntactic sugar and it comes down to personal preference. This is almost correct.

The first significant difference is that Visual Basic supports optional parameters, but Visual C# does not. Visual C# relies on an object-oriented approach of method overloads, which are not supported in Microsoft Visual Basic 6.0 and COM.

Note

Visual Basic supports method overloading, also.

You will find many method signatures in the Office object model that rely on optional parameters. This takes a little trickery in Visual C# that I am going to point out. Compare the following SaveAsDocument method in Word:

Friend Sub SaveAsDocument()
    ' This overwrites any previously existing 
    ' documents at this location.
    ThisDocument.SaveAs("c:\test\MyNewDocument.doc")
End Sub

private void SaveAsDocument() 
{ 
    // This overwrites any previously existing 
    // documents at this location. 
    object fileName = "c:\\test\\MyNewDocument.doc"; 
    ThisDocument.SaveAs(ref fileName, ref missing,
        ref missing, ref missing, ref missing,
        ref missing, ref missing, ref missing,
        ref missing, ref missing, ref missing,
        ref missing, ref missing, ref missing,
        ref missing, ref missing); 
}

You can see from the previous code example that Visual C# developers must supply values for every optional parameter, and if you do not want to pass a parameter, you must supply a reference to a special object instance of Type.Missing.

The second major difference between Visual C# and Visual Basic is in working with accessor methods. You must preface properties in the Office object models that are accessible as XXXX with Visual Basic with get_ or set_ when using Visual C#, for example:

    Dim range As Excel.Range = Application.Range("A1", "B5")

    Excel.Range range = Application.Range("A1", "B5");

Creating the Project

The New Project wizard in Visual Studio 2005 Tools for Office now has a project type of Office underthe language of your choice, which is slightly different from the Visual Studio Tools for Office, Version 2003.

To create an Excel workbook project

1. On the File menu, point to New, and then click Project.

2. The New Project dialog box appears.

3. In the Project Types pane, expand the language of your choice and then click Office.

4. In the Templates pane, select Excel Workbook.

5. Name the project, and store it in a local directory.

6. Click Create a new document and name it accordingly.

   Note

   For this solution, I chose to Use an Existing Document and specified our Excel invoice workbook.

7. Click OK.

Design Experience

When the project wizard completes, the first new Visual Studio 2005 Tools for Office feature you notice is depicted in Figure 4. The new Excel workbook is fully hosted inside Visual Studio 2005 Tools for Office as a design surface. Visual Studio 2005 Tools for Office merges menus and interfaces with the Excel application.

Figure 4. Excel hosted in Visual Studio 2005 Tools for Office

If you look at the Solution Explorer in Figure 5, you will notice three classes. One code-behind class is created for each worksheet and a single class for the workbook.

Figure 5. Solution Explorer

By default, Excel always creates three worksheets for a new workbook. To delete a worksheet from the workbook you can right-click the tab in the Designer view and click Delete. Visual Studio 2005 Tools for Office deletes the class file in the project when you do this. Most of the solutions I worked with have required fewer than three worksheets, and the extra code annoys me, so it is possible to change the Excel default for the next workbook you create.

To change the number of worksheets in a new workbook

1. On the Tools menu, click Options.

   The Options dialog box appears.

   Tip

   If you are doing this while in the Visual Studio environment, on the Tools menu, click Microsoft Office Excel Tools and then click Options. This demonstrates how Visual Studio merged the menus.

2. On the General tab, change the value of Sheets in new workbook to the number you want.

   Note

   This setting takes effect for the next workbook you create; it does not delete any existing worksheets you already have.

Code Behind

Clicking the View menu and then choosing Code or pressing F7 displays the code-behind class for the active worksheet. If you started with a new workbook instead of creating the solution from an existing document, your classes will be named Sheet1, Sheet2, and so on. These names do not mean a lot to me and I like to name my worksheets for the function they perform or the data they contain.

There are few steps to renaming a worksheet and it does not really matter which order you perform them.

To rename a worksheet class

1. In Solution Explorer, right-click the class file name and then click Rename.

2. Type the new name of the class and press ENTER.

   Tip

   Make sure that you include the file name extension when renaming the file.

Visual Studio 2005 Tools for Office performs a handful of steps behind the scenes when you do this. It renames the file on your file system, updates the project file, updates the class name declaration, and updates all references to the class name in each of the partial class files, which we will talk about shortly.

Renaming the class updates all the code for that class, but the Excel worksheet name remains the same. Visual Studio 2005 Tools for Office keeps track of this discrepancy by displaying the name of the Excel worksheet in parenthesis beside the class file name. This is visible in Solution Explorer as depicted in Figure 6.

Figure 6. Worksheet name differs from class name

To rename the worksheet

1. Display the worksheet in the designer by pressing SHIFT + F7 or clicking Designer from the View menu.

2. Right-click the worksheet tab and click Rename as shown in Figure 7.

   The worksheet name is highlighted.

   Figure 7. Renaming the worksheet

   

3. Type the new worksheet name and press ENTER.

If you switch back to the code view you see that there is not much code to start with, but you may notice a new modifier in the class declaration in Visual C# projects:

[C#]
public partial class Invoice : Microsoft.Office.Tools.Excel.Worksheet 

In order to hide much of the auto-generated implementation code, Visual Studio 2005 Tools for Office is using partial classes, which are a new feature of the.NET Framework 2.0. Clicking Show All in the Solution Explorer, as shown in Figure 8, shows all the files used in the project.

Figure 8. Show All Files button

If you inspect the Invoice.Designer.cs or Invoice.Designer.vb file, you notice the other part of the partial Invoice class. Partial classes support defining a type across many files. While this may not always be a good design methodology, it can help in team development where several people are working on various autonomous aspects of the class. It can also be extremely helpful for auto-generated code whereby the generator does not need to replace user code because it uses its own designated file.

Globals

A physical .cs or .vb file can contain more than one class, and if you examine the Invoice.Designer.cs or Invoice.Designer.vb file, somewhere near the bottom, notice the following code:

Partial Friend NotInheritable Class Globals
    
    Private Shared _Invoice As Invoice
    
    Friend Shared Property Invoice() As Invoice
        Get
            Return _Invoice
        End Get
        Set
            If (_Invoice Is Nothing) Then
                _Invoice = value
            Else
                Throw New System.NotSupportedException
            End If
        End Set
    End Property
End Class

internal sealed partial class Globals {
    
    private static Invoice _Invoice;
    
    internal static Invoice Invoice {
        get {
            return _Invoice;
        }
        set {
            if ((_Invoice == null)) {
                _Invoice = value;
            }
            else {
                throw new System.NotSupportedException();
            }
        }
    }
} 

There is a partial class named Globals in every one of the *.Designer.cs or *.Designer.vb worksheet and workbook classes. When the assembly is compiled, all these partial class files are combined to form a single class. This makes it easy for you to access public or internal properties and methods of other worksheets with code like this:

Globals.Data.Activate()

Globals.Data.Activate();

View Controls

As stated previously, host controls are controls that extend existing Word and Excel objects; however, they include events and data-binding capabilities. The NamedRange host control is an extension of the Excel Range object.

There are several ways to add a NamedRange host control to the document. One way is to drag the control from the Excel Controls section in the Toolbox and drop it on the document. Yet another way is to click the Insert menu, choose Name, and then click Define. Either way, a host control is created that represents that range. You can inspect the properties of the range at design time with the property grid in Visual Studio as shown in Figure 9.

Figure 9. NamedRange Properties

Without Visual Studio 2005 Tools for Office, to capture a change event with a native Excel Range object instead of a host control, you must handle the change event of the worksheet and determine if what changed is the range in which you are interested:

Private EventDel_CellsChange As Excel.DocEvents_ChangeEventHandler

Private Sub CellsChange(ByVal Target As Excel.Range)
      ' This is called when a cell or cells on a worksheet are changed.
      If Target.ID = "MyInterestingRange" Then
        ' Do something interesting.
      ElseIf Target.ID = "SomethingElseInteresting" Then
        ' Do something else interesting.
      End If
End Sub

But in Visual Studio 2005 Tools for Office, the NamedRange host control is a .NET-based control, complete with its own events. For example, to handle the Change event you could use the following code:

Private Sub myRange_Change(ByVal Target As _
        Microsoft.Office.Interop.Excel.Range) _
        Handles myRange.Change
    ' Do something interesting with this range here.
End Sub

// Assign the delegate.
this.myRange.Change += new 
     Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(
     this.myRange_Change); 

void myRange_Change(Microsoft.Office.Interop.Excel.Range Target)
{
     // Do something interesting with this range here.
}

If you choose, you can double-click the range from within the designer to automatically generate this code.

Table 1. Excel Host Controls

Control	Description
NamedRange	Represents a rich Excel.Range object that has its own events and supports simple data binding. Important To read and write the value of this object, use the Value2 property.
XMLMappedRange	An Excel.Range object that is created when a non-repeating XmlNode is mapped to a cell in Excel.
ListObject	The ListObject is a range that extends multiple columns and rows and supports sorting on any column. The ListObject is the only host control that supports complex data binding, which means it can be bound to more than one value.
Chart	An Excel.Application Chart object.

Actions Pane

Smart documents are a new feature introduced with Office 2003. They give users context-sensitive help and functionality using the Document Actions task pane. Smart documents are based on the document being marked up with a user-defined XML schema. Prior to Visual Studio 2005 Tools for Office, the ISmartDocument interface facilitated programmability of the task pane. All the functionality of a smart document is determined by the user's current XmlNode selection.

The actions pane implementation in Visual Studio 2005 Tools for Office is a Windows Forms control that is a managed implementation of the Document Actions task pane in Word and Excel. While you are not precluded from implementing similar XML selection functionality in the actions pane, it is not required. TheActionsPane control is a member of the ThisWorkbook class in an Excel project and ThisDocument in a Word project. This control acts as a container for other Windows Forms controls, including user controls and Actions Pane controls that you create. In this solution, I created a user control called ClientProjectsUserControl and added it to the actions pane in the ThisWorkbook_Startup event as follows:

' Load the ClientUserControl into the actions pane.
Dim clientPane As ClientProjectsUserControl = _
    New ClientProjectsUserControl(Me.InnerObject)
Me.ActionsPane.Controls.Add(clientPane)

// Load the ClientUserControl into the actions pane.
ClientProjectsUserControl clientPane = 
     new ClientProjectsUserControl();

this.ActionsPane.Controls.Add(clientPane);

Adding a control automatically shows the task pane as in Figure 2, but you can explicitly show or hide it with the following code:

Me.ActionsPane.Show()
Me.ActionsPane.Hide()

this.ActionsPane.Show();
this.ActionsPane.Hide();

To remove a control from the actions pane, simply call the Remove method:

' Remove the ClientProjectsUserControl from the actions pane.
Me.ActionsPane.Controls.Remove(clientPane)

// Remove the ClientProjectsUserControl from the actions pane.
this.ActionsPane.Controls.Remove(clientPane);

Data Binding

Simple data binding exists when a single control property is bound to a single data source value. An example of simple data binding is the Text property of the Textbox control when it is bound to a single data value in a data set. If you update the data set, the text in the Textbox control updates and vice versa. Complex data binding occurs when your control is bound to more than one data element. Such is the case in a ListBox control list where the DisplayMember and ValueMember are bound to different columns in the data source.

This solution uses complex data binding with a ListObject on the Data worksheet to display time entries for a particular project as shown in Figure 3. The data source is an ADO.NET DataSet object. The data set is an in-memory cache of data retrieved from a data source including the tables that contain, order, and constrain the data, as well as the relationships between the tables. For this sample, the raw timesheet data source is an XML file located in the application.

Tip

While this sample uses local XML files, you could return the same data from a Web service call and still load it into a data set in a similar fashion.

<Timesheets>
    <Timesheet>
        <Tasks>
            <Task projectId="101">
                <Date>5/3/2004</Date>
                <Hours>5</Hours>
                <Description>Reconfigured mail server.</Description>
                <Resource>Craig Playstead</Resource>
                <Rate>80</Rate>
            </Task>
        </Tasks>
    </Timesheet>
</Timesheets>

The following code creates a data set and populates it from an XML file on the hard drive:

' Load Timesheet XML into the data set.
Dim timesheetDataSet As DataSet = New DataSet()
timesheetDataSet.ReadXml(filePath)

// Load timesheet XML into the data set.
DataSet timesheetDataSet = new DataSet();
timesheetDataSet.ReadXml(filePath);

The data set is loaded from the XML file, including all the tables and relationships implicitly derived from the schema of the XML document. This data source contains all timesheet entries for a specified period. As you saw in Figure 2, the user has the ability to choose a specific client and project for which to get data.

When the user clicks Get Hours, I get the current project from the list box and use the Globals object to access the current instance of the Data class to assign the ProjectId property as shown below.

Private Sub getHours_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles getHours.Click
        Globals.Data.ProjectId = _
                Integer.Parse(projectComboBox.SelectedValue.ToString())
        Globals.Data.Activate()
    End Sub

private void getHours_Click(object sender, EventArgs e)
{
    Globals.Data.ProjectId =
        int.Parse(projectComboBox.SelectedValue.ToString());
    
    Globals.Data.Activate();
}

If I bind the ListObject to the data set, it returns all time entries. Instead, what I need to do is filter this list for only data that applies to the active project. This is accomplished by using another ADO.NET object called a DataView. The DataView is a customized view of a DataTable that we can use for filtering. To create the desired view in my ListObject, I create a DataView, then filter the list based on the current project and bind it to the ListObject:

' Create the DataView to be used for filtering by projectId.
projectView = new DataView(timesheetDataSet.Tables("Task"))

' Only show time for the selected project.
projectView.RowFilter = String.Format("projectId='{0}'", _projectId)

' Data Bind to the ListObject. Notice the 2nd parameter is ""
timesheetList.SetDataBinding(projectView, "", "Date", "Hours", _
      "Description", "Resource", "projectId")

// Create the DataView to be used for filtering by projectId.
projectView = new DataView(timesheetDataSet.Tables["Task"]);

// Only show time for the selected project.
projectView.RowFilter =
        string.Format("projectId='{0}'", projectId);

// Data Bind to the ListObject. Notice the 2nd parameter is ""
timesheetList.SetDataBinding(projectView, "", "Date", "Hours",
        "Description", "Resource", "projectId");

By creating a filtered DataView, we can filter only the relevant rows that are displayed in the ListObject. In addition, the SetDataBinding method of ListObject allows us to specify which columns we want displayed. When you call SetDataBinding with a DataView as the data source, make sure that you set the second parameter to an empty string or the data binding throws an exception.

One other caveat with ListObjects is that they cannot extend into merged cells. This is not a limitation of Visual Studio 2005 Tools for Office, but an Excel constraint. For this reason, we are using a NamedRange on the Invoice worksheet and populating it by iterating through each DataRowView in the DataView in the AddInvoiceItems method which is called when the user clicks Add To Invoice from the Data worksheet.

Because the solution supports appending data to the range, the first thing I need to look for in the AddInvoiceItems method is a blank line in the invoiceItems range:

' Starting row on the Invoice sheet.
Dim row As Integer = 0
Dim lastRow As Integer = Globals.Invoice.invoiceItems.Rows.Count + _
    Globals.Invoice.invoiceItems.Row

' Determine the first blank row to start appending to.
For row = Globals.Invoice.invoiceItems.Row To lastRow - 1
    ' Check to see if a value exists in the first cell.
    Dim start As Excel.Range = DirectCast(Globals.Invoice.Cells(row, _
        Globals.Invoice.invoiceItems.Column), Excel.Range)

    ' Exit at the first blank row.
    If (start.Value2 Is Nothing) Then Exit For
Next

// Starting row on the Invoice sheet.
int row = 0;
int lastRow = Globals.Invoice.invoiceItems.Rows.Count +
    Globals.Invoice.invoiceItems.Row;

// Determine the first blank row to start appending to.
for (row = Globals.Invoice.invoiceItems.Row; row < lastRow; row++)
{
    // Check to see if a value already exists in the first cell.
    Excel.Range start = (Excel.Range)Globals.Invoice.Cells[row,
        Globals.Invoice.invoiceItems.Column];

    // Exit at the first blank row.
    if (start.Value2 == null)
    {
        break;
    }
}

Another point of caution is that I do not want to cause an overflow in the designated invoice items range on the invoice worksheet. The worksheet only holds a certain number of invoice lines. A future expansion capability might be a good idea, but we have not needed to do this so far. The way the code is written, if we needed to expand the number of items, I just expand the worksheet and update the range. To prevent overflow and support future changes in the worksheet, I add a bit of code that ensures the row to insert does not exceed the number of rows in the range.

Dim lineItem As DataRowView
For Each lineItem In projectView
    ' Ensure we don't overrun the Range.
    If (row >= lastRow) Then
        MessageBox.Show("The invoice items you are attempting to" & _
            " add will overrun the size of the invoice.\nPlease " & _
            "clear the current invoice items before running this" & _
            " operation again.", "Invoice", MessageBoxButtons.OK, _
            MessageBoxIcon.Warning)

        addToInvoice.Enabled = False
        Exit For
    End If

    With Globals.Invoice
        columnOffset = .invoiceItems.Column
        Dim hours As Excel.Range = _
            DirectCast(.Cells(row, columnOffset), Excel.Range)
        columnOffset += 1
        Dim itemDate As Excel.Range = _
            DirectCast(.Cells(row, columnOffset), Excel.Range)
        columnOffset += 1
        Dim resource As Excel.Range = _
            DirectCast(.Cells(row, columnOffset), Excel.Range)
        Dim rate As Excel.Range = _
            DirectCast(.Cells(row, columnOffset + 7), Excel.Range)

        hours.Value2 = lineItem("Hours")
        itemDate.Value2 = lineItem("Date")
        resource.Value2 = lineItem("Resource")
        rate.Value2 = lineItem("Rate")

    End With

    ' Increment to the next row.
    row += 1
Next

foreach (DataRowView lineItem in projectView)
{
    // Ensure we don't overrun the Range.
    if (row >= lastRow)
    {
        MessageBox.Show("The invoice items you are attempting to" +
            " add will overrun the size of the invoice.\nPlease " +
            "clear the current invoice items before running this" +
            " operation again.", "Invoice", MessageBoxButtons.OK,
            MessageBoxIcon.Warning);
        
        addToInvoice.Enabled = false;
        break;
    }

    columnOffset = Globals.Invoice.invoiceItems.Column;

    Excel.Range hours =
        (Excel.Range)Globals.Invoice.Cells[row, columnOffset++];
    Excel.Range date =
        (Excel.Range)Globals.Invoice.Cells[row, columnOffset++];
    Excel.Range resource =
        (Excel.Range)Globals.Invoice.Cells[row, columnOffset];
    Excel.Range rate =
        (Excel.Range)Globals.Invoice.Cells[row, columnOffset+7];

    hours.Value2 = lineItem["Hours"];
    date.Value2 = lineItem["Date"];
    resource.Value2 = lineItem["Resource"];
    rate.Value2 = lineItem["Rate"];

    // Increment to the next row.
    row++;
}

Embedding Windows Forms Controls

The final feature we describe is the ability to embed Windows Forms controls in the Excel worksheet. You generally put controls on the document surface when you want that control present all the time and put controls on the actions pane when they are based on context within the document. To embed a control in the Excel worksheet, you can easily drag and drop it from the Toolbox. Figure 3 shows a button control on the Data worksheet, labeled Add to Invoice, and named addToInvoice.

Adding a Windows Forms control to the worksheet versus a Microsoft ActiveX control gives us the same benefits we discussed earlier with host controls. To respond to the user's button click, we can simply write the following code:

' Add the items to the invoice and show the Invoice sheet.
Private Sub addToInvoice_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles addToInvoice.Click
    AddInvoiceItems()
    Globals.Invoice.Activate()
End Sub

// Assign the delegate.
this.addToInvoice.Click += new 
     System.EventHandler(this.addToInvoice_Click);

// Add the items to the invoice and show the Invoice sheet.
private void addToInvoice_Click(object sender, EventArgs e)
{
     AddInvoiceItems();
     Globals.Invoice.Activate();
}

When you add a Windows Forms control to the worksheet, a little magic happens behind the scenes. The embedded data in the document is actually an ActiveX control named WinForms.Control.Host that hosts the managed control. The reasons for this are really outside the scope of this article, but the first is security and the second is COM registration. In order for the Excel application to call a managed .NET-based control, you must register a COM-callable wrapper for each type of control. This requires rather insidious registry entries and is not .NET-oriented, so the WinForms.Control.Host acts as a proxy for Excel.

Deploying the Excel 2003 Invoice Application

The invoice solution presented here can be deployed in several ways. There are three standard deployment options for Visual Studio 2005 Tools for Office solutions, depending on your requirements:

The Excel document and the associated assembly are deployed to a user's local computer.

• The Excel document is deployed to a user's local computer. The associated assembly is deployed to a network share (UNC) or Web server (HTTP).

• The Excel document and the associated assembly are deployed to a network share (UNC) or Web server (HTTP).

For more information, see Deploying Office Solutions.

Code Security

No matter which option you choose for deployment, Visual Studio 2005 Tools for Office solutions require full-trust .NET code access security to run. When running from your development computer, Visual Studio 2005 takes care of granting full-trust code access security to your application so you don't need to worry about it. If you deploy the document to an end user computer, or run it from another location, you must ensure that location is trusted on the computer running the solution.

The sample code referred to in this article is intended for instructional purposes, and should not be used in deployed solutions without modifications. In particular, you must consider code security.

To illustrate the simplicity of this sample solution, a list of potential threats are identified using the threat modeling process and tools described in the Threat Modeling section in the Microsoft Security Developer Center.

The following is an example of an identified threat that you should take into consideration before expanding or deploying this solution.

Table 2. Example of Threats

Threat effect	Entry point	Known mitigation
The XML data files have been compromised and contain invalid data.	DataSet.ReadXml	Mark the XML data files as read only.

For more information on code security, please visit the Microsoft Security Developer Center.

Additional Resources

The following links can provide more information.

Visual Studio 2005 Tools for Office

• Microsoft Visual Studio Developer Center

• Visual Studio Tools for Office online documentation

• Microsoft Office Developer Center: Visual Studio Tools for the Microsoft Office System

• Visual Studio Tools for Office blog

• Visual Studio Tools for Office newsgroup: microsoft.public.vsnet.vstools.office

Code Security

• Writing Secure Code

• Microsoft Security Developer Center

• Protect Your PC

About the Author

J. Jason De Lorme is a principal consultant with Simplesheet, Inc., a software architecture and development firm specializing in solutions built with the .NET Framework and Microsoft Office 2003 Editions. As an early adopter, he enjoys the opportunity to work with new products, especially when it involves the .NET Framework. When he's not meeting with clients or writing code, he's probably cycling, hiking or skiing.