Chapter 14: Building Custom Solutions (Part 2 of 2)

  • Article

Previous Part: Chapter 14: Building Custom Solutions (Part 1 of 2)

Contents

  • User-Defined Functions (UDFs)

  • ECS XLL UDF

  • Xlviewer Customizations

  • Summary

User-Defined Functions (UDFs)

Excel Services UDFs represent another tier in the Excel Services capability to support custom solutions. UDFs provide a means to call custom-managed code functions from within a workbook. Without UDFs, a workbook is restricted to using only the intrinsic Excel functions. With UDFs, custom functions can be called from the workbook as well.

UDFs are very instrumental to custom server solutions because Excel Services does not support loading workbooks that contain code behind (VBA). Nor does Excel Services support the Excel client add-ins used by workbooks to extend functionality. With the right UDF-managed wrapper solution, you could leverage existing custom client solutions on the server, but that topic isn’t discussed here.

This section describes how to author managed UDFs and deploy them to the server to make them available to workbooks that are loaded from the trusted file locations. An example solution is provided to demonstrate the authoring and building of a UDF assembly. Additional material is provided to explain how the workbook interacts with UDF methods, and how to pass and return arguments to the workbook.

UdfMethodAttribute Class

Each public method in the public UDF class must have the [UdfMethod] attribute if the UDF is to be treated as a public UDF. The UdfMethodAttribute has two Boolean properties: IsVolatile and ReturnsPersonalInformation.

The IsVolatile property has a default value of false. When set to true, the UDF method is treated like an Excel volatile function. A volatile function always calculates when any part of a workbook needs to be calculated. UDF volatile methods are called when the Volatile Function Cache Lifetime setting has passed. This setting is defined on the trusted file location where the workbook was loaded.

The ReturnsPersonalInformation property also has a default value of false. When set to false, the thread’s Windows identity is hidden, so all callers of the UDF method share the same results cache. When set to true, the UDF method returns results based on the identity, which ensures that callers of the method are not sharing cached values. If a UDF method is expected to return results based on the caller’s identity, then the ReturnsPersonalInformation should be true so that each caller gets only their identity-specific results.

Argument Data Types

The supported UDF argument data types are in the .NET System namespace. Excel supports a smaller set of data types that can be applied to data in cells. The following table describes the behavior that you can expect from combinations of UDF argument types and Excel types. The first column represents the UDF argument data type. The remaining columns represent the Excel types that are passed into the UDF through the argument. The contents of the table indicate the error that is returned if the pair is unsupported, or what to expect if an error is not going to be returned.

Excel Data Type

UDF Argument Data Type Notation

Double

String

Boolean

Empty

Numeric

Tries to cast; Byte and Sbyte return #NUM

#VALUE

#VALUE

0

String

#VALUE

String

#VALUE

String.Empty

Boolean

#VALUE

#VALUE

Boolean

False

DateTime

Double*

#VALUE

#VALUE

#VALUE

Object

Boxed double

Reference to a string

Boxed Boolean

Null

Here is a further explanation of the data types in the table:

  • Numeric—Refers to the following System namespace types: Byte, Double, Int16, Int32, Sbyte, Single, UInt16, and UInt32. The Int64 and UInt64 types are not supported.

  • DateTime*—Internally, Excel treats dates as a double. The ECS converts a DateTime double from Excel into a .NET DateTime.

  • Object—Defines the behavior for each cell in the range that is passed into the array.

  • #VALUE—Can be returned for different reasons, including the following:

    • The Excel type is an error, such as division by zero (#DIV/0!).

    • The UDF argument is an unsupported type, such as Int64.

    • The Excel and .NET type pair is not supported by ECS.

    • The type conversion fails, which can occur for a DateTime type.

Ranges as Arguments

A UDF argument can be either a one-dimensional or two-dimensional array argument. Only object arrays are supported; strong typed arrays are not. #VALUE! is returned if the dimensions of the array argument are insufficient to hold the passed-in range. A single cell range can fit into a one-dimensional array, and a one-dimensional range can fit into a two-dimensional array.

A one-dimensional array can receive a range consisting of a single row. The following UDF method has a single object array argument (xlRow) and returns an integer that represents the number of columns (xlRow.Length) in the array argument. A row is passed in, and the number of columns in that row is returned.

[UdfMethod]
public int ReturnNumberOfColumns(object[] xlRow)
{
   return (xlRow.Length);
}

In Excel, you call the ReturnNumberOfColumns by entering the following code into a cell. Excel evaluates the method as #NAME?. The example noted here uses E5:H5 as the argument to pass in, and 4 is the return value to represent the column count in that range.

=ReturnNumberOfColumns(E5:H5)

A two-dimensional array can receive a range that spans one or more rows. The following UDF method has a single object array argument (xlRange) and returns an integer that represents the number of cells (xlRange) in the two-dimensional array argument. One or more rows are passed in, and the number of cells in that range is returned.

[UdfMethod]
public int ReturnNumberOfCells(object[,] xlRange)
{
   return (xlRange)
}

In Excel, you call the ReturnNumberOfCells method by entering the following code into a cell. Excel evaluates the method as #NAME?. The example noted here uses E5:H6 as the argument to pass in, and 8 is the return value to represent the cell count in that range.

=ReturnNumberOfCells(E5:H6)

Parameter Arrays as Arguments

You can also use a parameter array argument to get values into a UDF. This approach provides the flexibility of passing in a variable number of scalar arguments (such as an int type) or as an object-array type.

A one-dimensional parameter array can receive values or single cell references. The following method has a single params array argument (xlCells) and returns an integer that represents the number of cells passed in through the params array argument:

[UdfMethod]
public int ReturnNumberOfCellsReceived(params int[] xlCells)
{
   return (xlCells.Length);
}

In Excel, you call the ReturnNumberOfCellsReceived method by entering the following code into a cell. Excel evaluates the method as #NAME?. The example noted here provides one value (6) and two cell references (F2 and E5) as the arguments being passed in, and 3 is the return value that represents the number of items (or cells) passed in from Excel.

=ReturnNumberOfCellsReceived(6,F2,E5)

A second way to use parameter arrays as arguments is to create a two-dimensional array argument that can receive multiple ranges. The following method has a two-dimensional object array argument (xlArray), and returns an integer that represents the number of cells in the items passed in from Excel.

[UdfMethod] 

public int ReturnCountOfCellsReceived(params object[][,] xlArray)
{
   int elements = 0;
   for (int x = 0; x < xlArray.Length; x++)
   {
      elements += xlArray[x].Length;
   }
   return (elements)
}

In Excel, you call the ReturnCountOfCellsReceived method by entering the following code into a cell. Excel evaluates the method as #NAME?. The example noted here provides one value (6), a cell reference (F2), and a range (G2:H3) as the arguments being passed in, and 6 is the return value that represents the number of cells passed in from Excel.

=ReturnCountOfCellsReceived(6,F2,G2:H3)

Return Data Types

The supported UDF return data types are in the .NET System namespace. Excel supports a smaller set of data types that can be applied to data in cells. The following table describes supported return types, as well as the behavior that you can expect from combinations of UDF return types and Excel types. The first column represents the UDF return data type. The second column represents the Excel behavior.

UDF Return Type

Excel Behavior

Numeric

Cast to double

String

String

DateTime

Recognizes the Double as a DateTime

Object[] Type[]

Array (first value goes into the first cell, and so on)

Object[,] Type[,]

Array (first value goes into the first cell, and so on)

Object

Excel tries to map to one of the types noted above and handles it accordingly

Object(Null)

Empty/Null String

The Numeric data type refers to the following System namespace types: Byte, Double, Int16, Int32, Sbyte, Single, UInt16, and UInt32. The Int64 and UInt64 types are not supported.

Returning a Range

In addition to returning single-valued data types (also referred to as scalar data types), a UDF can return one- or two-dimensional arrays. Only object arrays are supported; strong typed arrays are not.

A one-dimensional array can hold a range consisting of a single row. The following UDF method has a single object array argument (xlRow) and returns an object array that represents the object that was passed in. A row is passed in and the same row is returned.

[UdfMethod] 
public object[] Return1dObjectArray(object[] xlRow) 
{
   return (xlRow); 
}

In Excel, you call the Return1dObjectArray method by entering the following code into cell C7. To create the array formula, select cells C7:F7, press F2, and then press Ctrl+Shift+Enter. Excel automatically inserts the formula between {} (curly braces). The C7:F7 range represents the cells where the object[] returned from Return1dObjectArray is applied. Excel evaluates the method as #NAME?. The example noted here uses E5:H5 as the argument to pass in. After Return1dObjectArray returns, C7:F7 contains the same values as E5:H5.

=Return1dObjectArray(E5:H5)

A two-dimensional array can receive a range that spans one or more rows. The following UDF method has a single two-dimensional object array argument (xlRange) and returns a two-dimensional object array that represents the object that was passed in. One or more rows are passed in, and the same rows are returned.

[UdfMethod]
public object[,] Return2dObjectArray(object[,] xlRange)
{
   return (xlRange)
}

In Excel, you call the Return2dObjectArray method by entering the following code into cell C11. To create the array formula, select cells C11:E15, press F2, and then press Ctrl+Shift+Enter. Excel automatically inserts the formula between {} (curly braces). The C11:E15 range represents the cells where the object[,] returned from Return2dObjectArray is applied. Excel evaluates the method as #NAME?. The example noted here uses H8:J12 as the argument to pass in. After Return2dObjectArray returns, C11:E15 contains the same values as H8:J12.

=Return2dObjectArray(H8:J12)

Creating a UDF

This section focuses on creating an Excel Services UDF, deploying the managed UDF assembly to the ECS, and calling the UDF methods from an Excel workbook. Here are a few prerequisites that must be met before you get started:

  • Microsoft Office SharePoint Server 2007 must be available.

  • Excel Services must be set up and ready to load workbooks.

  • There must be an Excel 12 client and an ECS trusted location where at least View permissions are set.

  • A Microsoft .Net Framework 2.0 development environment must be set up (The procedures use Microsoft Visual Studio 2005.)

Creating the C# Class Library

Follow these steps to launch Visual Studio and create the C# class library:

  1. Start Visual Studio.

  2. Select File, then New and click Project to open the New Project dialog box.

  3. In the Project Types frame, expand the Visual C# node and select Windows. In the frame, select Class Library.

  4. In the Name field, enter xlUdf.

  5. Set the Location field to the location where you want to create the solution.

  6. Click OK to create the library. The New Project dialog box closes, and Visual Studio opens the xlUdf solution with Class1.cs open and visible. The namespace is xlUdf.

Adding the UDF Run-Time Reference

The Excel Services run-time assembly is installed with each Complete or Stand-Alone (evaluation) type of MOSS installation. The run-time assembly may be available as a download some day, so you might want to search www.Microsoft.com for this. Obtain a copy of the assembly (Microsoft.Office.Excel.Server.Udf.dll) and place it in a location where your project can access it. Ensure that the assembly is compatible with the ECS that will ultimately host the UDF by using either a 32-bit or a 64-bit version of the assembly.

Follow these steps to add the UDF run-time reference:

  1. Select Project and click to open the Add Reference dialog box.

  2. Select the Browse tab in the Add Reference dialog box. Navigate to the directory that contains the Microsoft.Office.Excel.Server.Udf.dll run-time assembly, and select the file. Click OK. The Solution Explorer shows the run-time assembly as a reference for the xlUdf solution.

  3. In Class1.cs, add a directive for the run-time assembly namespace. At the beginning of Class1.cs add the following using statement:

    using Microsoft.Office.Excel.Server.Udf

Adding Attributes and a Method

Follow these steps to add attributes and a method:

  1. In Class1.cs, add the [UdfClass] attribute to the class. Enter the following on the line immediately preceding public class Class1:

    [UdfClass]

  2. Define a UDF method within Class1. Add the following [UdfMethod] attribute to any public UDF method being created:

    [UdfMethod]
public string EchoInput(string userInput)
{
   return "Input: " + userInput;
}

    The complete solution for the UDF assembly is as follows:

    using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;
namespace xlUdf
{
   [UdfClass]
   public class Class1
   {
      [UdfMethod]
      public string EchoInput(string userInput)
      {
         return "Input: " + userInput;
      }
   }
}

Deploying the UDF Assembly

UDF assemblies are enabled at the Shared Services Provider (SSP) level. Any UDF method calls in a workbook to a UDF assembly that is not enabled will fail. Each ECS server in an SSP must be able to access all of the enabled UDF assemblies, so ensure that the assemblies are accessible to each ECS that needs access.

Follow these steps to make the UDF assembly accessible:

  1. Build the xlUdf.dll assembly if you have not already done so.

  2. Copy xlUdf.dll to a local folder on the ECS server. For example, place the assembly in D:\Udfs\xlUdf.dll on the ECS server.

Follow these steps to add xlUdf.dll to the list of trusted UDF assemblies for the SSP:

  1. 1.Open the MOSS Central Administration by selecting Start, then All Programs, then Microsoft Office Server and click SharePoint 3.0 Central Administration. The Central Administration page loads in the browser.

  2. Navigate to the trusted UDF assemblies administration page by clicking the SSP name (ShareServices1, for example). Locate the link for the UDF assemblies and click it.

  3. Register and enable the UDF by clicking the Add User-Defined Function Assembly link. The page to add the UDF assembly is displayed.

  4. Enter the assembly full path. For example, enter D:\Udfs\xlUdf.dll. You can use a network share or a local file path. A second option is to add the assembly to the Global Assembly Cache (GAC) and enter the Strong Name here instead of a file path.

  5. If you entered a file path in the previous step, select File Path in the Assembly Location section of the page. If instead you added the UDF assembly to the GAC and entered a Strong Name in the previous step, select Global Assembly Cache.

  6. Click the Assembly Enabled check box to enable the UDF.

  7. Select OK to save the UDF Assembly settings and close the page. The xlUdf.dll assembly is now registered and enabled.

  8. Reset IIS. (This is necessary to enable a workbook to make calls to the UDF.)

Follow these steps to enable UDFs at the trusted file location level:

  1. Click the Trusted File Locations link. The Trusted File Locations page loads and a list of the defined trusted locations is displayed.

  2. Click the trusted location where UDFs are to be supported. The Trusted Location edit page is displayed. Scroll to the bottom and select the User-Defined Functions Allowed option.

Calling the UDF

The syntax you use to make a UDF method call from a workbook is essentially the same as the syntax to call a built-in Excel function. Follow these steps to create the workbook, create a defined name to be used as a parameter, and make a UDF method call that takes the parameter input as an argument:

  1. Create a new workbook by launching Excel 12 and selecting File, then New, then Blank Workbook and click Create.

  2. Create a defined name by selecting Formulas then Define Name. For the name, enter String_Input and set the Refers To field to =Sheet1!$A$1. Click OK to create the defined name.

  3. Make the UDF method call by selecting cell A3 on Sheet1 and typing the following code.

    =EchoInput(String_Input)

  4. Press Enter. The UDF method call evaluates to #NAME? because Excel doesn’t know about the UDF method.

  5. Publish the UDF to a trusted file location by selecting File, then Publish and click Excel Services. Type the trusted file location path in the File Name field. Uncheck the Open In Excel Services box to deselect this option. Click Save.

  6. Configure the defined name as a parameter and use the EWA to load the workbook by selecting File, then Publish and click Excel Services. Click the Excel Services Options link to open the Excel Services Options dialog box. Click the Parameters tab and then select Add. Click the check box next to the String_Input entry on the Add Parameters dialog box. Select OK on both the Add Parameters and the Excel Services Options dialog boxes. In the Save As dialog box, select the Open in Excel Services option and then click Save.

    A browser launches and displays the EWA with the workbook. There is a Parameters pane on the right side of the EWA, and cell A3 contains Input:.

  7. Enter text in the String_Input parameter field and click Apply. The ECS applies the parameter value to cell A1, calculates cell A3 by passing the String_Input value to the method, and then returns the new value for cell A3. The EWA reloads and displays the new values in cells A1 and A3.

ECS XLL UDF

The Excel client supports UDFs in Excel add-ins (or XLLs). This type of UDF implementation is very common for Excel, and many customers have invested heavily in the XLL type of add-in. As mentioned earlier in this chapter, you can craft a solution that will wrap existing functionality and essentially create a UDF solution for the server. Because Excel Services supports only managed UDFs, the XLL requires a managed wrapper to make the calls to the XLL UDF.

Xlviewer Customizations

You can use the Excel Services Excel Web Access Web Part in one of two ways: embedded in a Share­Point page or by leveraging the built-in xlviewer.aspx solution. When the EWA is embedded, the configurable Web Part properties are exposed through SharePoint’s Modify Shared Web Part interface. The xlviewer.aspx solution doesn’t expose the Web Part properties through any user interface. The xlviewer.aspx page isn’t coded to accept URL parameters for the properties either.

This section demonstrates the steps necessary to create a solution based on the functionality provided by the xlviewer.aspx method of using the EWA. The more popular EWA properties are explained and code examples are provided for setting the EWA properties.

There are a couple of scenarios in Excel Services where the xlviewer.aspx is used to render workbooks with the EWA. A SharePoint document library functionality called View in Web Browser uses xlviewer.aspx to render the workbook. Each EWA Web Part in a SharePoint page has a clickable title that loads the workbook using xlviewer.aspx. By default, xlviewer.aspx takes over the entire browser window and renders the workbook using the EWA. This provides better viewing of the workbook, with less scrolling, because the EWA isn’t sharing screen space with other SharePoint components. You can also construct links in web pages or in e-mail messages that use xlviewer.aspx to load a workbook (for example, http://TheExcelServer/_layouts/xlviewer.aspx?id=\\fileShare\trustedLocation\workbook.xlsb).

Creating a customized version of xlviewer.aspx is a straightforward operation. In the following section, you create a copy of xlviewer.aspx and add code for setting EWA properties.

Note

You could apply the changes described here directly to xlviewer.aspx, which would affect the behavior for all consumers of xlviewer.aspx. This may be your intention. However, for this example solution, you modify a copy of xlviewer.aspx.

Setting New Defaults and URL Parameters

Follow these steps to create a customized version of xlviewer.aspx:

  1. Locate the xlviewer.aspx file at C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS. Make a copy of xlviewer.aspx and keep it in the same directory. For this solution, name the copy xlEwa.aspx.

  2. Open xlEwa.aspx in a text editor (such as Notepad) or, if you like extra bells and whistles, use a program like Visual Studio. Locate the following statement in the file and change the AutoEventWireUp attribute’s value to true:

    <%@ Page language="C#" Codebehind="XlViewer.aspx.cs" AutoEventWireup="true" 
Inherits="Microsoft.Office.Excel.WebUI.XlViewer,Microsoft.Office.Excel.WebUI,
Version=12.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c" %>

  3. Insert the following code into xlEwa.aspx between the opening <html> tag and the opening <head> tag:

    <script >

private void Page_Load(object sender, System.EventArgs e)
{
   try
   {
      if (Request.QueryString["RowsToDisplay"] != null)
      {
         m_excelWebRenderer.RowsToDisplay = Int32.Parse(
            Request.QueryString["RowsToDisplay"]);
      }
      else
      {
         m_excelWebRenderer.RowsToDisplay = 50;
      }
      if (Request.QueryString["ColumnsToDisplay"] != null)
      {
         m_excelWebRenderer.ColumnsToDisplay = Int32.Parse(
          Request.QueryString["ColumnsToDisplay"]);
      }
      else
      {
         m_excelWebRenderer.ColumnsToDisplay = 30;
      }
   }
   catch (Exception exc)
   {
      //error handler
   }
}
</script>

The xlEwa.aspx file is now a customized alternative to using xlviewer.aspx. This version of xlEwa.aspx renders the workbook and displays a grid size of 50 rows by 30 columns by default. In contrast, the xlviewer.aspx always renders a grid size of 75 rows by 20 columns.

In addition, the code you added to the xlEwa.aspx file enables you to input URL parameters and apply values to both the RowsToDisplay and the ColumnsToDisplay properties.You can now use xlEwa.aspx as shown in the following URL:

http://TheExcelServer/_layouts/xlEwa.aspx?id=\\fileShare\trustedLocation\work
book.xlsb&RowsToDisplay=77&ColumnsToDisplay=15

Configurable Properties

The EWA Web Part has approximately 43 configurable properties. About half of these are applicable for using the EWA in a nonembedded manner, as is the case with xlEwa.aspx.

The following table shows the properties that you can use for this solution. Instructions for obtaining the complete list of properties are provided after the table. Each of the configurable EWA Web Part properties exposed through xlEwa.aspx is also available through the EWA Web Part properties task pane that is displayed when an EWA is embedded in a SharePoint page.

Property Name

Type

User Interface Equivalent

Default Value

RowsToDisplay

int

Rows

75

ColumnsToDisplay

int

Columns

20

VisibleItem

string

Named Item

ShowVisibleItemButton

bool

Named Item drop-down menu

true

WorkbookUri

string

Workbook

AllowInExcelOperations

bool

Open In Excel; Open Snapshot In Excel

true

AllowPeriodicData-Refresh

bool

Refresh Selected Connection; Refresh All Connections

true

AllowRecalculation

bool

Calculate Workbook

true

AllowNavigation

bool

Workbook Navigation

true

AllowHyperlinks

bool

Hyperlinks

true

AllowInteractivity

bool

All workbook interactivity

true

AllowParameter-Modification

bool

Parameter modification

true

ShowWorkbookParameters

bool

Display Parameters task pane

true

AllowSorting

bool

Sorting

true

AllowFiltering

bool

Filtering

true

AllowPivotSpecific-Operations

bool

All PivotTable interactivity

true

AllowManualDataRefresh

bool

Periodically Refresh If Enabled In Workbook

true

AutomaticPeriodic-DataRefresh

enum AutomaticPeriodicData-RefreshMode

Display Periodic Data Refresh prompt

Always

CloseWorkbookSessions

bool

Close Session Before Opening A New One

false

ToolbarStyle

enum Toolbar-Visibility-Style

Type of toolbar

Full

You can obtain a complete listing of the EWA Web Part properties by exporting the Web Part from a SharePoint page. Follow these steps to see all of the properties:

  1. Navigate to a SharePoint page that contains an EWA Web Part, or add one to an existing page.

  2. Open the EWA Web Part menu and click Export.

  3. Save the .webpart file to disk and then open the file using Notepad. The available EWA Web Part properties are displayed in the file.

The AutomaticPeriodicDataRefresh and ToolbarStyle properties expect an enum value, as follows:

  • AutomaticPeriodicDataRefresh—Has three supported values: Disabled, Optional, and Enabled. The user interface choices are Always, Optionally, and Never.

  • ToolbarStyle—Has four supported values: FullToolbar, SummaryToolbar, NavigationOnlyToolbar, and None. The user interface choices are Full, Summary, Navigation Only, and None.

Add the following code to the previous xlEwa.aspx sample solution to try out the AutomaticPeriodicDataRefresh and ToolbarStyle the properties. With these settings, if you use periodic data refresh, the data refresh happens automatically without prompting the user. Also, the EWA toolbar functionality is reduced to navigation operations only.

m_excelWebRenderer.AutomaticPeriodicDataRefresh = 
AutomaticPeriodicDataRefreshMode.Enabled;
m_excelWebRenderer.ToolbarStyle = ToolbarVisibilityStyle.NavigationOnlyToolbar;

EWA and API Working Together

This section describes two approaches for using custom code to interact with an EWA session when the EWA Web Part is embedded as part of a SharePoint page. The first approach leverages the Content Editor Web Part, JavaScript, and Excel web services. The second approach is a bit more advanced, and makes use of a custom web application and managed code, the Page Viewer Web Part, and Excel web services. Each approach demonstrates how the Excel API can interact with an active EWA session.

JavaScript Approach

Of the two approaches, this one is easiest to implement, but it isn’t nearly as powerful as the managed-code approach that is discussed later. Anyone who is well-versed in client-side browser scripting can probably take this approach and make it quite impressive. The usage described here stops well short of accomplishing anything extraordinary, but it provides a starting point and hopefully causes you to think about what could be possible.

To implement this approach, you create a web page that contains an EWA and a Content Editor Web Part. You then use the Content Editor Web Part to host the HTML and <script> that gets the session ID from the EWA instance. The <script> uses the session ID to make an Excel Services web service GetSessionInformation call. This approach also demonstrates the use of three EWA JavaScript functions.

  • Before you begin, the following prerequisites must be met:

  • Microsoft Office SharePoint Server 2007 must be available.

  • Excel Services must be set up and ready to load workbooks.

  • An Excel workbook must be published to a trusted location where at least View permissions are set.

  • You must have at least the Designer permission on a web page.

Creating a SharePoint Web Page

If you do not already have a web page where you can try out this approach, follow these steps to create one:

  1. Use the browser to navigate to site where you want to create the web page.

  2. Select Site Actions and click Create.

  3. On the create.aspx page, click Web Part Page. When the New Web Part Page is displayed, provide a name for the page. For this exercise, name the page testPage1.aspx. Choose the Full Page template and click the Create button. The testPage1.aspx page loads in Design mode.

  4. Click the Add A Web Part button and add two Web Parts: Context Editor Web Part and Excel Web Access Web Part. The testpage1.aspx page now contains both Web Parts and remains in Design mode.

  5. Configure the EWA to load a default workbook by opening the Properties task pane. To do this, click the edit link on the EWA Web Part and click Modify Shared Web Part. In the Workbook input field, enter the full path to a workbook from a trusted location. Click OK to apply the change. The workbook loads in the EWA.

  6. Before moving to the coding part of this approach, you need to provide the EWA Web Part ID. To obtain the ID, right-click the page above the Full Page label near the top of the page. Select View Source from the IE context menu, and then search the source for the ID of the EWA Web Part. The ID will be similar to WebPartWPQ4. Make a note of the ID, because you will need it later.

Configuring the Context Editor Web Part

Follow these steps to configure the Context Editor Web Part:

  1. Webservice.htc must be in the same directory as the testPage1.aspx file, because it is used as part of the solution to make Excel Services web service calls. Obtain Webservice.htc from https://msdn.microsoft.com.

  2. Click the edit link on the Context Editor Web Part and click Modify Shared Web Part. Click the Source Editor button to open a Text Entry window where you can enter HTML and <script>.

  3. Enter the following code into the Source Editor. Search for ‘WebPartWPQ4’ and replace the string with the EWA Web Part ID that you noted in a previous procedure. Search for theexcelserver:38712 and replace the string with your server information.

    <SCRIPT LANGUAGE="JavaScript">
<! — 

var iSessID;

function callSvc()
{
   service.useService(
      "http://theexcelserver:38712/_vti_bin/ExcelService.asmx?WSDL",
      "EcsWs");
}

function callGetSessionInfo()
{
   callSvc();
   var sessionId = document.getElementById(‘_sessionId_xl’).value;
   iSessID = service.EcsWs.callService("GetSessionInformation", sessionId);
}

function onWSGetResult()
{
   // Error was returned
   if((event.result.error)&&(iSessID==event.result.id))
   {
      // Get the error details
      var errCode   = event.result.errorDetail.code;
      var errString = event.result.errorDetail.string;
      var errSoap   = event.result.errorDetail.raw;

      // Add code to handle specific error codes here
      if(iSessID==event.result.id)
      {
         document.getElementById(‘_sessionInfo_xl’).value = "ERROR";
      }
   }

   // No error
   else if((!event.result.error)&&(iSessID==event.result.id))
   {
      // Put results in input control
      if(iSessID==event.result.id)
      {
         document.getElementById(‘_sessionInfo_xl’).value =
            event.result.raw.parentNode.firstChild.parentNode.text;
      }
   }
   else
   {
      // Not an event to be caught here
   }
}
//  — >
</SCRIPT>

<div id="service" style="behavior:url(webservice.htc)" 
onresult="onWSGetResult();" />

<input type="text" style="width:150px" id="_sessionId_xl" />
<input type="button" style="width:100px" value="Get Session" 
onclick="document.getElementById(‘_sessionId_xl’).value = 
(EwaGetSessionId(‘WebPartWPQ4’));" />
<br />
<input type="text" style="width:30px" id="_reload_xl" />
<input type="button" value="Reload EWA" 
onclick="document.getElementById(‘_reload_xl’).value = 
(EwaReloadWorkbook(‘WebPartWPQ4’));" />
<br />
<input type="text" style="width:150px" id="_cellFocus_xl" />
<input type="button" style="width:100px" value="Selected Cell" 
onclick="document.getElementById(‘_cellFocus_xl’).value = 
(getHighlightedCellA1Reference(‘WebPartWPQ4’));" />
<br />
<input type="text" style="width:160px" id="_sessionInfo_xl" />
<input TYPE="button" VALUE="Session Info" NAME="GetSessionInfo" 
onclick="callGetSessionInfo()" />

  4. Click Save to keep the code. Click OK in the pane to apply the changes to the Web Part and close the pane. The web page reloads, no longer in Design mode.

Solution Functionality

The web page you created loads the workbook in the EWA and the Content Editor renders the custom code. Figure 14-1 shows what the page looks like when the custom code is executed.

Note the following:

  • Get Session button—Clicking this button makes a call to the EWA EwaGetSessionId function, which returns the sessionId of the currently loaded workbook.

  • Reload EWA button—Clicking this button makes a call to the EWA EwaReloadWorkbook function, which returns a TRUE or FALSE status to reflect whether or not the EWA reload call was successful. The function reloads the original workbook, so any private state that existed previously is lost.

    Figure 14-1

    Figure 14-1

  • Selected Cell button—Clicking this button makes a call to the EWA getHighlightedCellA1Reference function, which returns the currently selected cell in the EWA grid. The string is returned in the format ‘Sheet1’!C15. When no cell is selected, null is returned.

  • Session Info button—Clicking this button makes an Excel Services web service GetSessionInformation method call using the sessionId of the currently loaded workbook.

Managed-Code Approach

To implement this approach, you create a .NET Web application using C#. Next, you create a web page that contains an EWA and a Page Viewer Web Part. The Page Viewer Web Part hosts the web application to get the session ID from the EWA instance. The application then uses this session ID to make Excel Services web service calls through local linking (not as a web reference).

Before you begin, the following prerequisites must be met:

  • Microsoft Office SharePoint Server 2007 must be available.

  • Excel Services must be set up and ready to load workbooks.

  • An Excel workbook must be published to a trusted location where at least View permissions are set.

  • You must have at least the Designer permission on a web page.

  • Microsoft Visual C# and Microsoft Visual Studio 2005 must be available (to reproduce the steps described here).

Creating the C# Web Application Project

Follow these steps to launch Visual Studio on the MOSS server and create the C# Web Site application:

  1. Start Visual Studio.

  2. Select File, then New and click Web Site to open the New Web Site dialog box.

  3. In the Templates frame, select ASP.NET Web Site. Set the Location to HTTP and the Language to Visual C#.

  4. Enter the path for the web application in the form of http://<server>/_layouts/<applicationName>. For example, http://TheExcelServer/_layouts/EwaCustApp creates the application in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS.

  5. Select to create the application. The New Web Site dialog box closes and Visual Studio opens the EwaCustApp solution with Default.aspx displayed. The Solution Explorer shows that Default.aspx.cs has been created as well.

Adding References

Follow these steps to add references:

  • Select Website and click Add Reference to open the Add Reference dialog box.

  • On the tab of the Add Reference dialog box, select each of the following components and then click OK to add them as references to the application:

    • Windows SharePoint Services component to add the Microsoft.SharePoint.dll

    • Excel web services component to add the Microsoft.Office.Excel.Server.WebServices.dll

    • System.Windows.Forms to add the System.Windows.Forms.dll

Adding the Default.aspx Code

Enter the following code between the <div> and </div> tags in Default.aspx:

<asp:Button ID="_getApiVersionButton_xl"  
OnClick="_getApiVersionButton_xl_Click" Text="GetAPIVersion" Width="130px" />
<asp:TextBox ID="_getApiVersionTextBox_xl"  
Width="160px"></asp:TextBox>
<br /><br />
<input type="button" style="width:140px" value="Get EWA Session ID" 
onclick="getElementById(‘_sessionIdTextBox_xl’).value = 
(window.top.EwaGetSessionId(‘WebPartWPQ4’));" />
<asp:TextBox ID="_sessionIdTextBox_xl"  Width="305px"></asp:TextBox>
<br /><br />
<asp:ListBox ID="_workbookTypeListBox_xl"  Height="35px" 
Width="148px">
<asp:ListItem Value="FullWorkBook"></asp:ListItem>
<asp:ListItem Value="FullSnapshot"></asp:ListItem>
<asp:ListItem Value="PubishedItemSnapshot"></asp:ListItem>
</asp:ListBox>
<br />
<asp:Label ID="_saveworkbookLabel_xl"  Text="Workbook Name:" 
></asp:Label>
<asp:TextBox ID="_fileNameTextBox_xl"  Width="200px"></asp:TextBox>
<asp:Button ID="_getWorkbookButton_xl"  
OnClick="_getWorkbookButton_xl_Click" Text="GetWorkbook" Width="130px" />
<br /><br />
<input type="button" style="width:160px" value="Reload EWA Workbook" 
onclick="window.top.EwaReloadWorkbook(‘WebPartWPQ4’);" />
<br /><br />
<input type="button" style="width:160px" value="Get Selected EWA Cell" 
onclick="getElementById(‘_cellWithFocusTextBox_xl’).value = 
(window.top.getHighlightedCellA1Reference(‘WebPartWPQ4’));" />
<asp:TextBox ID="_cellWithFocusTextBox_xl"  
Width="160px"></asp:TextBox>

Adding the Default.aspx.cs Code

Use the Solution Explorer in Visual Studio to open Default.aspx.cs. Enter the following code in Default.aspx.cs. You can replace the entire contents of Default.aspx.cs with the code provided here.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Office.Excel.Server.WebServices;
using System.Windows.Forms;
using System.Web.Services.Protocols;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
   ExcelService xlEcsApi = new ExcelService();
   Status[] xlStatus;
   protected void Page_Load(object sender, EventArgs e)
   {
   }
   protected void _getApiVersionButton_xl_Click(object sender, EventArgs e)
   {
      try
      {
         _getApiVersionTextBox_xl.Text = xlEcsApi.GetApiVersion(out xlStatus);
         if (xlStatus != null)
         {
            for (int i = 0; i < xlStatus.Length; i++)
            {
               // Status handler
            }
         }
      }
      catch (SoapException exc)
      {
         // Error handler
      }
      catch (Exception exc)
      {
         // Error handler
      }
   }
   protected void _getWorkbookButton_xl_Click(object sender, EventArgs e)
   {
      WorkbookType wbType = WorkbookType.FullWorkbook;
      switch (_workbookTypeListBox_xl.SelectedIndex)
      {
         case 0:
            wbType = WorkbookType.FullWorkbook;
            break;
         case 1:
            wbType = WorkbookType.FullSnapshot;
            break;
         case 2:
            wbType = WorkbookType.PublishedItemsSnapshot;
            break;
      }
      try
      {
         byte[] bits = xlEcsApi.GetWorkbook(
            _sessionIdTextBox_xl.Text,
            wbType,
            out xlStatus);
         if (xlStatus != null)
         {
            for (int i = 0; i < xlStatus.Length; i++)
            {
               //status handler
            }
            if (0 < bits.Length)
            {
               string saveToPath = @"e:\savedFiles\";
               using (BinaryWriter binWriter = new BinaryWriter(File.Open(
                  saveToPath + _fileNameTextBox_xl.Text,
                  FileMode.CreateNew)))
               {
                  binWriter.Write(bits);
                  binWriter.Flush();
                  binWriter.Close();
               }
            }
         }
      }
      catch (SoapException exc)
      {
         // Error handler
      }
      catch (Exception exc)
      {
         // Error handler
      }
   }
}

When you have finished entering the code, save the application. Then build the application to confirm that there are no errors.

Creating the Web Page

If you do not already have a SharePoint Web Part Page where you can load the web application, follow these steps to create one:

  1. Use the browser to navigate to the site where you want to create the web page.

  2. Select Site Actions and click Create.

  3. On the create.aspx page, click . When the New Web Part Page is displayed, provide a name for the page. For this example, name the page testPage2.aspx. Choose the Full Page template and click the Create button. The testpage2.aspx page loads in Design mode.

  4. Click the Add a Web Part button and add two Web Parts: Page Viewer Web Part and Excel Web Access Web Part. The testpage2.aspx page now contains both Web Parts and remains in Design mode.

  5. Configure the EWA to load a default workbook by opening the Properties task pane. To do this, click the edit link on the EWA Web Part and click Modify Shared Web Part. In the Workbook input field, enter the full path to a workbook from a trusted location. Click OK to apply the change. The workbook loads in the EWA.

  6. Obtain the EWA Web Part ID by right-clicking the page above the Full Page label near the top of the page. Select View Source from the IE context menu, and then search the source for the ID of the EWA Web Part. The ID will be similar to WebPartWPQ4.

  7. Update the Default.aspx code to use the EWA Web Part ID for your web page. Search for ‘WebPartWPQ4’ and replace the string with the EWA Web Part ID.

Configuring the Page Viewer Web Part

Next, you configure the Page Viewer to load the Web application. Follow these steps:

  1. Click the Edit link on the Page Viewer Web Part.

  2. In the Link field, enter the URL of the application. This is the same value that you used when you created the web application in Visual Studio (for example, http://TheExcelServer/_layouts/EwaCustApp/default.aspx).

  3. Click OK in the Properties pane to apply the changes to the Web Part and close the pane. The web page reloads, no longer in Design mode.

Solution Functionality

The web page you created loads the workbook in the EWA and the Page Viewer renders the custom code. Figure 14-2 shows what the page looks like when the custom code is executed.

Note the following:

  • GetAPIVersion button—Clicking this button makes an Excel Services web service GetApiVersion method call. The returned version information is displayed in the adjacent text box.

  • Get EWA Session ID button—Clicking this button makes a call to the EWA EwaGetSessionId function, which returns the sessionId of the currently loaded workbook.

  • Get Workbook button—Clicking this button makes an Excel Services web service GetWorkbook method call using the two adjacent controls as arguments. The drop-down control is used to select the WorkbookType, and the Workbook Name field is used to provide the name of the workbook to be saved. The sample code currently has a hard-coded target folder for these workbooks that you need to update to work in your environment.

  • Reload EWA Workbook button—Clicking this button makes a call to the EWA EwaReloadWorkbook function, which returns a TRUE or FALSE status to reflect whether or not the EWA reload call was successful. The function reloads the original workbook, so any private state that previously existed is lost.

  • Get Selected EWA Cell button—Clicking this button makes a call to the EWA getHighlightedCellA1Reference function, which returns the currently selected cell in the EWA grid. The string is returned in the format ‘Sheet1’!C15. When no cell is selected, null is returned.

    Figure 14-2

    Figure 14-2

Summary

This chapter introduced some of the programmability options that you can use to extend the functionality of Excel Services. The chapter explained the API and all of its methods, and provided examples for writing a managed API solution. Server UDFs were identified as a means of calling custom functions from within workbooks, and you learned how to use the API to extend the EWA functionality within SharePoint pages. Examples were included for customizing the xlviewer.aspx file to allow EWA property values to be passed through the URL.

To summarize, you should now know how to do the following:

  • Create a managed code application to use the Excel Services web service

  • Author and deploy an Excel Services managed UDF assembly

  • Customize xlviewer.aspx to set EWA properties

  • Programmatically get the EWA session ID and author either JavaScript or managed-code to interact with the EWA and its session using the API