This sample shows how to set and get data types using the formatted number properties.

Data types: Formatted numbers.EXCEL.yaml

name: 'Data types: Formatted numbers'
description: >-
  This sample shows how to set and get data types using the formatted number
  properties.
host: EXCEL
api_set: {}
script:
  content: >-
    $("#setup").click(() => tryCatch(setup));

    $("#set-formatted-number-date").click(() =>
    tryCatch(setFormattedNumberDate));

    $("#set-formatted-number-currency").click(() =>
    tryCatch(setFormattedNumberCurrency));

    $("#get-formatted-number").click(() => tryCatch(getFormattedNumber));

    $("#get-values").click(() => tryCatch(getValues));


    async function setFormattedNumberDate() {
      // This function creates a formatted number data type,
      // and sets the format of this data type as a date.
      await Excel.run(async (context) => {
        // Get the Sample worksheet and a range on that sheet.
        const sheet = context.workbook.worksheets.getItemOrNullObject("Sample");
        const dateRange = sheet.getRange("A1");

        // Write a number formatted as a date to cell A1.
        dateRange.valuesAsJson = [
          [
            {
              type: Excel.CellValueType.formattedNumber,
              basicValue: 32889.0,
              numberFormat: "m/d/yyyy"
            }
          ]
        ];
        await context.sync();
      });
    }


    async function setFormattedNumberCurrency() {
      // This function creates a formatted number data type,
      // and sets the format of this data type as a currency.
      await Excel.run(async (context) => {
        // Get the Sample worksheet and a range on that sheet.
        const sheet = context.workbook.worksheets.getItemOrNullObject("Sample");
        const currencyRange = sheet.getRange("A2");

        // Write a number formatted as currency to cell A2.
        currencyRange.valuesAsJson = [
          [
            {
              type: Excel.CellValueType.formattedNumber,
              basicValue: 12.34,
              numberFormat: "$* #,##0.00"
            }
          ]
        ];

        await context.sync();
      });
    }


    async function getFormattedNumber() {
      // This function prints information about data types
      // in cells A1 and A2 to the console.
      await Excel.run(async (context) => {
        // Get the Sample worksheet and two ranges on that sheet.
        const sheet = context.workbook.worksheets.getItemOrNullObject("Sample");
        const currencyRange = sheet.getRange("A2");
        const dateRange = sheet.getRange("A1");

        // Load the data type property of the ranges.
        currencyRange.load("valuesAsJson");
        dateRange.load("valuesAsJson");
        await context.sync();

        const currencyValues = currencyRange.valuesAsJson[0][0];
        const dateValues = dateRange.valuesAsJson[0][0];

        // Print information about the data types to the console.
        console.log("Date");
        console.log("  Type: " + dateValues.type);
        console.log("  Basic value: " + dateValues.basicValue);
        console.log("  Basic type: " + dateValues.basicType);
        console.log("  Number format: " + (dateValues as Excel.FormattedNumberCellValue).numberFormat);

        console.log("Currency");
        console.log("  Type: " + currencyValues.type);
        console.log("  Basic value: " + currencyValues.basicValue);
        console.log("  Basic type: " + currencyValues.basicType);
        console.log("  Number format: " + (currencyValues as Excel.FormattedNumberCellValue).numberFormat);
      });
    }


    async function setup() {
      await Excel.run(async (context) => {
        // Create a new worksheet called "Sample" and activate it.
        context.workbook.worksheets.getItemOrNullObject("Sample").delete();
        const sheet = context.workbook.worksheets.add("Sample");
        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);
      }
    }


    async function getValues() {
      await Excel.run(async (context) => {
        const range = context.workbook.getSelectedRange();
        range.load(["text", "values", "valuesAsJson"]);
        await context.sync();

        console.log(`text:\n${JSON.stringify(range.text)}\n`);
        console.log(`values:\n${JSON.stringify(range.values)}\n`);
        console.log(`valuesAsJson:\n${JSON.stringify(range.valuesAsJson)}\n`);
      })
    }
  language: typescript
template:
  content: |-
    <section class="ms-font-m">
      <p>This sample shows how to work with the formatted number data type.</p>
    </section>
    <section class="setup ms-font-m">
      <h3>Set up</h3>
      <button id="setup" class="ms-Button">
        <span class="ms-Button-label">Add worksheet</span>
      </button>
      <h3>Try it out</h3>
      <button id="set-formatted-number-date" class="ms-Button">
        <span class="ms-Button-label">Set formatted date</span>
      </button>
      <button id="set-formatted-number-currency" class="ms-Button">
        <span class="ms-Button-label">Set formatted currency</span>
      </button>
      <button id="get-formatted-number" class="ms-Button">
        <span class="ms-Button-label">Log data types</span>
      </button>
    <button id="get-values" class="ms-Button">
        <span class="ms-Button-label">Get values</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

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

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

  [email protected]
  @types/[email protected]