EWA Companion Part 4: Refresh/Recalc functionality
In the first part of this post series I showed the EWA companion and its various capabilities. In this part, I will delve deeper into the "Automatic Refresh/Recalc" capabilities of the companion.
This feature is divided into two parts - refresh and recalc. Refresh is mostly only useful when your workbook contains a Pivot-Table that's not based on external data. Since the Excel Services feature of Periodic Refresh is a property of an Excel connection, there's nowhere to set it for PivotTables that are based on data inside Excel.
The Calculate option is mostly useful if your workbook contains volatile formulas (such as =NOW()). This will allow the workbook to refresh periodically.
To enable this feature, you need to go to the companion's properties and decide which of these you want:
You can set up either operation you want (or both). You can also set the interval (in seconds) the EWA will use to refresh.
Once this is set up, you are pretty much done. Once every N seconds, the browser will issue AJAX calls to refresh/recalc the workbook and refresh the page. Note that as opposed to the previous features, there is no visual cue on the Companion web-part to show that something is happening.
The Code
On the server side, the code that controls this feature is pretty simple. All it does is inject some javascript into the page:
if (!DesignMode && RefreshPeriodically || RecalcPeriodically)
{
int flags = 0;
if (RefreshPeriodically)
{
flags |= 1;
}
if (RecalcPeriodically)
{
flags |= 2;
}
string scriptlet = String.Format(
"EcIssueRefreshRecalcPeriodically('{0}', {1}, {2});",
ConnectedEwa.ClientID,
flags,
RefreshInterval * 1000);
Page.ClientScript.RegisterStartupScript(this.GetType(), ClientID + "_ForRefreshRecalc", scriptlet, true);
}
What this does is add code that calls the EcIssueRefreshRecalcPeriodically js function. The number that is passed in as the second parameter governs what needs to be done (1 - refresh, 2 - recalc, 3 - both).
The js function simply uses the setTimeout method to defer the call to whatever timeout was given by the properties of the web-part:
function EcIssueRefreshRecalcPeriodically(id, flags, interval)
{
window.setTimeout("EcRefreshRecalcPeriodically('" + id + "', " + flags + "," + interval + ");", interval);
}
This method will issue the EcRefreshRecalcPeriodically when the timeout is reached:
function EcRefreshRecalcPeriodically(id, flags, interval)
{
var sessionId = EwaGetSessionId(id);
if (sessionId == null)
{
EcIssueRefreshRecalcPeriodically(id, flags, 5000);
return;
}
var es = new ExcelServices();
var info = new EcRefreshRecalcState();
info.sessionId = sessionId;
info.flags = flags;
info.es = es;
info.wpid = id;
info.interval = interval;
es.setUserState(info);
if (info.flags & 1)
{
EcRefreshAll(info);
}
else if (info.flags & 2)
{
EcRecalc(info);
}
}
This is where things get funky. What we do here is create an object that contains a bunch of information and we then issue an AJAX request to the server using the Excel Services AJAX library.
Depending on the flags, we either call the EcRefreshAll() or the EcRecalc() method. The helper object (EcRefreshRecalcState) contains all our state, including the Excel Services object (the .es property).
Next, take a look at the EcRefreshAll() method:
function EcRefreshAll(info)
{
info.es.refreshAll(info.sessionId, EcRefreshComplete);
}
function EcRefreshComplete(webMethod)
{
var info = webMethod.excelServices.userState;
if (info.flags & 2)
{
EcRecalc(info);
}
else
{
EcRefreshIFrame(info);
}
}
The method calls the Excel Services AJAX wrapper called "refreshAll", passing it a callback called EcRefreshComplete. Once the Refresh is complete, it uses the userState property to continue the call (if needed - depending on the flags). Then either EcRecalc will be called (which issues the Recalc method on the Excel Services AJAX object), or the EcRefreshIFrame is called (which refreshes the browser to show the new results).
That's about it for this series of posts. If I add functionality to this library, I will add posts that explain it. If you have any questions, feel free to use the blog or our forums to post them.
Comments
Anonymous
May 23, 2008
The comment has been removedAnonymous
February 21, 2009
Hi, I have an UDF that obtains data from an external data source through web services. I've also created a pivot table based on the data range returned by the UDF, however the pivot table does not show the correct data unless I hit the Refresh All Connections button in the EWA web part toolbar. I've tried to use this functionality of the EWA Companion Web part but with no success. The EWA zone gets refreshed but the pivot table still holds no data. Am I doing something wrong? Is it possible to fire the same event that Refresh All Connections option fires after the loading of the Excel file? Having to explicitly hit this button for the pivot table to be created is an enormous overhead for the customer...Anonymous
June 08, 2010
I created UDFs to Get datafrom SharePoint List and using the data, PIVOT charts were created. but using EWA companion, the PIVOT data not getting refreshed. Is possible to invoke PIVOT chart refresh on load of the EWA webpart page. Kindly help us in this. thanks in advance.Anonymous
October 29, 2012
Could I use this in SharePoint 2010?