Example: Using the SharePoint 2010 Silverlight Client Object Model and Excel Services REST API

NOTE: This code is based on the SharePoint 2010 Release Candidate version.

Download: ExcelChartViewerSL.zip (975.87 kb) 

I finally had a chance to put together a little sample of how to use the SharePoint 2010 Silverlight Client Object Model which I wanted to share. Now this example could have been just as easily done with jQuery and the Client OM for Javascript but I really didn't want to write another post on jQuery (I think everyone knows how much I like it) and instead, I wanted to show how much I like Silverlight and WPF as well J (so much so that I'm actually currently writing a Facebook desktop application in WPF).

In this example, I built a Silverlight app that can be used to display charts inside of an Excel workbook. The concept I had was simple, really. I wanted to take all charts contained in an Excel workbook located in a SharePoint document library and display them as navigational elements first (as thumbnails of the chart) and after one was selected, to display the entire chart itself. I thought this would be a good starting point to show Silverlight + SharePoint + Excel and an example that can be extended further for use by anyone. So this is the mockup for what I intended to build:

Figure 1 – Mockup

The first thing to do is to open Visual Studio 2010 and create a new Silverlight Application Project. The wizard will ask you if you want to add a Web project to the solution as well. For this example, it is not required. The project will contain two XAML files, an App.xaml and a MainPage.xaml. The App.xaml is for handling the application itself and the MainPage.xaml is for the main UI component of the application.

With MainPage.xaml, make sure to name the UserControl as this will later be used as part of some of the bindings defined in controls later. In this case, I named the control 'thisControl' (line 1). Then I defined the layout of the control by adding three grid row definitions to the LayoutRoot grid container (lines 8-12). All have been defined with a width of Auto so that each row will collapse if there is nothing inside of them. Then, I dropped an Image control and a TextBlock control in the xaml. The Image control, named 'imgMain', targets the second row of the grid container (line 37, Grid.Row="1") and the TextBlock control, 'tbStatus', targets the third row of the container (line 43, Grid.Row="2").

Look back at the Image control. In lines 40-41, I am defining a maximum height and width for the image by binding it to properties I defined in this MainPage user control class (MainPage.xaml.cs). The properties are MainImageMaxHeight and MainImageMaxWidth, both of which are ints. The maximum height and width has a default that I set (later) but can be modified by the user. Notice also that for each binding's ElementName, I specified the name I gave to the control itself, 'thisControl'. In line 38, I am binding the Source to a property called ID. Now this ID property isn't part of my MainPage user control class. It is actually a property of another class I defined called ExcelChart (also inside of the MainPage.xaml.cs) file. The ExcelChart class has two properties: ID (the url for the chart object) and Title. An instance of the ExcelChart class is what is used as the DataContext for the image, which you will see in the code.

For the thumbnails row, all I'm using is a ListBox control. This is an extremely versatile control. The first thing I want to make sure of is to make sure that the items in the listbox are presented horizontally rather than vertically as is the default. So in lines 16-21, I define the template used for the items panel (ItemsPanelTemplate) to use a StackPanel as the container with Orientation set to Horizontal. Then, I need to define how the actual items in the listbox will be displayed. For that, I defined the ItemTemplate of the listbox (lines 21-34). The template uses a two-row grid as the container. The first row contains a TextBlock with the chart's title and the second row contains an Image control that displays the chart. Notice the binding for the TextBlock's text uses the Title property and the Image source uses the ID property of the ExcelChart class.

The only other thing to pay attention to in the MainPage.xaml are the event handlers. There are only two events I care about, when the control gets loaded (line 5) and when the selection has changed on the list box (line 15).

Here is the full MainPage.xaml:

    1 <UserControl x:Class="ExcelChartViewerSL.MainPage" Name="thisControl"

    2    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    3    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    4    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

    5    Loaded="UserControl_Loaded" Height="Auto" Width="Auto">


    7     <Grid x:Name="LayoutRoot" Background="White">

    8         <Grid.RowDefinitions>

    9             <RowDefinition Height="Auto"/>

   10             <RowDefinition Height="Auto"/>

   11             <RowDefinition Height="Auto"/>

   12         </Grid.RowDefinitions>


   14         <ListBox Name="lbCharts" Grid.Row="0" BorderBrush="Transparent" HorizontalAlignment="Center"

   15                 SelectionChanged="lbCharts_SelectionChanged">

   16             <ListBox.ItemsPanel>

   17                 <ItemsPanelTemplate>

   18                     <StackPanel Orientation="Horizontal"/>

   19                 </ItemsPanelTemplate>

   20             </ListBox.ItemsPanel>

   21             <ListBox.ItemTemplate>

   22                 <DataTemplate>

   23                     <Grid>

   24                         <Grid.RowDefinitions>

   25                             <RowDefinition Height="Auto"/>

   26                             <RowDefinition Height="Auto"/>

   27                         </Grid.RowDefinitions>

   28                         <TextBlock Text="{Binding Title}" FontWeight="Bold" HorizontalAlignment="Center" />

   29                         <Image Source="{Binding ID}" Grid.Row="1"

   30                               MaxWidth="250" MaxHeight="250"

   31                               Margin="3,2,3,2" HorizontalAlignment="Center"/>

   32                     </Grid>

   33                 </DataTemplate>

   34             </ListBox.ItemTemplate>

   35         </ListBox>


   37         <Image Name="imgMain" Grid.Row="1"

   38               Source="{Binding ID}"

   39               Margin="0, 10, 0, 0" HorizontalAlignment="Center"

   40               MaxHeight="{Binding ElementName=thisControl, Path=MainImageMaxHeight}"

   41               MaxWidth="{Binding ElementName=thisControl, Path=MainImageMaxWidth}"/>


   43         <TextBlock Name="tbStatus" Grid.Row="2" HorizontalAlignment="Left" TextWrapping="Wrap" Text="" Visibility="Collapsed"/>


   45     </Grid>

   46 </UserControl>



Now take a look at MainPage.xaml.cs (shown below). In lines 22-26 is where I define the simple ExcelChart class with my two properties, ID and Title. In the main page class, I defined three properties (lines 34-36), WorkbookLocation, MainImageMaxWidth, and MainImageMaxHeight. The MainImageMaxWidth/Height I already discussed. The WorkbookLocation is used to specify which workbook to get the charts from.

In the UserControl_Loaded event handler (starting at line 45), the first thing I want to make sure of is that the WorkbookLocation is set. At the very least, that needs to be specified. If not, show an error message and stop. If it is specified, we then call the BeginLoad() method to start processing.

In the BeginLoad method (lines 61-68), I use the SharePoint client OM to request the workbook file. The client OM is new for SharePoint 2010. If you haven't already heard, the difference with the client OM and the server OM is that with the client OM, the objects have to be explicitly loaded. In other words, I can't just start trying to access properties of a SharePoint object as those properties won't be available right away. So in this case, I have this _wkbkFile object (of type Microsoft.SharePoint.Client.File) I defined as a field of my MainPage class. Before I can do anything with this object, I need to load it by explicitly asking the ClientContext object to load it (and execute a query) for me (lines 65-67). Note that with the Silverlight OM, the queries have to be done asynchronously.

When you call ExecuteQueryAsync, it expects two callbacks, one for if the query succeeds and one for if the query fails. The callback I defined for the failure is between line 76-88. If the query failed, then I just display a message to the user about the failure. An important thing to remember here is that when the query is called and is executing, it is executing on a different thread. It is not executing on the UI thread. The implication here is that in order to update the TextBlock text to display the status message, I need to make sure I do that on the UI thread. In order to do this, we use Dispatcher.BeginInvoke() to wrap the call that updates the text and changes the TextBlock's visibility.

The succeeded callback (_wkbkFile_LoadSucceeded) simply calls the LoadWorkbookFeed() method (lines 90-99). In SharePoint 2010, Excel Services has added the ability to expose objects contained in an Excel workbook via RESTful services. In this case, I create a string (wkbkRestUrl) to the Excel REST service that will return to me all the charts in a particular workbook (line 93). Then, I use a System.Net.WebClient object to request that URL. The WebClient also makes the request asynchronously so I have to provide a callback function that should be executed when the request completes (wcFeedReader_OpenReadCompleted).

When you call this Excel REST service the response is going to be in ATOM format. In wcFeedReader_OpenReadCompleted (lines 101-134), I use an XDocument object to load up the response that comes back from the REST service. I then use LINQ to XML (lines 116-122) to get an IEnumerable<ExcelChart> collection of all entry objects. Finally, I set the ItemsSource property of my ListBox (lbCharts) to my 'entries' collection, select the first chart, and set the DataContext of my imgMain image to the selected ExcelChart object. Again, since I am now updating something with the UI and because I am currently executing on a different thread than the UI thread, I need to make sure to wrap the code that updates the UI with a call to Dispatcher.BeginInvoke().

Here's the full MainPage.xaml.cs:


   19 namespace ExcelChartViewerSL

   20 {


   22     public class ExcelChart

   23     {

   24         public string ID { get; set; }

   25         public string Title { get; set; }

   26     }


   28     public partial class MainPage : UserControl

   29     {

   30         private const string EXCEL_REST_URL = "/_vti_bin/ExcelRest.aspx/";

   31         private const string MODEL_CHART_URL = "/Model/Charts/";

   32         private const string NS_ATOM = "http://www.w3.org/2005/Atom";


   34         public string WorkbookLocation { get; set; }

   35         public int MainImageMaxWidth { get; set; }

   36         public int MainImageMaxHeight { get; set; }


   38         private Microsoft.SharePoint.Client.File _wkbkFile;


   40         public MainPage()

   41         {

   42             InitializeComponent();

   43         }


   45         private void UserControl_Loaded(object sender, RoutedEventArgs e)

   46         {

   47             //make sure the web part was actually configured with a workbook location.

   48             if (string.IsNullOrEmpty(WorkbookLocation))

   49             {

   50                 tbStatus.Text = "Please configure at least the workbook location. Init Params Available: " +

   51                     "workbookLocation(string), mainImageMaxHeight(int), mainImageMaxWidth(int)";


   53                 tbStatus.Visibility = Visibility.Visible;

   54                 return;

   55             }


   57             //start the loading of the workbook file

   58             BeginLoad();

   59         }


   61         private void BeginLoad()

   62         {

   63             //first, use the clientcontext object to request the workbook file

   64             _wkbkFile = ClientContext.Current.Web.GetFileByServerRelativeUrl("/sites/adventureworksbi" + "/" + this.WorkbookLocation);

   65             ClientContext.Current.Load(_wkbkFile);


   67             ClientContext.Current.ExecuteQueryAsync(_wkbkFile_LoadSucceeded, _wkbkFile_LoadFailed);

   68         }


   70         private void _wkbkFile_LoadSucceeded(object sender, ClientRequestSucceededEventArgs e)

   71         {

   72             //file load succeeded. start loading the workbook REST feed

   73             LoadWorkbookFeed();

   74         }


   76         private void _wkbkFile_LoadFailed(object sender, ClientRequestFailedEventArgs e)

   77         {

   78             //file loading failed. show the error

   79             //Dispatcher.BeginInvoke is used because this is currently not executing on the UI thread

   80             Dispatcher.BeginInvoke(delegate()

   81             {

   82                 this.tbStatus.Text = "Error message: " + e.Message

   83                     + "\nWorkbook file: " + WorkbookLocation

   84                     + "\nCode: " + e.ErrorCode.ToString();


   86                 this.tbStatus.Visibility = Visibility.Visible;

   87             });

   88         }


   90         private void LoadWorkbookFeed()

   91         {

   92             //the feed url format is: http://{server/sitecollection}/{site}/_vti_bin/ExcelRest.aspx/{workbooklocation}/model/charts

   93             string wkbkRestUrl = ClientContext.Current.Url + EXCEL_REST_URL + WorkbookLocation + MODEL_CHART_URL;


   95             //use a web client object to request the workbook REST feed

   96             WebClient wcFeedReader = new WebClient();

   97             wcFeedReader.OpenReadCompleted += new OpenReadCompletedEventHandler(wcFeedReader_OpenReadCompleted);

   98             wcFeedReader.OpenReadAsync(new Uri(wkbkRestUrl));

   99         }


  101         void wcFeedReader_OpenReadCompleted(object sender, OpenReadCompletedEventArgs e)

  102         {

  103             //the response sent back will be ATOM. load the response in an XDocument first.

  104             XDocument doc;           

  105             using (Stream s = e.Result)

  106             {

  107                 doc = XDocument.Load(s);

  108             }


  110             //ATOM is the default namespace in the response.

  111             //we need to prepend this when referring to any elements or attributes of the document

  112             XNamespace nsAtom = XNamespace.Get(NS_ATOM);


  114             //use LINQ to XML to get all 'entry' objects in the feed

  115             //with each 'entry' element, create an instance of our ExcelChart class (defined above)

  116             var entries =

  117                 from entry in doc.Root.Descendants(nsAtom + "entry")

  118                 select new ExcelChart()

  119                 {

  120                     ID = entry.Element(nsAtom + "id").Value,

  121                     Title = entry.Element(nsAtom + "title").Value

  122                 };


  124             //remember that the REST feed request was started on a different thread.

  125             // we need to use Dispatcher.BeginInvoke here to update the UI thread.

  126             Dispatcher.BeginInvoke(delegate()

  127             {

  128                 this.lbCharts.ItemsSource = entries;

  129                 if (this.lbCharts.Items.Count > 0) this.lbCharts.SelectedIndex = 0;


  131                 this.imgMain.DataContext = this.lbCharts.SelectedItem;

  132             });


  134         }


  136         private void lbCharts_SelectionChanged(object sender, SelectionChangedEventArgs e)

  137         {

  138             this.imgMain.DataContext = this.lbCharts.SelectedItem;

  139         }

  140     }

  141 }



Lastly, I need to edit the App.xaml.cs. There's not much to explain here. I'm just retrieving the initparams that is passed by the Silverlight host object and using it to set up my application.

App.xaml.cs (Application_Startup)

   27         private void Application_Startup(object sender, StartupEventArgs e)

   28         {

   29             //Load up any init parameters from the silverlight web part host.

   30             IDictionary<string, string> parms = e.InitParams;


   32             string workbookLocation = parms.ContainsKey("workbookLocation") ? parms["workbookLocation"] : string.Empty;

   33             int mainImageMaxHeight = parms.ContainsKey("mainImageMaxHeight") ? int.Parse(parms["mainImageMaxHeight"]) : 400;

   34             int mainImageMaxWidth = parms.ContainsKey("mainImageMaxWidth") ? int.Parse(parms["mainImageMaxWidth"]) : 640;


   36             //Create our main user control (MainPage) and set it up as the app's main UI.

   37             MainPage mainPage = new MainPage()

   38             {

   39                 WorkbookLocation = workbookLocation,

   40                 MainImageMaxHeight = mainImageMaxHeight,

   41                 MainImageMaxWidth = mainImageMaxWidth

   42             };


   44             this.RootVisual = mainPage;

   45         }


After the project is built, then all you need to do is drop the XAP file into the ClientBin directory (c:\program files\common files\microsoft shared\web server extensions\14\templates\layouts\clientbin). I actually created a subdirectory structure (DeviantPoint\ExcelChartViewerSL) under clientbin to store my xap for this application. Then, drop a Silverlight web part (found under Media and Content) and configure it to point to the new XAP file.

Figure 2 – Add a Silverlight Web Part


Figure 3 – Configure the web part to point to the XAP


Figure 4 – Configuring the appearance


Figure 5 – Configuring other settings, which contains the Initialization Parameters passed to the Silverlight application

(Full Initialization Parameters: workbookLocation=AnalyticsReports/AW Charting Samples.xlsx,mainImageMaxHeight=500,mainImageMaxWidth=800)

So that's it. It was pretty simple to build, actually. It probably took me about an hour or so to get it all working the way I expected it to. Here's a screenshot of the Silverlight web part with my Silverlight app:

Figure 6 – Silverlight web part for displaying Excel charts

About the author

Bart X. Tubalinal is a Solutions Architect with over 10+ years experience in building enterprise applications. He also considers himself to be, pound for pound, one of the best developers there is.



Comment RSS