Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Integrating Excel and Word with ASP.NET 2.0 Web Sites

Office 2003

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

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

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

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

  1. In Visual Studio 2005, add an ASP.NET Web Service project called AdventureWeb to the Adventure Works project.

  2. 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.

  3. Next, create a function called Verify.

    NoteNote

    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.

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

  1. 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
    
    
  2. 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.

  3. 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
    
  4. 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.

    NoteNote

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

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

  1. 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.

  2. Click OK to create the document. The Excel IDE opens inside Visual Studio 2005.

  3. 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.

  4. Select Web service in this solution option and name it AdvWebservice.

  5. From the Visual Studio Toolbox, drag the ListObject control into the A-5 box of sheet1 of DiagramOrder.xls and call it List1.

  6. Add a reference in the project to the namespace of the Web service, as follows:

    Imports DiagramOrder.AdvWebservice
    
    
  7. 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
    
    
  8. 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.

  9. 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

    Launching the Graph Wizard
  10. Click the Chart button to launch the Chart Wizard.

  11. Select the 3-D Column and then click Next. Verify the data source.

  12. Click the Series tab.

  13. In the Name field, verify that the correct column heading is selected.

  14. In the Value field, verify that the correct data is selected.

  15. Type a Chart title and Axis titles, if desired, and then click Finish

    Figure 3. Building the chart

    Building the chart
  16. Right-click the DiagramOrder project and select Debug.

  17. Click Start New Instance. Visual Studio 2005 launches the application.

    Figure 4. Sample Excel worksheet

    Sample Excel worksheet

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.

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

  2. 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
    
    
  3. 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
    
    
  4. 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
    
    
  5. Last, you must republish the AdventureWeb Web project to a server running ASP.NET 2.0 and IIS 6.0 or IIS 5.0.

    NoteNote

    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

The Employee Sales Web service user interface
Figure 6. The Employee Sales Web service

The Employee Sales Web service

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).

  1. First, add a new Word 2003 project, written in Visual Basic .NET, to the AdventureWorks solution. Name this project CongratulationLetter.

  2. 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.

  3. Select Copy an existing document and then click Browse.

  4. Select the CongratulationLetter.doc and then click OK.

    Figure 7. Congratulations letter in Visual Studio

    Congratulations letter in Visual Studio

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

NoteNote

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.

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

  1. In the Word 2003 IDE, right-click the Solution Explorer and add a new folder called TaskPane.

  2. In the TaskPane folder, add a new Item. Choose Actions Pane Control type and call it TaskMenu.vb.

  3. Add the following controls to this object:

    1. N° 2 DropDownList

    2. N° 3 Label

    3. N° 2 Button

  4. Figure 8 shows the task pane after you add these controls.

    Figure 8. Sample task pane in Word 2003

    Sample task pane in Word 2003
  5. 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
    
    
  6. 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
    
    
  7. 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

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).

Show:
© 2014 Microsoft