Accessing Excel Data using Excel Rest APIs

Excel Services 2010 provides remote access to Excel data. Developers can use the Rest APIs available with Excel Services 2010 to query and process Excel data in their applications. Excel Services and the Rest APIs can also update the workbook and use the powerful Excel formulas to complete complex calculations. The updates are not persisted and are only in memory. This lab will demonstrate how Excel and Excel Rest APIs are used to return data and calculations.

Overview

Excel Services 2010 provides remote access to Excel data. Developers can use the Rest APIs available with Excel Services 2010 to query and process Excel data in their applications. Excel Services and the Rest APIs can also update the workbook and use the powerful Excel formulas to complete complex calculations. The updates are not persisted and are only in memory. This lab will demonstrate how Excel and Excel Rest APIs are used to return data and calculations.

Objectives

• Modify Excel Workbooks for easy data access.
• Use Silverlight and the Excel Rest API to retrieve and display list data.
• Use in-memory modifications supporting workbook calculations.

System Requirements

You must have the following items to complete this lab:

• 2010 Information Worker Demonstration and Evaluation Virtual Machine
• Microsoft Visual Studio 2010
• Visual Studio 2010 SharePoint Power Tools
• Silverlight 4 Toolkit
• Silverlight WebPart

Setup

You must perform the following steps to prepare your computer for this lab...

1. Download the 2010 Information Worker Demonstration and Evaluation Virtual Machine from https://tinyurl.com/2avoc4b and create the Hyper-V image.
2. Install the Visual Studio 2010 Silverlight Web Part. The Silverlight Web Part is an add-on to Visual Studio 2010 and can be downloaded from https://code.msdn.microsoft.com/vsixforsp.
3. Install the Silverlight 4 Toolkit located at https://silverlight.codeplex.com/
4. Install the Visual Studio 2010 SharePoint Power Tools located at https://visualstudiogallery.msdn.microsoft.com/en-us/8e602a8c-6714-4549-9e95-f3700344b0d9

Exercises

This Hands-On Lab comprises the following exercises:

1. Modify an Existing Excel Workbook for Data Access
2. Retrieve Excel Workbook Data using Silverlight and Excel Rest
3. Display the Query Results in the Silverlight Interface
4. Use In-Memory Functionality to the Silverlight Application.

Estimated time to complete this lab: 40 minutes.

Starting Materials

This Hands-On Lab includes the following starting materials.

• Visual Studio solutions. The lab provides the following Visual Studio solutions that you can use as starting point for the exercises.
  • <Install location>\Labs\AccessingExcelDataExcelRestAPIs\Source\Begin\SilverlightExcelRest.sln: This soution is a a Silverlight Web Part with the starter code commented out.
  Note:

  Inside each exercise folder, you will find an end folder containing a solution with the completed lab exercise.