Thursday, May 27, 2010

JSOM or Excel Service Java Script Model


It's high time to reveal another screte which microsoft is shipping with SPS 2010. For some it may not be screte but I am sure the code snippet is screte for most of the readers. let's jump to topic directly.

The New functionality is Editable excel sheet, which means the user can edit sheet in EWA. more detailed is - developer uses Excel Web Access EWA to render excel sheet in SharePoint, Till MOSS it was just readonly now user can edit the sheet there only. How it is possible?
The developer team introduced another property 'Allow Type and Formula' which is visible from web part properties, once this check box is checked user can edit the sheet right away.

 now why I am so excited about this -> hey it gives us javascript based Object Model to interact with rendered sheet. Ideally what I do is use on content editor web part and assosiate it wih .txt file which contains javascript code.

So below is small snippet about setting selected value using Javascript OM -

function setCellValue(selectedValue)

ewa= Ewa.EwaControl.getInstances().getItem(0); // --get handle of currently rendered workbook

workbook = ewa.getActiveWorkbook(); // handle of active workbook;
rangelocal = workbook.getActiveCell(); //handle of active cell
var column = rangelocal.getColumn(); //
var row = rangelocal.getRow();
var valueArray = new Array(1);
valueArray[0] = new Array(1);
valueArray[0][0] = selectedValue;
function asyncCallbackSet(asyncResult)
lets talk highlighted Text. we will reverse track here, will start from green one.
  • setValuesAsync() is the function responsible for setting range value using async call back.
  • asyncCallbackSet is my call back function,  'SetValue' is context.
  • valueArray this is the 2-Dimensional array contains value for the cell. Now why it is 2 - dimensional as it is excel where cell is row and column, so the first index represents row and second column.
since I just wanted to set value of one cell I my array is of length one but it has to be two -dimension as second dimension represents column value.
asyncResult gives option to trace error using asyncResult.getCode()  and asyncResult.getDescription()
excecute this simple code and feel the power.

worth mentioshing is since SPS 2010 supports silverlight OOB , and communication between Silverlight and Javascript is possible the power is just double :) and this Object model give you control for excel  events like cell changed, tab out etc.
Enjoy :)

No comments:

Post a Comment