Excel services is yet another important pillar in
Microsoft's business intelligence offering. Business users really like
Excel. This is because Excel for them is easy to use and they are able
to add complex formulas to Excel to express their logic. They can do so
without involving the IT guy. The problem with this scenario, however,
is that it becomes very difficult to share this Excel sheet with their
coworkers. Usually they would e-mail the sheet around, but sometimes
these sheets are too large to be emailed, sometimes they have backend
data connections, and sometimes emailing causes version confusion hell.
Excel services solves all of
those problems. In short, Excel services allows you to publish an Excel
sheet in a document library, the Excel sheet is then calculated on the
server, and is then presented to one or more clients. In the
calculation, Excel sheets can involve external datasources or even
custom UDF's (user defined functions) written in .NET. Once this Excel
sheet has been published, it can be consumed directly through the
browser by using the Excel web application component, Excel web access
webpart, or Excelservice.asmx web service. The Excel sheet functionality
is also available over REST based APIs and thus can be exposed as atom
feeds or JSON.
Then, start Excel 2010, and
click on the data tab in the ribbon. In the data tab, click "From other
sources ", and choose to import data from SQL server. You would note
that you have the ability to import data from various other sources as
well. When prompted to import data from SQL server, choose to import the
data from the "Orders" table in the Northwind database using windows
authentication. You have the ability to use either windows
authentication, SQL server authentication, or to provide a secure store
service ID and get credentials at runtime. Once you have finished
importing the data from SQL server, choose to save the .odc file in a
SharePoint "Data Connections Library " that you will need to create
At this point, Excel will
prompt you to import the data as either a table, a pivot table, or a
pivot table and pivot chart. Choose to import the data as a pivot table
and pivot chart.
Now in the pivot table make the following changes:
Make ShipCountry the Report Filter
and ShippedDate as Row Labels
Show sum of freight values
Choose to make it filtered by selected country. Your pivot table should look like Figure 1.
Figure 1. My PivotTable
Note that the pivot chart
has been updating itself and is showing you a graphical view of the
data you see in the pivot table. Thus, the pivot chart and pivot table
are connected with each other. Format the chart a bit, like choosing to
show a line graph instead of a bar chart, and your pivot chart looks
like Figure 2.
Figure 2. My PivotChart
Next, add a column next to the
pivot table and give it a heading of "Difference from Avg.". Give it a
formula of "=B4-AVERAGE(B4:B15)" and choose to repeat this formula on
all available cells. Also, apply conditional formatting to this cell, so
it graphically shows you all cities have freight less than average and
which cities have freight greater than average. The pivot table now
looks like Figure 3.
Figure 3. DataBars added within each cell in my pivot table
Finally, select the A1 cell in
the pivot table to make the options tab in the ribbon visible. With the
options visible, click on insert slicer, and then choose to make
shipregion available in the slicer. This will allow you to slice the
data at runtime and subsequently affect the pivot table and pivot chart.
The final Excel sheet looks like Figure 4.
Figure 4. The final Excel sheet
From the backstage view of
Excel 2010, click save and send\Save to SharePoint\Browse for a
location. For now, publish this sheet to a document library called
sheets. You will have to pre-create this document library in your site
collection. Also, you will need to pre-activate the enterprise features
in your site collection. If you have used Excel services with SharePoint
2007, you would remember that you would also have to go into central
administration and add a trusted file location. This is no longer
necessary in SharePoint 2010. By default, all SharePoint sites are
available as trusted file locations. You can verify this by going to
central administration, clicking on manage service applications, and
choosing to manage the Excel services service. In there, click on
trusted file locations and you should see an entry similar to Figure 5.
Figure 5. Trusted file locations within SharePoint for Excel Services
This entry makes the entire SharePoint farm available as a trusted file location.
With the Excel sheet
published, drop the Excel web access WebPart on the homepage of your
SharePoint site collection and configure it to show your newly published
Excel sheet. You should see your Excel sheet running with full
interactivity in both the pivot table, pivot chart, the slicer, and
showing live data from the Northwind database. This can be seen in Figure 6.
Figure 6. The Excel Sheet running in Excel Services
This is a very
compelling example, because the end business user can craft up such
sheets talking to real data and publish them for the world to see.
However, it gets even more interesting than this. For the current web
session, these sheets can be edited in the browser and can recalculate
themselves and present new data. In Excel services, you also have the
ability of parameterizing certain sheets. Finally, the logic of the
sheets is exposed over a web service and REST API.
1. Accessing Excel Services Over REST
Here is the best news. The
Excel sheet that you have so far been working with is already being
exposed over the rest based API. Don't believe me? Assuming that your
filename was "Northwind Orders.xlsx ", Visit the following URL:
As you will note, the URL
provides you with all the details embedded in your Excel sheet over an
atom feed. Now visit the following URL:
You would note that your
Excel sheet chart is being exposed as a simple image. This is really
useful. I'm going to show a practical demonstration of this. Imagine
that your task is to craft up a document that shows the graph of the
freight cost for USA. Usually, you would copy then paste such a chart
from an existing web site and embed it in the document. That is not a
perfect approach because sooner or later the chart will become out of
date. However, you can use Excel services to create a chart that updates
with live data every time the document is opened. To do so, start Word
2010 and under the insert tab click quick parts. Choose to include a new
field and in the ensuing dialog box choose to "IncludePicture ", which
provides the appropriate URL as shown in Figure 7.
Figure 7. Customizing the QuickPart in Word
This will immediately insert a
chart from the web-based URL, and every time the Word document is opened
this chart will automatically be refreshed. This can be seen in the Figure 8.
Figure 8. Data coming from Excel services embedded in a Word document
It goes without saying that
you can embed this chart in any web-based content, such as a content
editor WebPart, blog post, or even a non SharePoint application. What is
really compelling is that this functionality of Excel service being
exposed as atom-based feeds is also available on sky drive in the cloud
as well. Therefore, you can technically host Excel sheets in your sky
drive, and have those generate graphs that can be embedded in your blog
posts or anywhere else.
Not only can you embed charts,
but you can embed truly any content being exposed from an Excel sheet
into any other consumer. For example, now try visiting the URL:
Note the end of the query string
parameter in the previous URL. By default, the content is exposed as
atom feeds, but you can choose to request JSON or even HTML.
The previous URLs renders an HTML table, as shown in Figure 9.
Figure 9. Data coming from Excel services available as an HTML table
Now this HTML table can be
embedded into any container that can render HTML. This can be your
browser, Word or Excel, or something else. For example, to insert this
content into Word again choose to insert a quick part, but this time
choose to insert using "IncludeText".