Excel REST meets XSL

Firstly, I would like to thank all those that came along to my sessions at the Australian and New Zealand SharePoint Conferences in Sydney and Auckland. I am a big fan of empowering end-users and wanted to show a realistic example of what was possible so the level of interest and number of relevant questions made it all worthwhile.

So, my scenario is that I have a country manager who wants to see a dashboard of his team performance against controlled targets. Given that I am in Australia I will stick with a local example so the team are state managers. The country manager is a competent Excel user and wants to keep as much control over the chart types etc as possible. The state managers should be able to input their results but not change their targets.

To start with we will create our Excel spread sheet – this will work fine with either 2010 or 2013, Excel or SharePoint in any combination.

Create a worksheet called Targets with headings for State, Q1, Q2, Q3 and Q4. Enter your data for each state. Eg QLD, 24, 28, 32, 26. I often use the randbetween() function to create dummy data quickly. Format as a table using your preferred style and name the table Targets.

Create another worksheet called Chart, in A1 type State and in B1 type one of your states eg QLD. Under the Formulas ribbon define a name for B1 called State.
From B2:E2 type Q1 to Q4. In A3 type Target and A4 type Actual. Now we want to get our target values from the Targets table depending on which state is entered into B1. Use a vlookup function in cell B3 as follows =vlookup(State, Targets, 2, FALSE). This will use the defined name State as the lookup value, search for it in the Targets table and return the second column (which is the target for Q1). FALSE means it needs an exact match. Copy this formula for Q2 to Q4 changing the column returned to be 3 to 5 accordingly.
Now enter some dummy data for your Actual values in row 4.

Last step is to create our chart. Select the data in cells A2:E4 and insert your preferred chart. Name the chart as Chart.

All done – you should now have something that looks like this:

Chart

If you change the state typed into B1 then the appropriate targets should automatically populate in row 3 and the chart redraw accordingly. If this is not the case, recap the previous steps.

Save your Excel file as States.xlsx and upload to your Docs library in SharePoint. Note that you will need Excel services to be running which also means you will need an Enterprise licence of SharePoint 2010 or 2013. If you are using Office 365 then all should be fine.
Now you have the file in SharePoint we can try out some Excel REST fun. Excel REST allows me to get data or objects from the Excel file using a URL. The URL is constructed as follows:
 http:// – the server name
 /_vti_bin/ExcelRest.aspx – the Excel REST page
 /Docs/States.xlsx – the path to your spread sheet from the site you make the call
 /model/Charts(‘Chart’) – the chart (or other object/model) we want to retrieve
The models available are:
 Charts
 Ranges
 Tables
 PivotTables
Try this out in your browser window. Type the URL using the elements as defined above:
Eg //server/site/_vti_bin/ExcelRest.aspx/Docs/States.xlsx/model/Charts('Chart')

You should now see the chart from your Excel file in your browser window. If not, then make sure you check you have the correct library name and file name and also the name of your chart in the Excel file. I have used everything from my example spread sheet above so if you have done anything differently then you will need to change the path or names to match.

The chart is returned as an image so this can be used in a variety of ways such as adding an image to a page and entering the URL as above.
So far so good, but we are only using the data within the spread sheet at the moment. To tell the spreadsheet to use different data we add parameters to our URL.
Eg //server/site/_vti_bin/ExcelRest.aspx/Docs/States.xlsx/model/Charts('Chart')?Ranges('Chart!B4')=40

Following on from the example above, this will set the value of the cell B4 in the spread sheet to 40 (the actual for Q1) and redraw the chart before returning the image.

Now if all you wanted was a chart from your list data then you could edit your page and insert a picture from address and use this as the URL. Easy. However, I did promise to mix this with some XSL to give you a dashboard so here goes.

I have created some custom XSL below which as you can see is pretty simple. It creates a table and then for each row put the Title in the first column and then an image in the second. The URL for the image is our REST call but with the values coming from our list columns.

<xsl:stylesheet version="1.0" exclude-result-prefixes="xsl" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
  <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
  <table style="width:100%">
   <xsl:for-each select="$Rows">
    <xsl:call-template name="RowView" />
   </xsl:for-each>
  </table>
 </xsl:template>
 <xsl:template name="RowView">
  <tr>
   <td>
    <xsl:value-of select="@Title" />
   </td>
   <td>
    <img src="/sites/SPC2013/IV/_vti_bin/ExcelRest.aspx/Documents/States.xlsx/Model/Charts('Chart')?Ranges('Chart!B1')={@Title}&amp;Ranges('Chart!B4')={@Quarter1}&amp;Ranges('Chart!C4')={@Quarter2}&amp;Ranges('Chart!D4')={@Quarter3}&amp;Ranges('Chart!E4')={@Quarter4}" />
   </td>
  </tr>
 </xsl:template>
</xsl:stylesheet>

Notice that the ampersands separating the URL parameters have to be escaped – ie &amp;
In my example, I have saved this stylesheet to /Style Library/custom/Charts.xsl.

So far so good – now we need a SharePoint list to apply this to. Create a list called States. Rename the Title column to State and create number columns for Quarter1, Quarter2, Quarter3 and Quarter4.

Interesting thing to note here is that if you try to abbreviate your column names to Q1 or Qtr1 you will have trouble with your XSL. For some reason SharePoint does not like column names which are less than 5 characters and so will encode the first character. This makes it pretty horrible in XSL so I suggest you stick with the column names as above.

Add some data to your list and make sure you use the same names for the States.

Now for the fun bit. Create a new view on your States list and call it Dashboard. No need to apply filters but make sure all the input columns are showing. Edit the page in the view and now edit the properties of the web part. In the Miscellaneous section add the URL to your XSL file in the XSL Link box.

ChartXSLlink

Finally the magic bit. Click OK and stop editing the page. You will now see the name of each state followed by a chart redrawn for the data from each item in your list.

RESTdashboard

Excel REST and XSL – what a great combination!
Download the blog post as PDF – Excel REST Meets XSLT
Download the spreadsheet example – States
Download the XSL stylesheet – Chart.xsl

Good luck and have fun.

This entry was posted in SharePoint and tagged , , , , , , , , , , . Bookmark the permalink.

9 Responses to Excel REST meets XSL

  1. Hi Dave, thanks for posting these details. The perfect complement to your prezzo at AUSPC and NZSPC.

    • paylord says:

      Hey Ingeborg!

      It was great to meet you there and hope you find this useful🙂

      Dave

      • Hi Dave, I’m dying to try out a few of those gems of yours, as soon as I come up for air. I was away for almost two weeks to prance around at conferences while our SP 2010 document management project is in the hot phase. Now I have some catching up to do before I can get into the fun learning space. Looking forward to that, thanks to your ideas and examples. Catch you later!

  2. Hello Dave, It always has been a pleasure to attend your sessions. I was wondering if you can blog on your Visio services demo as well. I have a question on the excel services blog above. I thought moving to SP2013 would mean moving away from XSLT. Is the blog above more relevant to SP2010?

    • paylord says:

      Hello Sundeep,

      I will consider adding the Visio as a blog post though have always felt it was a little too technical.

      In SP2013 XSLT is still available though is used less – certainly in search. It is also possible to do call the Excel REST API using Display Templates so this is probably a better way in 2013 – and this will also probably be my next blog🙂

      Hopefully see you again next year.

      Dave

  3. paylord says:

    Updated to present script examples better

  4. Pingback: Dashboard from Excel file with Data Validation and Protected Sheets | DL-UAT

  5. SharePoint says:

    Dave,

    Please post more articles, trust me I`m enjoying reading your post and learning so much.

    Few questions with respect to Excel using REST or EXCEL web Services.

    a) Will this model allow to ingest data from SPS 2013 List to the local Excel sheet residing on the document lib? If yes, will it allow then to build Dashboard in Excel from the data ingested and present Dashboard on real time data basis?

    b) Will be storing the XSL file in style library and Excel document in a document library. Going forward, can I use it as a site template mode. Where in users create a new site using the site template and it should provide them access to Dashboard on homepage created using Excel wherein data is ingested via multiple SharePoint Lists within the same site?

    c) Is it possible to copy specific data from all Excel files part of sites created using the site template and store onto a parent site level to showcase Dashboard wherein it gives a Gross view of how the Sales is working from Multiple sub- sites part of the same site collection?

    Thanks for your time and sorry to be a pain, as I`m asking for your mentoring.

    Regards,

    Jason

    • paylord says:

      Hey Jason,

      I am currently working on a presentation for the local O365 Saturday and a volunteer project for Marine Rescue QLD so blogging has fallen down the priority list for the moment.

      You can definitely pass data from the list to Excel and then get a chart returned as an image which is redrawn based on the data. However, as each item needs to be passed as a separate parameter it is not suitable for large data volumes. As the API works by passing the data to Excel as part of the call for the chart then it is always refreshed with the latest data – though purists would certainly not call this real-time.

      Yes, you can use this concept in site templates – though note the point above about passing in data and consider the complexity of this with multiple lists.

      If you use a CQWP then you can configure it to collect data from the current site level. This means that in your template sites it will only take data from that one site, and from your parent site it will take data from all the sub-sites created using the template. However, this will pull data from lists and not from the contents of Excel files. You can still use the Excel Services API to pass summarised data to a spreadsheet and get a chart returned.

      My biggest concern is that what you want to achieve is relatively complex so I think you are likely to struggle unless you have some good technical skills and can pick this up very quickly – or maybe get some local consulting support. I am certainly not going to be able to support you building this remotely!

      Good luck

      Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s