Requirement: Use Excel web Access web part to show Excel report(which will have charts) and the worksheet active cells values can be changed through a slider.
Note: Active cell is nothing but the selected cell from the worksheet.
Image:
Excel web Access web part is used to display excel worksheet with different charts.
To configure Excel web Access web part follow below link:
http://office.microsoft.com/en-in/sharepoint-server-help/display-a-workbook-in-an-excel-web-access-web-part-HA101794181.aspx.
To connect Excel web Access web part to the slider plugin, add a content editor web part and the below scripts and html Tags:
<link href=”/sites/Test/Documents/jquery-ui.css” rel=”stylesheet”/> <script src=”/sites/Test/Documents/jquery-1.9.1.js”></script> <script src=”/sites/Test/Documents/jquery-ui.js”></script>
<script> $(function() { $( ‘#slider’).slider({ value:200, min: 0, max: 4000, step: 50, change: function( event, ui ) { $( ‘#amount’ ).val( ui.value ); $(‘#Test’).text(ui.value);
} }); $( ‘#amount’).val($( ‘#slider’ ).slider( ‘value’ ) ); $(this).setRangeValuesButton();
});</script>
<script type=”text/javascript”>
var ewa = null;
// Add event handler for onload event.
if (window.attachEvent)
{ window.attachEvent(“onload”, ewaOnPageLoad); }
else {
window.addEventListener(“DOMContentLoaded”, ewaOnPageLoad, false); }
// Add event handler for applicationReady event.
function ewaOnPageLoad() {
Ewa.EwaControl.add_applicationReady(onApplicationReady); }
function onApplicationReady() {
// Get a reference to the Excel Services Web Part.
ewa = Ewa.EwaControl.getInstances().getItem(0); }
function setRangeValuesButton() {
// Get a reference to the active selection as a range.
var selection = ewa.getActiveWorkbook().getActiveSelection();
// Set up the values that will be set in the call to setValuesAsync.
var values = new Array(1);
// Create a 2 dimensional array with 3 array elements,
// where each element represents a row in the range.
values[0] = new Array(1);
// Set values for the first element.
values[0][0] = document.getElementById(“amount”).value;
// Call setValuesAsync to set the values.
selection.setValuesAsync(values,setRangeValues,null); }
function setRangeValues(asyncResult) { window.status = “Set values completed: ” + asyncResult.getSucceeded(); } </script>
<p> <label for=”amount”>Investment:</label> <input id=”amount” onchange=”setRangeValuesButton()” type=”text” value=”2950″ style=”border-bottom: 0px; border-left: 0px; color: #f6931f; border-top: 0px; font-weight: bold; border-right: 0px”/> </p> <div id=”slider”></div>
<input id=”SetRangeValues” onclick=”setRangeValuesButton()” type=”button” value=”Set Range Values”/>
Note: The above code basically use ECMA scripts and register a windows event.To set active cell value we use EWA namespace, we use “setValuesAsync(values,setRangeValues,null);” function.
When some values is change through slider, the worksheet cell value and the charts get refreshed.
Note: Register the required JQuery script files