How to seamlessly embed data from Excel Services spreadsheets into Word
One of the fun things about working on the programmability aspects of Excel Services is realizing just how many interesting scenarios exist that you never thought of originally. I got an IM from Dany Hoter asking for help on an issue he ran into with one of his customers. I have been working with Dany for about 10 years now and he always has interesting questions and ideas. This time around, the problem revolved around a complicated word document that contained links to Excel data – the word document would update and grab data from the workbook via automation (OLE embedding essentially). For a number of reasons, Dany wanted to remove the dependency on the Excel client and was wondering how to go about it. Eran from the Excel Services team suggested looking into fields and that’s how Dany landed in my IM window.
This is where the fun part begins. In Word, one can insert fields into the document that have certain functionality. The most used ones are probably the document Index, page numbers and Date/Time. One of those fields is an IncludeText field which can take text from various sources and put it inside the field. To add such a field, you use the “Quick Parts” drop-down in the “Insert” ribbon tab:
Once selected, you are presented with a slew of options – the one you want is the “IncludeText” field:
When the IncludeText field is selected, the user is presented with two more panes – the file name or URL from which to take the data (that’s in the middle) and the options of the field (that’s on the right).
In the URL field, we supply an Excel REST URL that points to our desired cell – this should be a REST URL that’s presented in the Atom format – that way we can traverse the data and get back what we want. In my post about getting range data from Excel Services, there’s an example for such a link. Here’s what the link looks like for our case here:
https://tsdlt2/_vti_bin/ExcelRest.aspx/docs/Bugs.xlsx/Model/Ranges('PersonalBugCount')?$format=atom
As you can see, we are going to a spreadsheet called “Bugs.xlsx” and asking for the range called “PersonalBugCount”. The parameter $format is set to atom to instruct Excel Services to return an XML representing the data. The result (removed some elements for brevity) looks like this:
<?xml version="1.0" encoding="utf-8"?>
<entry 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">AverageBugCount</title>
<!-- Removed stuff -->
<content type="application/xml">
<x:range name="AverageBugCount">
<x:row>
<x:c>
<x:v>1.3</x:v>
<x:fv>1.3</x:fv>
</x:c>
</x:row>
</x:range>
</content>
</entry>
We now need to set two more fields in the dialog – the namespace mappings and the XPath expression – luckily, for the case where you want a single value, both of these are the same and require no modification.
Namespace Mapping is used in conjunction with the XPath expression – the XPath expression needs to know what aliases to use for what namespaces. In this case, we have two namespaces we need to use:
https://www.w3.org/2005/Atom – this is the namespace for the ATOM scheme.
https://schemas.microsoft.com/office/2008/07/excelservices/rest – this is the namespace for the Excel REST data scheme.
Both of these will need to have aliases associated with them (the aliases are not really that important). The setting that should be entered in the “Namespace mapping” field is then:
This goes in the “Namespace Mappings” field:
xmlns:a="https://www.w3.org/2005/Atom" xmlns:x="https://schemas.microsoft.com/office/2008/07/excelservices/rest"
The XPath needs to point to the “fv” element that’s in the XML and so it should be set to:
This goes in the “XPath Expression” field:
/a:entry/a:content/x:range/x:row/x:c/x:fv
And that’s pretty much it! Once you hit OK, the dialog will close and you should see the value from the workbook. Here’s text from a word document with fields toggled to see the codes:
And here’s the text as it appears when the fields are shown as text:
The highlighted areas are the values that came from Excel Services. The cool part is that when the workbook updates and the word document is updated, this data will update as well, showing the new values!
Now, another nifty aspect of this is that the same mechanism can be used to embed richer data – like HTML. If you will recall, at the bottom of this post I showed how one can access Ranges from Excel and get back HTML fragments. Guess what – Word knows how to embed those as well. Using the same mechanism, we can enter a field and point it to a REST URL returning an HTML fragment. In this case, we do not need the namespace mappings or the XPath expression – just the URL (in this example a URL to a range that looks like a score-card):
https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('PersonalScoreCard')
The URL resolves to the following HTML:
| Shaharp: | 10 | |
| Average: | 1.3 |
When embedded in the workbook by using the IncludeText field, this is what it looks like:
The same thing applies as before – if the data of the workbook changes and the scorecard data and formatting changes, the next time the Word document will be updated, this text and formatting will also be updated.
Finally, if you look just above the IncludeText field, you will see an IncludePicture field – using that allows you to embed live images from Excel Services within the workbook – in this example, the URL we will embed is the chart that shows the progress made on the bugs and the goals:
To summarize – as you can see, you can create richer and self-updating documents in word by using fields that connect to Excel data and bring back plain text (the first example with the Atom) or formatted text (the second example with the HTML fragment) or charts (last example). These can be updated at will just like any other field in Excel and guarantee that your document will contain data that is up to date and is synchronized with the source – no more checking to see if the data was copied properly etc.
Comments
Anonymous
December 19, 2012
This is a neat feature, but {IncludeText} strips the HTML for important information from the Excel spreadsheet like indentations, doesn't translate if an   has been used in a format mask. It has potential, but returning HTML to IncludeText doesn't provide the rich HTML experience you do with FireFox or IE. Any suggestions?Anonymous
October 08, 2013
Hi Shahar, It is very good learning for me, i need your help on second part inserting range as HTML on word, I have tried on SharePoint 2013 to MS word 2010 but the format after insert is totally wrong. When I saw the HTMl of the range it is formed using DIV tags. Please help me on this.