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:
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">
6
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>
13
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>
36
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}"/>
42
43 <TextBlock Name="tbStatus" Grid.Row="2" HorizontalAlignment="Left" TextWrapping="Wrap" Text="" Visibility="Collapsed"/>
44
45 </Grid>
46 </UserControl>
47
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:
MainPage.xaml.cs
19 namespace ExcelChartViewerSL
20 {
21
22 public
class
ExcelChart
23 {
24 public
string ID { get; set; }
25 public
string Title { get; set; }
26 }
27
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";
33
34 public
string WorkbookLocation { get; set; }
35 public
int MainImageMaxWidth { get; set; }
36 public
int MainImageMaxHeight { get; set; }
37
38 private Microsoft.SharePoint.Client.File _wkbkFile;
39
40 public MainPage()
41 {
42 InitializeComponent();
43 }
44
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)";
52
53 tbStatus.Visibility = Visibility.Visible;
54 return;
55 }
56
57 //start the loading of the workbook file
58 BeginLoad();
59 }
60
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);
66
67 ClientContext.Current.ExecuteQueryAsync(_wkbkFile_LoadSucceeded, _wkbkFile_LoadFailed);
68 }
69
70 private
void _wkbkFile_LoadSucceeded(object sender, ClientRequestSucceededEventArgs e)
71 {
72 //file load succeeded. start loading the workbook REST feed
73 LoadWorkbookFeed();
74 }
75
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();
85
86 this.tbStatus.Visibility = Visibility.Visible;
87 });
88 }
89
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;
94
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 }
100
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 }
109
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);
113
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 };
123
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;
130
131 this.imgMain.DataContext = this.lbCharts.SelectedItem;
132 });
133
134 }
135
136 private
void lbCharts_SelectionChanged(object sender, SelectionChangedEventArgs e)
137 {
138 this.imgMain.DataContext = this.lbCharts.SelectedItem;
139 }
140 }
141 }
142
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;
31
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;
35
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 };
43
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
Subscribe