Excel JavaScriptAPIでSQL

Excel_JavaScriptAPI_SQL.EXCEL.yaml

name: Excel_JavaScriptAPI_SQL
description: Excel JavaScriptAPIでSQL
host: EXCEL
api_set: {}
script:
  content: >
    $("#run").click(() => tryCatch(run));


    type student = {
      id: number;
      name: string;
      age: number;
      gender: string;
      test_score: number;
      birthday: string;
    };


    const data: student[] = [
      {
        id: 1,
        name: "佐藤",
        age: 12,
        gender: "男",
        test_score: 89,
        birthday: "2000-05-09"
      },
      {
        id: 2,
        name: "鈴木",
        age: 9,
        gender: "男",
        test_score: 64,
        birthday: "1997-04-09"
      },
      {
        id: 3,
        name: "高橋",
        age: 8,
        gender: "女",
        test_score: 90,
        birthday: "1996-03-11"
      },
      {
        id: 4,
        name: "田中",
        age: 10,
        gender: "男",
        test_score: 45,
        birthday: "1998-04-05"
      },
      {
        id: 5,
        name: "伊藤",
        age: 11,
        gender: "女",
        test_score: 25,
        birthday: "1999-01-13"
      },
      {
        id: 6,
        name: "渡辺",
        age: 8,
        gender: "女",
        test_score: 50,
        birthday: "1996-02-14"
      },
      {
        id: 7,
        name: "山本",
        age: 11,
        gender: "男",
        test_score: 55,
        birthday: "1999-03-30"
      },
      {
        id: 8,
        name: "中村",
        age: 12,
        gender: "男",
        test_score: 100,
        birthday: "2000-06-01"
      },
      {
        id: 9,
        name: "小林",
        age: 10,
        gender: "女",
        test_score: 99,
        birthday: "1998-07-28"
      },
      {
        id: 10,
        name: "加藤",
        age: 10,
        gender: "女",
        test_score: 14,
        birthday: "1998-08-25"
      }
    ];


    async function run() {
      await Excel.run(async (context) => {
        //init(context, data);
        selectAll(context, data);
        selectAllId(context, data);
        selectAllScore(context, data);
        selectByAge(context, data, 12);
        selectByGender(context, data, "女");
        selectByAgeGrater(context, data, 10);
        selectByScoreLessEqual(context, data, 50);

        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);
      }
    }


    const display = (sheet: any, tableName: any, data: student[]) => {
      let table = sheet.tables.add("A1:F1", true);
      table.name = tableName;
      table.getHeaderRowRange().values = [["ID", "名前", "年齢", "性別", "点数", "生年月日"]];

      const newData = data.map((item: student) => [
        item.id,
        item.name,
        item.age,
        item.gender,
        item.test_score,
        item.birthday
      ]);

      table.rows.add(null, newData);

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


    const init = (context: Excel.RequestContext, data: student[]) => {
      const sheet = context.workbook.worksheets.getActiveWorksheet();
      sheet.set({ name: "オリジナルデータ" });
      display(sheet, "成績表オリジナルデータ", data);
    };


    /**
     * 全件選択
     * SELECT * FROM Student;
     * @param context コンテキスト
     * @param data データ
     */
    const selectAll = (context: Excel.RequestContext, data: student[]) => {
      const select = (data) => data.map((i) => i);
      console.log(select(data));
      context.workbook.worksheets.getItemOrNullObject("全選択").delete();
      const sheet = context.workbook.worksheets.add("全選択");
      display(sheet, "成績表", select(data));
    };


    /**
     * 全ID選択
     * SELECT id FROM Student;
     * @param context コンテキスト
     * @param data データ
     */
    const selectAllId = (context: Excel.RequestContext, data: student[]) => {
      const select_id = (data) => data.map((i) => i.id);
      console.log(select_id(data));
      context.workbook.worksheets.getItemOrNullObject("全ID選択").delete();
      const sheet = context.workbook.worksheets.add("全ID選択");
      let table = sheet.tables.add("A1", true);
      table.name = "ID一覧";
      table.getHeaderRowRange().values = [["ID"]];

      const newData = data.map((item: student) => [item.id]);

      table.rows.add(null, newData);

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


    /**
     * 全点数選択
     * SELECT id,test_score FROM Student;
     * @param context コンテキスト
     * @param data データ
     */
    const selectAllScore = (context: Excel.RequestContext, data: student[]) => {
      const select_id_test_score = (data) => data.map((i) => ({ id: i.id, test_score: i.test_score }));
      console.log(select_id_test_score(data));
      context.workbook.worksheets.getItemOrNullObject("全点数選択").delete();
      const sheet = context.workbook.worksheets.add("全点数選択");
      let table = sheet.tables.add("A1:B1", true);
      table.name = "点数一覧";
      table.getHeaderRowRange().values = [["ID", "点数"]];

      const newData = data.map((item: student) => [item.id, item.test_score]);

      table.rows.add(null, newData);

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


    /**
     * 年齢を条件に選択
     * SELECT * FROM Student WHERE age=12;
     * @param context コンテキスト
     * @param data データ
     * @param age 年齢
     */
    const selectByAge = (context: Excel.RequestContext, data: student[], age:
    number) => {
      const select_age = (data, age) => data.filter((i) => i.age === age);
      console.log(select_age(data, age));
      context.workbook.worksheets.getItemOrNullObject("年齢選択").delete();
      const sheet = context.workbook.worksheets.add("年齢選択");
      display(sheet, "年齢成績表", select_age(data, age));
    };


    /**
     * 性別を条件に選択
     * SELECT * FROM Student WHERE gender="女";
     * @param context コンテキスト
     * @param data データ
     * @param gender 性別
     */
    const selectByGender = (context: Excel.RequestContext, data: student[],
    gender: string) => {
      const select_gender = (data, gender) => data.filter((i) => i.gender === gender);
      console.log(select_gender(data, gender));
      context.workbook.worksheets.getItemOrNullObject("性別選択").delete();
      const sheet = context.workbook.worksheets.add("性別選択");
      display(sheet, "性別成績表", select_gender(data, gender));
    };


    /**
     * 年齢を条件に選択
     * SELECT * FROM Student WHERE age>10;
     * @param context コンテキスト
     * @param data データ
     * @param age 年齢
     */
    const selectByAgeGrater = (context: Excel.RequestContext, data: student[],
    age: number) => {
      const select_age_grater = (data, age) => data.filter((i) => i.age > age);
      console.log(select_age_grater(data, age));
      context.workbook.worksheets.getItemOrNullObject("年齢選択2").delete();
      const sheet = context.workbook.worksheets.add("年齢選択2");
      display(sheet, "年齢成績表2", select_age_grater(data, age));
    };


    /**
     * 成績を条件に選択
     * SELECT * FROM Student WHERE test_score<=50;
     * @param context コンテキスト
     * @param data データ
     * @param score 点数
     */
    const selectByScoreLessEqual = (context: Excel.RequestContext, data:
    student[], score: number) => {
      const select_test_score_grater_less_equal = (data, test_score) => data.filter((i) => i.test_score <= test_score);
      console.log(select_test_score_grater_less_equal(data, 50));
      context.workbook.worksheets.getItemOrNullObject("点数選択2").delete();
      const sheet = context.workbook.worksheets.add("点数選択2");
      display(sheet, "点数成績表2", select_test_score_grater_less_equal(data, score));
    };
  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

  https://cdnjs.cloudflare.com/ajax/libs/mocha/2.2.1/mocha.min.css
  https://cdnjs.cloudflare.com/ajax/libs/mocha/2.2.1/mocha.min.js
  @types/mocha

  https://unpkg.com/expect@%3C21/umd/expect.min.js
  @types/jest

  https://cdnjs.cloudflare.com/ajax/libs/lodash.js/4.17.9/lodash.min.js
  @types/lodash