Chapter 6: Integrating Spreadsheets into the Enterprise

This article is an excerpt from Pro SharePoint Solution Development: Combining .NET, SharePoint and Office 2007, from Apress (ISBN 978-1590598085, copyright Apress 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Download Sample Code that Accompanies the Book

Information workers have become accustomed to modeling business calculations with spreadsheets in Microsoft Office Excel. These spreadsheets may be updated on a time interval and distributed throughout the organization. This distribution is likely through email. There are many consequences to this strategy. Since there are so many copies of the spreadsheet, the organization loses its sense of what the authoritative version is. The spreadsheets themselves may be very large and laden with computations that make it unresponsive on an average desktop computer. In addition, the distributed spreadsheet contains the formulas and calculation logic that may be intellectual property that needs to be protected. Microsoft Office SharePoint Server 2007 provides a new application service called Excel Services that supports the publishing of such a spreadsheet so that users can view it using only their Web browser. This means that there is one version of the truth, no large files to distribute, and the spreadsheet is processed on the server using its resources rather than the user's desktop.

Those advantages are usually enough to pique the interest of an organization, but developers will alert the business that such spreadsheets are usually stand-alone and don't play a major role in the enterprise's applications. Often users are copying values from legacy systems into the spreadsheets. There may be organizational systems that should impose their logic on the spreadsheet that are left out. And most importantly, the spreadsheet's calculation is not reusable. Developers often have to dissect the spreadsheet to incorporate the same logic into their applications.

For this chapter, we will detail how, as a developer, you can enhance Excel Services to incorporate methods of a .NET class. This way, the spreadsheet will be able to connect to external systems, databases, and even Web services. Plus the methods can perform calculations that are not possible with the Excel native functions. We will also show you how the spreadsheet’s calculation logic can be incorporated into a custom application without removing the possibility of the spreadsheet being modified by a business user.

Contents

  • Real-World Examples

  • Solution Overview

  • Solution Walkthrough

  • Important Lessons

  • Extension Points

  • Further Reading

Real-World Examples

We have encountered many customers whose users maintain and distribute large numbers of spreadsheets as part of the daily business process. Teams that coordinate floors of a hospital maintain spreadsheets of resources allocated to rooms. Housing companies maintain goals for production schedules. We have even worked with a military branch that uses Excel to record all the open positions and assignments of officers. In every instance, the maintenance of this data was a manual process and productivity was lost when users were unaware that they had an out-of-date version of the file. Even if the organization decides to centralize the spreadsheets in a file share or a document library, there is still the issue of download times for branch offices since the whole Excel file must be transported to the user before it will open. As a consultant, it is not unusual to walk into a requirements meeting where the customer has a pile of spreadsheet files that “show” what an application has to do. Of course, this logic is almost never complete and the calculations change over time. Unfortunately, developers often bake this calculation logic into their application, making it necessary to continually release updates to accommodate them. Decoupling this logic from the application would make it possible to decrease maintenance costs.

Solution Overview

For this solution, we will work with a spreadsheet that contains calculations for pricing a product. This begins with the base price it costs the company to manufacture the product. This price incorporates costs associated with raw materials, energy, etc. There is then a percentage markup for a retail cost. Customers may get a volume discount based on the number of items they are placing in the order. Sales tax as well as shipping and handling costs must also be incorporated into the calculation.

The solution integrates this spreadsheet into the enterprise by registering methods of a .NET class so that Excel can call them as it would native functions. These methods will show how you can incorporate calls to external systems such as databases and Web services. This connects the spreadsheet into other enterprise resources so that it is using accurate data as part of its calculations.

Once extended, the spreadsheet is published to Microsoft Office SharePoint Server Excel Services so that users can view and interact with the spreadsheet through their Web browser. This technique guarantees that there is a single, authoritative version of the spreadsheet. It also reduces the workload on the viewer's desktop since the spreadsheet's calculations are performed on the server. Additionally, it protects the logic in the spreadsheet by not exposing formulas or workbook elements that an end user doesn't need to see, and in fact we can leverage the Office SharePoint Server item-level security to prevent direct access to the original spreadsheet file. Even after the spreadsheet has been published, we will be able to support a properly authorized business user opening the spreadsheet and tweaking the calculations such as the markup percentage and the volume discount table.

Last, the solution will show you how developers can incorporate the spreadsheet's calculation logic in their custom applications. This approach increases the flexibility of their solution since the calculation logic remains in the spreadsheet and is not hard-coded into the custom application. In fact, authorized business users can continue to edit the spreadsheet after the custom solution has been deployed. This integration is possible because of the Excel Web Services API.

Solution Walkthrough

This section will detail the major elements of the solution and the decisions that were made in coding it. The walkthrough will introduce you to the product-pricing spreadsheet we will use as our example. We will construct two external sources for the spreadsheet to communicate with: a products database containing base price information, and a sample Web service used to calculate shipping costs. We will go into detail about how to build a .NET class whose methods will be exposed as Excel functions. We will also show you the necessary COM plumbing that the class needs so it can be used in Excel 2007, as well as the configuration tasks needed for it to run in Excel Services. We will then walk you through publishing the spreadsheet to Excel Services. Finally, we will show how a custom application can interact with it through Web services.

Introducing the Spreadsheet

For our example, we will be working with an instance of the Product Pricing Calculator spreadsheet template. This spreadsheet, shown in Figure 6-1, provides a way of capturing the rules and calculations involved in determining a discounted price for a product. These rules include the percentage markup from the company's base price and volume discounts, as well as calculations for shipping and sales tax.

Figure 6-1. The Product Pricing Calculator spreadsheet

Product pricing calculator spreadsheet

This template can be obtained from Microsoft Office Online. From the New Workbook dialog box in Excel 2007, select More Categories and then Calculators to locate it. In case you have difficulty finding this template, we have included a copy in the Source Code/Download section of the Apress Web site. The file is named product pricing calculator.xlsx and is stored in a Starter Files directory for this chapter. If you download your own from Microsoft Office Online, make sure you save your spreadsheet in the Office 2007 file format with an .xlsx extension. You will also want to make sure that you are not running Excel in the backwards-compatibility mode. You can tell the mode you are in by looking at the title bar of the Excel window, as shown in Figure 6-2. You may need to close and reopen your spreadsheet in Excel after saving it in the 2007 file format to get out of this mode.

Figure 6-2. Determining Excel compatibility mode

Determining Excel compatibility mode

Our vision is that this spreadsheet will be integrated into the enterprise. This includes connectivity to back-end services as well as custom applications. For this reason we will be extending the rules in this spreadsheet to increase its touch points with other systems. Remember that we still want our business user to be able to tweak the rules within their control; however, there are several places where the company likely already has other systems in place that should influence values on this spreadsheet.

For example, it is reasonable to assume that the company would have a products system that stores its current base-unit costs. These likely change as costs fluctuate for raw materials and energy. Instead of having the base price of the product as a static data element, as it is in cell D11, we will extend Excel to retrieve this value from a SQL Server database.

The shipping cost in cell D27 is also a static value. It isn't realistic to think that the cost of shipping would remain the same regardless of where the customer is located and the number of items in the order. For this reason, we will add a shipping worksheet that captures the zip codes of the customer and the company’s warehouse as well as calculations for weight and handling fees. This shipping worksheet is shown in Figure 6-3.

Figure 6-3. The shipping worksheet

The shipping worksheet

To calculate the shipping cost, we will rely on a Web service call. There are many different providers of such services (UPS and the United States Postal Service, for instance), so this is a reasonable calculation method. We don’t want you to have to set up accounts for these services and we don't anticipate that every development environment will have Internet access. For this reason, we will build a test shipping Web service that takes the zip codes and weight as inputs and returns a cost. Of course, this service will not do any heavy lifting of trying to calculate distance; instead it will just return different costs for our different sets of test data.

The Per Unit Discount value of 10% is also a static value. Here we want to increase the flexibility of the rule and allow our business user to maintain a table that sets different discounts based on the number of items purchased. Later in this chapter we will show you how to incorporate a discount table that will be maintained on its own worksheet. Figure 6-4 shows a sample of this table, which specifies a different percentage discount based on the quantity of items in the order.

Figure 6-4. The volume-discount table

The volume-discount table

To round out our different techniques of leveraging managed code in the Excel spreadsheet, we will have the sales tax value in cell D25 be set based on the result of .NET class's method. Where the other examples include database or Web service calls, this one will just be a single function of the class.

Despite taking over these elements of the spreadsheet by integrating them with back-end systems, the business user will still be able to have control over critical calculations. The initial markup value is a percentage that they can adjust. The business user can also change the end points of the volume-discount table. Our goal in this solution is to achieve a high level of integration while maintaining flexibility. In fact, our custom application that consumes this spreadsheet will be able to implement the business user’s adjustments immediately.

Setting up an External Source: Products Database

For our sample products system, we will use a Microsoft SQL Server 2005 database named ProductsTest. This database stores details of the company’s products and, in particular, the current base price. The code download at http://www.apress.com includes the database's data and log file, which you can attach to your development box using SQL Server Management Studio. Figure 6-5 is a diagram of the database showing the product table.

Figure 6-5. Diagram of the ProductsTest database

Diagram of the ProductsTest database

The database also includes a stored procedure. The GetProductBasePrice stored procedure is used to retrieve a particular row of data from the product table. For this reason, the stored procedure accepts a ProductCode as a parameter and returns the matching row. Listing 6-1 details this procedure.

Listing 6-1. The GetProductBasePrice Stored Procedure

CREATE PROCEDURE [dbo].[GetProductBasePrice]
          @ProductCode varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT BasePrice FROM Product
    WHERE ProductCode = @ProductCode
END

To set up the database in your environment, copy MDF and LDF files of the ProductsTest database to your server running SQL Server. If you did a default install, SQL Server 2005 is using the following path to store these database files: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Once they are in place, right-click on the Databases node of the Management Studio Object Explorer and select Attach. In the dialog box, click Add and locate the ProductsTest.mdf file. The default options are fine. Your dialog box should look like Figure 6-6. Click OK; the Object Explorer will refresh to include the ProductsTest database.

Figure 6-6. Attaching the ProductsTest database

Attaching the ProductsTest database

If you need more help attaching databases in SQL Server 2005, use the steps outlined on MSDN.

Setting up an External Source: Shipping Web Service

In a real-world solution, the calculation of shipping cost would likely rely on a Web service such as the ones provided by UPS and the United States Postal Service. Instead, for this chapter we will create our own service to simulate this calculation for the sets of test data we are interested in. Typically, shipping costs are a function of the zip codes of the beginning and end points as well as the weight of the shipment. For this reason we will create a TestShippingService ASP.NET Web service project.

This ASP.NET service can be created as its own Visual Studio solution or as an added project with the others in this chapter. If you want to match the way we set them up in the code download, you will first want to create a Visual Basic 2005 class library project named ProductPricingCalc in a solution of the same name. The class library project will be used for our managed code functions for Excel and will be detailed in the next section of the chapter. With the ProductPricingCalc solution created, you can add the Visual Basic ASP.NET Web service project by using the following steps:

  1. On the Visual Studio File menu, click Add New Web Site.

  2. Select the ASP.NET Web Service project template.

  3. Make sure that the Location drop-down is set to File System and that Language is set to .

  4. Specify the path to be a folder within the ProductPricingCalc solution. We used C:\Projects\ProductPricingCalc\TestShippingService in our solution.

  5. Click OK.

When the project is added, Visual Studio will add the default files Service.asmx and a Service.vb code file that is contained in the App_Code directory. Delete both of these and add a new ASP.NET Web service item named Shipping.asmx. The corresponding code file in the App_Code folder will be added for you. The Web service needs exactly one method that accepts two zip codes and the total weight as input parameters. The method will return a price. Again, we are just simulating the calculation here, so we will create a few paths through the method for a Boston zip code of 02108 and an zip code of 32803. We will also return different costs if the weight is over 70 pounds. Listing 6-2 details this method.

Listing 6-2. The Web service method for calculating shipping

<WebMethod()> _
Public Function CalcShipping(ByVal startZipCode As String, _
    ByVal endZipCode As String, ByVal totalWeight As Double) _
        As Double

    Dim retVal As Double = -1
    Select Case endZipCode
        Case "02108"
            If (totalWeight <= 70) Then
                retVal = 13.74
            Else
                retVal = 23.74
            End If
        Case "32803"
            If (totalWeight <= 70) Then
                retVal = 17.56
            Else
                retVal = 27.36
            End If
        Case Else
            Throw New ApplicationException("We do not ship to that location")
    End Select
    Return retVal
End Function

Once it's coded, you can test the Web service by right-clicking on the Shipping.asmx file and selecting View in Browser. This will cause the Visual Studio local Web server to run and a browser window will be opened to the Web service. You can tell that the Web server is running by the additional ASP.NET Development Server icon in the system tray. Notice that the URL is a specific port off of localhost. Remember this port—we will need to use this information to call the Web service. We assume that you are developing with Visual Studio on your server running Office SharePoint Server and the localhost reference will be good enough for Excel Services to call. If not, you will want to use Visual Studio to publish this Web service to a new Internet Information Services (IIS) Web site. Figure 6-7, shows what the test client will look like in the browser window. Be sure to leave the ASP.NET Development Server running, or your calls to this service will go unanswered.

Figure 6-7. Testing the shipping Web service

Testing the shipping Web service

Constructing the UDF Class

User-defined functions (UDFs) provide a developer the opportunity to extend Microsoft Excel calculation capabilities. To build a UDF, you construct a .NET class appropriately decorated with UDF attributes. Methods of this class that are marked with the UdfMethod attribute are then available to be invoked from cells in Excel, just like the native functions. A developer would look to construct a UDF when a spreadsheet needs a particular function that is not provided natively in Excel or when the spreadsheet must interact with external data provided by custom applications. This data could be retrieved from databases and even Web services. Both Microsoft Office Excel 2007 and the Excel Services of Office SharePoint Server 2007 support UDF classes. However, the deployment methods are dramatically different since the Excel client relies on COM plumbing and Excel Services relies on .NET assemblies. In this section, we will focus on the construction of the class, the UDF attributes, and the COM plumbing. Following this section we will detail the deployment steps for both Excel and Excel Services.

If you are following along, you have already created a ProductPricingCalc solution containing the Web service we've discussed, as well as a ProductPricingCalc class library project that we have not touched on yet. This project will compile into the DLL assembly that will contain the UDF methods. Delete the default class file and add new one named ProductPricingUdf.vb. To have access to the UDF attributes, you will need to add a reference to the Microsoft.Office.Excel.Server.Udf assembly. If you are developing a single server running Office SharePoint Server, this assembly should be on the .NET tab of the Visual Studio Add Reference dialog box, but if it is not, the default location for this assembly is C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI. You will find this assembly only on a computer that has Excel Services. If you are developing remotely, you can copy this assembly to your development environment. With the reference set up, add the Import statements in Listing 6-3 to the top of the class file; that'll give us easy access to the UDF attributes, SQL Server ADO.NET objects, and the namespaces containing the classes for the COM plumbing.

Listing 6-3. Import statements for namespaces used in the UDF class

Imports Microsoft.Office.Excel.Server.Udf
Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Imports System.Data.Sql
Imports System.Data.SqlClient

Though it is not required, we will want our assembly to have a strong name. This opens up the deployment option of adding our assembly to the global assembly cache and is also recommended for classes that are going to be COM-registered in the Windows registry. To give your assembly a strong name, do the following:

  1. Right-click on the ProductPricingCalc project in Solution Explorer and select Properties.

  2. Select Signing in the left-hand navigation.

  3. Select the check box to sign the assembly. The drop-down directly below the check box will enable. Figure 6-8 depicts this choice.

    Figure 6-8. Signing the assembly

    Signing the assembly

  4. Select New.

  5. For the name of the file, type in key.

  6. Clear the check box for protecting the file with a password.

  7. Click OK.

  8. Click the Visual Studio Save toolbar button to commit this setting.

First we'll detail the class method that connects to the products database we added earlier. This method will be responsible for retrieving the base price of a product based on its product code (the table's primary key). Earlier you saw that the GetProductBasePrice stored procedure provided this capability. Before looking at the code of the method, we have to deal with where to store the connection string to the database. Ideally, we don't want to specify this in compiled code. Instead we will store this information as a setting that will be stored separately in a ProductPricingCalc.dll.config file. Use the following steps to add the connection-string setting:

  1. Right-click the ProductPricingCalc project in Solution Explorer and select Properties.

  2. Select Settings in the left-side navigation.

  3. Add an item to the grid. Set the Name column to ConnectionString. Set the Type as String. Set the Scope as Application. For the Value, specify the connection string to your SQL Server and the ProductsTest database. Be sure to use values that match your environment. The connection string should be in this format: Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ProductsTest;Data Source=SQLServerName

  4. Click the Visual Studio Save button to commit the setting.

The GetProductBasePrice method receives a string that contains the product's code as an input parameter and returns the product's base price stored in the external database. Notice the use of the UdfClass and UdfMethod attributes in Listing 6-4. These attributes make this class and this method available to Excel Services (the COM portion handles the Excel client). If you forget to add them, then the spreadsheet will not be able to call the method. The ADO.NET code is rather uneventful other than the retrieval of the connection string from the My.MySettings class. When the command object is executed, notice that an ExecuteScalar method is used. This is done since we are expecting only a single value ever to be returned. In a production system, you should incorporate your organization's technique for handling and logging exceptions. Here we just print it to the debug window and rethrow the exception.

Listing 6-4. The GetProductBasePrice method

<UdfClass()> _
Public Class ProductPricingUdf

<UdfMethod()> _
Public Function GetProductBasePrice(ByVal productCode As String) As Double
    Dim retVal As Double
    Dim conn As SqlConnection = Nothing
    Try
        conn = New _ 
            SqlConnection(My.MySettings.Default.ConnectionString)
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = conn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "GetProductBasePrice"
        Dim param As SqlParameter = New SqlParameter()
        param.DbType = SqlDbType.VarChar
        param.Direction = ParameterDirection.Input
        param.IsNullable = False
        param.ParameterName = "@ProductCode"
        param.Size = 50
        param.Value = productCode
        cmd.Parameters.Add(param)
        conn.Open()
        retVal = cmd.ExecuteScalar()
        conn.Close()
        Return retVal
    Catch ex As Exception
        Debug.Print(ex.Message)
        Throw (ex)
    Finally
        If (conn IsNot Nothing AndAlso _
            conn.State <> ConnectionState.Closed) Then
                conn.Close()
        End If
    End Try
End Function

Remember that we defined shipping costs as a function of the distance of the customer from the warehouse and the weight of the shipment. We also created a simulation Web service for testing in a development environment. Make sure that the ASP.NET Development Server is still running. If it is not, you can start it by right-clicking on your Shipping.asmx file and selecting View in Browser. With the Web service available, we will add a Web reference to the ProductPricingCalc project:

  1. In the Visual Studio Solution Explorer, right-click on the ProductPricingCalc project and select to Add Web Reference.

  2. Type in the URL to your Web service. You should be able to retrieve this from the browser window where you were testing the Web service. The chosen port will be different in different environments. For our development computer it was https://localhost:1180/TestShippingService/Shipping.asmx.

  3. Name the Web reference ShippingService and click Add Reference.

Visual Studio will communicate with the Web service, retrieve its WSDL, and generate a proxy class for you. Just like the database-connection string, we do not want the Web service's URL to be hard-coded. If you examine the properties of the Web reference, you should see that its URL Behavior is set to Dynamic. This means that the URL of the Web service is stored in the same settings file we used for the connection string. Figure 6-9 shows the information that is automatically written there.

Figure 6-9. Examining the project’s configuration settings

Examining the project configuration settings

The GetShipping method in Listing 6-5 calls the Web service. This method is decorated with the UdfMethod attribute so that it will be callable from Excel Services. The URL is retrieved from the configuration settings and assigned to the URL property of the proxy class.

Listing 6-5. The GetShipping method

<UdfMethod()> _
Public Function GetShipping(ByVal warehouseZipCode As String, _
ByVal customerZipCode As String, ByVal totalWeight As Double) _
As Double
    Dim retVal As Double = -1
    Try
        Dim service As ShippingService.Shipping = New _   
            ShippingService.Shipping()
        service.Url = _
    My.MySettings.Default.ProductPricingCalc_ShippingService_Shipping
        retVal = service.CalcShipping(warehouseZipCode, customerZipCode, totalWeight)
    Return retVal
Catch ex As Exception
    Debug.Print(ex.Message)
    Throw (ex)
End Try
End Function

You can obtain the GetSalesTax method from the code download at http://www.apress.com. It is not nearly as interesting as the other two UDF methods, as it tests only for our two different zip codes and returns 5% for Boston and 6% for . But remember that Excel is calling this method, and within it you have access to the whole .NET Framework. This means that you could perform complex operations and logic that are not available in Excel natively.

If you were going to use this spreadsheet only in Excel Services and you didn’t mind not seeing any results as you build the spreadsheet in the Excel client, you would be done with this class. However, our vision is one where a business user can continue to use the Excel spreadsheet to tweak the calculations. Therefore these UDF methods need to be available in the client as well as in Excel Services. This means that we will have to COM-register this assembly. This will require a bit more code to be written in the class. First the class must be marked as COM-visible and decorated with other COM attributes (shown in Listing 6-6). Notice that the ClsId and ProgId values are new constants containing the full name of the class as well as a unique GUID. Specifying both of these values is a best practice and makes the registration process predictable and repeatable as opposed to some autogenerated values.

Listing 6-6. Adding COM attributes to the UDF class

<UdfClass()> _
<Guid(ProductPricingUdf.ClsId)> _
<ProgId(ProductPricingUdf.ProgId)> _
<ClassInterface(ClassInterfaceType.AutoDual)> _
<ComVisible(True)> _
Public Class ProductPricingUdf
Public Const ClsId As String = "C1C9EC29-16CB-4b13-9698-15810ACD3389"
Public Const ProgId As String = "ProductPricingCalc.ProductPricingUdf"

In addition to the class-level attributes, we will need to add two methods to control the registry information that is added or removed when this class is registered or unregistered. Listing 6-7 contains the Registration and Unregistration methods. You will see the impact of this code when we use regasm to register this class with the Windows registry (in the following section).

Listing 6-7. Methods for COM registration

<ComRegisterFunction()> _
Public Shared Sub RegistrationMethod(ByVal t As Type)
    If (GetType(ProductPricingUdf) IsNot t) Then
        Exit Sub
    End If
    Dim key As RegistryKey = Registry.ClassesRoot. _
        CreateSubKey("CLSID\{" & ClsId & "}\Programmable")
    key.Close()
End Sub

<ComUnregisterFunction()> _
Public Shared Sub UnregistrationMethod(ByVal t As Type)
    If (GetType(ProductPricingUdf) IsNot t) Then
        Exit Sub
    End If
    Registry.ClassesRoot.DeleteSubKey("CLSID\{" & ClsId _
& "}\Programmable")
End Sub

Build the ProductPricingCalc project. This will create the ProductPricingCalc.dll assembly as well as the ProductPricingCalc.dll.config settings file. Take the time to navigate to where these files were built and open the configuration settings file in Notepad. Notice how these values could be changed without your having to recompile the solution.

Deploying the UDF Class and Registering It for Use in Excel

For this section we expect that you are using Visual Studio and Excel on the same computer (or virtual computer) as your server running Office SharePoint Server. Though this is not required, some of the steps here, like the use of a UDFs folder on the local drive of the computer, are done more for the sake of Excel Service than Excel. Regardless, this UDF class you constructed earlier must be COM-registered for it to work within the Excel client. Use the following steps to complete the configuration for use in Excel:

  1. Create a folder named UDFs at the root of your local drive (C:\UDFs).

  2. Copy the ProductPricingCalc.dll and ProductPricingCalc.dll.config files from your build directory to the UDFs folder. Remember that this location contains the assembly that the client will be executing just in case you want to make some changes and update it.

  3. From a Visual Studio command prompt, navigate to the UDFs directory and type the following command: regasm /codebase ProductPricingCalc.dll

The register assembly command will write the Windows registry subkey information we specified in Listing 6-7. You can see the impact of this registration by opening the Windows registry and locating the subkey. To open the registry, type regedit in the Run dialog box. Expand HKEY_CLASSES_ROOT and its CLSID folder. This location is organized by the GUIDs assigned via the CLSID attribute. Locate the GUID for the ProductPricingUdf class. If you used the same one provided here, it is {C1C9EC29-16CB-4b13-9698-15810ACD3389}. Figure 6-10 shows this registry information. Make sure the Programmable node is present in your environment. If not, Excel will not make your class available to be loaded.

Figure 6-10. Viewing the Windows registry information for the UDF class

View Windows registry information for UDF class

If you want to remove the registry information (to try again), you can use the following command to unregister the class: regasm /unregister ProductPricingCalc.dll.

Once the class is registered, you will need to load it in the Excel client. Open your spreadsheet in Excel 2007 and from the File menu, choose the Excel Options button. From the Add-Ins tab, make sure the Manage drop-down at the bottom is set to Excel Add-ins and click the Go button. In the new dialog box, click the Automation… button, which will bring up a list of COM automation servers. Locate our class in this list. It will be named ProductPricingCalc.ProductPricingUdf. If it is not listed, then there was an error in the COM registration. Check the Windows registry and the COM RegistrationMethod in the class. Select our class in the list and click OK. You will get a warning about mscoree.dll that you can ignore. Click No in response to the warning since we do not want the file removed. Figure 6-11 shows the final result of this work in the Add-Ins dialog box. You should also confirm that the class is loaded in the Add-Ins tab of the Excel Options dialog box.

Figure 6-11. Loading the UDF class in Excel 2007

Loading the UDF class in Excel 2007

If you would like to avoid the dialog box containing the mscoree.dll warning, you will need to create a shim for your managed code to run through COM Interop. You can find more information on techniques to create the shim at the following URLs:

Enhancing the Spreadsheet

With the UDF class loaded in the Excel client, we can now begin to incorporate it into the spreadsheet and make the enhancements we detailed at the beginning of this walkthrough. (If you do not want to follow along and make these changes yourself, you can use the completed spreadsheet included in the code download.) Before going in and modifying formulas, we want to specify named ranges for certain cells that we will refer to often. To name a cell/range, do the following:

  1. Click the cell (or highlight the range) you want to name.

  2. Click the Formulas tab in the Microsoft Office Fluent UI Ribbon.

  3. Click the Define Name button in the Defined Names group.

  4. Specify the name and the scope, and click OK.

Table 6-1 details the named ranges that we want set up in the spreadsheet. You do not have all of these cells in your worksheet yet, but we wanted to present this as a single source for you to refer to. For all of our named ranges, use Workbook as the scope. We also specified the format we used for the cells.

Table 6-1. Named ranges used in the spreadsheet

Name Format Worksheet Cell Referred To

ProductCode

General

Pricing Calculator

C8

BaseUnitCost

Accounting

Pricing Calculator

D11

CustomerZipCode

Text

ShippingSheet

B3

DiscountedTotal

Accounting

Pricing Calculator

E29

HandlingFee

Currency

ShippingSheet

B7

OrderQuantity

General

Pricing Calculator

D15

PerUnitDiscount

Percentage

Pricing Calculator

E17

SalesTax

Percentage

Pricing Calculator

D25

ShippingAndHandling

Accounting

Pricing Calculator

D27

Total

Accounting

Pricing Calculator

D29

TotalShipping

General

ShippingSheet

B6

TotalWeight

General

ShippingSheet

B5

WarehouseZipCode

Text

ShippingSheet

B2

Begin to change the spreadsheet by deleting the worksheet that contains the chart. We will not use it as part of the example.

Add a worksheet named DiscountSheet. Build the discount table that was shown in Figure 6-4. Be very careful of formatting in Excel. The % Discount cells are formatted as a Percentage with two decimal places. You should not be typing in the % character yourself. You can access the format options by highlighting the cell, right-clicking, and selecting the Format Cell option from the context menu. Now go back to the Per Unit Discount cell (E17) on the Pricing Calculator worksheet and change it from a static value to a function. In this case, the function should be a vertical lookup on the discount table. The VLOOKUP function performs this action in Excel, looking for a match in the first column. If a match is not found, it matches against the greatest number that's less than the specified value. The function you need to enter is =VLOOKUP(OrderQuantity,DiscountSheet!A3:B8,2). This formula specifies the order quantity as the input parameter, the range of the discount table, and that the value in the second column of the table should be returned. Test this method by changing the order quantity so that the discount should change.

To incorporate the UDF method that queries the products database, modify the BaseUnitCost cell on the Pricing Calculator spreadsheet (cell D11). Instead of the static value, enter the following formula: =GetProductBasePrice(ProductCode). Notice how we can call the method of the UDF class just as if it were a native Excel function. There are two possible errors you could run into. If the cell displays a #NAME? value, then it is having difficulty locating your UDF class and method. If the cell displays #VALUE!, then an exception was encountered while calling your method. This could be because the input and return parameters of the method may not have matched what Excel expected or an exception within the execution of the method was encountered.

Note

At the end of this section we discuss debugging in the Excel client. The #NAME? and #VALUE! error values are also used by Excel Services. However, Excel and Excel Services rely on different types of casting for their parameters. Excel relies on casting in COM, whereas Excel Services relies on .NET casting. This means that it is possible to experience problems in only one of the environments. Make sure you test both fully.

Add a worksheet named ShippingSheet. Build the shipping worksheet that was shown in Figure 6-3. Be certain to format the zip codes as text. Excel should display a green corner in these cells warning you that you are formatting a number as text. Since zip codes are not used for arithmetic and we want leading zeros, this is fine. Be sure to use only the Boston (02108) and (32802) zip codes that we have incorporated into the test shipping Web service. The formula for TotalWeight is =OrderQuantity*ShippingSheet!B4. To incorporate the call to the Web service, set the TotalShipping cell's formula to =GetShipping(WarehouseZipCode,CustomerZipCode,TotalWeight). Make sure your ASP.NET Web service is running, or this will result in an exception.

To incorporate the sales-tax calculation of the UDF class, change the SalesTax cell's formula to =GetSalesTax(CustomerZipCode). Finally, add the formula to total the shipping and handling fees. This is the ShippingAndHandling cell, whose formula should be set to =TotalShipping+HandlingFee.

Again remember that the #NAME? and #VALUE! results point to two separate issues. If you are getting the #VALUE! error, you may want to place Visual Studio in debug mode and step through your code. With your spreadsheet open in Excel 2007 and the UDF loaded, you can attach Visual Studio to the Excel.EXE process as shown in Figure 6-12. Once it's attached, set a breakpoint in a UDF method and simply modify a cell that causes one of the UDF class's methods to be invoked.

Figure 6-12. Debugging the UDF class in Excel 2007

Debugging the UDF class in Excel 2007

Preparing Excel Services for the Spreadsheet

In this section we will be publishing the spreadsheet to Excel Services on the server running Office SharePoint Server. When published, the spreadsheet is placed in a document library, but for Excel Services to open the workbook, the library must be set up as a trusted location. Use the following steps to set up your target library as a trusted location:

  1. From the Start Menu, launch SharePoint 3.0 Central Administration.

  2. In Quick Launch navigation on the left side, open your shared services administration. This is usually called something like SharedServices1. This must be the same Shared Services Provider (SSP) that the site containing the document library leverages for services. If your environment contains more than one SSP, you can confirm which one your site uses in the Shared Services Administration screen.

  3. In the Excel Services section, click Trusted File Locations.

  4. Click Add Trusted File Location.

  5. On the Add Trusted File Location page, enter the URL to your library in the Address text box. In our development environment, the library was named Excel in a team site named dev2. Therefore the Address URL was http://portal.sample.com/SiteDirectory/dev2/Excel.

  6. Under the Location Type prompt, set the storage type to Windows SharePoint Services.

  7. Leave the remaining items as their defaults except at the bottom of the page, where we want to enable User-Defined Functions. This check box should be checked so that Excel services will consider loading our assembly.

  8. Click OK.

Setting up the trusted location allowed Excel Services to open our workbook, and we informed the system that we want to allow UDF assemblies to be executed. However, we also need to register our UDF assembly so that Excel Services trusts it. Having these levels of trust enables administrators to control custom code that their environment is calling. Remember that we published our assembly and its configuration file to a local UDFs folder on the server running Office SharePoint Server. Therefore, use the following steps to register the UDF assembly:

  1. From the SSP administration page, access the Excel Services User-Defined Functions page in the Excel Services section.

  2. Click the Add User-Defined Function Assemblies button.

  3. Enter the path to the assembly as C:\UDFs\ProductPricingCalc.dll.

  4. Select File Path in the Assembly Location selection.

  5. Make sure the Assembly enabled check box is selected.

  6. Click OK.

As you may have noticed, there was an option to register the assembly if it were located in the global assembly cache. We did sign our assembly so this is an option for you to explore, but remember that you will have to account for the configuration file. If you were to have the assembly in the GAC, the assembly path would need to be ProductPricingCalc,Version=1.0.0.0,Culture=neutral,PublicKeyToken=a7da6d4f64e40eec. (Replace the public key token with the actual value for your solution, of course.)

It is possible to deploy UDFs using Windows SharePoint Services solution packages. If you are interested in this area, the following "How To" in the SDK is a good reference: https://msdn.microsoft.com/en-us/library/aa981325.aspx. This would be appropriate for deploying in a production environment, as the solution would deploy your files to the appropriate servers of the farm and also support retraction. It is important to know that regardless of whether your UDF assembly is in the global assembly cache or the local folder, it runs with a code-access security level of Full Trust. This is potentially a dangerous scenario—it means the code included in the assembly has full reign over the server and therefore must be reviewed carefully on each deployment. For this reason, it is a best practice to use code-access security to restrict the actions your UDF assembly can perform. The following reference details the steps for doing so: How to: Restrict UDF Code Access Security Permissions.

Publishing to Excel Services

We are now ready to publish the spreadsheet that we created earlier to the library that we configured as a trusted location. Make sure you have saved your changes to the spreadsheet locally, and then use the following steps to publish it:

  1. With the spreadsheet open in Microsoft Excel 2007, select Publish, then click Excel Services from the File menu. See Figure 6-13.

    Figure 6-13. Publishing the spreadsheet to Excel Services

    Publishing the workbook to Excel Services

  2. Enter the URL of the trusted location in the File Name prompt and press Enter. The dialog box should communicate with the server and provide you with a view of the SharePoint library. Name the file product pricing calculator.xlsx.

  3. Click the Excel Services Options button. This area allows us to configure how the spreadsheet will be run in Excel Services.

  4. The first tab allows us to restrict which elements of the workbook will be available in the Web-only view. This is an important choice, as it allows us to hide elements that were for our business user who was maintaining the calculations (and that were not necessarily interesting to the average viewer). Select Sheets in the drop-down and uncheck all of the sheets except the Pricing Calculator.

  5. The Parameters tab allows us to promote named ranges within the spreadsheet to be input parameters that Excel Services will ask for to open the workbook. Click the Add button to add a parameter.

  6. Select CustomerZipCode, OrderQuantity, and ProductCode—these items need to be supplied by the end user for the spreadsheet to perform the calculation.

  7. Click OK. The Parameters tab should look like Figure 6-14.

    Figure 6-14. Specifying input parameters for Excel Services

    Specifying input parameters for Excel Services

  8. Click OK.

  9. Click the Save button.

When the save completes, a browser window will launch with the workbook open in Excel services. There will be a pane on the right side for you to enter the input parameters. Try both zip codes (01208 and 32803), the product code of A-123435, and quantities in the different discount ranges. Notice how the spreadsheet performs the calculations just like the full Excel client. Also notice how only the Pricing Calculator worksheet is viewable in the browser even though the calculations that rely on the other sheets still perform. The result should look like Figure 6-15.

Figure 6-15. Viewing the workbook in the browser using Excel Services

Viewing the workbook in the browser using Excel

Use the breadcrumb navigation just above the toolbar to return to the Excel document library. Once there, look at the Actions menu for your Excel file in the library. There are three options that relate to Excel.

The first, Edit in Microsoft Office Excel, would be employed by the business user who wishes to update some of the calculations like markup percentage or the volume-discount table. Provided such users have the UDF registered on their computers such that it works within the Excel client, they can edit the spreadsheet, make the adjustments, and save it back to the library. Don't forget that we are using a reference of localhost for the Web service, and it would need to be altered if Excel were on a remote computer. Of course, the SharePoint library's security settings will control who is allowed access to update this file. After the save, there is no need to republish unless the parameters or viewable items need to change.

The second action, View in Web Browser, launches the workbook in the thin client Excel Services viewer. It is possible to have this view inside a Web Part so that the user does not have to come to the library first. The Web Part for this is called Excel Web Access Viewing and it is in the Business Data group of the Add Web Parts dialog box.

Last, the Snapshot in Excel action would allow a user to have the spreadsheet loaded in Excel, but without any formulas or hidden items. This is useful for a viewing user who wishes to do additional filtering, sorting, or charting, but has no need to change the data in the spreadsheet. This option also protects the organization from distributing its intellectual property, such as Excel formulas.

Tip

When you are working with the spreadsheet in Excel Services, you may need to do further debugging of your UDF assembly. To attach Visual Studio to Excel Services, you first need to discover which process corresponds with the shared service provider (named SharedServices1 in our environment). The following command in a command window will return you the correct process ID IISAPP /a SharedServices1. With the process ID, you can then use the Visual Studio Attach to Process command and connect to the appropriate IIS worker process. Also, Excel Services loves to cache data to increase performance. If you make changes to your assembly and redeploy, make sure this cache is purged. You can accomplish this with the same command that will reset the SSP's application pool: IISAPP /a SharedServices1 /r.

Integrating the Spreadsheet into a Custom Application

Our goal when we presented this solution was to integrate the spreadsheet into the enterprise. This included connecting it to back-end line-of-business applications, which we accomplished with the products database and the Web service call for shipping costs. However, the calculation this spreadsheet performs is likely useful for other applications (such as printing invoices or estimating sales). This is the power of Excel Services. We can interact with the published spreadsheet through code, asking it to perform calculations as if we were acting with it directly. This is a powerful technique since we are able to separate the spreadsheet from the custom application, allowing the business user to adjust the calculations without having to modify, retest, and redeploy a custom application.

In the code download on the Apress Web site we have provided a simple Windows form project called PricingApp. This application shows you the steps necessary for interacting with a published spreadsheet through the Excel Web Services. The form is rather straightforward, allowing the parameters to be specified and the result to be displayed. Figure 6-16 shows the form in the Visual Studio designer.

Figure 6-16. The custom application that will interact with Excel Services

Custom application interacting with Excel Services

To enable this application to communicate with Excel Services, you have to add a Web reference. The Web service can be found in the _vti_bin folder at the root of your SharePoint Web application. The URL should be similar to http://portal.sample.com/_vti_bin/excelservice.asmx. In this custom application, the reference was named ExcelWebService. Just like with the UDF assembly, make sure that the Web service URL is set to Dynamic so that it will be stored externally in a configuration file. The proxy for the Web service class will pick up this URL automatically. We also added a WorkbookPath configuration setting to store the URL to our Excel spreadsheet. This setting is of type String, scoped to Application, and has a value of http://portal.sample.com/SiteDirectory/dev2/Excel/product pricing calculator.xlsx.

With these elements set up, the Calculate button's click event handler in Listing 6-8 sets up the proxy for the Web reference. We will call Excel Services using the current user's logged-in Windows identity, assuming that this application is running within the same (or a trusted) security boundary as Excel Services. The very first call to the Web service uses the OpenWorkbook method, which returns the session identifier that is used in subsequent calls.

Listing 6-8. Establishing a session with Excel Services

Private Sub btnCalculate_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnCalculate.Click
    Dim es As ExcelService = New ExcelService()
    Dim outStatus() As Status = Nothing
    Dim targetWorkbookPath As String = _
        My.MySettings.Default.WorkbookPath
    Dim sessionId As String = String.Empty
    es.Credentials = System.Net.CredentialCache.DefaultCredentials
    Try
        sessionId = es.OpenWorkbook(targetWorkbookPath, "en-US", _
"en-US", outStatus)

Once a session has been created, the event handler uses the SetCellA1 method to provide values for the cells that are the named ranges representing the input parameters. As shown in Listing 6-9, the SetCellA1 method accepts four parameters: the session ID, spreadsheet name, named range, and value. (You do not need to provide the spreadsheet name if you are using a named range.) It is important that the data types match, which is why the order quantity is parsed to be a double.

Listing 6-9. Passing the input parameters to Excel Services

es.SetCellA1(sessionId, String.Empty, "ProductCode", _
    Me.txtProductCode.Text)
es.SetCellA1(sessionId, String.Empty, "CustomerZipCode", _
    Me.txtCustomerZipCode.Text)
es.SetCellA1(sessionId, String.Empty, "OrderQuantity", _
    Double.Parse(Me.txtQuantity.Text))

Now that we've set all of the input parameters, we can retrieve from the cells the values we want as output. For this application we are interested in retrieving the totals both before and after the discount have been applied. These values are stored in the named ranges Total and DiscountedTotal. As Listing 6-10 shows, the Web service's GetCellA1 method returns these values. Since the cells were formatted as Accounting and we are requesting a formatted value, the data type returned is String. The Boolean value in the call designates whether we want the cell's formatted value (True) or the raw value (False).

Listing 6-10. Retrieving values of cells through Excel Services

Dim totalSales As String = es.GetCellA1(sessionId, String.Empty, _
    "Total", True, outStatus)
Dim discountedTotal As String = es.GetCellA1(sessionId, String.Empty, _
    "DiscountedTotal", True, outStatus)
Me.lblTotal.Text = totalSales
Me.lblDiscountedTotal.Text = discountedTotal

You may have noticed the use of an array of Status objects being used in some of the Web service calls in this application. These status objects would return soft errors by Excel Services when it encountered something unexpected but was able to continue with the operation. These types of errors are called Continue errors since Excel Services can still perform the request. With Continue errors, no exception is raised. When Excel Services encounters an error that halts the execution of the request, it will raise an exception just like other .NET code will. Though we are not examining any Continue errors in this application, we do have some response to exceptions, as shown in Listing 6-11. If the exception occurred within Excel Services, it would be returned to our application as a SoapException and its SubCode would contain information about the error. If our application itself threw an exception, the subsequent catch clause would be invoked. For more information on Excel Services alerts, review the following portion of the SDK: https://msdn.microsoft.com/en-us/library/ms564121.aspx. Of course, you should implement your organization’s strategy for logging and reporting exceptions. We simply show them to make it easier to debug. We also use the Finally block to complete the event handler by closing the workbook asynchronously; not waiting for a response. This is a "fire and forget" technique.

Listing 6-11. Handling exceptions

Catch ex As SoapException
    'would return InvalidSheetName or FileOpenNotFound
    MessageBox.Show(ex.SubCode.Code.Name)
Catch ex As Exception
    MessageBox.Show(ex.Message)
Finally
    If (sessionId <> String.Empty) Then es.CloseWorkbookAsync(sessionId)
End Try
End Sub

Important Lessons

The solution in this chapter incorporated several key techniques that are worth highlighting, as they could easily be reused in other projects.

User-defined functions: The solution utilized a .NET class to host methods that were surfaced in Excel as functions. This allows a developer to use the capabilities of the .NET Framework within Excel. You can call databases, perform complex calculations, and call Web services.

Registering the UDF as a COM server for use in Excel: To allow our business user to see the results of the UDF methods, these methods needed to be available in the full Excel 2007 client. This required our class to comply with COM plumbing so that it could be loaded as an Excel add-in.

Preparing Excel Services: The solution needed to specify several configuration settings in the shared service provider admin pages for Excel Services to properly open the published spreadsheet. This involved registering the document library as a trusted source with UDF enabled. The UDF assembly also needs to be registered.

Publishing a spreadsheet to Excel Services: This feature allows the spreadsheet to use server resources for its calculations and presents the spreadsheet through a thin-client interface. Interestingly, not all elements of the spreadsheet need to be made available in the thin-client version. Parameters can be specified, and the interface automatically builds a pane for capturing them from the user.

Using the Excel Services Web service: An added benefit of publishing a spreadsheet to Excel Services is that it can be invoked from a Web service. This technique decouples the calculation logic from the consuming application. The separation means that the business user can continue to use Excel to tweak and update the calculations without needing to change the consuming application.

Extension Points

While coding this example, we thought of several variations to the solution that we didn't incorporate. Mostly, these were not included because they distracted from the overall objective of the solution. We call them out now as extension points since they may be applicable to a specific project you are working on.

Invoke real shipping cost and sales tax Web services: In our example, we used test methods to calculate shipping costs and sales tax. Several organizations provide these as Web services and your organization may have their own services. Provided your development environment has network or Internet access, determine the input parameters for these services and adjust accordingly.

Incorporate the business data catalog: In this solution we had the UDF method connect to the database directly using ADO.NET code. This could have been abstracted by having the solution use the Business Data Catalog to retrieve this value once the external application had been registered. To learn about the Business Data Catalog feature of Office SharePoint Server and how to expose it as a Web service, read the solution in Chapter 12.

Further Reading

The following links are to resources a reader interested in this chapter's material may find useful: