Bulk moving data from the browser to Excel Services

Scenario: You need to interact with Excel Services from the browser, perhaps to access complex logic in a workbook or insert data into the EWA WebPart.  This interaction needs to send more data than can reasonably be set cell by cell so there is a need to upload data in bulk.

How To:   Excel Services exposes it's functionality through a web service and this can be accessed from JavaScript running in the browser just as it can from other clients.  The difficulty with JavaScript is the creation of the proxy to a service that isn't designed with technologies such as REST, oData, JSON etc.  Excel Service's web service is ASMX so there is some work to do to make the needed calls.

There is an excellent post on Shahar Prish's blog which covers generating a proxy class to allow you to call Excel Services from JavaScript here.

What's needed for the scenario is the ability to get data into a workbook in bulk.  If you examine the proxy you'll see logic catering for jagged arrays (where each element in an array is, in turn, another array) which is how Excel Services likes to pass around data in bulk.  You can see this in the getRangeA1 method.  To set data we need its partner method, setRangeA1.  The proxy has decoding of jagged arrays for the get method but doesn't have the encoding or an implementation of the set method, so these need to be added.

The method signature can be added by copying the pattern used for other, similar methods in the code.  The harder part is the encoding and serialising of a JavaScript jagged to array to the format the service expects which is how a .NET client would serialize it:

 <rangeValues>
   <anyType xsi:type="ArrayOfAnyType">
      <anyType xsi:type="xsd:int">0</anyType>
      <anyType xsi:type="xsd:int">1</anyType>
   </anyType>
   <anyType xsi:type="ArrayOfAnyType">
      <anyType xsi:type="xsd:int">1</anyType>
      <anyType xsi:type="xsd:int">2</anyType>
   </anyType>
</rangeValues>

To do this, add a property to the ExcelServicesWebMethodParam class holding a translatorCallback, which will ultimately be a function that gets invoked as the SOAP request is built, .  The can be set as we build the parameters for the setRangeA1 method.  When we come to build the SOAP request in the ExcelServicesWebMethodCall_createEnvevelope method, we can check to see if a translator has been set and call it to do the translation of the jagged array we need.  This translation function would be as follows:

function translator_JaggedArray_out(param, parent, doc) {

    var excelServicesNamespace = "https://schemas.microsoft.com/office/excel/server/webservices";

    for (var i = 0; i < param.value.length; i++) {
        var arrayXnode = doc.createNode(1, "anyType", excelServicesNamespace);
        var arrayXattribute = doc.createNode(2, "xsi:type", "https://www.w3.org/2001/XMLSchema-instance");
        arrayXattribute.nodeValue = "ArrayOfAnyType";
        arrayXnode.setAttributeNode(arrayXattribute);

        parent.appendChild(arrayXnode);

        for (var j = 0; j < param.value[i].length; j++) {
            var arrayYnode = doc.createNode(1, "anyType", excelServicesNamespace);
            arrayYnode.text = param.value[i][j];
            var arrayYattribute = doc.createNode(2, "xsi:type", "https://www.w3.org/2001/XMLSchema-instance");
            arrayYattribute.nodeValue = "xsd:string";
            arrayYnode.setAttributeNode(arrayYattribute);

            arrayXnode.appendChild(arrayYnode);
        }
    }
}

With this in place we can now move data in bulk to the workbook from the browser.  We can then use other methods to recalculate the sheet, pull out single cells or further ranges or even update a session opened in an EWA WebPart.