Applies common types of conditional formatting to ranges.

Basic conditional formatting.EXCEL.yaml

name: Basic conditional formatting
description: Applies common types of conditional formatting to ranges.
host: EXCEL
api_set: {}
script:
  content: >
    $("#setup").click(() => tryCatch(setup));

    $("#apply-color-scale-format").click(() => tryCatch(applyColorScaleFormat));

    $("#apply-preset-format").click(() => tryCatch(applyPresetFormat));

    $("#apply-databar-format").click(() => tryCatch(applyDataBarFormat));

    $("#apply-icon-set-format").click(() => tryCatch(applyIconSetFormat));

    $("#apply-text-format").click(() => tryCatch(applyTextFormat));

    $("#apply-cell-value-format").click(() => tryCatch(applyCellValueFormat));

    $("#apply-top-bottom-format").click(() => tryCatch(applyTopBottomFormat));

    $("#apply-custom-format").click(() => tryCatch(applyCustomFormat));

    $("#list-conditional-formats").click(() =>
    tryCatch(listConditionalFormats));

    $("#clear-all-conditional-formats").click(() =>
    tryCatch(clearAllConditionalFormats));


    async function applyColorScaleFormat() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange("B2:M5");
        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.colorScale);
        const criteria = {
          minimum: { formula: null, type: Excel.ConditionalFormatColorCriterionType.lowestValue, color: "blue" },
          midpoint: { formula: "50", type: Excel.ConditionalFormatColorCriterionType.percent, color: "yellow" },
          maximum: { formula: null, type: Excel.ConditionalFormatColorCriterionType.highestValue, color: "red" }
        };
        conditionalFormat.colorScale.criteria = criteria;

        await context.sync();
      });
    }


    async function applyPresetFormat() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange("B2:M5");
        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.presetCriteria);
        conditionalFormat.preset.format.font.color = "white";
        conditionalFormat.preset.rule = { criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevAboveAverage };

        await context.sync();
      });
    }


    async function applyDataBarFormat() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange("B8:E13");
        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.dataBar);
        conditionalFormat.dataBar.barDirection = Excel.ConditionalDataBarDirection.leftToRight;

        await context.sync();
      });
    }


    async function applyIconSetFormat() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange("B8:E13");
        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.iconSet);
        const iconSetCF = conditionalFormat.iconSet;
        iconSetCF.style = Excel.IconSet.threeTriangles;

        /*
                The iconSetCF.criteria array is automatically prepopulated with
                criterion elements whose properties have been given default settings.
                You can't write to each property of a criterion directly. Instead,
                replace the whole criteria object.

                With a "three*" icon set style, such as "threeTriangles", the third
                element in the criteria array (criteria[2]) defines the "top" icon;
                e.g., a green triangle. The second (criteria[1]) defines the "middle"
                icon, The first (criteria[0]) defines the "low" icon, but it
                can often be left empty as this method does below, because every
                cell that does not match the other two criteria always gets the low
                icon.            
            */
        iconSetCF.criteria = [
          {} as any,
          {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: "=700"
          },
          {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: "=1000"
          }
        ];

        await context.sync();
      });
    }


    async function applyTextFormat() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange("B16:D18");
        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.containsText);
        conditionalFormat.textComparison.format.font.color = "red";
        conditionalFormat.textComparison.rule = { operator: Excel.ConditionalTextOperator.contains, text: "Delayed" };

        await context.sync();
      });
    }


    async function applyCellValueFormat() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange("B21:E23");
        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.cellValue);
        conditionalFormat.cellValue.format.font.color = "red";
        conditionalFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };

        await context.sync();
      });
    }


    async function applyTopBottomFormat() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange("B21:E23");
        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.topBottom);
        conditionalFormat.topBottom.format.fill.color = "green";
        conditionalFormat.topBottom.rule = { rank: 1, type: "TopItems" };

        await context.sync();
      });
    }


    async function applyCustomFormat() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange("B8:E13");
        const conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.custom);
        conditionalFormat.custom.rule.formula = '=IF(B8>INDIRECT("RC[-1]",0),TRUE)';
        conditionalFormat.custom.format.font.color = "green";

        await context.sync();
      });
    }


    async function listConditionalFormats() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const worksheetRange = sheet.getRange();
        worksheetRange.conditionalFormats.load("type");

        await context.sync();

        let cfRangePairs: { cf: Excel.ConditionalFormat; range: Excel.Range }[] = [];
        worksheetRange.conditionalFormats.items.forEach((item) => {
          cfRangePairs.push({
            cf: item,
            range: item.getRange().load("address")
          });
        });

        await context.sync();

        if (cfRangePairs.length > 0) {
          cfRangePairs.forEach((item) => {
            console.log(item.cf.type);
          });
        } else {
          console.log("No conditional formats applied.");
        }
      });
    }


    async function clearAllConditionalFormats() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const range = sheet.getRange();
        range.conditionalFormats.clearAll();

        await context.sync();

        $(".conditional-formats").hide();
      });
    }


    async function setup() {
      await Excel.run(async (context) => {
        context.workbook.worksheets.getItemOrNullObject("Sample").delete();
        const sheet = context.workbook.worksheets.add("Sample");

        queueCommandsToCreateTemperatureTable(sheet);
        queueCommandsToCreateSalesTable(sheet);
        queueCommandsToCreateProjectTable(sheet);
        queueCommandsToCreateProfitLossTable(sheet);

        let format = sheet.getRange().format;
        format.autofitColumns();
        format.autofitRows();

        sheet.activate();
        await context.sync();
      });
    }


    function queueCommandsToCreateTemperatureTable(sheet: Excel.Worksheet) {
      let temperatureTable = sheet.tables.add("A1:M1", true);
      temperatureTable.name = "TemperatureTable";
      temperatureTable.getHeaderRowRange().values = [
        ["Category", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
      ];
      temperatureTable.rows.add(null, [
        ["Avg High", 40, 38, 44, 45, 51, 56, 67, 72, 79, 59, 45, 41],
        ["Avg Low", 34, 33, 38, 41, 45, 48, 51, 55, 54, 45, 41, 38],
        ["Record High", 61, 69, 79, 83, 95, 97, 100, 101, 94, 87, 72, 66],
        ["Record Low", 0, 2, 9, 24, 28, 32, 36, 39, 35, 21, 12, 4]
      ]);
    }


    function queueCommandsToCreateSalesTable(sheet: Excel.Worksheet) {
      let salesTable = sheet.tables.add("A7:E7", true);
      salesTable.name = "SalesTable";
      salesTable.getHeaderRowRange().values = [["Sales Team", "Qtr1", "Qtr2", "Qtr3", "Qtr4"]];
      salesTable.rows.add(null, [
        ["Asian Team 1", 500, 700, 654, 234],
        ["Asian Team 2", 400, 323, 276, 345],
        ["Asian Team 3", 1200, 876, 845, 456],
        ["Euro Team 1", 600, 500, 854, 567],
        ["Euro Team 2", 5001, 2232, 4763, 678],
        ["Euro Team 3", 130, 776, 104, 789]
      ]);
    }


    function queueCommandsToCreateProjectTable(sheet: Excel.Worksheet) {
      let projectTable = sheet.tables.add("A15:D15", true);
      projectTable.name = "ProjectTable";
      projectTable.getHeaderRowRange().values = [["Project", "Alpha", "Beta", "Ship"]];
      projectTable.rows.add(null, [
        ["Project 1", "Complete", "Ongoing", "On Schedule"],
        ["Project 2", "Complete", "Complete", "On Schedule"],
        ["Project 3", "Ongoing", "Not Started", "Delayed"]
      ]);
    }


    function queueCommandsToCreateProfitLossTable(sheet: Excel.Worksheet) {
      let profitLossTable = sheet.tables.add("A20:E20", true);
      profitLossTable.name = "ProfitLossTable";
      profitLossTable.getHeaderRowRange().values = [["Company", "2013", "2014", "2015", "2016"]];
      profitLossTable.rows.add(null, [
        ["Contoso", 256.0, -55.31, 68.9, -82.13],
        ["Fabrikam", 454.0, 75.29, -88.88, 781.87],
        ["Northwind", -858.21, 35.33, 49.01, 112.68]
      ]);
      profitLossTable.getDataBodyRange().numberFormat = [["$#,##0.00"]];
    }


    /** Default helper for invoking an action and handling errors. */

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
  language: typescript
template:
  content: |-
    <section id="main" class="ms-font-m">
        <p>This sample shows how to apply conditional formatting to ranges.</p>
    </section>

    <section id="main" class="setup ms-font-m">
        <h3>Set up</h3>
        <button id="setup" class="ms-Button">
            <span class="ms-Button-label">Add sample data</span>
        </button>
    </section>

    <section id="main" class="samples ms-font-m">
        <h3>Try it out</h3>
        <label class="ms-font-s">Add color scale to temperature table.</label>
        <button id="apply-color-scale-format" class="ms-Button">
            <span class="ms-Button-label">Apply color scale format</span>
        </button>
        <label class="ms-font-s">Use white font for high temperatures.</label>
        <button id="apply-preset-format" class="ms-Button">
            <span class="ms-Button-label">Apply preset format</span>
        </button>
        <label class="ms-font-s">Apply data bar to sales table.</label>
        <button id="apply-databar-format" class="ms-Button">
            <span class="ms-Button-label">Apply data bar format</span>
        </button>
        <label class="ms-font-s">Apply icons to sales table.</label>
        <button id="apply-icon-set-format" class="ms-Button">
            <span class="ms-Button-label">Apply icon set format</span>
        </button>
        <label class="ms-font-s">Use red font for delayed projects.</label>
        <button id="apply-text-format" class="ms-Button">
            <span class="ms-Button-label">Apply text format</span>
        </button>
        <label class="ms-font-s">Use red font for losses in profit/loss table.</label>
        <button id="apply-cell-value-format" class="ms-Button">
            <span class="ms-Button-label">Apply cell value format</span>
        </button>
        <label class="ms-font-s">Apply a green highlight to the highest value cell in the profit/loss table.</label>
        <button id="apply-top-bottom-format" class="ms-Button">
            <span class="ms-Button-label">Apply top bottom format</span>
        </button>
        <label class="ms-font-s">Use green font for cell values higher than the previous quarter value in sales table.</label>
        <button id="apply-custom-format" class="ms-Button">
            <span class="ms-Button-label">Apply custom format</span>
        </button>
        <label class="ms-font-s">Helper functions</label>
        <button id="list-conditional-formats" class="ms-Button">
            <span class="ms-Button-label">List conditional formats</span>
        </button>
        <button id="clear-all-conditional-formats" class="ms-Button">
            <span class="ms-Button-label">Clear all conditional formats</span>
        </button>
    </section>
  language: html
style:
  content: |-
    section.samples {
        margin-top: 20px;
    }

    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1