Snippets - Formatting


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);
});

Formatting - 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);
});

Formatting - 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);
});

Formatting - 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);
});

Formatting - 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();
}

Formatting - 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;
}





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