Integrating Excel and Word with ASP.NET 2.0 Web Sites

Article
05/23/2014

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.

Summary: Read about how you can integrate Microsoft Office Word 2003 and Microsoft Office Excel 2003 with ASP.NET 2.0 Web sites to generate form letters in Word and graphical reports in Excel. (14 printed pages)

Fulvio Giaccari, SB Soft S.r.l.

October 2006

Applies to: Microsoft Visual Studio 2005, Microsoft SQL Server 2005, Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System, Microsoft Office Excel 2003, Microsoft Office Word 2003

Contents

Overview
Creating a Web Service Using Visual Studio 2005
Connecting to a SQL Server 2005 Database
Creating the Excel Worksheet
Creating a Web Service to Extract Sales Data
Creating the Word 2003 Document Template
Creating the Actions Control Task Pane
Conclusion
Additional Resources

Overview

With the advent of the Microsoft .NET Framework 2.0 and Microsoft Visual Studio 2005, Microsoft introduced a new approach to Microsoft Office programmability, using Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003. Microsoft Visual Studio 2005 Tools for the Microsoft Office System improved on this initial release by including the ability to program in the integrated development environment (IDE) of Microsoft Office Word 2003 or Microsoft Office Excel 2003 directly from Visual Studio. For this reason, today it is simpler than before to create Office applications that can interact with the components we create.

This article shows how to take advantage of some of these new aspects of Office programmability including Web services, task panes, and interaction with Word and Excel. It walks through code that shows how to use Web services to connect to a Microsoft SQL Server database from an ASP.NET 2.0 Web site and create a Word letter and Excel graphs based on data from the database. The article includes code samples in Microsoft Visual Basic .NET that create two Web services. The first Web service extracts all the sales employee information (by territory) and creates a thank-you letter in Word. The second Web service extracts all the purchases for a specific period and populates a graph in Excel 2003.

Figure 1 shows the schema logic for this solution.

Figure 1. Application's schema logic

The Web site used in the example is based on the Adventure Works Web site used during the events of the Visual Studio 2005 launch. The Adventure Works Web site was created with ASP.NET 2.0 technology and uses Microsoft SQL Server 2005 as the database.

Required Software

This solution requires the following software:

Microsoft Windows Server 2003 Standard Edition
Microsoft Office 2003 Professional Edition
Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System
Microsoft Visual Studio 2005 Professional Edition
Microsoft SQL Server 2005
Microsoft Internet Information Services 6.0
Microsoft ASP.NET 2.0

Creating a Web Service Using Visual Studio 2005

First, create an empty project called Adventure Works, then:

In Visual Studio 2005, add an ASP.NET Web Service project called AdventureWeb to the Adventure Works project.
Next, add the first Web service, called AdvOrders.asmx. Excel uses this Web service to create graphs of sales of a fiscal year. Note that the Web service code is added to the Web project.
Next, create a function called Verify.

Note

This function verifies whether the Web service functions correctly. This enables you to confirm basic Web service functionality before launching the application. If the Web service is not working, any application interaction is blocked.

To create the function, insert the following code before the Web service:
```
      <WebMethod(Description:="Returns OK if the Web service is online", _
         EnableSession:=False)> _
         Public Function Verify() As String
         Return "OK"
      End Function
```
The function should return a value of OK.

Connecting to a SQL Server 2005 Database

Next, we need to create the function to generate the data set used to fill the ListObject object of the Excel worksheet.

First, import the necessary SQL-related namespaces. To do so, insert the following lines in the Web service code.

Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Imports System.Web.Services.Protocols

After importing the main SQL-related namespaces, you must create the function that creates the data set. This function, called RequestData, connects to the SQL database and launches a query that extracts data from the Sales.SalesOrderDetail table. It collects the sales amounts and adds them for the year. For example, it extracts and adds all the 2004 sales amounts.

Add the following function to your code.

<WebMethod(Description:="Returns dataset with information about orders", _
      EnableSession:=False, BufferResponse:=True, CacheDuration:=600000)> _
      Public Function RequestData() As DataSet

         Dim OrdersData As New DataSet   'This is the dataset to return
         Dim ConnectionToSql As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionSql"))
         ConnectionToSql.Open()
         Dim daLinks As New SqlDataAdapter("SELECT TOP (100) PERCENT SUM(Sales.SalesOrderDetail.LineTotal) AS Total, DATEPART(yy, Sales.SalesOrderHeader.OrderDate) AS Year FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID GROUP BY DATEPART(yy, Sales.SalesOrderHeader.OrderDate)ORDER BY Year", ConnectionToSql)
         daLinks.Fill(OrdersData, "View1")

         Return OrdersData
      End Function

After completing the Web service, save it and publish it on the Web server that includes Microsoft Internet Information Services (IIS) 6.0 and ASP.NET 2.0.

Note

You must set the connection string to the database in the web.config file.

Creating the Excel Worksheet

Next, we create an Excel worksheet that will generate the graph based on data extracted using the Web service.

First, in Visual Studio 2005, add an Excel project to the AdventureWorks project, and call it DiagramOrder. Visual Studio 2005 prompts you to create an empty Excel document named DiagramOrder.xls.
Click OK to create the document. The Excel IDE opens inside Visual Studio 2005.
Add a reference to the Web service you created previously. To do so, right-click the Excel project and select the Add Web Reference option. Visual Studio prompts you to find the Web service.
Select Web service in this solution option and name it AdvWebservice.
From the Visual Studio Toolbox, drag the ListObject control into the A-5 box of sheet1 of DiagramOrder.xls and call it List1.
Add a reference in the project to the namespace of the Web service, as follows:
```
Imports DiagramOrder.AdvWebservice
```

Insert the following code in the start up section of the worksheet.

'Verify that the Web service is online
      If AdvWebService.Verify.ToString = "OK" Then
         'If the Web service is online, create a new dataset and fill List1
         Dim ds As New DataSet
         ds = AdvWebService.RequestData

         List1.AutoSetDataBoundColumnHeaders = True 
         'Autoset cell
         List1.DataSource = ds
         List1.DataMember = "View1"
      Else
         'If the Web service is not online, Excel writes a message to the user
         MessageBox.Show("Attenction: The Web service is unreacheable", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
      End If

This code determines whether the Web service is online. If it is, it creates a data set and populates the List1 control that we created previously.
In the Excel worksheet, select the ListObject control. The control opens a menu that enables you to view the graph.

Figure 2. Launching the Graph Wizard
Click the Chart button to launch the Chart Wizard.
Select the 3-D Column and then click Next. Verify the data source.
Click the Series tab.
In the Name field, verify that the correct column heading is selected.
In the Value field, verify that the correct data is selected.
Type a Chart title and Axis titles, if desired, and then click Finish

Figure 3. Building the chart
Right-click the DiagramOrder project and select Debug.
Click Start New Instance. Visual Studio 2005 launches the application.

Figure 4. Sample Excel worksheet

Creating a Web Service to Extract Sales Data

Word 2003 uses a Web service to query the Adventure Works database for data to populate thank-you letters to salespeople for their annual sales.

This Web service includes four public functions:

Verify. Used by Word to verify whether the Web service is functioning correctly.
TerritoryReturn. Extracts all fields from the database.
ListEmployeeSales. Extracts all employee sales according to territory.
EmployeeInformation. Extracts all employee information according to ID.

The following procedure shows how to create the Web service to extract the sales data.

First, create a Web service in the Adventure Web Web project, called EmployeeSales.asmx.

Then, create the TerritoryReturn function that extracts all the territories from the database and returns a DataSet object that contains all the data of the territories.

'Return all territories from the database.
      <WebMethod(Description:="Return all Territory", _
         EnableSession:=False)> _
         Public Function TerritoryReturn() As DataSet
         Dim TerritoryTable As New DataSet
         Dim ConnectionToSQL As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionSql"))
         Dim daLinks As New SqlDataAdapter("SELECT Sales.SalesTerritory.*" _
         & "FROM Sales.SalesTerritory", ConnectionToSQL)

         ConnectionToSQL.Open()
         daLinks.Fill(TerritoryTable, "Territory")

         Return TerritoryTable

         ConnectionToSQL.Close()

      End Function

Next, create the ListEmployeeSales function that extracts all the sales employee data according to territory, and returns a DataSet object.

'Return a DataSet object with sales employee data according to territory.
      <WebMethod(Description:="Returns dataset with information about Sales Employee", _
      EnableSession:=False, BufferResponse:=True, CacheDuration:=600000)> _
      Public Function ListEmployeeSales(ByVal Territory As String) As DataSet

         Dim SalesData As New DataSet
         'This is the dataset to return.
         Dim ConnectionToSql As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionSql"))
         ConnectionToSql.Open()
         Dim daLinks As New SqlDataAdapter("SELECT Person.Contact.FirstName, Person.Contact.LastName, Sales.SalesTerritory.TerritoryID, HumanResources.Employee.EmployeeID " _
   & "FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID " _
   & "INNER JOIN Sales.SalesPerson AS SalesPerson_1 ON HumanResources.Employee.EmployeeID = SalesPerson_1.SalesPersonID " _
   & "INNER JOIN Sales.SalesTerritory ON SalesPerson_1.TerritoryID = Sales.SalesTerritory.TerritoryID " _
   & "WHERE (SalesPerson_1.TerritoryID = " & Territory & ")", ConnectionToSql)
         daLinks.Fill(SalesData, "SalesEmployeeTable")

         Return SalesData

         ConnectionToSql.Close()
      End Function

The last function to create extracts all the information relating to the employee (such as their name and address) according to their ID. The function returns a DataSet object with all the extracted information.

      'Return all information about each sales employee
      <WebMethod(Description:="Return all information about sales employee", _
      EnableSession:=False)> _
      Public Function EmployeeInformation(ByVal EmployeeID As String) As DataSet
         Dim EmployeeInfo As New DataSet
         Dim ConnectionToSQL As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionSql"))
         Dim daLinks As New SqlDataAdapter("SELECT Person.Contact.FirstName, Person.Contact.LastName, Sales.SalesTerritory.TerritoryID, HumanResources.Employee.EmployeeID, " _
         & "Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode, SalesPerson_1.SalesLastYear, SalesPerson_1.SalesQuota " _
         & "FROM Person.Contact INNER JOIN " _
            & "HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID INNER JOIN " _
         & "Sales.SalesPerson AS SalesPerson_1 ON HumanResources.Employee.EmployeeID = SalesPerson_1.SalesPersonID INNER JOIN " _
         & "Sales.SalesTerritory ON SalesPerson_1.TerritoryID = Sales.SalesTerritory.TerritoryID INNER JOIN " _
            & "HumanResources.EmployeeAddress ON HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID INNER JOIN " _
         & "Person.Address ON HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID AND " _
            & "HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID AND " _
         & "HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID AND " _
         & "HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID " _
         & "WHERE (HumanResources.Employee.EmployeeID = " & EmployeeID & ")", ConnectionToSQL)

         ConnectionToSQL.Open()
         daLinks.Fill(EmployeeInfo, "EmployeeInformation")

         Return EmployeeInfo

         ConnectionToSQL.Close()

      End Function

Last, you must republish the AdventureWeb Web project to a server running ASP.NET 2.0 and IIS 6.0 or IIS 5.0.

Note

To ensure the Web service is working, in Visual Studio 2005, you can launch a debug request. By launching using a localhost mode, you can insert data to verify that the Web service and applications work as intended.

Figure 5. The Employee Sales Web service user interface

Figure 6. The Employee Sales Web service

Creating the Word 2003 Document Template

The Word document template connects to the Web service and displays all the territories using a drop-down list control in a custom task pane. After choosing the territory, the user clicks Search to extract all the sales employees of that territory.

Another list control appears on the task pane containing employee's last names. The user clicks an employee name and then clicks Select Employee to extract the employee data and populate the Word document with the data.

After you create the Web service, you must create the (dynamic) document template in Word 2003 using Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio Tools for Office).

First, add a new Word 2003 project, written in Visual Basic .NET, to the AdventureWorks solution. Name this project CongratulationLetter.
Similar to the Excel project, Visual Studio 2005 prompts you to specify whether you want to base the project on an existing document or a new document.
Select Copy an existing document and then click Browse.
Select the CongratulationLetter.doc and then click OK.

Figure 7. Congratulations letter in Visual Studio

Similar to the Excel project, the Word 2003 IDE appears within Visual Studio 2005.

Note

Inside the Word document you see existing form fields (grey square brackets). These form fields are objects in Visual Studio 2005 Tools for Office and have properties associated with them. To see the property of a field, place the cursor inside the field.

Creating the Actions Control Task Pane

Next, we need to create the controls that load within the custom task pane in Word 2003.

In the Word 2003 IDE, right-click the Solution Explorer and add a new folder called TaskPane.
In the TaskPane folder, add a new Item. Choose Actions Pane Control type and call it TaskMenu.vb.
Add the following controls to this object:
1. N° 2 DropDownList
2. N° 3 Label
3. N° 2 Button
Figure 8 shows the task pane after you add these controls.

Figure 8. Sample task pane in Word 2003

Before the TaskMenu.vb control, add the following code to the first DropDownList:

   Private Sub TaskMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim Ds As New DataSet

      Ds = AdvWebService.TerritoryReturn

      ListTerritory.DataSource = Ds
      ListTerritory.DisplayMember = "Territory.Name"
      ListTerritory.ValueMember = "Territory.TerritoryID"

   End Sub

Then create the function for the two buttons.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      ListEmployee.Visible = True
      Label3.Visible = True

      Dim Ds2 As New DataSet

      Ds2 = AdvWebService.ListEmployeeSales(ListTerritory.SelectedValue.ToString)
      ListEmployee.DataSource = Ds2
      ListEmployee.DisplayMember = "SalesEmployeeTable.LastName"
      ListEmployee.ValueMember = "SalesEmployeeTable.EmployeeID"

   End Sub

   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
      Dim Ds3 As New DataSet
      Ds3.Clear()
      Ds3 = AdvWebService.EmployeeInformation(ListEmployee.SelectedValue.ToString)

      Dim FName As New Binding("Text", Ds3, "EmployeeInformation.FirstName")
      Dim LName As New Binding("Text", Ds3, "EmployeeInformation.LastName")
      Dim AddressL As New Binding("Text", Ds3, "EmployeeInformation.AddressLine1")
      Dim PCode As New Binding("Text", Ds3, "EmployeeInformation.PostalCode")
      Dim City As New Binding("Text", Ds3, "EmployeeInformation.City")
      Dim DearName As New Binding("Text", Ds3, "EmployeeInformation.FirstName")
      Dim TotalSales As New Binding("Text", Ds3, "EmployeeInformation.SalesLastYear")
      Dim TotalRevenue As New Binding("Text", Ds3, "EmployeeInformation.SalesQuota")


      CongratulationLetter.Globals.ThisDocument.FirstName.DataBindings.Clear()
      CongratulationLetter.Globals.ThisDocument.FirstName.DataBindings.Add(FName)

      CongratulationLetter.Globals.ThisDocument.LastName.DataBindings.Clear()
      CongratulationLetter.Globals.ThisDocument.LastName.DataBindings.Add(LName)

      CongratulationLetter.Globals.ThisDocument.address.DataBindings.Clear()
      CongratulationLetter.Globals.ThisDocument.address.DataBindings.Add(AddressL)

      CongratulationLetter.Globals.ThisDocument.zip.DataBindings.Clear()
      CongratulationLetter.Globals.ThisDocument.zip.DataBindings.Add(PCode)

      CongratulationLetter.Globals.ThisDocument.city.DataBindings.Clear()
      CongratulationLetter.Globals.ThisDocument.city.DataBindings.Add(City)

      CongratulationLetter.Globals.ThisDocument.Dear.DataBindings.Clear()
      CongratulationLetter.Globals.ThisDocument.Dear.DataBindings.Add(DearName)

      CongratulationLetter.Globals.ThisDocument.TotalSales.DataBindings.Clear()
      CongratulationLetter.Globals.ThisDocument.TotalSales.DataBindings.Add(TotalSales)

      CongratulationLetter.Globals.ThisDocument.revenue.DataBindings.Clear()
      CongratulationLetter.Globals.ThisDocument.revenue.DataBindings.Add(TotalRevenue)

   End Sub

Then add the following code inside the subroutine "ThisDocument_Startup".

      'Add to Taskpane the usercontrol TaskMenu.vb
      Me.ActionsPane.Controls.Add(ActionMenu)
      Me.ActionsPane.Visible = True

You just added the custom task pane and the TaskMenu control to the Word document template.

Launch the debug version of the CongratulationLetter.doc application and notice how the data in the Word document changes when you change the territory and the employee data.

Figure 8. Sample congratulation letter

Conclusion

This article shows how you can integrate Office 2003 applications using Web services in an ASP.NET 2.0 Web site. Imagine other possible applications you can create for other scenarios such as invoices, transport documents, and more.

Additional Resources

For more information, see the following resources:

About the Author

Fulvio Giaccari is a project manager for the SB Soft S.r.l. Company, a Microsoft Certified Partner. He manages Microsoft Office and Web projects for private companies and public administrations. He has published several articles in Italian programming magazines and is currently working on a book about Visual Studio Tools for Office. In addition, Fulvio is a guest speaker at various Microsoft-related events dedicated to Microsoft Office. Fulvio also hosts a Web community dedicated to ASP.NET (www.freeaspx.it) and one of the first Italian user groups in the Microsoft Office developer community (www.ShareOffice.it).