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.