RequestContext

The RequestContext object facilitates requests to the Office application.
Because the Add-in and the Office application run in two different processes, request context is required to get access to the Office application from the add-in.
The JavaScript object declared is a proxy object for the real object until the add-in and Office application are synchronized.
A request context is created as shown.

var ctx = new Excel.RequestContext(); 

For example, the local JavaScript object selectedRange is declared to reference the selected range. This can be used to queue the setting of its properties and invoking methods. The actions on such objects are not realized until the sync() method is run.
var selectedRange = ctx.workbook.getSelectedRange();


Excel.run(function(context) { batch })

Excel.run() executes a batch script that performs actions on the Excel object model.
The advantage of batching requests in Excel.run() is that when the promise is resolved, any tracked range objects that were allocated during the execution will be automatically released.


load() Method - Reading Properties and Relationships

The load() method is used to fill in the proxy objects.
selects all scalar and complex properties of the object that is being loaded. The relationships are not loaded by default

object.load(string: properties);   -- comma separated string names 
//or
object.load(array: properties);
//or
object.load({loadOption}); -- selection, expansion, top and skip options


sync() Method - Writing

The sync() method available on the request context synchronizes the state between JavaScript proxy objects and real objects in Office application by executing instructions queued on the context and retrieving properties of loaded Office objects for use in your code.
This method returns a promise, which is resolved when synchronization is complete.


Examples

// Run a batch operation against the Excel object model. Use the context argument to get access to the Excel document.

Excel.run(function (ctx) { 

    var values = [
                 ["Type", "Estimate"],
                 ["Transportation", 1670]
                 ];

    var sheet = ctx.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getRange("A1:B2");
    range.values = values;
    range.load('text');

    // Synchronizes the state between proxy objects and real objects
    return ctx.sync().then(function() {
            console.log("Done");
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});


Copy the values from Range A1:A2 on the active worksheet to B1:B2.

Excel.run(function (ctx) { 

    var range = ctx.workbook.worksheets.getActiveWorksheet().getRange("A1:A2");
    range.load ("address, values, range/format");

    return ctx.sync().then(function() {
        // Assign the previously loaded values to the new range proxy object.
       // The values will be updated once the following .then() function is invoked.
        ctx.workbook.worksheets.getActiveWorksheet().getRange("B1:B2").values= range.values;
    });
}).then(function() {
      console.log("done");
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});


Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:B2";
    var myRange = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);

    myRange.load(["address", "format/*", "format/fill", "entireRow" ]);

    return ctx.sync().then(function() {
        console.log (myRange.address);
        console.log (myRange.format.wrapText);
        console.log (myRange.format.fill.color);
        //console.log (myRange.format.font.color); //not loaded

    });
}).then(function() {
      console.log("done");
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});


Null-Input (2D array)

null input inside two-dimensional array (for values, number-format, formula) is ignored in the update API. No update will take place when null is sent.
Only some parts of the Range Number Format are set while retaining the existing Number Format on the remaining part (by passing nulls).

  range.values = [["Eurasia", "29.96", "0.25", "15-Feb" ]]; 
  range.numberFormat = [[null, null, null, "m/d/yyyy;@"]];

null input for a property
null is not a valid single input for the entire property. For example, the following is not valid as the entire values cannot be set to null or ignored.

range.values= null; 

The following is not valid either as null is not a valid color value.

range.format.fill.color =  null; 


Null-Response

Representation of formatting properties that consists of non-uniform values would result in the return of a null value in the response.
An Excel Range can consist of one of more cells. In cases where the individual cells contained in the Range specified don't have uniform formatting values, the range level representation will be undefined.

  "size" : null, 
  "color" : null,


Blank Input and Output

Blank values in update requests are treated as instruction to clear or reset the respective property. Blank value is represented by two double quotation marks with no space in-between. ""
For values, the range value is cleared out. This is the same as clearing the contents in the application.
For numberFormat, the number format is set to General.
For formula and formulaLocale, the formula values are cleared.
For read operations, expect to receive blank values if the contents of the cells are blanks. If the cell contains no data or value, then the API returns a blank value.

  range.values = [["", "some", "data", "in", "other", "cells", ""]]; 
  range.formula = [["", "", "=Rand()"]];


Unbounded Range (Read)

Unbounded range address contains only column or row identifiers and unspecified row identifier or column identifiers (respectively), such as:
C:C, A:F, A:XFD (contains unspecified rows)
2:2, 1:4, 1:1048546 (contains unspecified columns)
When the API makes a request to retrieve an unbounded Range (e.g., getRange('C:C'), the response returned contains null for cell level properties such as values, text, numberFormat, formula, etc.. Other Range properties such as address, cellCount, etc. will reflect the unbounded range.


Unbounded Range (Write)

Setting cell level properties (such as values, numberFormat, etc.) on unbounded Range is not allowed as the input request might be too large to handle.
The following is not a valid update request because the requested range is unbounded. This will return an error.

    var range = ctx.workbook.worksheets.getActiveWorksheet().getRange("A:B"); 
    range.values = 'Due Date';


Large Range

Large Range implies a Range whose size is too large for a single API call.
Many factors such as number of cells, values, numberFormat, formulas, etc. contained in the range can make the response so large that it becomes unsuitable for API interaction.
The API makes a best attempt to return or write to the requested data.
The large size involved might result in an error.
To avoid such a condition, using read or write with smaller range sizes


Single Input Copy

To support updating a range with the same values or number-format or applying same formula across a range, the following convention is used in the set API. In Excel, this behavior is similar to inputting values or formulas to a range in the CTRL+Enter mode.
The API will look for a single cell value and, if the target range dimension doesn't match the input range dimension, it will apply the update to the entire range in the CTRL+Enter model with the value or formula provided in the request.



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext