range-get-range-edge.yaml

order: 20
id: excel-range-get-range-edge
name: Select used range edge
description: 'This sample shows how to select the edges of the used range, based on the currently selected range.'
host: EXCEL
api_set:
    ExcelAPI: '1.13'
script:
    content: |
        $("#setup").on("click", () => tryCatch(setup));
        $("#select-E9").on("click", () => tryCatch(selectE9));
        $("#select-D8-E9").on("click", () => tryCatch(selectD8E9));
        $("#get-range-edge-left").on("click", () => tryCatch(getRangeEdgeLeft));
        $("#get-range-edge-up").on("click", () => tryCatch(getRangeEdgeUp));
        $("#get-extended-range-right").on("click", () => tryCatch(getExtendedRangeRight));
        $("#get-extended-range-down").on("click", () => tryCatch(getExtendedRangeDown));

        async function getRangeEdgeLeft() {
          await Excel.run(async (context) => {
            // Get the selected range.
            const range = context.workbook.getSelectedRange();

            // Get the active cell in the workbook.
            const activeCell = context.workbook.getActiveCell();

            // Get the left-most cell of the current used range.
            // This method acts like the Ctrl+Arrow key keyboard shortcut while a range is selected.
            const rangeEdge = range.getRangeEdge(
              "Left", // Specify the direction as a string.
              activeCell // If the selected range contains more than one cell, the active cell must be defined.
            );

            // Select the edge of the range.
            rangeEdge.select();

            await context.sync();
          });
        }

        async function getRangeEdgeUp() {
          await Excel.run(async (context) => {
            // Get the selected range.
            const range = context.workbook.getSelectedRange();

            // Specify the direction with the `KeyboardDirection` enum.
            const direction = Excel.KeyboardDirection.up;

            // Get the active cell in the workbook.
            const activeCell = context.workbook.getActiveCell();

            // Get the top-most cell of the current used range.
            // This method acts like the Ctrl+Arrow key keyboard shortcut while a range is selected.
            const rangeEdge = range.getRangeEdge(
              direction,
              activeCell // If the selected range contains more than one cell, the active cell must be defined.
            );
            rangeEdge.select();

            await context.sync();
          });
        }

        async function getExtendedRangeRight() {
          await Excel.run(async (context) => {
            // Get the selected range.
            const range = context.workbook.getSelectedRange();

            // Get the active cell in the workbook.
            const activeCell = context.workbook.getActiveCell();

            // Get all the cells from the currently selected range to the right-most edge of the used range.
            // This method acts like the Ctrl+Shift+Arrow key keyboard shortcut while a range is selected.
            const extendedRange = range.getExtendedRange(
              "Right", // Specify the direction as a string.
              activeCell // If the selected range contains more than one cell, the active cell must be defined.
            );
            extendedRange.select();

            await context.sync();
          });
        }

        async function getExtendedRangeDown() {
          await Excel.run(async (context) => {
            // Get the selected range.
            const range = context.workbook.getSelectedRange();

            // Specify the direction with the `KeyboardDirection` enum.
            const direction = Excel.KeyboardDirection.down;

            // Get the active cell in the workbook.
            const activeCell = context.workbook.getActiveCell();

            // Get all the cells from the currently selected range to the bottom-most edge of the used range.
            // This method acts like the Ctrl+Shift+Arrow key keyboard shortcut while a range is selected.
            const extendedRange = range.getExtendedRange(
              direction,
              activeCell // If the selected range contains more than one cell, the active cell must be defined.
            );
            extendedRange.select();

            await context.sync();
          });
        }

        /** Select a range with one cell. */
        async function selectE9() {
          await Excel.run(async (context) => {
            // Get the active worksheet.
            const sheet = context.workbook.worksheets.getActiveWorksheet();

            // Select cell E9.
            const range = sheet.getRange("E9");
            range.select();
            await context.sync();
          });
        }

        /** Select a range with multiple cells. */
        async function selectD8E9() {
          await Excel.run(async (context) => {
            // Get the active worksheet.
            const sheet = context.workbook.worksheets.getActiveWorksheet();

            // Select range D8:E9.
            const range = sheet.getRange("D8:E9");
            range.select();
            await context.sync();
          });
        }

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

            const expensesTable = sheet.tables.add("C5:F5", true /*hasHeaders*/);
            expensesTable.name = "ExpensesTable";

            expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

            expensesTable.rows.add(null /*add at the end*/, [
              ["1/1/2017", "The Phone Company", "Communications", "$120"],
              ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
              ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
              ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
              ["1/11/2017", "Bellows College", "Education", "$350"],
              ["1/15/2017", "Trey Research", "Other", "$135"],
              ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
            ]);

            sheet.getUsedRange().format.autofitColumns();
            sheet.getUsedRange().format.autofitRows();

            sheet.activate();

            await context.sync();
          });
        }

        /** 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 class="ms-Fabric ms-font-m">
          <p>This sample shows how to select the edges of the used range, based on the currently selected range.</p>
        </section>
        <section class="ms-Fabric 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="ms-Fabric samples ms-font-m">
          <h3>Try it out</h3>
          <button id="select-E9" class="ms-Button">
            <span class="ms-Button-label">Select single cell</span>
          </button>
          <button id="select-D8-E9" class="ms-Button">
            <span class="ms-Button-label">Select multiple cells</span>
          </button>
          <br>
          <p>The first type of range edge selection selects the cell at the furthest edge of the current used range, in the directions up or left. This action matches the result of using the <kbd>Ctrl</kbd>+<kbd>Arrow key</kbd> keyboard shortcut while a range is selected.</p>
          <button id="get-range-edge-left" class="ms-Button">
            <span class="ms-Button-label">Go to left edge of used range</span>
          </button>
          <button id="get-range-edge-up" class="ms-Button">
            <span class="ms-Button-label">Go to top edge of used range</span>
          </button>
          <br>
          <p>The second type of range edge selection selects all the cells from the currently selected range to the furthest edge of the used range, in the directions right or down. This action matches the result of using the <kbd>Ctrl</kbd>+<kbd>Shift</kbd>+<kbd>Arrow key</kbd> keyboard shortcut while a range is selected.</p>
          <button id="get-extended-range-right" class="ms-Button">
            <span class="ms-Button-label">Extend range to right edge of used range</span>
          </button>
          <button id="get-extended-range-down" class="ms-Button">
            <span class="ms-Button-label">Extend range to bottom-most edge of used range</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-core@11.1.0/dist/css/fabric.min.css
    office-ui-fabric-js@1.5.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