SharePoint list view Sparkline with Excel REST API
June 1, 2012 1 Comment
In a previous post I showed how to create a Sparkline in a list view by using a ‘Hyperlink or Picture’ column. This previous example used the Google Chart API to render the Sparkline but this requires an active connection to the Internet. If you want to create something similar but an Internet connection is not available, why not use Excel Services and the REST API. Here’s what it can look like:
Here’s how you do it:
Step1 – Create a custom list, in my example I’ve called this ‘SparkLines’
Step 2 – Add a column to contain the numeric value you wish to plot. In my example I’ve called this ‘Value’
Step 3 – Add a column to host the Sparkline image. This needs to be created as a ‘Hyperlink or Picture’ column and ensure the display option for the column is set to ‘Format URL as: Picture’.
Step 4 – This step is vital – enable versioning for the list
These are the same steps from my last post but here is where the technique now differs. Next we need to create an Excel spread sheet that we’ll use to host our Sparkline (it’s actually a chart not an Excel Sparkline!). Here’s what my simple sheet looks like:
It really doesn’t matter what the spread sheet looks like but it will need a few key elements. Firstly, we’ll need a few cells that will be used to hold our plotted values, in my example I’m using 10 values (Value0 to Value9). The cells that hols these values must be named ranges:
I’ve named my value cells _0 to _9 – it’s important to keep the names short (more on this later). Next, create a chart that you want to use as your Sparkline, I’ve used a simple line chart and removed the axis, title, border and background. It important you know the name of your chart and in my example I’ve given my chart the name ‘c1’:
Now we need to save our workbook to SharePoint but when we do, choose ‘Publish Options’
From the Publish Options dialog, we need to ensure that our chart object is select, plus we need to add each named range (our cell values) as a parameter:
Having set the publish options, save the spread sheet to a document library somewhere in SharePoint.
Finally, using a similar technique to my previous post, we add an event receiver to our custom list that is used to calculate the required URL to render our Sparkline. The URL the code builds references the Excel REST URL, our workbook URL, our chart object (‘c1’ in my example) and then specifies the parameter values that will be passed to the named ranges. Example URL:
http://bc01/_vti_bin/ExcelRest.aspx/Documents/Sparkline.xlsx/Model/Charts(‘c1’)?Ranges(‘_9’)=143&Ranges(‘_8’)=24&Ranges(‘_7’)=12&Ranges(‘_6’)=12&Ranges(‘_5’)=99&Ranges(‘_4’)=17&Ranges(‘_3’)=76&Ranges(‘_2’)=53&Ranges(‘_1’)=45&Ranges(‘_0’)=78
The code required in the event receiver to construct the URL is pretty simple:
// get the item SPListItem i = properties.ListItem; SPListItemVersionCollection history = i.Versions; #region Build the 'Google' sparkline #endregion #region Build the 'Excel REST' sparkline string src = "/_vti_bin/ExcelRest.aspx/Documents/Sparkline.xlsx/Model/Charts('c1')?"; string range = "Ranges('_{0}')={1}&"; int valCounter = 9; StringBuilder sb = new StringBuilder(); // make sure we read only the first 10 values from the history // this will actually be the last 10 values chronologically for (int x = 0; x < (history.Count > 10 ? 10 : history.Count); x++) { SPListItemVersion version = history[x]; sb.AppendFormat(range, valCounter, version["Value"].ToString()); valCounter--; } string paramValues = sb.ToString(); paramValues = paramValues.Substring(0, paramValues.Length - 1); #endregion // update the item EventFiringEnabled = false; i["ExcelSparkLine"] = String.Format("{0}{1}{2}{3}", properties.WebUrl, src, paramValues, ", SparkLine from Excel REST API"); i.SystemUpdate(false); // false = do not create a new version of the item EventFiringEnabled = true;
Note: This technique does have some limitations; SharePoint Enterprise is required, URL length passed into the ‘Hyperlink or Picture’ column cannot be too long – that’s why I’m using very short parameter and chart object names and limiting the number of values passed to 10, it has a performance impact on your SharePoint environment where as the Google Chart API technique offloads this to the cloud, etc, etc.
I hope this helps….
SharePint Blog... Hic! 