I have an XSLX file in a SP 2010 document library. I use the excel web access webpart to display it in a web page. However, if I modify the file, the changes are not immediately visible on the page. I need to explicitly reload the worksheet ( there is a item in the menu ) or wait a little ( like 15 minutes ).
It is possible to change this time or ( better ) making the changes appear automatically after a save ?
Thanks
It is the cache that is storing the previous version of the workbook in memory and therefore producing an out of date version. As you have already answered, 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>
The solution I found so far is to add an entry in Central Administration > Excel Services Application > Trusted File Locations for the worksheet.
I changed the two values called Session Timeout and Short Session Timeout ( expressed in seconds ). These two values are measured respect to idle time, i.e. if you have the page open but you're not editing the worksheet.
I was unable to put 0 ( this would disable the sessions ) since in that case the webpart would enter an infinite reload cycle, but by putting 1 I achieve the same effect and I don't need to close the browser to see the changes anymore.
The user has to reload the workbook in the browser without making changes.
As soon as the workbook is rendered in the browser,
Go to File and click Reload Workbook.
