Snippets - Cells & Ranges

link - docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges
link - docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges-advanced
link - docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-multiple-ranges
link - docs.microsoft.com/en-us/office/dev/add-ins/excel/performance


var _range = context.workbook.names.getItem("myNamedRange").getRange(); 
var _rangeValues = _range.getRow(1).getBoundingRect(_range.getLastCell());
var _myCol = _range.getColumn(0);


Range - Writing

Set myMatrix in the document.

var _myMatrix = [["1","2","3"],["4","5","6"],["7","8","9"]]; 

Office.context.document.setSelectedDataAsync(_myMatrix,
                                             function (asyncResult) {
   if (asyncResult.status == "failed") {
    showMessage("Action failed with error: " + asyncResult.error.message);
   }
});

Be aware that a matrix can only be written into the spreadsheet if no other data in the surrounding cells will be overwritten.


Excel.run(function (context) { 
   context.workbook.worksheets.getItem("Sheet1").getRange("A1:C3").values = 7;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Excel.run(function (context) { 
   var _range = context.workbook.worksheets.getItem("Sheet1").getRange("A1:C3");
   _range.values = [[1, 2, 3], [4, 5, 6], [7, 8, 9]];
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

var _myMatrix = [[1,2,3],[4,5,6]]; 
Excel.run(function (context) {
   var _sheet = context.workbook.worksheets.getItem('Sheet1');
   var _celltop = _sheet.getCell(0,0);
   var _cellbottom = _sheet.getCell(_myMatrix.length - 1, _myMatrix[0].length - 1);
   var _range = _celltop.getBoundingRect(_cellbottom).insert('down');
   _range.values = _myMatrix;
   _range.format.font.bold = true;
   //_range.delete('up');
   return context.sync();
}).catch( function(error) {
   console.log(error);
})

Range - Reading

Excel.run(function (context) { 
   var _range = context.workbook.worksheets.getActiveWorksheet().getRange("A1:C3").load("values");
   return context.sync().then(function () {
     for (var i = 0; i < _range.values.length; i++) {
                     for (var j = 0; j < _range.values[i].length; j++) {
                                     console.log(_range.values[i][j]);
                     }
     }
     console.log("done");
   });
}).catch(function (error) {
   console.log(error);
});

Read a matrix from the current selection in the document and display its contents

Office.context.document.getSelectedDataAsync("matrix", 
                                             function (asyncResult) {
  if (asyncResult.status == "failed") {
    showMessage("Action failed with error: " + asyncResult.error.message);
  }
  else {
    showMessage("Selected data: " + asyncResult.value);
  }
});

The following example uses the getSelectedDataAsync method to read the currently selected range of cells. To specify that the selection is read as a range of cells, specify the coerctionType parameter as CoercionType.Matrix. The callback function returns an object containing the data currently selected by the user, which can use the asyncResult.value property to access the data.


Range - Clearing

Excel.run(function (context) { 
    context.workbook.worksheets.getActiveWorksheet().getRange("A1:C1").clear(Excel.ClearApplyTo.contents);
    return context.sync()
}).catch(function (error) {
   console.log(error);
});

Range - Inserting Cells

Excel.run(function (context) { 
    context.workbook.worksheets.getActiveWorksheet().getRange("A1:C3").insert("right");
    return context.sync();
}).catch(function (error) {
   console.log(error);
});

Range - Get Current Selection

Excel.run(function (context) { 
    var _rangeSelected = context.workbook.getSelectedRange().load();
    return context.sync().then(function() {
       console.log(_rangeSelected.address);
    });
}).catch(function (error) {
   console.log(error);
});

Range - Changing to Upper Case

Excel.run(function(context) { 
    var _range = context.workbook.getSelectedRange().load("values");
    return context.sync()
     .then(function() {
         var _vals = _range.values;
         for (var i = 0; i < _vals.length; i++){
                         for (var j = 0; j < _vals[i].length; j++){
                                         _vals[i][j] = _vals[i][j].toUpperCase();
                         }
         }
         _range.values = _vals;
     })
     .then(context.sync);
}).catch(function (error) {
   console.log(error);
});

Range - Using References

Excel.run(function (context) { 
    var _rangeOriginal = context.workbook.worksheets.getActiveWorksheet().getRange("A1:C4");
    var _rangeNew = _rangeOriginal.insert(Excel.InsertShiftDirection.down);
    _rangeOriginal.format.fill.color = "Red";
    _rangeNew.format.fill.color = "Yellow";
    return context.sync();
}).catch(function (error) {
   console.log(error);
});

RefEdit - Prompt

function event_runTestingCode() { 
        var _bindingOptions = {
            id: "MyBinding",
            promptText: "Please select your cells."
        };

        //creates a binding by prompting the user to make a selection
        Office.context.document.bindings.addFromPromptAsync(
            Office.BindingType.Matrix,
            _bindingOptions,
            onBindingCallback);
    }

    function onBindingCallback(asyncResult) {
        if (asyncResult.status === Office.AsyncResultStatus.Failed) {
            console.log("Error get_rangecoords. " + asyncResult.error.message, 3);
        }
        else {
            console.log("Added new binding with type: " + asyncResult.value.type + " and id: " + asyncResult.value.id, 1);

            Excel.run(function (context) {
                //var _binding = context.workbook.bindings.getItem("MyBinding");
                var _binding = context.workbook.bindings.getItemAt(0);

                var _range = _binding.getRange();
                _range.load("address");

                return context.sync()
                    .then(function () {
                        console.log("Binding range address is " + _range.address);
                    });
            });
        }
    }


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext