autofit bug sample new.EXCEL.yaml

name: autofit bug sample new
description: ''
host: EXCEL
api_set: {}
script:
  content: >
    $("#run").click(() => tryCatch(run));

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

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

    $("#checkRange").click(() => tryCatch(checkValues));


    async function run() {
      await setup();
    }


    async function setup() {
      await Excel.run(async (context) => {
        await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sample");

        const sheet = context.workbook.worksheets.getItem("Sample");

        // Generate range data
        const data = [
          ["Year", "Category", "Product", "Sales", "Rating"],
          ["2017", "Components", "Chains", 20000, 0.75],
          ["2015", "Clothing", "Socks", 3700, 0.22],
          ["2016", "Clothing", "Bib-Shorts", 4000, 0.22]
        ];

        const range = sheet.getRange("A1:E4");
        console.log("Original Width");
        await checkValuesInternal(context);

        range.values = data;
        range.numberFormat = createFormatsForTable(sampleColumnFormats, 4, 5);
        range.format.autofitColumns();
        console.log("After Autofit");
        await checkValuesInternal(context);

        sheet.tables
          .add(range, true)
          .getRange()
          .select();
        //range.format.autofitColumns();
        console.log("After CreateTable");
        await checkValuesInternal(context);

        sheet.activate();
      });
    }


    async function setup1() {
      await Excel.run(async (context) => {
        await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sample");

        const sheet = context.workbook.worksheets.getItem("Sample");

        // Generate range data
        const data = [
          ["Year", "Category", "Product", "Sales", "Rating"],
          ["2017", "Components", "Chains", 20000, 0.75],
          ["2015", "Clothing", "Socks", 3700, 0.22],
          ["2016", "Clothing", "Bib-Shorts", 4000, 0.22]
        ];

        const range = sheet.getRange("A1:E4");
        console.log("Original Width");
        await checkValuesInternal(context);

        range.values = data;
        range.numberFormat = createFormatsForTable(sampleColumnFormats, 4, 5);
        range.format.autofitColumns();
        console.log("After Autofit");
        await checkValuesInternal(context);
      });
    }


    async function setup2() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");

        const range = sheet.getRange("A1:E4");

        sheet.tables
          .add(range, true)
          .getRange()
          .select();
        //range.format.autofitColumns();
        console.log("After CreateTable");
        await checkValuesInternal(context);

        sheet.activate();
      });
    }


    const sampleColumnFormats: string[] = [
      "General",
      "General",
      "General",
      '_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)',
      "0%"
    ];

    //const sampleColumnFormats: string[] = ["General", "General", "General",
    "General", "General"];


    function createFormatsForTable(columnFormats: string[], rows: number, cols:
    number): string[][] {
      const formats: string[][] = [];

      // The first row has the headers and should not have any special formatting
      const headerRow: string[] = [];
      for (let i = 0; i < cols; i++) {
        headerRow.push("General");
      }
      formats.push(headerRow);

      // Each row should have the same formatting
      for (let i = 1; i < rows; i++) {
        formats.push([...columnFormats]);
      }

      return formats;
    }


    async function checkValuesInternal(context) {
      const sheet = context.workbook.worksheets.getItem("Sample");
      var formats = sheet.getRange("A1:E1").getColumnProperties({ format: { columnWidth: true } });

      await context.sync();

      formats.value.forEach(function(value, index, array) {
        console.log("" + value.format.columnWidth);
      });
    }


    async function checkValues() {
      await Excel.run(checkValuesInternal);
    }


    /** 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: |-
    <button id="run">run</button>
    <button id="setup1">setup1</button>
    <button id="setup2">setup2</button>
    <button id="checkRange">checkRange</button>
  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/jquery