Programmatically clear an Excel web access session

Question

I have an Excel workbook in a document library which is programmatically overwritten with a new version every so often. I have set up a web part for Excel Web Access to display the workbook on a page. This is all SharePoint 2010.

As I understand it.. when a user visits the page, Excel services starts a session and caches the workbook for that session. Refreshing the page maintains the cached version, even when the file has been updated. Closing the browser and reopening successfully clears the session and retrieves the latest.

Interactivity and editing has been disabled anyway. Is there any way to disable this session caching per workbook, or can I programatically clear the session somehow? Basically, with every refresh or hit of the page I want my users to see the latest data in the workbook without being forced to hit File > Reload Workbook every time.

Answer 1

Per https://sharepoint.stackexchange.com/a/31205/9345,

It is the cache that is storing the previous version of the workbook in memory and therefore producing an out of date version. The best way to prevent this is to reduce the cache configuration against the Excel Services application to its lowest value. This would be recommended for a development Sharepoint instance so that you can see your changes immediately and then leave the production environments with a healthy cache duration to benefit performance to the end users.

Another thing you can do is update the XLViewer.aspx file in your Templates\Layouts Sharepoint installation directory. From here, you can add a small javascript block to immediately refresh the connections of the Excel Services viewer so that the view is refreshed on every visit. I'd only recommend this in your development environment though as it will negate the benefit of the server caching.

Here's an example of using the Excel Services client side API to refresh the connections:

    <script>
            var jQueryScriptOutputted = false;

            function initJQuery() {
                //if the jQuery object isn't available
                if (typeof(jQuery) == 'undefined') {
                    if (! jQueryScriptOutputted) 
                    {
                        jQueryScriptOutputted = true;
                        document.write("<scr" + "ipt type=\"text/javascript\" src=\"http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js\"></scr" + "ipt>");
                    }
                    setTimeout("initJQuery()", 1000);
                } 
                else 
                {
                    $(document).ready(function() {
                        Ewa.EwaControl.add_applicationReady(SetupRefreshEwa);
                    });
                }
            }
            initJQuery();

            function SetupRefreshEwa()
            {
                om =Ewa.EwaControl.getInstances().getItem(0);
                om.getActiveWorkbook().refreshAllAsync(refreshAllAsyncCallBack); // automatically refresh all connections on open
            }
function refreshAllAsyncCallBack(asyncResult)
        {
            // Get the value from asyncResult if the asynchronous operation was successful.
            if (asyncResult.getCode() != 0)
            {
                 alert("Refresh operation failed with error message " + asyncResult.getDescription() + ".");
            }else{
                alert("Excel Analysis view refreshed on open");
            }
        } 
    </script>

Hope this answers your question.