Snippets - Formatting


Set Number Format

Excel.run(function (context) { 
   context.workbook.worksheets.getActiveWorksheet().getRange("A1").numberFormat = "d-mmm";
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Table Rows

Excel.run(function (context) { 
   var rows = context.workbook.tables.getItem("Table1").rows.load("values");
   return context.sync()
      .then(function () {
          for (var i = 0; i < rows.items.length; i++){
                                                                
             var rng = rows.getItemAt(i).getRange();
                                                                
             if (rows.items[i].values[0][1] > 2){
               rng.format.fill.color = "#ff0000";
             }
             else{
               rng.format.fill.color = "#00ff00";
             }
          }
    })
   .then(context.sync);
}).catch(function (error) {
   console.log(error);
});

Table Rows based on largest value

Excel.run(function (context) { 
   var rows = context.workbook.tables.getItem("Table1").rows.load("values");
   return context.sync()
       .then(function () {
          var largestRow = 0;
          var largestValue = 0;
                                                
              for (var i = 0; i < rows.items.length; i++){
                 if (rows.items[i].values[0][1] > largestValue){
                    largestRow = i;
                    largestValue = rows.items[i].values[0][1];
                 }
              }
                                                
          var largestRowRng = rows.getItemAt(largestRow).getRange();
          largestRowRng.format.fill.color = "#ff0000";
     })
    .then(context.sync);
}).catch(function (error) {
   console.log(error);
});

Clear table row formatting

Excel.run(function (context) { 
                context.workbook.tables.getItem("Table1").getDataBodyRange().clear(Excel.ClearApplyTo.formats);
                return context.sync();
}).catch(function (error) {
   console.log(error);
});

Changing the font

Excel.run( function(context) { 
   var wks = context.workbook.worksheets.getActiveWorksheet();
   var range = wks.getRange("A1");
   range.values = "something";
   range.format.font.name = "Arial";
   range.format.font.size = 16;
   return context.sync();
}

indenting selection

Excel.run(function (context) { 
    var selectedRange = context.workbook.getSelectedRange().load();
    context.sync().then( function () {
        var selectedAddress = selectedRange.Address;
        var selectedCells = wsh.getRange(selectedAddress);
        selectedCell.load('values');
        FirstCell.load('rowIndex');
        FirstCell.load('columnIndex');
        context.sync().then( function () {
            var vals = selectedCells.values;
            var count = vals.length;
            var row = FirstCell.rowIndex;
            var col = FirstCell.columnIndex;
            for (var rowno = row; rowno < (row + count); rowno ++) {
                 var Cell = wsh.getCell(rowno, col);
                 Cell.values = " " + Cell.values;
             }
             context.sync();
        }

var _range = _worksheet.getRange("A1:D10"); 
_range.load = (["format/borders"]);
for var _bordertype in ['EdgeTop', 'EdgeBottom','EdgeLeft','EdgeRight','InsideHorizontal','InsideVertical']) {
   var _item = _range.format.borders.items[_bordertype];
   var _style = _item.style;
   var _weight = _item.weight;
}


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