Registers event handlers that run when data is changed in worksheet, the selected range changes in a worksheet, or the worksheet is recalculated.

Drilldown by single click.EXCEL.yaml

name: Drilldown by single click
description: >-
  Registers event handlers that run when data is changed in worksheet, the
  selected range changes in a worksheet, or the worksheet is recalculated.
host: EXCEL
api_set: {}
script:
  content: >
    $("#setup").click(() => tryCatch(setup));


    $("#register-on-single-left-click-handler").click(() =>
    tryCatch(registerSingleLeftClickHandler));

    $("#select-range").click(() => tryCatch(selectRange));


    $("#register-on-changed-handler").click(() =>
    tryCatch(registerOnChangedHandler));

    $("#register-onCalculated-handler").click(() =>
    tryCatch(registerOnCalculatedHandler));

    $("#recalculate").click(() => tryCatch(recalculate));


    var isExpand = false;

    var isColumnExpand = false;

    var myRange;


    async function registerSingleLeftClickHandler() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        sheet.onCellLeftClicked.add(onCellLeftClick);
        sheet.onCellLeftClicked.add(onColumnLeftClick);
        await context.sync();
      });
    }


    async function selectRange() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        let range = sheet.getRange("B7:C7");
        range.select();

        await context.sync();
      });
    }


    async function onColumnLeftClick(event) {
      await Excel.run(async (context) => {
        if (event.cellAddress == "F1") {
          if (event.x < 400) {
            let sheet = context.workbook.worksheets.getItem("Sample");
            let salesTable = sheet.tables.getItemAt(0);
            var range = sheet.getRange("F1");
            if (isColumnExpand == false) {
              range.numberFormat = "\"[\"-\"]\"@";
              salesTable.columns.add(null, [
                ["Oct"],
                [ 400],
                [12000],
                [1550],
                [230],
                [90],
                [750]
              ]);
              sheet.getRange("G1").numberFormat = "";
              isColumnExpand = true;
            } else {
              range.numberFormat = "\"[\"+\"]\"@";

              salesTable.columns.getItemAt(6).delete();
              isColumnExpand = false;
            }
          }
        }
        console.log("The selected range has changed to: " + event.x);
      });
    }



    async function onCellLeftClick(event) {
      await Excel.run( async (context) => {
        // myRange.load();
        // await context.sync();
        // console.log("Range address " + myRange.address);

        if (event.cellAddress == "B7")  {
          if (event.x < 95) {
            let sheet = context.workbook.worksheets.getItem("Sample");
            let salesTable = sheet.tables.getItemAt(0);
            var range = sheet.getRange("B7");

            //console.log("My Test" + range.numberFormat);
            if (isExpand == false) {
              range.numberFormat = "\"[\"-\"]\"@";
              salesTable.rows.add(null, [
                [7, "    Beijing", 5000, 7000, 6544, 4377],
                [8, "    Shanghai", 400, 323, 276, 651],
                [9, "    Guangzhou", 12000, 8766, 8456, 9812],
                [10, "    Shenzhen", 1550, 1088, 692, 853,]
              ]);
              isExpand = true;
            } else {
              range.numberFormat = "\"[\"+\"]\"@";
              var rangeDelete = sheet.getRange("A8:F11");
              rangeDelete.delete();
              rangeDelete.clear();
              isExpand = false;
              await context.sync();
            }
          }
        }
        context.trackedObjects.add(myRange);
        console.log("The selected range has changed to: " + event.x);
      })
      .catch(function(e) {
        console.log(JSON.stringify(e));
      })
      ;
    }



    async function registerOnCalculatedHandler() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        sheet.onCalculated.add(onCalculated);
        await context.sync();
        console.log("Added worksheet selection changed event handler.");
      });
    }


    async function recalculate() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        let randomResult = context.workbook.functions.randBetween(1, 3000).load("value");
        await context.sync();

        let row = sheet.tables.getItem("SalesTable").rows.getItemAt(0);
        let newValue = [["Frames", 5000, 7000, 6544, randomResult.value, "=SUM(B2:E2)"]];
        row.values = newValue;
        row.load("values");
        await context.sync();
      });
    }


    async function onCalculated(event) {
      await Excel.run(async (context) => {
        console.log("The worksheet has recalculated.");
      });
    }


    async function registerOnChangedHandler() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        sheet.onChanged.add(onChange);

        await context.sync();
        console.log("A handler has been registered for the onChanged event.");
      });
    }


    async function changeData() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        let range = sheet.getRange("B5");
        range.values = [[800]];
        range.format.autofitColumns();

        await context.sync();

        console.log("B5 value has been changed.");
      });
    }


    async function onChange(event) {
      await Excel.run(async (context) => {
        console.log("Handler for worksheet onChanged event has been triggered.  Data changed address : " + event.address);
      });
    }


    async function setup() {
      await Excel.run(async (context) => {
        let sheet = await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sample");
        let salesTable = sheet.tables.add("A1:F1", true);
        salesTable.name = "SalesTable";

        salesTable.getHeaderRowRange().values = [["Id", "Product", "Qtr1", "Qtr2", "Qtr3", "Qtr4"]];

        salesTable.rows.add(null, [
          [1, "USA", 5000, 7000, 6544, 4377],
          [2, "UK", 400, 323, 276, 651],
          [3, "Germany", 12000, 8766, 8456, 9812],
          [4, "Japan", 1550, 1088, 692, 853],
          [5, "Korea", 225, 600, 923, 544],
          [6, "China", 6005, 7634, 4589, 8765]
        ]);
        var range = sheet.getRange("B7");
        range.numberFormat = "\"[\"+\"]\"@";
        range = sheet.getRange("F1");
        range.numberFormat = "\"[\"+\"]\"@";

        myRange = sheet.getRange("B7");
        context.trackedObjects.add(myRange);
        sheet.activate();
        await context.sync();
      });
    }


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

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
      }
    }
  language: typescript
template:
  content: |-
    <section class="ms-font-m">
        <p>This sample shows how to register and use an event handler for the worksheet onSelectionChanged event.</p>
    </section>

    <section 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 class="samples ms-font-m">
        <h3>Try it out</h3>

        <p><b>Selection Changed</b></p><section class="samples ms-font-m">
        <button id="register-on-single-left-click-handler" class="ms-Button">
            <span class="ms-Button-label">onSingleLeftClick</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/beta/hosted/office.js
  https://appsforoffice.microsoft.com/lib/beta/hosted/office.d.ts

  [email protected]/dist/css/fabric.min.css
  [email protected]/dist/css/fabric.components.min.css

  [email protected]/client/core.min.js
  @types/core-js

  @microsoft/[email protected]/dist/office.helpers.min.js
  @microsoft/[email protected]/dist/office.helpers.d.ts

  [email protected]
  @types/[email protected]