SharePoint Excel web Access webpart: dynamically set the active cell value from a slider using ECMA script

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