Exercise 2: Retrieve Excel Workbook data using Silverlight and Excel Rest

In this exercise, you will modify the existing Silverlight application project to use Excel Services Rest APIs to retrieve a list of product items and sales details.

Task 1 – Updating the ViewModel to Retrieve Data Using List

1. Using Visual Studio, open the starter solution from <Install>\Labs\AccessingExcelDataExcelRestAPIs\Source\Begin\SilverlightExcelRest.sln.
2. Right-click the GearItemsViewModel.cs file located in the ViewModels folder in the Silverlight project named Silverlight.ExcelRest and select Open.

3. Use snippet 4.4.1 to add a set of constants denoting the column positions.

   Figure 9

   Constants denoting column positions

4. Use snippet 4.4.2 to add constants used to set the Rest Url. This lab uses Atom format, which is appended as a command.

   Figure 10

   Rest url

5. Create a public class-level collection of GearItems named Items using snippet 4.4.3 to hold the GearItems objects returned from the query.

   Figure 11

   GearItem ObservableCollection

6. Create a public property named CurrentItem of type GearItem using snippet 4.4.4 to contain a reference to the currently selected GearItem. Notice the property raises the onPropertyChanged method and calls GetDetails.

   Figure 12

   CurrentItem public property

7. Initialize the Items collection in the class constructor.

   Figure 13

   GearItemsViewModel method

8. Edit the GetData method using snippet 4.4.5. The GetData method clears any existing GearItems objects from the Items collection and then creates an HTTPWebRequest object. The Excel Rest APIs use the Get HTTP method. The method starts an asynchronous Get passing in the callback method. Silverlight only supports asynchronous communications.

   Figure 14

   GetData method

9. Add a call to GetData in the class constructor.

   Figure 15

   GearItemsViewModel method

10. Edit the existing ResponseCallback method using snippet 4.4.6. The body of the ResponseCallback retrieves the response stream and reads it into a string variable. The method then uses the Dispatcher object to interact with the Items collection that will be data bound to the user interface. The response is Xml that is parsed. For each row in the response a new GearItem object is created and added to the Items collection.

    Figure 16

    ResponseCallback code

Task 2 – Creating the ViewModel

1. Right-click on the Mainpage.xaml file in the Solution Explorer and select Open.

2. Review the markup for the existing MainPage.xaml.

   Figure 17

   MainPage.xaml markup

3. Notice the ExcelRestl namespace is included in the xaml.

   Figure 18

   ExcelRestl namespace

4. This lab also uses custom formatters. The code for the custom formatters is located in the Silverlight project in the Formatter.cs file. Custom formatters are uses during data binding to format data appropriately. The formatter is included in the Xaml as a resource.

   Figure 20

   Custom formatters

5. Notice the user interface is data bound to the Items and CurrentItem property in the application’s datacontext.
6. Right-click MainPage.xaml in the Solution Explorer and select View Code.
7. Add a private class-level variable named viewModel of type GearItemsViewModel.

8. Initalize the viewModel variable and set the DataContext to the viewModel reference in the MainPage constructor.

   Figure 21

   MainPage method

9. Right-click the SharePoint.Client.ExcelRest project in Solution Explorer and select Set as StartUp Project.
10. Press F5 to build and deploy the solution.

11. Navigate to https://intranet.contoso.com/ExcelRestDemo.aspx using Internet Explorer.

    Figure 22

    Product screen

12. Click the Product drop down and select a product. The product information is displayed on the right-hand side of the application. You can change the discount code but the change does not affect the calculations.

13. Click the Download Workbook link. This link uses an Excel Rest url with a format set to workbook.

    Figure 23

    NavigateUri