Export (0) Print
Expand All

Chapter 12: Excel Services (SharePoint 2010 Web App The Complete Reference)

SharePoint 2010

Summary:  This chapter discusses building Microsoft Excel web-based applications in Microsoft SharePoint 2010 by using OpenXML.

Last modified: March 10, 2011

Applies to: Business Connectivity Services | Excel 2010 | Office 2010 | SharePoint Foundation 2010 | SharePoint Server 2010 | Visual Studio

In this article
Introduction
Excel Capabilities on SharePoint 2010
Configuring Excel Services
Demonstration Scenario
Using the Excel Services REST API
User-Defined Functions
Using the JavaScript Object Model
Using Data Connection Libraries
Summary
Recommended Reading
About the Author

This article is an excerpt from Microsoft SharePoint 2010 Web Applications The Complete Reference by Charlie Holland from McGraw-Hill (ISBN <<0071744568 / 9780071744560>>, copyright McGraw-Hill 2011, 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.

Contents

Microsoft SharePoint started life as a collaboration tool that allowed teams to create basic web sites and use them for sharing documents. Building on this idea, the good folks at Microsoft added some integration between Microsoft Office applications and SharePoint, allowing documents to be opened and edited from a SharePoint site in the same way as a network file share. Given that the aim of the game was to make collaboration easier, this worked very well. Users had a central location for all documents relating to a particular project and could seamlessly access them.

There was a drawback, however: This system was great for static documents, such as Microsoft Word docs, that can easily be displayed on a web site without losing any of their original functionality. But what about interactive documents such as Microsoft Excel? A spreadsheet’s purpose is to allow users to perform calculations and analyze the results; doing this effectively requires some interactivity. A static spreadsheet is no more useful than a list of numbers in a Word document. Realizing this, the SharePoint team at Microsoft introduced Excel Services in Microsoft Office SharePoint Server 2007. In effect, Excel Services provide the calculation and display engine of Microsoft Excel as a server-side component. It allows documents created using the Excel client application to be stored on a SharePoint site and used via a web browser, all without the need to install the Excel client application.

SharePoint 2010 extends this concept. Although the inclusion of Excel data and visualizations within SharePoint pages was possible with Office SharePoint Server 2007, using SharePoint 2010, a new REST API makes this easier than ever before. Furthermore, the addition of a JavaScript object model means that the calculation and visualization capabilities of Excel can now easily be used to deliver richer browser-based applications.

In addition to the functionality of Excel Services, Microsoft also provides Office Web Applications, web-based versions of Microsoft Office tools that can be deployed as a feature on SharePoint Server 2010. Using the Excel web application also allows real-time sharing of web-based Excel documents: more than one user can be working on the same document at the same time, and changes made by all parties are simultaneously applied to all open documents.

Excel is very much the tool of choice when it comes to analyzing numerical data. As we progress through this chapter, you’ll see how you can leverage this tool to add a new dimension to your business applications that would previously have been impossible without a huge amount of development work.

As mentioned, Office SharePoint Server 2007 introduced Excel Services as a mechanism for using Excel-based models and data on the server. With SharePoint 2010, Microsoft has extended this offering to Excel Services 2010, an updated version of the existing Excel Services functionality, and the Excel Web Application, a browser-based version of the Excel client application.

Let’s take a look at the features available in Excel Services 2010.

Excel Application Services

The Excel Services application service can be configured on SharePoint Server 2010. Comprising Excel Calculation Services and potentially a collection of user-defined functions, Excel Services is responsible for loading workbooks and performing appropriate calculations. Where workbooks contain links to external data or user-defined functions, Excel Calculation Services is responsible for making the appropriate connections and loading any required external assemblies. Excel Calculation Services maintains a session for each open workbook user, and the session remains open until it is either explicitly closed by the user or it times out. Furthermore, when loading workbooks into memory, Excel Services maintains a cache of workbooks as well as any associated external datasets.

Excel Calculation Services does not support the complete range of features offered by the Excel client application. In the previous version of Excel Services, if a workbook contained a feature that was unsupported, it was impossible to load it using Excel Services. With Excel Services 2010, a more pragmatic approach has been taken; it’s now possible to load any workbook, and if the workbook contains unsupported features, a warning is generated. This allows users to continue using supported features within Excel Services without having to modify the spreadsheet.

User-Defined Functions

As mentioned, Excel Calculation Services has the responsibility of calling external functions as required by a workbook. User-defined functions (UDFs) make it easy to extend the capabilities of Excel Services to include interactions with external systems as well as custom calculation logic. In fact, anything that can be defined using a .NET assembly can be referenced as a UDF provided the appropriate interfaces are implemented.

Excel Client Service

As you’ve seen, Excel Calculation Services is responsible for loading workbooks and performing the necessary calculations. However, when it comes to accessing the results of those calculations both programmatically and for display purposes, the Excel Client Service provides a number of different mechanisms.

UDF Real-World Example

Here’s an example of where all of this could be useful: I was involved in the redesign of a hydrocarbon accounting system. Hydrocarbon accounting, for those uninitiated in the art, is a consequence of the fact that most oil extraction companies do not operate refineries or their own dedicated pipelines. Generally speaking, a refinery operator provides a pipeline that connects up all oil extraction companies to the refinery. In an ideal world where oil was just oil, this would be a straightforward affair; oil extraction companies would simply meter how many barrels of oil they sent down the pipeline and receive payment from the refinery based on the number of barrels. Unfortunately, oil isn’t just oil. In fact, oil is a generic name for a collection of hydrocarbons, each with different relative values—for example, lighter hydrocarbons that make up petroleum tend to have higher values than the heavier components of bitumen. As a consequence of the different hydrocarbon blends that are pumped into the pipeline by each extractor, each blend must be periodically sampled to determine exactly which hydrocarbons are present, and these samples are used to determine which portion of the consolidated mix that reaches the refinery belongs to which extractor. Considering that a pipeline may have 40 to 50 different extractors and each extractor may have several different wells all producing a different blend, you can see that determining the relative value of the product being pumped is no easy feat.

The system that I worked on made extensive use of Microsoft Excel and Visual Basic for Applications (VBA) in performing these complex calculations. Samples were stored in a database and were extracted into a complex series of spreadsheets that were ultimately used to produce statements for the connected extraction companies. Since the application worked its magic on the client, all calculations had to be performed in real-time whenever a statement was required. As you can imagine, this was a time-consuming process. However, given the technologies of the day, Excel as a calculation engine was unsurpassed and the system worked well for a number of years.

Now to get back to UDFs and Excel Services—had I been rebuilding this system today, Excel Services would have allowed the calculations to be performed on the server. Furthermore, the calculation results would be automatically cached and available to multiple users effectively instantly. UDFs could be easily used to replace the complex calculation functions that were previously coded using VBA and sample data; in my example, this was stored in an Oracle database and entered via a PowerBuilder user interface, and this could easily be captured using an InfoPath form and stored in a SharePoint list. The key thing to note in all of this is that the exact same spreadsheets that we were using in the original system could be reused with Excel Services with practically no major modifications.

Excel Web Access

So that workbooks calculated using Excel Calculation Services can be rendered in the browser, Excel Services provides the Excel Web Access web part. This web part effectively creates HTML based on the output of Excel Calculation Services that mirrors the output we would expect to see if we were using the Excel client application. As well as rendering output, the web part also provides a similar level of interactivity to the Excel client application as well as a few custom features that have been designed for use in web applications. For example, an Excel Web Access web part can be placed on a page and configured to display only a particular chart. If the chart is generated based on a table of data within the spreadsheet, the data that makes up that table can be filtered by hooking up a filter web part to the Excel Web Access web part. By using techniques such as this, you can create highly interactive data visualization tools simply by leveraging the functionality of Excel.

Excel Web Services

Sometimes we don’t really need to see an entire spreadsheet; sometime we’re just interested in the bottom line and we want to use that value in our application. Using Excel Web Services allows us to interact with a workbook in much the same way as we can interact with the Excel object model when building complex client applications. For example, using Web Services, we can set values within a spreadsheet or extract values from particular cells. We can even generate a snapshot of a spreadsheet that can be downloaded.

If, for example, we apply this to the hydrocarbon accounting story (in the sidebar "UDF Real-World Example") using Excel Web Services, we could produce a simple application that accepted a range of dates and use those to provide production statistics for a particular extraction company.

JavaScript Object Model

Using Web Services is all fine and well when it comes to interacting with Excel Services from a client application, but what happens if we simply want to display a particular value of a web page? Or perform a calculation based on user input? Of course, we can still use Web Services, but calling Web Services from JavaScript is not for the faint of heart and imposes restrictions on the implementation of the Web Service itself. One of the new features of Excel Services 2010 is the JavaScript Object Model. (Strictly speaking, it’s called the ECMAScript object model, but that term’s always had a hint of Ghostbusters for me so I’m going to stick with the JavaScript Object Model.) The JavaScript Object Model (JSOM) can be used by inserting JavaScript onto a page containing the Excel Web Access web part. In effect, the web part emits the client-side objects necessary to interact with its contents via JavaScript.

REST API

One technology that’s gained a lot of momentum in recent years is Representational State Transfer (REST). I say it’s gained a lot of momentum because REST, like XML, is a description of an aspect of something that we already know and love and have been using successfully for many years: the World Wide Web. REST describes the architecture of the Web, and one of its key principals is that each resource should have a unique global identifier. Resources can be accessed using a standardized interface, and representation of the resource can be exchanged using a well-known format. Sound familiar? In web terms, this means that each resource should have a uniform resource indicator (URI), and the URI can be accessed using the ubiquitous Hypertext Transfer Protocol (HTTP), and a representation of the resource generally in the form of HTML can be retrieved.

The reason REST has gained a lot of attention in recent years is due to the extension of these principles into other areas. What if a resource is not just a web page? What if a resource is a record in a dataset? And, what if, rather than an HTML representation, a JSON representation is returned? You can see that we’re stepping into the world of Web Services without the formality that comes with Simple Object Access Protocol (SOAP). For a lot of purposes, this is a more attractive proposition.

The key difference between a REST-ful web service and a SOAP web service is the existence of a message. SOAP is all about sending a well-defined message to a particular endpoint (or resource in REST parlance), whereas REST is about communicating with the endpoint only. There is no message, just a simple request for a specific resource. We could, of course, make the case that SOAP is an implementation of a REST-ful service, but that’s a whole different story.

The introduction of REST to Excel Services is an immensely useful feature, especially when you consider that one of the main uses of Excel Services is to create and use server-based calculation models. The thing with models is that they exist only to provide results. More often than not, the model itself is not of interest, only the conclusions that can be reached by using it. By applying REST principles to a model, we can retrieve only the conclusions. More importantly, we can do so by using a human-readable uniform resource locator (URL), and this exponentially increases the potential for reusing that data.

For example, if an Excel workbook contains a chart of sales figures that is generated using data in the workbook, using REST we can pick up the URL for the chart itself and use it within our web site in the same way that we’d use any other image. The difference between using a normal image and a REST URL is that the chart will be updated automatically as the data in the underlying workbook changes. To give some contrast, consider what would be involved if we had to do this using SOAP.

Excel Web App

With the announcement of Office 2010, Microsoft introduced a new product version: the Office Web Apps. Office Web Apps consists of new web-based versions of Excel, Word, PowerPoint, and OneNote, which are available via Windows Live, Microsoft Office Online, or as an add-on service for SharePoint 2010.

The key aim of the Excel Web App is to mirror the user experience of the client application within the browser, allowing users to access their spreadsheets from anywhere using only a web browser. Beyond this, the web-based nature of the product delivers a few additional features that are not available in the client version. Probably the most significant of these is the ability to co-author documents. Consider the current situation when using Excel client, where each user editing a file must apply a lock, and a locked file cannot be edited by any other user. As developers, we experience this type of problem regularly when using source code control systems. There’s no more heinous a crime than a developer locking a file and then going off on vacation for two weeks. The Excel Web App prevents this problem by allowing all users with the appropriate permissions to edit a document simultaneously. Changes are pushed down in real time to all user sessions.

Using Excel Services, this collaboration can be further enhanced by using Excel Web Services or the REST API. It’s possible for an external application to update a spreadsheet using a web service call or the REST API. Just as with other changes, these will be automatically pushed down to open user sessions.

Power Pivot

I read a paper recently that suggested that the average cost of a single reusable business report could be as high as $20,000. At first, this seemed like an astronomical figure, but the more I thought about it, when you factor in the infrastructure costs and the to and fro between business users and developers, it’s not an unrealistic cost.

Of course, the bigger question that this raises is whether the information provided by the report is sufficiently valuable to justify the cost incurred in obtaining it, and this highlights one of the big conflicts of business intelligence. To achieve the goal of business intelligence is to make up-to-the-minute, relevant information available to the right people at the right time. The problem is that determining what information is relevant and how it should be collected and presented is usually filtered through business analysts and IT project staff. Even when report definitions remain true to their original purpose, the world has changed by the time the reports are delivered. True business intelligence must incorporate a large self-service element, not just in terms of retrieving data from a selection of predefined reports but in using this data together with data from many heterogeneous sources in a manner that best fits the problem at hand.

PowerPivot is an add-in for SQL Server 2008 R2 and Excel 2010 that aims to address this issue directly. In effect, PowerPivot allows users to create unique data models from a range of data sources and embed the completed model within an Excel workbook. After the data is embedded within Excel, it can be easily analyzed using PivotTables, charts, or any of the myriad analysis features available within Excel.

You might be thinking, what’s different between PowerPivot and simply using external data sources? Excel is a versatile tool, but it’s fair to say that it has two main functions: to create numerical models and to analyze numerical data. When it comes down to creating models, using external data sources is a great way to import raw data into a model; however, when we’re using Excel as an analysis tool, there are a few issues with imported data. The first issue is volume: Excel has a limit of 1 million rows. The next issue is performance: If you’ve tried to run a PivotTable on a sheet with a huge number of rows, you know it’s not a pretty picture! PowerPivot addresses both of these issues by using an in-memory version of Analysis Services to process queries and store the underlying data. This means that it’s possible to analyze data sets containing tens or even hundreds of millions of rows, and, furthermore, it’s fast. As the old adage goes, "Always use the right tool for the job," and the right tool for analyzing high volumes of data is Analysis Services. PowerPivot is all about making the right tool available to the right audience at the right time.

Impressive as PowerPivot is, this chapter doesn’t cover it in full; see Chapter 18 for more.

Developing solutions using Excel Services is pretty straightforward. We’re all familiar with Excel and what it can do, so all we really need to do is save our spreadsheet onto our SharePoint Server and our mission is accomplished. (Maybe it’s not quite as simple as that, but the point I’m making is that we’re not facing a near vertical learning curve here.)

Where we do need to pay a bit more attention is in the configuration of Excel Services. Left unchecked, users could upload server-crushing calculation mayhem. They could easily create a spreadsheet that dragged in the entire contents of the company customer relationship management (CRM) database and performed string matches on description fields before extracting the top ten customers and the value of their sales during the past two weeks. We could, of course, argue that this is a job for the IT pros and it’s probably fair to say that as a day-to-day task it is, but what we’ll find as we start developing solutions using Excel Services is that practically all the problems we’ll encounter come down to configuration issues.

Service Application Settings

Chapter 9 covered the Service Application Framework and how it’s used by SharePoint to provide services that can be shared among web applications. It will probably come as no surprise to learn that Excel Services is implemented using the Service Application Framework.

To configure Excel Services, we use the SharePoint 2010 Central Administration tool. From the Central Administration home page, the Excel Services configuration settings can be reached by clicking Application Management | Manage Service Applications | Excel Services. Of course, if more than one instance of Excel Services is running, you’ll be able to configure that using whatever name you assigned when you set it up.

When you’re configuring and using Excel Services, keep in mind the notion of trust. System administrators determine what is trusted and what isn’t, and users are allowed to create and use workbooks that make use of trusted resources only. So, for example, a system administrator may decide that data from a particular source—such as a data warehouse—is trusted, whereas data from a second source—such as a CRM application—isn’t trusted. Making this distinction doesn’t mean that the CRM system data is any less accurate than the data warehousing data; of course, the opposite is likely true. What it means is that the system administrator has determined that the schema and data volumes within the CRM system are likely to cause issues if they are used freely within Excel. By choosing not to trust this data source, users will be unable to reference it when creating workbooks. So the notion of trust is actually about trusting the resource to play nicely within Excel Services.

The configuration settings for Excel Services involve five sections, as shown next. I’ll quickly run through these, calling out any settings that are relevant to developers.

Figure 1. Configuration Settings

Configuration settings

Global Settings

As you might expect, this section covers most of the high-level configuration options such as load balancing and memory utilization. For the most part, these settings are unlikely to cause developers problems and are best left to the IT pros as part of maintaining and configuring the farm overall—with one exception: the external data settings. When a spreadsheet is created that connects to an external data source, you need to consider a few options for authentication. These are covered in more detail later in the section "Using Data Connection Libraries," but for now you should know that if the authentication type is set to None, connections are made using the unattended service account. Since the unattended service account may be used to connect to many different types of data source, the Secure Store Service is used to map appropriate credentials to the account. This allows non-Windows credentials to be used where appropriate. The configuration of this is covered in the "Demonstration Scenario" section later in the chapter.

Trusted File Locations

The Trusted File Locations section is probably not going to cause you too many problems. In previous versions of SharePoint, this was not configured by default, so no Excel Service workbooks would load properly. Thankfully, with SharePoint 2010, the default setting is to allow all workbooks stored within SharePoint to be accessed using Excel Services.

You can use workbooks that are not stored within SharePoint within Excel Services. Trusted file locations can be created for Universal Naming Convention (UNC) paths or other web sites. A few things worthy of mention include the User-Defined Functions setting and the settings in the External Data section. Although User-Defined Functions have a separate configuration heading, they can be disabled or enabled at the trusted file location level. By default, UDFs are allowed. In the External Data section, external data can be disabled at the trusted file location, and the ability to refresh external data when using the REST API is determined by a setting in this section.

Trusted Data Providers

Trusted Data Providers defines the lists of drivers that can be used when connecting to external data sources. If a driver isn’t listed, it can’t be used to create a connection. Having said that, even if a driver is listed, there’s no guarantee that it can be used.

Trusted Connection Libraries

Data Connection libraries serve a few functions: They allow a system administrator to create a library with a series of preconfigured connections that can be easily used by business users for various tasks. In addition, data connection information is stored in a central location, and any changes that may be required can be made globally without having to update myriad documents throughout an organization. At the Trusted File Location level, you can restrict data connections to trusted data connection libraries only. Unless this option is selected, users are free to create their own data connections using any of the trusted providers and embed those connection details within a workbook.

User-Defined Function Assemblies

In this section, you can configure any assemblies containing user-defined functions. UDF assemblies can either be loaded from a file location or from the Global Assembly Cache (GAC). Note that the assembly must be available on all machines running Excel Services. For ease of administration, an assembly can be disabled, meaning that it remains configured but can’t be used by Excel Services.

To demonstrate the capabilities of Excel Services, consider the following scenario:

AdventureWorks, one of your clients, has implemented SharePoint 2010 as its corporate intranet platform. To increase awareness of company performance within the organization, you’ve been asked to add an indicator of global sales to the front page of the intranet site. Since AdventureWorks is a global organization, it should be possible to filter the sales figures by geographic region, and the amounts shown should be visible in a range of currencies, selectable by the user.

It probably comes as no surprise to learn that we can implement this solution using Excel Services. We can render an interactive chart using the Excel Web Access web part, and if we base the chart on a pivot table, data will be automatically refreshed. To filter the sales figures by geographic region, we can incorporate a slicer into the design, which will allow users to select from a graphical list of available regions.

Displaying the results in various currencies is a bit more involved, since the data in the underlying database is stored in a single currency only. To achieve this, we’ll create a custom UDF that will retrieve current exchange rates via a web service call. We’ll then make use of that data to recalculate the workbook in the appropriate currency. Because we want to allow the user to select from a list of currencies, we’ll make use of the JavaScript API to pass in a currency code selected from a drop-down list. When the currency code is passed into the workbook via the API, the workbook will be refreshed using the appropriate currency.

Set Up Adventure Works Sample Database

To provide some sample data to work with in our various examples, we need to download and install the SQL Server 2008 sample databases from Samples. Our examples make use of the AdventureWorks OLTP database installed on the local instance of SQL Server 2008.

Create a Sample Site

Now we’ll create a new blank site to use for development. Note that if we were creating a complete Business Intelligence (BI) solution or intended to use the site mainly for hosting dashboards, the Business Intelligence Center site template would be a more appropriate place to start. Since we’re using only a single Excel workbook, we’ll make use of a basic blank site.

  1. In SharePoint Designer, choose File | Sites, and then click the New Blank Web Site button. Type the name of the new site as http://localhost/Chapter12.

  2. We’ll add a new document library to host our Excel Workbook. From the Site Objects pane, select Lists and Libraries.

  3. From the ribbon, select Document Library | Document Library. Name the new library Excel Workbooks:

    Figure 2. Create List or Document Library

    Create list or document library

Create a Workbook for Use with Excel Services

Before we can make use of an Excel workbook in SharePoint 2010, we need to create it using the Excel client application. For our demonstration scenario, we need a simple workbook that contains a pivot table and a pivot chart. The pivot table should be generated from data stored in the organization’s ERP system.

  1. Open Excel 2010. A new blank workbook will be automatically created, and we can make use of this workbook for our demonstration. First, we need to add a data connection so that we can retrieve appropriate sales data. On the Data tab, in the Get External Data section of the ribbon, select From Other Sources | From Data Connection Wizard, as illustrated:

    Figure 3. Get External Data

    Get external data
  2. In the Data Connection Wizard dialog, accept the default selection of Microsoft SQL Server by clicking Next to proceed.

  3. In the Server Name text box, enter .\SQLExpress. For the Log On Credentials, select Use Windows Authentication. Click Next.

  4. In the Select Database and Table step, change the selected database to AdventureWorks, and uncheck the Connect To A Specific Table checkbox as shown. Click Finish to complete the wizard.

    Figure 4. Data Connection Wizard

    Data Connection Wizard
  5. After the wizard has completed, the Select Table dialog is displayed. Although the data that we require comes from more than one table, we need to select a table to create a connection in our workbook. Select the SalesOrderHeader table, as shown, and then click OK.

    Figure 5. Select Table Dialog Box

    Select table dialog box
  6. The Import Data dialog allows us to select what we want to do with the connected data and includes a few options, such as Table and PivotTable as well as a range selector that we can use to specify where the data should appear in the workbook. When using Excel Services, you should be aware that only PivotTables can be refreshed on the server. Although it is possible to add a table and use the data within the table in Excel Services, the only way to refresh the table data will be to open the spreadsheet in Excel and refresh manually. Bearing this in mind, select PivotTable Report and put the data at the default location of $A$1 in the current worksheet.

Configure a Data Connection

A new PivotTable is inserted on the page at the specified location. Before we start configuring the PivotTable, we need to review our connection settings. Recall that we selected the SalesOrderHeader table as the source of our data; before we can set up our pivot table, we need to change this to use a SQL query instead.

  1. From the Data tab, select the Connections option. In the Workbook Connections dialog, select the ._sqlexpress AdventureWorks connection. Notice that your connection may have a slightly different name, depending on your database server and whether an existing connection with that name already exists.

  2. Click Properties to view the connection details. Change the connection name to AdventureWorksLast30DaysSales.

  3. Click the Definition tab to see details of the connection string, the command type, and the command text as well as a few other options. Change the Command type to SQL and enter the following SQL statement in the Command Text text box:

    SELECT   H.OrderDate,
             T.Name as Territory,
             T.CountryRegionCode as CountryCode,
             sum(TotalDue) as TotalSales
    FROM     [Sales].[SalesOrderHeader] as H
    INNER    JOIN [Sales].[SalesTerritory] as T
    ON       H.TerritoryID=T.TerritoryID
    WHERE    H.OrderDate>'2004-07-01'
    GROUP BY H.OrderDate, T.Name, T.CountryRegionCode
    
  4. When a data connection is used by Excel, a copy of the connection information is stored in the workbook. In the Properties dialog, we’re effectively editing the properties of this cached copy of the data connection. To update our locally saved connection file, click Export Connection File and then, in the file dialog that appears, type the filename as AdventureWorksLast30DaysSales.odc. Click Save to create the new Office Database Connection file.

  5. Click OK to close the Properties dialog, and then click Close to close the Workbook Connections dialog. Notice that the fields listed in the PivotTable Field List have changed to match those in our amended query.

Configure a Pivot Table to Act like an External Datalist

PivotTables are a great help for analyzing a data set interactively. We can easily add in row headers or columns headers or formulas and grouping to the data. Sometimes we don’t need to do any of that clever stuff, though; we might want a simple list of the data as it looks in the database. In Excel client, we could of course achieve such a result by creating an External Data List as opposed to a PivotTable. However, External Data Tables aren’t supported in Excel Services, so we’re stuck trying to reign in the analytical faculties of the PivotTable to produce a more sedate output.

To create a PivotTable that behaves in a similar manner to an External Data List, take the following steps:

  1. From the PivotTable Field List, drag OrderDate, CountryCode, and Territory into the Row Labels section. Drag Sum of TotalSales into the Values section, as illustrated:

    Figure 6. PivotTable Field List

    Pivot table field list
  2. From the PivotTable Tools tab, select the Design menu. In the Layout section of the ribbon, select Report Layout | Show In Tabular Form. Again from the Layout section, select Report Layout | Repeat All Item Labels.

  3. The resulting PivotTable is starting to look a bit like a data list. We can now remove the total rows by selecting Subtotals | Do Not Show Subtotals from the Layouts section of the ribbon.

  4. To remove the +/– buttons, open the Options menu from the PivotTable Tools tab. Click the +/– button on the Show section of the ribbon to toggle the buttons off.

Using Named Ranges in Excel Services

You may be wondering why we had to go to the trouble of changing our PivotTable to a flat data list. It’s fair to say that, generally speaking, we wouldn’t normally need to take this step when using data in Excel Services, but this case is a bit different. The TotalSales value retrieved from the database represents the sales value in US dollars (USD). However, our demonstration scenario requires us to be able to present this data using a variety of currencies.So that we can convert this value to a different currency, we need to use a formula, and formulas within PivotTables are limited to include only data from within the PivotTable. In our case, the exchange rate value that will be used by our formula will be stored elsewhere in the workbook, so using a PivotTable formula isn’t an option. We can achieve our desired outcome by flattening our PivotTable and then adding appropriate formulae in adjacent cells.

Let’s move on to add a few named ranges that will be used on our calculation logic:

  1. Navigate to Sheet2 in the Excel workbook. We’ll use this sheet to store the values required by our exchange rate calculation.

  2. In cell A1, type Exchange Rate. In the adjacent cell (B1), type the number 1. We’ll come back to this later when we create a UDF. With the cell B1 selected, in the Name box, enter ExchangeRate, as illustrated:

    Figure 7. ExchangeRate

    Exchange rate
  3. In cell A2, type Currency Code. In the adjacent cell (B2), type USD. With cell B2 selected, in the Name box, type CurrencyCode.

  4. In cell A3, type Chart Title. In the adjacent cell (B3), add the following formula:

    ="Last 30 Days Sales in " & CurrencyCode

    When completed, the first few cells of Sheet2 should look like this:

    Figure 8. Exchange Rate

    Exchange rate

Perform Calculations Using Pivot Table Values

Now that we’ve defined the parameters for our exchange rate calculation, we can add the necessary formulae to Sheet1.

  1. Switch back to Sheet1. In column E, cell E1, add header text SelectedCurrencyValue.

  2. In cell E2, add this formula:

    =GETPIVOTDATA("TotalSales",$A$1,"OrderDate",A2,"Territory",C2,"CountryCode", B2)*ExchangeRate

    This formula extracts the value of the TotalSales column from the PivotTable, where the OrderDate, Territory, and CountryCode columns match the values contained in cells A2, C2, and B2. In plain English, the formula returns the TotalSales value for the current row.

  3. Since we want to perform this calculation for each row in the table, we need to use this formula in every cell down to the bottom of the table. To do this, type E2:E206 in the Name box, and then press CTRL-D. Alternatively, we can manually select the cells in question and then click Fill | Down from the Editing section of the Home ribbon.

    NoteNote

    Using formulae in this manner requires special consideration when the PivotTable referenced will be periodically refreshed. If, during a subsequent refresh, the PivotTable ends up with a different number of rows, the formulae will not automatically be filled down to accommodate the growth of the table. It is important that you ensure that the size of the returned dataset remains constant, and generally this can be done using Transact-SQL (T-SQL) or by calling a stored procedure to produce the required data.

  4. Since we’ll use the data contained in the PivotTable and our calculated column later, we’ll give it a name for ease of reference. Either manually highlight the cells in the range A1:E206 or enter the range in the Names box. Once the range is highlighted, type SourceDataTable. Sheet1 should now look like this:

    Figure 9. SourceDataTable

    Source data table

Add a Pivot Chart

Now that we’ve created a data source that can be automatically refreshed by Excel Services, we can move on to create a chart based on the source data. We’ll render the chart on our web page to provide a graphical representation of the sales data.

  1. Select Sheet3. We’ll use this sheet to contain the elements of our workbook that will be rendered on our sample site. Choose Insert | PivotTable | PivotChart.

  2. In the Create PivotTable with PivotChart dialog, type SourceDataTable as the Table/Range:

    Figure 10. Create PivotTable with PivotChart

    Create pivot table with pivotchart
  3. From the PivotTable Field List, drag OrderDate into the Axis Fields section, CountryCode into the Legend Fields section, and SelectedCurrencyValue into the Values section. The field lists should look as shown:

    Figure 11. PivotTable Field List

    Pivottable field list
  4. Our chart is automatically generated based on our PivotTable data. However, the default clustered bar chart type doesn’t make it easy to visualize our data set, so let’s change this to something more appropriate. From the Design menu, select the Change Chart Type button. In the Change Chart Type dialog, select the Stacked Area chart type.

  5. To add a title to our chart, select the Chart Title button from the Layout menu. Since we want our chart title to be automatically generated based on the currency code selected, we can add the following formula:

    =Sheet2!$B$3

The PivotChart should look like this:

Figure 12. Sum of SelectedCurrencyValue

Sum of SelectedCurrencyValue

Publish to Excel Services

The first version of our workbook is now complete and ready to be published to our SharePoint site:

  1. In Excel, click the File menu to enter the backstage area. Select Share from the list of options and then select Publish to Excel Services.

  2. Set the path to http://localhost/Chapter12/Excel Workbooks and the filename to Last30DaysSales.xlsx.

  3. Click to save the file to SharePoint.

TipTip

When using Excel 2010 on Windows 2008 server, trying to save files to SharePoint doesn’t quite work as it should. This is because the WebClient service that maps the SharePoint URL to a UNC path behind the scenes, isn’t configured by default since it has no purpose on a server operating system. To fix this problem, install the Desktop Experience feature using Server Manager. See Chapter 2 for a step-by-step guide on configuring Windows 2008 Server as a development machine.

Create a User Interface Using the Excel Web Access Web Part

Now that we have our workbook published to SharePoint, we can move on to make use of it when generating a user interface for our sample application. We’ll customize the homepage of our site to include our sales chart.

  1. Navigate to http://localhost/Chapter12/Default.aspx. From the Site Actions menu, choose Edit Page.

  2. In the Left Web part zone, click Add A Web Part.

  3. Select the Excel Web Access (EWA) web part from the Office Client Applications category. Click Add to include the web part on the page.

    NoteNote

    If this web part is missing from the web part gallery, ensure that the SharePoint Server Enterprise Site Features and SharePoint Server Enterprise Site Collection Features are enabled for the current site and site collection.

  4. To set the properties of the web part, click the Click Here To Open The Tool Pane link.

  5. In the Workbook Display section, type the workbook as /Chapter12/Excel Workbooks/ Last30DaysSales.xlsx.

  6. Since we’re interested only in the chart for now, in the Named Item field type Chart 1. Click Apply to see the results.

We’ve now got our PivotChart on the page ready for use. Let’s tidy up a few of the remaining web part settings to give the page a more integrated look:

  1. Set the Type of Toolbar to None. This will hide the toolbar that appears above the chart.

  2. In the Appearance section, set the Chrome type to None.

  3. Click OK to commit the changes and stop editing the web part.

  4. From the Page ribbon, click the Stop Editing button to switch back to View mode.

Adding Interactivity Using a Slicer

You’ve seen how easy it is to make use of Excel data on a page when using the Excel Web Access web part. Let’s move on to look at an example of the interactive features available via the web part. Our demonstration scenario requires that the data displayed in our chart be filterable using geographical locations. Although we have listed multiple series, one for each country code, at the moment we don’t have any way to select which series should be displayed on the chart.

This section introduces the Slicer, one of the new features available in Excel 2010 that works very well with Excel Services. Before we can use a Slicer, we need to add it to our Excel workbook.

  1. Navigate to the Excel Workbooks document library at http://localhost/Chapter12. Open the Last30DaysSales.xlsx file using Microsoft Excel by right-clicking the file and selecting Edit in Microsoft Excel from the context menu.

  2. Note that the workbook opens in Protected View mode. This happens because the workbook is opened from a web site as opposed to a local folder. Click Enable Editing to proceed.

  3. The next warning we receive says that "Data Connections have been disabled." This is a standard security feature of Excel that prevents active content from running until it is explicitly allowed. Click Enable Content to refresh the connected data.

  4. We have the option to make the document trusted. Click Yes, and the workbook won’t open in Protected View mode each time. This is OK since we’ll be doing a bit of work with it.

  5. Adding a Slicer is simple. Select the PivotChart and then, from the Analyze menu, click the Insert Slicer button.

  6. From the Insert Slicers dialog, check the Territory checkbox and then click OK to add the Slicer to our worksheet.

  7. To see the Slicer in action, try selecting one or more (by holding down the CTRL key while clicking) of the listed Territory values. We can see that the PivotTable data is filtered based on our selection and the PivotChart is redrawn accordingly.

Grouping Excel Items for Display Purposes

Since we want to display only the Slicer and chart on our web page, we need to lay them out in a specific manner. You’ll remember that when we configured the EWA web part earlier in the chapter, we entered a specific named item to display—Chart 1. We now need to display the chart and the Slicer, and since we can enter only one named item, we need to group these into a single item.

As you’ve seen earlier, named ranges can be defined by selecting a range of cells and then adding a name. We’ll use a named range to refer to our chart and Slicer control.

  1. Place the chart and the Slicer next to each other on the sheet.

  2. Resize the chart and the Slicer so that they fill entire cells as much as possible. This will reduce unnecessary white space when the control is rendered in the web page. The zoom function is very useful for this purpose.

  3. Select the underlying range using one of the methods described earlier and type the name ChartAndSlicer in the Name box.

  4. Click the Save icon in the upper-left corner to save the changes to SharePoint. We’ll keep the workbook open for now since we’ll be making additional changes later.

If we now open the home page of our sample site using Internet Explorer, unsurprisingly we’ll find that our chart is still there just as before, without our new Slicer control. One thing that may be apparent is that the chart now shows only the territories that we selected earlier when we were messing around with the Slicer in Excel client. Note that slicer selections are published along with the workbook, so it’s important to make sure that an appropriate default is set before saving.

Let’s move on to change our Excel Web Access web part to display our Slicer as well as our chart.

  1. From the SiteActions menu, select Edit Page.

  2. Highlight the Excel Web Access web part by clicking the header; then, from the Web Part Tools tab, select Web Part Properties from the Options menu.

  3. Change the Named Item to ChartAndSlicer. Click Apply to view the change. Our recently defined named item should be displayed on the page, but, instead, we’re presented with the following error message stating that the named item cannot be displayed:

    Figure 13. Excel Web Access

    Excel web access error message
  4. Click OK to acknowledge the error message. Then from the Page menu, select Stop Editing. The page will now be blank except for the error message.

Change Published Items within a Workbook

When we initially published our workbook to Excel Services, we simply gave it a name and accepted the default values. Whenever we click the Save icon, rather than re-publishing the workbook, we’re merely saving the data back to the document library. The significance here is that when publishing a workbook to Excel Services, we have the option of specifying additional metadata, but when saving, the metadata is not changed. We received the error because the metadata did not contain details of our new named item.

  1. Switch back to Excel client. Click the File menu to enter the backstage area. Select the Share option, as shown:

    Figure 14. Share Option

    Share option
  2. The Share section offers two options: Save to SharePoint and Publish to Excel Services. As described, the difference between these two options is the ability to add metadata that’s useful to Excel Services. Let’s see what that means in practice. Click Save to SharePoint.

  3. Click the Current Location icon to open the Save As dialog, which automatically displays the contents of our Excel Workbooks document library and allows us to save the workbook in the normal manner. Click Cancel and then return to the Share section of the backstage area.

  4. This time, click Publish To Excel Services to open the Save As dialog, but notice that an Excel Services Options button now appears in the bottom section of the dialog.

  5. Click the Excel Service Options button to define or overwrite metadata for the document. In the Excel Services Options dialog’s Show tab, select Items In The Workbook from the drop-down list.

  6. Check the All Named Ranges and the All Charts options to ensure that they will be available for use by the EWA web part.

    NoteNote

    At the time of writing, a bug (or feature, depending on your point of view) exists within Excel 2010. Named ranges that are blank are not detected by the Excel Services publishing process and therefore don’t appear in the list of Items in the workbook. To resolve this issue, select the ChartAndSlicer named range and press the SPACEBAR. This will ensure that the range appears in the list of metadata.

  7. Click Save to complete the publishing process.

With our metadata updated appropriately, if we return to the sample site home page, we can see that our EWA web part now displays our chart and Slicer as expected. The Slicer behaves in much the same manner as we saw earlier when we used it within the Excel client application.

Although we could add another EWA web part to our page to display the underlying data for our chart, doing so would introduce unnecessary overhead since we don’t need any level of interactivity with the data. Notwithstanding our unquestioned dedication to ensuring optimum performance at all times, another really good reason for not adding a second EWA web part is to give us an opportunity to explore the new REST API that ships with Excel Services 2010.

Excel Services REST API Syntax

The REST API, as discussed earlier, provides a lightweight mechanism for accessing content within Excel workbooks that have been published using Excel Services. In effect, accessing data using a REST-ful service comes down to using the correct URL, and for the Excel Services REST API, the URL format is http://<RootUrl>/_vti_bin/ExcelRest.aspx/<Filename>/model/<Selector>? <Parameters>.

RootUrl

The RootUrl value contains the URL of the SharePoint site containing the workbook. In our case, this will be http://localhost/Chapter12.

Filename

The Filename value contains the relative path to the Excel workbook. In our case, this will be Excel Workbooks/Last30DaysSales.xlsx. However, since we’re creating a URL, we need to encode the space in Excel Workbooks. This value then becomes Excel%20Workbooks/Last30DaysSales.xslx.

Selector

The Selector value is where the fun begins in the REST URL. Let’s run through a quick demonstration of how it works.

If you enter the following URL into a web browser, you’ll see a page listing the types of data available within the model:

http://localhost/Chapter12/_vti_bin/ExcelRest.aspx/Excel%20Workbooks/Last30DaysSales.xlsx/model

As you can see, in the case of Excel, the types available are Ranges, Charts, Tables, and PivotTables.

You can build up the selector value by first deciding in which type of data you’re interested. In our case, it’s Charts. Enter the following URL into a web browser to see a list of available charts:

http://localhost/Chapter12/_vti_bin/ExcelRest.aspx/Excel%20Workbooks/Last30DaysSales.xlsx/model/Charts

From the returned data, you can see that our workbook contains only one chart, Chart 1. Let’s create a selector for the Chart 1 object. In the browser, enter the following URL:

http://localhost/Chapter12/_vti_bin/ExcelRest.aspx/Excel%20Workbooks/Last30DaysSales.xlsx/model/Charts(‘Chart%201’)

Notice that this simply specifies that you want Chart 1 from the Charts collection. Again the space is encoded in the name Chart 1 since we’re building a URL. This time the browser will display a Portable Network Graphics (PNG) image representing our chart.

Parameters

The Parameters value allows us to pass values into Excel Services as part of our request. For example, our workbook defines a named range called CurrencyCode. The value of CurrencyCode is used to produce the chart title, so by changing CurrencyCode as part of our REST URL, our chart title will change as well. To see this in action, in the browser, enter the following URL:

http://localhost/Chapter12/_vti_bin/ExcelRest.aspx/Excel%20Workbooks/Last30DaysSales.xlsx/model/Charts(‘chart%201’)?Ranges(‘CurrencyCode’)=MyCurrencyCode

You can see that the chart has been produced with a label that reads Last 30 Days Sales in MyCurrencyCode.

Retrieving a Pivot Table Using REST

Now that you understand how a REST URL can be generated, let’s put this knowledge to good use by creating a URL that we can use to include data from our PivotTable on our sample site home page.

If we enter the following URL in the browser, we’ll get a 404 error:

http://localhost/Chapter12/_vti_bin/ExcelRest.aspx/Excel%20Workbooks/Last30DaysSales.xlsx/model/PivotTables(‘PivotTable1’)

Before we can access items using the REST API, we need to make sure that we’ve published the appropriate metadata from the Excel client. You’ll remember earlier that we didn’t select any of the PivotTables in our list of items, and that’s why we’re seeing a 404 error now.

  1. Switch back to the Excel client application. Before we publish our PivotTables, let’s give them useful names. Switch to Sheet3 and then click anywhere within the PivotTable.

  2. From the PivotTable Tools tab, select the Options menu. In the PivotTable section, in the PivotTable Name box, type ChartData, as illustrated:

    Figure 15. ChartData

    ChartData
  3. Switch to Sheet1 and type the name of the PivotTable as SourceData.

  4. We’ll add an additional PivotTable to summarize our sales figures. Add a new worksheet named Sheet4. Insert a new PivotTable that makes use of SourceDataTable as its data source.

  5. Add OrderDate as a RowLabel and SelectedCurrencyValue as a Value.

  6. Right-click the Sum of SelectedCurrencyValue column header, and then select Value Field Settings. In the Custom Name text box, type Total Sales.

  7. Name the new PivotTable TotalSalesData.

  8. Click the File menu to enter the backstage area, and then click the Publish To Excel Services button in the Share section.

  9. Click the Excel Services Options button, and then select All Pivot Tables in the Items In The Workbook section.

  10. Save the workbook to update the metadata.

We can now enter the following URL in the browser and see an HTML representation of our chart data:

http://localhost/Chapter12/_vti_bin/ExcelRest.aspx/Excel%20Workbooks/Last30DaysSales.xlsx/model/PivotTables(‘TotalSalesData’)

Using REST-Generated Contact within a Web Part Page

Now that we have a URL, the next step is to make use of that on our home page. The easiest way to include additional content on the page is to use a PageViewer web part.

  1. From the SiteActions menu, select Edit Page.

  2. Click Add a Web Part in the Right web part zone, and then select the Page Viewer web part from the Media and Content category.

  3. Set the Link property of the Page Viewer web part to the REST API URL for our TotalSalesData PivotTable.

  4. Click OK to commit the changes, and then click Stop Editing from the Page ribbon to return to view mode.

We’ve now created a user interface for our sales data that allows users to filter data by territory. As well as using the EWA web part, we’ve also included content generated using the REST API on our page via a Page Viewer web part. All of the items presented in the user interface are dynamically generated based on data from an external data source. As the underlying data changes, the user interface will be automatically updated to reflect those changes. Bear in mind, so far, we haven’t written a single line of code.

We’ve managed to achieve some pretty impressive results by using the tools that are available out of the box with Excel 2010 and Excel Services. However, one of the areas from our demonstration scenario that we haven’t properly addressed is the requirement to dynamically convert the sales data to a range of currencies. We’ve designed our workbook to allow for such a calculation; we just haven’t done the actual calculation part yet, and there’s a good reason for that: Excel doesn’t have a function for automatically retrieving exchange rates for a stated currency.

In this section, we’ll create a UDF that accepts a currency code as an input parameter. The function will then connect to a web service to retrieve a current exchange rate for the selected currency and will pass the resulting value back to Excel for use by the calculations within our workbook.

Attributes Used when Creating UDFs

UDFs are simply managed code assemblies that have been marked up using specific attributes that denote methods and classes that are callable from Excel Services. The attributes in question can be found in the Microsoft.Office.Excel.Server.Udf namespace, which is defined in the Microsoft.Office.Excel.Server.Udf.dll assembly. The following attributes are available.

UdfClassAttribute

This attribute is applied to a public class and is used to denote that the class may contain UDFs that can be used by Excel Services.

UdfMethodAttribute

This attribute can be applied to public methods and is used to indicate that the method is a UDF and can be used by Excel Services. The UdfMethodAttribute accepts a couple of properties:

IsVolatile

This Boolean value specifies whether or not the UDF should be considered volatile. When referring to Excel functions, specifying that a function is volatile indicates that the function should be reevaluated whenever any value in the workbook changes. The default behavior is non-volatile, which means that the function is reevaluated only when any of the parameters that are passed into it change. Generally, the default behavior is appropriate unless the UDF is reading other related data behind the scenes that may have changed as a result of activities elsewhere in the workbook.

ReturnsPersonalInformation

This Boolean value determines whether the identity of the calling user can be accessed via the executing thread’s current principal. This means that where this value is set to true, details of the calling user can be retrieved as follows:

[UdfMethod(ReturnsPersonalInformation = true)]
public string GetCallersUsername()
{
    if (Thread.CurrentPrincipal.Identity != null)
    {
        return Thread.CurrentPrincipal.Identity.Name;
    }
    else
    {
        return string.Empty;
    }
}

Usable Data Types within UDFs

The .NET Framework contains many different data types, and any of these types can be used as a parameter or return value for a method. However, as you may imagine, Excel doesn’t necessarily know how to process each and every data type and can therefore use only a small subset of the types available. General speaking, only types defined in the System namespace are supported, such as String, Boolean, DateTime, and Object. Exceptions to this rule are Int64 and UInt64, which are not supported.

As well as passing simple values, contiguous ranges can also be passed into a UDF as one- or two-dimensional object arrays. For example, the following two functions accept a single row and a range of cells, respectively:

[UdfMethod]
public int ProcessSingleRow(object[] row)
{
    return row.Length;
}

[UdfMethod]
public int ProcessRange(object[,] range)
{
    return range.Length;
}

Creating a UDF Using Visual Studio 2010

For most of our code samples throughout this book, we’ve made use of the SharePoint 2010 templates that are available in Visual Studio. Since a UDF is simply a managed assembly, we can create it using a basic class library project. We can then configure SharePoint to pick up the assembly from the file system whenever it is required by a workbook.

  1. Open Visual Studio 2010. Choose File | New | Project.

  2. Create a new Class Library project and name it SampleFunctions, as illustrated:

    Figure 16. SampleFunctions

    SampleFunctions
  3. Rename Class1.cs to CurrencyConvertor.cs.

  4. As mentioned, this UDF will make a web service call to obtain a current exchange rate. Before we add the code for the UDF function, we need to add a reference to the web service. Choose Project | Add Service Reference.

  5. The Add Service Reference wizard is used to create WCF service references. Generally speaking, this wouldn’t cause any problems. However, the web service to which we’re connecting makes use of the ISO-8859-1 encoding standard, and unfortunately the binding types provided with WCF don’t support this standard. Rather than writing a load of code to support the standard in WCF, we’ll work around the problem by creating a .NET 2.0 Web Reference instead. Click the Advanced button in the Add Service Reference dialog and then in the Service Reference Settings page, click the Add Web Reference button.

  6. In the Add Web Reference dialog’s URL text box, enter http://xurrency.com/api.wsdl.

  7. Type XurrencySoap in the Web Reference Name text box, as shown, and then click Add Reference to generate a proxy for the web service.

    Figure 17. Add Web Reference

    Add web reference
  8. Before we can add our UDF method, we need to add a reference to the Excel Services UDF Framework. Choose Project | Add Reference. In the .NET tab, select Excel Services UDF Framework. Click OK to add the reference.

  9. We’re now ready to add some code to our CurrencyConvertor class. In the CurrencyConvertor.cs file, add the following code:

    using System;
    using Microsoft.Office.Excel.Server.Udf;
    namespace SampleFunctions
    {
        [UdfClass]
        public class CurrencyConvertor
        {
            [UdfMethod(IsVolatile = false, ReturnsPersonalInformation = true)]
            public double GetExchangeRate(string currencyCode)
            {
                XurrencySoap.xurrency client = new XurrencySoap.xurrency();
                client.Url = "http://xurrency.com/servidor_soap.php";
                return client.getValue(1, "usd", currencyCode.ToLower());
            }
        }
    }
    

    Notice a few things about this short code sample. First, the CurrencyConvertor class is marked as public and has the UdfClass attribute attached. Second, the GetExchangeRate method is also marked as public and has the UdfMethod attribute attached.

Within the GetExchangeRate method, we create an instance of the XurrencySoap web service proxy class, and then we use the proxy to call the getValue method. Since getValue actually performs a currency conversion rather than simply providing the exchange rate, we’ve specified via our parameters that the method should return the value of $1 when converted into whatever currency code is passed into the function.

Configuring UDFs for Development

We’ve created a simple UDF using Visual Studio. We can now build the project and then move on to make the necessary configuration changes within SharePoint. As discussed earlier, configuration of Excel Services is done via the Central Administration tool.

  1. Open Central Administration and then select Manage Service Applications in the Application Management section.

  2. Select Excel Services from the list of application either by clicking the Excel Services hyperlink or by highlighting the Excel Services row and then clicking Manage from the Service Applications menu.

  3. We can configure UDF assemblies by clicking the User Defined Function Assemblies link on the Manage Excel Services page.

When it comes to adding UDF assemblies, we have two options. We can either place the assembly in the GAC or we can access it directly from the file system. For development purposes, reading the assembly from the file system is easiest since we can simply build the project on our development machine and the new assembly will be immediately available to SharePoint without us having to take any additional steps. However, bear in mind that reading assemblies directly from the file system represents a significant security risk. It’s a trivial task to tamper with the assembly and have it perform all kinds of nefarious acts under the security context of any unfortunate Excel user who happens to open a workbook that references the function. It is therefore best practice to sign all UDFs on production servers and deploy them to the GAC.

Let’s look at how we’d configure an assembly to be picked up from the file system and how we can debug UDFs.

  • Click the Add User-Defined Function Assembly link.

  • Select File Path as the Assembly Location.

  • In the Assembly text box, enter the path to the assembly. In our case, we’ll enter C:\Code\Chapter12\SampleFunctions\SampleFunctions\bin\Debug\SampleFunctions.dll.

  • Click OK to add the assembly. The User-Defined Functions page will look as illustrated:

    Figure 18. User-Defined Functions

    User-defined functions

Using UDFs within Excel

We can now make use of our custom function in our Excel workbook.

  1. If it’s not already open, in our sample site, navigate to the Excel Workbooks document library and open the Last30DaysSales workbook using the Excel client application.

  2. Switch to Sheet2, and in the cell B1, enter the following formula:

    =IFERROR(GetExchangeRate(CurrencyCode),1)

    This formula simply calls our UDF GetExchangeRate and passes in the value of the CurrencyCode named range. The call to our UDF is wrapped in an IFERROR function so that data is still rendered on the client if an error occurs. Where an error occurs, we’ve used an exchange rate of 1, which will effectively generate charts and PivotTables based on the raw data as opposed to a pile of #NAME? or #VALUE? errors. Note that UDFs are not accessible within Excel client and will always display a #NAME? error.

  3. Commit the changes to SharePoint by clicking the Save button in the upper-left corner. We can use the Save button in this instance because the underlying metadata hasn’t changed. We don’t need to use the Publish to Excel Services function that we used previously.

Using the REST API to Test a UDF

As it stands, we don’t have a user interface to switch between currency codes, but we can check that our UDF is working properly by using the REST API. In a web browser, enter the following URL:

http://localhost/Chapter12/_vti_bin/ExcelRest.aspx/Excel%20Workbooks/Last30DaysSales.xlsx/model/Ranges(‘ExchangeRate’)?Ranges(‘CurrencyCode’)=AUD

This URL is using the REST API to return the contents of the ExchangeRate named range when the CurrencyCode named range contains the value AUD. In other words, we’re using the REST API to display the exchange rate between USD and AUD. The resulting output should be a number similar to 1.15, depending on the actual exchange rate at the time you call the API.

If the number 1 is returned, this indicates that you’ve either set the CurrencyCode to USD (or left it blank) or an error has occurred. The value of 1 is returned because we wrapped our UDF call in an IFERROR function within Excel.

Debugging UDFs

Debugging UDFs is not quite as simple as debugging other SharePoint artifacts. The UDF is called by the Excel Services service application, which runs as a separate process. Bearing this in mind, we have two options: we can either attach our debugger to all instances of the w3wp.exe process, or we can find out which process is being used to run service applications on our server.

Discovering the Process ID Used to Run a UDF

Even though most of us will take the lazy option of attaching to all worker processes, here’s how we can find out the correct Process ID.

As mentioned, Excel Services runs under a separate process. All service applications running on a server are published under the SharePoint Web Services application in Internet Information Server 7.0 (IIS7) and run within a specific application pool. Knowing the name of this application pool is important when we’re working with UDFs since we may need to recycle it to free up locked assemblies.

  1. In IIS Manager, navigate to the SharePoint Web Services site, expand the root node, and select the first service application.

  2. From the Actions pane, select Advanced Settings. In the General section, the name of the Application Pool appears:

    Figure 19. Advanced Settings

    Advanced settings
  3. Armed with the name of the application pool, we can now do a bit of digging to discover the Process ID. Open a command prompt and navigate to the C:\Windows\System32\inetsrv folder.

  4. Enter the following command to list all the worker processes that are being used by IIS:

    appcmd list wp

    The returned list will be formatted as follows:

    WP "7260" (applicationPool:SharePoint - 80)
    WP "7444" (applicationPool:SharePoint Central Administration v4)
    WP "4556" (applicationPool:1c549b9ed5ad4dac8e977df6da3c733b)
    WP "4428" (applicationPool:ae7c416ce0ac4df7a2cfa46b8fa7327c)

    The first column, WP, identifies the type of record—in our case worker process. The second column contains the process ID, and the last column contains the application pool that is using the process. By finding the process that corresponds to our SharePoint Web Services application pool, we can determine the process ID.

Note that the process ID may change between debugging sessions, but the application pool name will remain constant unless SharePoint is reinstalled.

Manually Attaching the Visual Studio 2010 Debugger

Now let’s manually attach the Visual Studio Debugger and recycle the SharePoint Web Services application pool.

  1. In Visual Studio 2010, choose Debug | Attach to Process.

  2. Select the w3wp.exe process with the correct ID value, and then click Attach.

  3. Add a breakpoint within the UDF function. Execution will stop on the breakpoint, allowing debugging to take place.

You’ve seen how to debug UDFs using Visual Studio; now let’s step into our UDF code. What happens if we need to make changes to our code and recompile our assembly? You’d think it would simply be a case of clicking the Build button, but unfortunately it’s not quite that straightforward if we’ve configured SharePoint to pick up the UDF from our bin folder. The Excel Services service application will maintain a lock on the file while it’s in use, making it impossible for Visual Studio to overwrite when compiling a new version. Thankfully, this is an easy problem to fix. All we need to do is recycle the SharePoint Web Services application pool and the lock will be released. This can be done using IIS or using the command line by entering the following:

appcmd recycle apppool /apppool.name:<the name of the app pool>

Configuring UDFs for Production

You’ve already learned the two ways for SharePoint to reference UDF assemblies: either directly via file path or indirectly via the Global Assembly Cache. I covered the steps required to reference an assembly via a file path in a development environment and the steps required to debug UDF assemblies.

Now that development of our UDF is complete, let’s take a look at the steps required to reference our assembly via the GAC:

  1. Within Visual Studio, right-click the SampleFunctions node in the Solution Explorer pane. Select Properties from the context menu.

  2. In the Solution Properties Designer, click the Signing tab and check the Sign The Assembly checkbox.

  3. From the Choose A Strong Name Key File drop-down, select <New…> to create a new Strong Name Key.

  4. Now that we’ve specified that the assembly should be signed, we must recompile it before the signature is applied. Choose Build | Build SampleFunctions.

  5. Now copy our assembly to the C:\Windows\Assembly folder to register it with the Global Assembly Cache.

  6. Switch over to the SharePoint 2010 Central Admin site, where we’ll change the UDF configuration within the Manage Excel Services page. Delete the previous development entry for SampleFunctions.dll.

  7. Click Add User-Defined Function Assembly. Set the Assembly Location to Global Assembly Cache.

  8. To find the strong name for our assembly, switch back to Visual Studio 2010. Either press CTRL-ALT-A or choose View | Other Windows | Command Window to display the Visual Studio Command Window.

  9. In the command window, enter the following command:

    ? typeof(SampleFunctions.CurrencyConvertor).Assembly.FullName

    The resulting output will be the strong name for our assembly:

    SampleFunctions, Version=1.0.0.0, Culture=neutral,

    PublicKeyToken=your-token-here

  10. Copy this value into the Assembly text box on our Add User-Defined Function Assembly page, as shown:

    Figure 20. Add User-Defined Function Assembly Page

    Add user-defined function assembly page
  11. Click OK to register the assembly and then recycle the SharePoint Web Services application pool to pick up the new reference.

We can now test using the REST API URL that we created earlier to confirm that our assembly is being correctly picked up from the GAC.

We’ve almost met the requirements of our demonstration scenario. The only item missing is a user-interface element that can be used to switch currencies. Let’s move on and take a look at how we can implement this functionality using the JavaScript Object Model (JSOM).

A Whirlwind Tour of Excel JSOM

Before we jump into adding script to the page, let’s look at the objects that are exposed via JSOM and the mechanism for connecting to EWA web parts.

EwaControl

The EwaControl is the entry point to the object model and represents an EWA web part. As well as representing a specific instance of an EWA web part, the EwaControl has a static getInstances method that can be used to retrieve a collection of the EWA web parts on a page. For example, the following code snippet returns an EwaControl object that represents the first EWA web part on a page:

var myEwa = Ewa.EwaControl.getInstances().getItem(0);

As well as the methods mentioned, all events exposed by JSOM are defined on the EwaControl object. These include events such as activeCellChanged, gridSynchronized, and workbookChanged.

Event-handling delegates can be hooked up using JavaScript code similar to this snippet:

var myEwa;
_spBodyOnLoadFunctionNames.push("EwaPageLoad");
function EwaPageLoad() 
{
    Ewa.EwaControl.add_applicationReady(GetEwa);
}
function GetEwa()
{
    myEwa = Ewa.EwaControl.getInstances().getItem(0);
    if(myEwa)
    {
        myEwa.add_activeSelectionChanged(activeSelectionChangedHandler);
    }
}

Workbook

The Workbook object represents an Excel workbook. A reference to the workbook used by a particular EwaControl can be retrieved using the following code:

var myWorkbook = myEwa.getActiveWorkbook();

The Workbook object provides a number of methods that can be used to retrieve particular ranges, worksheets, or named ranges. As well as these methods, refreshing the underlying data of a workbook can be performed by calling the refreshAllAsync method. Note that to refresh a workbook, the referenced EWA web part must have the Refresh Selected Connection, Refresh All Connections option checked.

Range

The Range object represents one or more cells. The most common use of the Range object is to get or set particular values within a workbook. The following code snippet sets the value of the first cell in a workbook:

var theRange = myWorkbook.getRange("A1", 1, 1, 1, 1);
theRange.setValuesAsync(values, myCallBack, null);

Notice a few things about this snippet. First, when calling setValuesAsync, the values property must contain a two-dimensional array of cell values. Second, all interactions with the Excel workbook are performed asynchronously. This makes sense when you think about it, since the workbook is actually being processed on the server and is being accessed via Asynchronous JavaScript and XML (AJAX).

Sheet

The Sheet object represents a single worksheet within a workbook. References to specific sheets can be obtained using the getSheets method of the Workbook object, which will return a SheetCollection object, or using the getActiveSheet method of the Workbook object, which will return a reference to the currently active sheet.

NamedItem

The NamedItem object represents a named range. For all practical purposes, the NamedItem object is useful only for selecting a particular named range via the activateAsync method. When it comes down to reading data from a named range or writing data to a named range, both of these actions must be performed using a Range object. For example, this code snippet reads values from a named range:

function someFunction()
{
    var myEwa = Ewa.EwaControl.getInstances().getItem(0);
    var myWorkbook = myEwa.getActiveWorkbook();
    var theRange = myWorkbook.getRange("MyNamedRange", 1, 1, 1, 1);
    var values = theRange.getValuesAsync(Ewa.ValuesFormat.Formatted, getCallBack, null);
}
function getCallBack(returnValues) 
{
    window.status = returnValues.getReturnValue();
}

Adding a Content Editor Web Part Containing JavaScript

To make our demonstration focus on the specifics of communicating with Excel via JSOM, our user interface will consist of a simple drop-down list of currencies. We can include JavaScript on a web part page in a few ways. One is to modify the page using SharePoint Designer and physically insert the script into the page. Another method, and the one that we’ll use for our demonstration, is to add a Content Editor web part that uses a link to a text file containing the appropriate script.

When I covered the JSOM earlier, I mentioned that it works by leveraging a collection of JavaScript objects provided by one or more Excel Web Access web parts. As a result of this, we can access the Excel JavaScript API only on pages containing at least on EWA web part. We’ll make use of this functionality on the home page of our sample site.

  1. Navigate to http://localhost/Chapter12/Default.aspx, and choose Site Actions | Edit Page.

  2. In the Left web part zone, click Add a Web Part and then select the Content Editor web part from the Media and Content category.

  3. Select the Content Editor web part by clicking the header; then, from the Options menu, select Web Part Properties. In the Content Link text box, enter this: http://localhost/Chapter12/Excel%20Workbooks/JSOM.content.txt.

  4. Click OK to save the changes to the web part properties. Then click Stop Editing from the Page menu to return to View mode.

Creating JSOM Content in an External Fix

We’ve configured our content editor web part to read its contents from an external file; we’ll now move on to create a file containing the appropriate JSOM content. Note that we could enter the JavaScript directly into the Content Editor web part, but using an external file makes debugging and editing easier.

  1. Open Notepad.exe and enter the following text in a new document:

    <select id="Select1" onchange="UpdateChart(this)">
        <option value="ARS">Argentine Peso</option>
        <option value="AUD">Australian Dollar</option>
        <option value="GBP">British Pound</option>
        <option value=vJPY">Japanese Yen</option>
        <option value="EUR">Euro</option>
        <option value="USD" selected="selected">US Dollar</option>
    </select>
    <script type="text/javascript">
        var myEwa;
        _spBodyOnLoadFunctionNames.push("EwaPageLoad");
    
        function EwaPageLoad()
        {
            Ewa.EwaControl.add_applicationReady(GetEwa);
        }
    
        function GetEwa()
        {
            myEwa = Ewa.EwaControl.getInstances().getItem(0);
        }
    
        function UpdateChart(sender)
        {
            if (myEwa) 
            {
                var values = new Array();
                values[0] = sender.value;
                var values2 = new Array();
                values2[0] = values;
                var currencyCode=myEwa.getActiveWorkbook().getRange("CurrencyCode", 1, 1, values.length, 1);
                currencyCode.setValuesAsync(values2, setCallBack, null);
            }
            else
            {
                window.status = 'Excel Web Access not ready';
            }
        }
        function setCallBack(returnValues) 
        {
            myEwa.getActiveWorkbook().refreshAllAsync(refreshCallback,null);
        }
        function refreshCallback(returnValues) 
        {
            window.status = 'Sales chart has been refreshed';
        }
    </script>
    
  2. Save the file as JSOM.Content.txt to \\localhost\Chapter12\Excel Workbooks.

We can now refresh our home page to see the fruits of our labor. We can select a currency from the drop-down list and our chart will be recalculated using that currency, as shown next:

Figure 21. Currency Chart

Currency chart

As you can see, when we try to select a new currency, however, things are not quite as simple as we’d hoped. For example, if we switch the currency to Japanese Yen, we see an Excel Web Access error telling us "A Setting on Excel Services does not allow the requested operation to be performed." Errors of this type can happen quite often when using JSOM, and the reason comes back to what I said at the beginning of this section: JSOM works by using a set of objects that are exposed by an Excel Web Access web part. Although the error suggests that an Excel Services setting is responsible for our problem, more often than not it’s a property setting on the EWA web part that we’re referencing in script. In our case, the problem is that we’re trying to set the value of the CurrencyCode named range but the Interactivity Settings for the EWA web part don’t allow Typing and Formula Entry. Switch to Edit mode and modify the properties of the EWA web part to resolve this issue. Our home page will now behave as expected.

We’ve managed to meet the requirements of our demonstration scenario and conveniently in the process have touched upon all the major features of Excel Services. Although our demonstration site works as required, in a real-world environment, we’d need to consider a few other aspects, particularly those with regard to how data connection information is stored and used.

Our sample workbook includes an embedded data connection that has been configured to use the credentials of the currently logged-in user. This approach has a few drawbacks, however. First, all users of the web site must also be granted permissions to access the data source referred to by the workbook. Second, anybody with permissions to edit the workbook can make changes to the data connection, possibly creating a connection to a server that’s not generally accessible. In such a case, the generated workbook would display just fine for users with appropriate permissions on the data source but would display an error message for other users. As well as creating connections to restricted servers, a user might also be able to create a connection that returned an unnecessarily large volume of data. For example, a user could read every single sales transaction record from an ERP system into a pivot table and then summarize the total sales data by quarter. While this would deliver the required end result, the performance implications of using such a Workbook in Excel Services are considerable.

To resolve these issues, you can restrict data connection availability to specific data connection libraries. Permissions can be set on the libraries so that only authorized users can create connections. This provides a much higher degree of control over what data sources can be used, how authentication is handled, and how queries are written. Furthermore, it allows users who are not familiar with the nuances of connecting to database servers and retrieving data to create useful Excel workbooks simply by selecting the appropriate data source from a list.

Restricting Data Connection Types

Let’s start by denying our embedded connection the rights to run under Excel Services:

  1. Using SharePoint 2010 Central Administration, select Manage Service Applications from the Application Management section.

  2. Select the appropriate Excel Web Service Application instance from the list of available services.

  3. In the Trusted File Locations section, add a new location specifically for our sample site. This will allow us to override the default settings for our sample site without affecting the settings for other sites that use our Excel Services instance. Click the Add Trusted File Location link.

  4. In the Location section, type the Address as http://<your server name>/Chapter12 and then check the Children Trusted checkbox. Notice that we’re using the physical server name rather than localhost because Excel Services configuration uses the URL that was assigned to an application when it was first created. Although we’ve accessed our sample site using the URL http://localhost/Chapter12, this URL isn’t configured within SharePoint and therefore can’t be used as a SharePoint trusted file location.

  5. In the External Data section, select the Trusted Data Connection Libraries Only option for Allow External Data.

  6. Currently our workbook is set up to refresh external data content manually. As it happens, the first time we select a currency from the drop-down list, the workbook is refreshed, causing the underlying external data to be reloaded. The caching settings within the External Data section determine how often external data is reloaded, and the default values mean that external data is cached for a period of 5 minutes. When we reconfigure our data connection to use a Data Connection library, we’ll set it up to refresh automatically when the workbook is loaded. To prevent the user from having to confirm this refresh every time the workbook is opened, uncheck the Refresh Warning Enabled checkbox.

  7. In the User-Defined Functions section. check the User-Defined Functions Allowed checkbox.

  8. Click OK to apply the settings.

We can now revisit our sample site home page to see the damage that we’ve done to our application. Bearing in mind that external data is refreshed only whenever the currency code is changed, select an alternative currency from the drop-down list to trigger a refresh.

If our configuration changes have been properly applied, we should see an error, as shown:

Figure 22. Excel Web Access

Excel web access

Excel Services makes extensive use of caching, both in terms of the workbooks and the external data that’s used within them. If the expected error is not shown, it’s most likely because the workbook has been cached on the server. To force the configuration changes to take immediate effect, you can recycle the SharePoint Web Services application pool using the methods described earlier.

Adding Connections to Data Connection Libraries

Now that we’ve broken our sample application, we need to fix it again. We can do this by adding a new data connection library and then creating an Office Data Connection (.odc) file containing our connection settings. We’ll then tweak our workbook to use our new connection file instead of an embedded connection.

  • Create a new Data Connection library. From our sample site home page, click the Documents link to view the list of document libraries. Click the Create link to show the Create dialog.

  • From the Content & Data category, select Data Connection Library. Type the name of the new library as Sample Data Connections, and then click the Create button.

  • With our new library available, we need to let Excel Services know that all data connections stored there can be trusted. Switch back to the Manage Excel Services page in Central Administration and click the Trusted Data Connection Libraries link.

  • Click the Add Trusted Data Connection Library to add a new library. Type the address http://<your server name>/Chapter12/Sample%20Data%20Connections, and then click OK to save the changes.

    Tip Tip

    In the real world, where trusted connection libraries are used, it makes sense to have a single central connection library at a well-known location. Given that the purpose of the connection library is to allow all users to access trusted business data freely, making connections to the data as easy to find as possible is a worthwhile aim.

  • Now we’ll create a data connection in the library and reconfigure our Excel workbook to use that instead. Navigate to the Excel Workbooks document library in our sample site and then edit our Last30DaysSales workbook using Excel client.

  • From the Data menu, select the Connection button.

  • In the Workbook Connection dialog, make sure the AdventureWorksList30DaysSales connection is selected and then click the Properties button.

  • While we’re changing stuff, we’ll configure our data connection to reload external data when the file is opened. In the Usage tab, check the Refresh Data When Opening The File checkbox.

  • Switch to the Definition tab. Click the Export Connection File button and then in the File Save dialog, save the connection to http://<your server name>/chapter12/Sample Data Connections.

  • Since we’re uploading the file to SharePoint, we’ll be prompted for some additional metadata. Make sure that the Content Type is set to Office Data Connection File, and then click OK to complete the upload.

  • We can see in the Connection Properties dialog that the Connection file path has changed to our data connection library. Even though we’ve saved the connection details to our SharePoint Server, Excel still uses an embedded copy of the connection details. To force a reload every time the connection is used, check the Always Use Connection File checkbox.

  • Click OK to close the Connection Properties dialog, and then click Close to return to Excel. We can now save our revised workbook back to SharePoint by clicking the Save icon in the upper-left corner.

When we return to the home page of our sample site, we’ll find that our chart now functions properly. If an error is still being displayed, try recycling the application pool to clear out any cached copies of the workbook.

Connecting to Data Using Alternative Credentials

One of the reasons for creating a central library of trusted data connections is to allow administrators to identify specific user credentials for each connection rather than using the Windows credentials of the calling user. So far, our connection is still set up to use Windows authentication, so let’s take a quick look at how we can change this to use specific credentials.

  • Before we can change our connection to use specific credentials, we need to set up the credentials in question. Create a new local user account named testuser, and give this account read-only access to our sample database.

  • Within SQL Server Management Studio, add a new Login for our testuser account. This can be achieved by expanding the Security node, right-clicking the Logins node, and then selecting New Login from the context menu.

  • In the Login - New dialog, enter details of our testuser account in the Login Name textbox. Type the default database as AdventureWorks.

  • Switch to the User Mapping page and map our account to the db_datareader role for the AdventureWorks database, as shown:

    Figure 23. User Mapping Page

    User mapping page
  • Click OK to complete the process.

Now that we have a created specific user account with the appropriate permissions to our database, we can look at how these permissions can be used within Excel Services. Three possibilities exist for using specific credentials within Excel Services:

Embedded in Connection String We could embed credentials in the connection string that we used when creating the data connection. This has an obvious drawback in that the username and password are freely visible to anybody with access to view the data connection. Furthermore, this won’t allow us to use a specific Windows user account.

No Credentials This option isn’t as crazy as it sounds. When a data connection is created and the authentication type is set to None, Excel Services uses default credentials to connect. This account is known as an "unattended service account" and is configured using the Secure Store Service.

Secure Store Service Account The Secure Store Service provides a secure mapping of user credentials between systems. We can make use of this service within Excel Services to retrieve securely stored connection credentials. The difference between explicitly using the Secure Store Service and using it indirectly via the Unattended Service account is that the Unattended Service account is configured globally for the entire Excel Services application. However, when we’re explicitly using the Secure Store Service, we can specify which application ID should be used for each connection.

Configuring the Secure Store Service

Since two of our three options make use of the Secure Store Service, let’s look at how to set it up:

  1. Within SharePoint Central Administration, select Manage Service Applications from the Application Management section.

  2. From the List of Service Applications, select the Secure Store Service Application and then, from the Service Applications menu, click Manage.

  3. If this is the first time the Secure Store Service has been used, we need to initialize it by clicking the Generate New Key button in the Key Management section of the Edit menu.

Once the service has been initialized with a key, we can add a new application for use with our Excel Services external data store. Before we move on to take this step, however, I’ll clarify what an application is and how it works within the Secure Store Service. An application is similar to an Excel worksheet: along the top of the worksheet are columns that relate to the properties that are defined by the application. These might include things like Username and Password but can include practically any content. Each row represents a mapping for a particular user account or group of users. For each mapping, values are stored in the respective columns. When a request is made to the Secure Store Service, the request will contain details of the application, and using this together with the SharePoint user credentials, the appropriate row will be selected and returned.

Now let’s add a new application for Excel Services:

  1. In the Manage Target Applications section, click the New button.

  2. In the Target Application Settings page, enter the Target Application ID as ExcelServicesUnattendedAccount.

  3. Set the Display Name to the same name as the Target Application ID, and add an appropriate e-mail address in the Contact E-Mail text box.

  4. Various Target Applications Types are available when we’re creating applications, but these can be split into two broad categories: Individual and Group. Individual types create a one-to-one mapping between a SharePoint user and a set of properties, or to refer back to our earlier analogy, each row in our workbook represents only one SharePoint user. Group types, on the other hand, create a many-to-one mapping. Effectively, our workbook has only one row, which can be mapped to any number of SharePoint user accounts or groups.

  5. For our Excel Services application, we’ll create a Group application, and this will allow us to map all users to a single set of credentials. Click Next to proceed to the next step of the process.

  6. Specify which field will be used by our application. To refer to our earlier analogy, each field is a column in our worksheet. For our purposes, the default fields—Windows User Name and Windows Password—are sufficient. Click Next to move on.

  7. Now specify which accounts have administrative permissions for this application. Enter an appropriate username in the Target Application Administrators picker. As well as specifying administrative users, we also need to specify which users and groups will use the credentials that are mapped to our application. In this case, we want everybody to use the same credentials, so we’ll specify All Users (windows).

    Click OK to finish creating our application.

  8. To set credentials for our application, we must select the application by clicking the checkbox next to it and then click the Set button in the Credentials section of the ribbon, as shown:

    Figure 24. Credentials Section

    Credentials section
  9. Enter details of the testuser account that we created earlier, remembering to prefix the username with the local computer name (that is, yourcomputer\testuser). Click OK to store the credentials.

Note Note

Although we’ve created a group application for use with our Unattended Service account, we could also have created an Individual application and mapped the Windows username of the Excel Services service account to our testuser account. The drawback in doing this, however, is that if the service account changes, somebody must remember to add the new service account manually to the application. However, this approach does benefit from being more secure since only an appropriately configured account can use our testuser credentials. Our Group application allows our testuser account to be used by any Windows account.

Now that we’ve set up our Secure Store Service application, we can take the final step necessary to use it as our Unattended Service Account within Excel Services.

  1. Navigate to the Manage Excel Services page within Central Administration. Click the Global Settings link.

  2. Scroll down to the External Data section, and in the Application ID text box, enter the ID for our Secure Store Service application—in our case, type ExcelServicesUnattendedAccount. Click OK to commit the configuration changes.

Before we can see our unattended service account in action, we need to reconfigure our data connection to use no authentication:

  1. Browse to the Sample Data Connections library of our sample site and then, from the context menu, edit the AdventureWorksList30DaysSales connection file using Excel.

  2. The Excel client application is opened automatically. In the Security Notice dialog, click Enable to allow our data connection to execute. A table of data from our external data source will be displayed. This data is just for reference purposes; it effectively allows us to see what our data connection will return when executed.

  3. To modify the data connection properties, from the Data menu click Properties, and then in the External Data Properties dialog, select the icon to the right of the Name text box, as shown:

    Figure 25. External Data Properties

    External data properties
  4. We’ll be presented with the familiar Connection Properties dialog that we used earlier when creating our workbook. Switch to the Definition tab, and then click the Authentication Settings button.

  5. Set the authentication type to None, and then click OK to close the dialog.

  6. As we did when we modified the connection, click Export Connection File to save the changes back to our data connection library.

  7. After the connection has been exported, click OK to close the dialog, and then close Excel. Discard the workbook that was automatically created.

We’re now ready to return to our sample site home page to confirm that our data is still being refreshed properly. We can use SQL Server Profiler to confirm that connections to the database are now being made using our testuser account.

You’ve learned how to set up an Unattended Service account using the Secure Store Service. To use a specific application ID, the process is practically identical. The only difference is that in the Connection Properties dialog, rather than specifying the authentication type as None, the type is set to SSS and the application ID is entered.

In this chapter, you’ve seen how Excel Services allows you to leverage the power of Excel within a web-based environment. Creating powerful data visualization solutions is now achievable for users who have little or no development skills. Furthermore, the powerful calculation engine that is Excel can be used by skilled developers to address practically any business specific problem. The key to bear in mind as application developers is this: If weneed even basic data visualization or calculation facilities, the amount of work involved in creating a tool with even 10 percent of the flexibility of Excel Services is considerable. By leveraging Excel Services on SharePoint Server 2010, we give our users a lot more bang for their buck, and we can focus on the aspects of our application that are completely custom made.

For more information and further reading, see the Excel Services Developer Resource Center.

Community Contributor   Charlie Holland, Freelance SharePoint Consultant, Author and Trainer, is a long term contributor to Microsoft Office. For more information, visit his Web site at http://www.chaholl.com.

Show:
© 2015 Microsoft