单元格赋值

单元格赋值.EXCEL.yaml

name: 单元格赋值
description: 单元格赋值
host: EXCEL
api_set: {}
script:
  content: |
    $("#run").click(() => tryCatch(run));

    async function run() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        // 给一个单元格赋值
        sheet.getRange("A1").values = [[12345]];

        // 给一个单元格设置公式
        sheet.getRange("B1").formulas = [["=A1*100"]];

        // 给多个单元格赋值
        sheet.getRange("A2:E2").values = [[1, 2, 3, 4, 5]];

        // 给一片单元格赋值
        sheet.getRange("A3:E5").values = [
          [1, 2, 3, 4, 5],
          [2, 3, 4, 5, 6],
          [3, 4, 5, 6, 7]
        ];

        // 从一组对象赋值给单元格,
        const users = [
          { name: "ares", age: 18 },
          { name: "tom", age: 20 }
        ];

        sheet.getRange("A6:B8").values = convertObjectToArray(users);

        // 从网络资源赋值给单元格

        const orders:any[] = await fetch('https://services.odata.org/V4/Northwind/Northwind.svc/Orders').then(x=>x.json()).then(x=>x.value);

        sheet.getRange("G1").getResizedRange(orders.length,Object.keys(orders[0]).length-1).values = convertObjectToArray(orders);

        await context.sync();
      });
    }

    function convertObjectToArray(obj:any[]):(string|number|boolean)[][]{
      // 这个方法可以从一组对象转换为Excel需要的数组，可以适合于任何对象集合
      let result:(string|number|boolean)[][] = [];
      let first = obj[0];
      result.push(Object.keys(first));
      obj.forEach(v=>{
        result.push(Object.keys(v).map(k=>v[k]));
      });

      return result;
    }

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        console.error(error);
      }
    }
  language: typescript
template:
  content: |
    <button id="run" class="ms-Button">
        <span class="ms-Button-label">Run</span>
    </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/1/hosted/office.js
  @types/office-js

  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