Partager via

Getting ranges via Excel Services REST as well as Charts and Discovery

  • Article

The previous post (and the one before) showed how the Excel Services REST API allows developers to discover information about a workbook as well as get back images of live charts from an Excel Workbook.

It is also quite possible to get other types of data – specifically ranges from the spreadsheet. Now, Excel Services supports two mechanisms for getting ranges through REST. The first is used mainly to allow applications to get to the raw-data of the workbook (i.e. Raw numbers/values from a sheet) and the other is to get HTML fragments for use inside a browser. In future posts, I will show how you can leverage these elements inside a page to create a seamless experience, but for now, I will just show the simplest use for both.

I will use the same workbook shown in the previous post – the one containing bug status information. In this case however, we will look at a different element in there – a named range called “PersonalScoreCard”. Using the discovery mechanism described in the previous post, if you go to the “Ranges” atom feed on the server, you land on a page that shows all the named ranges currently in the workbook (there’s only the one in this example):

image

Clicking on the name of the range will navigate to this URL: https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('PersonalScoreCard')?$format=atom

Inside IE, the result looks like an error though:

image

If you look closely, you will see that it’s just that IE does not know how to show a single-entry atom feed item. Viewing the source of the document will show the XML the feed item contains:

<?xml version="1.0" encoding="utf-8"?>

<entry xml:base="https://excel.live.com/REST" xmlns:x="https://schemas.microsoft.com/office/2008/07/excelservices/rest" xmlns:d="https://schemas.microsoft.com/ado/2007/08/dataservice" xmlns:m="https://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="https://www.w3.org/2005/Atom">

  <title type="text">PersonalScoreCard</title>

  <id>https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('PersonalScoreCard')</id>

  <updated>2009-10-29T00:43:26Z</updated>

  <author>

    <name />

  </author>

  <link rel="self" href="https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('PersonalScoreCard')?$format=atom" mce_href="https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('PersonalScoreCard')?$format=atom" title="PersonalScoreCard" />

  <category term="ExcelServices.Range" scheme="https://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />

  <content type="application/xml">

    <x:range name="PersonalScoreCard">

      <x:row>

        <x:c>

          <x:fv>Shaharp:</x:fv>

        </x:c>

        <x:c>

          <x:v>10</x:v>

          <x:fv>10</x:fv>

        </x:c>

      </x:row>

      <x:row>

        <x:c>

          <x:fv>Average:</x:fv>

        </x:c>

        <x:c>

          <x:v>1.3333333333333333</x:v>

          <x:fv>1.333333333</x:fv>

        </x:c>

      </x:row>

    </x:range>

  </content>

</entry>

 

 

 

As you can see, the feed item contains XML representing the data inside the range. Here’s the quick and dirty on the xml elements (though most of them are pretty self explanatory):

<range> : The container of the returned range.

<row> : Each row in the range that was requested is represented by one of these.

<c> : Each cell in a row is represented by this.

<fv> : Formatted Value – this is the value as it is formatted by Excel. If the value is of type string in the workbook, this element will be the only one under <c>.

<v> : In the case where the value in the cell is not a string but a number, the Value element will contain that information.

In this way it’s very easy to get data out of an Excel range and use it in your application.

But that’s not all. If you will notice, the final part of the URL contains a parameter called $format which is set to Atom. This parameter can also take the value of html – when that’s the case, the URL will return an HTML fragment rather than an atom feed:

https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('PersonalScoreCard')?$format=html

If you were to change the URL in IE, here’s the result:

image

This HTML can either be directly consumed in an IFRAME, or used in JavaScript to create a more seamless experience (I will show examples of this in later posts).

Next post will recap the basics of the Excel Services REST API and what resources are available through it.

Comments