Harvesting and Using the Session ID from EWA.
Every time you open a workbook in Excel Services you are actually creating your own instance of that workbook. Excel Services keeps track of those instances by assigning them a text string called a session ID. Once a session is created you can perform API calls to that session simply by passing that string into the API call.
For the greater part of this release it was impossible to determine what session ID was being used by an EWA web part. After some complaining from myself and others, as well as an infrastructure change due to other issues this is now possible. Excel Services now provides a Javascript function that will return the session ID corresponding to the web part ID passed in. Here’s the signature:
EwaGetSessionId(id)
Where id is a text string corresponding to the web part ID for the Excel Services web part (you can get this programmatically or by looking at the source of a web part page containing an EWA part, and searching for ECSFrame. The ID will be of the form “WebPartWPQ#” where # will be 1, 2, 3…).
Show Me:
One useful thing to do with the session ID would be to pass it to the EWA web part through a parameter, so that it can be referenced from within the workbook, or even used in a call to a User Defined Function. You can see where this would be useful in creating a session management workbook, user tracking, or to enable your UDFs to make API calls that affect the current, or other live sessions. NOTE: Changing sessions being used in EWA through API calls can be troublesome, as sessions are not re-entrant and EWA will return an error if it attempts to access session information while an API call is currently using that session.
Below I will show you how to harvest the session ID from an EWA part, and pass it into an Excel workbook through a parameter.
Example:
First you will need to create a web part or dashboard page with the following Sharepoint web parts:
1. Excel Web Access
2. Text Filter
3. Filter Actions
4. Content Editor (This one should be below all other web parts, as it will contain Javascript that will reference the parts created above)
Once you publish this page you will need to get the following information from the page’s source (right click in IE and View Source).
1. ECS Web Part ID – Search for “ECSFrame” in the source and get the corresponding “WebPartWPQ#”.
2. Text Filter Web Part ID – Same as above, but search for “TextSlicer”.
3. Filter Actions Web Part ID – Same as above, but search for “Apply Filters”.
Now that you have the values for the above ID’s you can create a text file which will contain your Javascript. Here’s what I have (replace the IDs under “DEFINE WEB PART NAMES” with the values you got from above):
<script type='text/javascript' language='javascript''text/javascript'>
function setSession(sessionId)
{
if(sessionId == null)
{
<!-- Session not ready yet. Try again in 1 second -->
setTimeout('setSession(EwaGetSessionId(ECSWebPartID))', 1000);
}
else
{
<!-- Set up real Text Filter ID -->
var filterId = 'ctl00$m$g_' +
document.getElementById(TextSlicerWebPartID).WebPartID.replace(/-/g, "_") +
'$SPTextSlicerValueTextControl';
<!-- If the Text Filter doesn't already have the current session ID, set it -->
if(sessionId != document.getElementById(filterId).value)
{
document.getElementById(filterId).value = sessionId;
<!-- Set up function call Filter Action would make if clicked -->
var actionId = 'ctl00_m_g_' +
document.getElementById(FilterActionsWebPartID).WebPartID.replace(/-/g, "_") +
'_PostBack(this)';
<!-- Send text filter value to EWR parameter through Filter Actions -->
setTimeout(actionId,0);
}
}
}
<!-- DEFINE WEB PART NAMES -->
var ECSWebPartID = 'WebPartWPQ3';
var TextSlicerWebPartID = 'WebPartWPQ5';
var FilterActionsWebPartID = 'WebPartWPQ4';
<!-- In 1 second, check to see if we have a session -->
setTimeout('setSession(EwaGetSessionId(ECSWebPartID))', 1000);
</script>
Now save this text file to a Sharepoint document library and remember the path to it (e.g. https://MYSERVER/Documents/Scripts.txt). On your web part or dashboard page modify the Content Editor web part such that it’s Content Link points to your script file above. That script will now run every time the page is loaded.
Load a workbook in your EWA web part with a parameter defined, and connect the Text Filter web part to send it’s value to EWA’s parameter. If you like, you can also minimize the Filter Actions and Text Filter web parts to get them out of the way.
You’re DONE!!!
Now, every time you load this page the Javascript above will run, and once it successfully harvests the session ID from EWA it will stuff it into the Text Filter web part and call the Filter Actions function to pass it along to EWA. You should see your session ID in the workbook cell defined as the parameter.