Data label APIs from Scratch.EXCEL.yaml

name: Data label APIs from Scratch
description: ''
host: EXCEL
api_set: {}
script:
  content: >
    $("#prepare-chart").click(() => tryCatch(prepareChart));


    const SHEET_NAME = "SampleSheet";

    const CHART_NAME = "SampleChart";

    const RANGE_DATA = [
      ["Month", "GDP"],
      ["January", 190],
      ["February", 169],
      ["March", 194],
      ["April", 166],
      ["May", 122],
      ["June", 234],
      ["July", 181],
      ["August", 146],
      ["September", 155],
      ["October", 118],
      ["November", 152],
      ["December", 189]
    ];


    // Microsoft Product Group Starter Code provided pursuant to SIA Variation
    No.7.

    async function prepareChart() {
      await Excel.run(async (context) => {
        let sampleSheet = context.workbook.worksheets.getItemOrNullObject(SHEET_NAME);
        await context.sync();
        if (sampleSheet.isNullObject) {
          sampleSheet = context.workbook.worksheets.add(SHEET_NAME);
        }
        sampleSheet.activate();
        let range = sampleSheet.getRange("A1:B13");
        range.values = RANGE_DATA;
        let chart = sampleSheet.charts.getItemOrNullObject(CHART_NAME);
        await context.sync();
        if (!chart.isNullObject) {
          chart.delete();
          await context.sync();
        }
        chart = sampleSheet.charts.add(Excel.ChartType.columnClustered, range);
        chart.set({
          name: CHART_NAME,
          left: 200,
          top: 150,
          height: 350,
          width: 600
        });
        await context.sync();
      });
    }


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

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        console.error(error);
      }
    }


    $("#add-data-label").click(() => tryCatch(addDataLabel));

    const LABEL_INDEX = 5;

    const LABEL_INDEX_2 = 9;


    // Microsoft Product Group Starter Code provided pursuant to SIA Variation
    No.7.

    async function addDataLabel() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItemOrNullObject(SHEET_NAME);
        await context.sync();

        if (sheet.isNullObject) {
          console.log("Please click the 'Prepare a chart' button at first.");
          return;
        }

        // Get the chart on the sheet.
        let chart = sheet.charts.getItemOrNullObject(CHART_NAME);
        await context.sync();

        if (chart.isNullObject) {
          console.log("Please click the 'Prepare a chart' button at first.");
          return;
        }

        // Create a new data label at specific point in series.
        let series = chart.series.getItemAt(0);
        let point = series.points.getItemAt(LABEL_INDEX);
        let point2 = series.points.getItemAt(LABEL_INDEX_2);
        point.hasDataLabel = true;
        point2.hasDataLabel = true;
        await context.sync();

        // Set the new label properties.
        point.dataLabel.set({
          geometricShapeType: Excel.GeometricShapeType.rectangle,
          text: "The city held a Sports event."
        });
        point2.dataLabel.set({
          geometricShapeType: Excel.GeometricShapeType.rectangle,
          text: "One-week public holidays."
        });
        await context.sync();
      });
    }


    $("#format-data-label").click(() => tryCatch(formatDataLabel));


    // Microsoft Product Group Starter Code provided pursuant to SIA Variation
    No.7.

    async function formatDataLabel() {
      await Excel.run(async (context) => {
        // To simplify the code, we use the first chart on current sheet.
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const chart = sheet.charts.getItemAt(0);
        const series = chart.series.getItemAt(0);
        await context.sync();

        // Get data label at specific point in series.
        let label = series.points.getItemAt(LABEL_INDEX).dataLabel;
        await context.sync();

        // Change the text format properties.
        let labelFormat = label.format;
        labelFormat.font.bold = true;
        labelFormat.font.size = 12;
        labelFormat.font.color = "red";
        await context.sync();
      });
    }


    $("#format-subtext").click(() => tryCatch(formatSubtext));


    // Microsoft Product Group Starter Code provided pursuant to SIA Variation
    No.7.

    async function formatSubtext() {
      await Excel.run(async (context) => {
        // To simplify the code, we use the first chart on current sheet.
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const chart = sheet.charts.getItemAt(0);
        const series = chart.series.getItemAt(0);
        await context.sync();

        // Get data label at specific point in series.
        let label = series.points.getItemAt(LABEL_INDEX).dataLabel;
        label.format.font.color = "grey";
        label.load("text");
        await context.sync();

        let searchStr = "Sports";
        let substrStart = label.text.indexOf(searchStr);
        if (substrStart === -1) return;

        let formatString = label.getSubstring(substrStart, searchStr.length);
        formatString.font.name = "Calibri";
        formatString.font.size = 14;
        formatString.font.bold = true;
        formatString.font.color = "red";
        await context.sync();
      });
    }


    $("#change-shape").click(() => tryCatch(changeDataLabelShape));


    // Microsoft Product Group Starter Code provided pursuant to SIA Variation
    No.7.

    async function changeDataLabelShape() {
      await Excel.run(async (context) => {
        // To simplify the code, we use the first chart on current sheet.
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const chart = sheet.charts.getItemAt(0);
        const series = chart.series.getItemAt(0);
        await context.sync();

        // Hide the leader lines
        series.showLeaderLines = false;

        // Get data label at specific point in series.
        let label = series.points.getItemAt(LABEL_INDEX).dataLabel.load("left");
        await context.sync();

        label.horizontalAlignment = Excel.ChartTextHorizontalAlignment.left;
        label.geometricShapeType = Excel.GeometricShapeType.wedgeRRectCallout;
        await context.sync();
      });
    }


    $("#format-series-labels").click(() => tryCatch(formatSeriesDataLabels));


    // Microsoft Product Group Starter Code provided pursuant to SIA Variation
    No.7.

    async function formatSeriesDataLabels() {
      await Excel.run(async (context) => {
        // To simplify the code, we use the first chart on current sheet.
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const chart = sheet.charts.getItemAt(0);
        const series = chart.series.getItemAt(0);
        await context.sync();

        series.dataLabels.format.fill.setSolidColor("90EE90");
        await context.sync();
      });
    }


    $("#remove-data-label").click(() => tryCatch(removeDataLabel));


    // Microsoft Product Group Starter Code provided pursuant to SIA Variation
    No.7.

    async function removeDataLabel() {
      await Excel.run(async (context) => {
        // To simplify the code, we use the first chart on current sheet.
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const chart = sheet.charts.getItemAt(0);
        const series = chart.series.getItemAt(0);
        await context.sync();

        // Remove the data label.
        series.points.getItemAt(LABEL_INDEX).hasDataLabel = false;
        await context.sync();
      });
    }
  language: typescript
template:
  content: "<section class=\"ms-font-m\">\n\t<h2>Enjoy the chart data label APIs!</h2>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Prepare A Chart</h3>\n\t<button id=\"prepare-chart\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Prepare a chart</span>\n    </button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Try the data label APIs</h3>\n\t<button id=\"add-data-label\" class=\"ms-Button\">\n\t\t\t<span class=\"ms-Button-label\">Add data label</span>\n\t</button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<button id=\"format-data-label\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Format the data label</span>\n\t</button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<button id=\"format-subtext\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Format substring of the data label</span>\n\t</button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<button id=\"format-series-labels\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Format data labels on a series</span>\n\t</button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<button id=\"change-shape\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Change shape of the data label</span>\n\t</button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<button id=\"remove-data-label\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Remove a data label</span>\n\t</button>\n</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-experiment52.d.ts

  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