広島ITエンジニア合同忘年会2022 LT

Excel JavaScript API Demo.EXCEL.yaml

name: Excel JavaScript API Demo
description: 広島ITエンジニア合同忘年会2022 LT
host: EXCEL
api_set: {}
script:
  content: >
    $("#run_sql").click(() => tryCatch(runSql));


    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 runSql() {
      await Excel.run(async (context) => {
        selectAll(context, data);
        selectAllScore(context, data);
        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));
    };


    /**
     * 全点数選択
     * 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 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("年齢選択").delete();
      const sheet = context.workbook.worksheets.add("年齢選択");
      display(sheet, "年齢成績表", 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("点数選択").delete();
      const sheet = context.workbook.worksheets.add("点数選択");
      display(sheet, "点数成績表", select_test_score_grater_less_equal(data, score));
    };


    $("#run_db").click(() => tryCatch(runDB));


    type friend = {
      id: number;
      name: string;
      age: number;
    };


    async function runDB() {
      await Excel.run(async (context) => {
        const db = localDB(context);
        const result: friend[] = await db.selectAll();
        renderDBSheet(context, result);
        await context.sync();
      });
    }


    const renderDBSheet = (context: Excel.RequestContext, data: friend[]) => {
      console.log(data);
      context.workbook.worksheets.getItemOrNullObject("FriendTable").delete();
      const sheet = context.workbook.worksheets.add("FriendTable");

      let friendTable = sheet.tables.add("A1:C1", true);
      friendTable.name = "FriendTable";
      friendTable.getHeaderRowRange().values = [["id", "name", "age"]];

      const newData = data.map((item) => [item.id, item.name, item.age]);
      friendTable.rows.add(null, newData);

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


    const localDB = (context: Excel.RequestContext) => {
      const db = new Dexie("FriendDatabase");

      // DB with single table "friends" with primary key "id" and
      // indexes on properties "name" and "age"
      db.version(1).stores({
        friends: `
            id,
            name,
            age`
      });

      // Now add some values.
      db.friends
        .bulkPut([
          { id: 1, name: "Josephine", age: 21 },
          { id: 2, name: "Per", age: 75 },
          { id: 3, name: "Simon", age: 5 },
          { id: 4, name: "Sara", age: 50, notIndexedProperty: "foo" }
        ])
        .then(() => {
          return db.friends
            .where("age")
            .between(0, 25)
            .toArray();
        })
        .then((friends) => {
          alert("Found young friends: " + friends.map((friend) => friend.name));

          return db.friends
            .orderBy("age")
            .reverse()
            .toArray();
        })
        .then((friends) => {
          alert("Friends in reverse age order: " + friends.map((friend) => `${friend.name} ${friend.age}`));

          return db.friends
            .where("name")
            .startsWith("S")
            .keys();
        })
        .then((friendNames) => {
          alert("Friends on 'S': " + friendNames);
        })
        .catch((err) => {
          alert("Ouch... " + err);
        });

      const selectAllFriend = async () => {
        return await db.friends.toArray();
      };

      const insertFriend = async () => {
        await db.friends.add({
          name: "Camilla",
          age: 25,
          street: "East 13:th Street"
        });
      };

      const selectFriendByAage = async () => {
        return await db.friends
          .where("age")
          .between(18, 65)
          .toArray();
      };

      return {
        selectAll: selectAllFriend,
        insert: insertFriend,
        selectBy: selectFriendByAage
      };
    };


    $("#run_api").click(() => tryCatch(runAPI));


    type reservableRoom = {
      meetingRoom: {
        roomId: { value: string };
        roomName: string;
      };
    };


    type reservableRoomList = {
      list: reservableRoom[];
    };


    async function runAPI() {
      await Excel.run(async (context) => {
        const authService = new AuthService();
        await authService.login("U000001", "pAssw0rd");
        const roomService = new RoomService();
        const result = await roomService.list(new Date());
        renderApiSheet(context, result.data);
        authService.logout();
        await context.sync();
      });
    }


    const renderApiSheet = (context: Excel.RequestContext, data:
    reservableRoomList) => {
      console.log(data.list);
      context.workbook.worksheets.getItemOrNullObject("会議室予約一覧").delete();
      const sheet = context.workbook.worksheets.add("会議室予約一覧");

      let reservableRoomTable = sheet.tables.add("A1:B1", true);
      reservableRoomTable.name = "ReservableRoomTable";
      reservableRoomTable.getHeaderRowRange().values = [["RoomId", "RoomName"]];

      const newData = data.list.map((item: reservableRoom) => [item.meetingRoom.roomId.value, item.meetingRoom.roomName]);

      reservableRoomTable.rows.add(null, newData);

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


    const BASE_API_URL = "https://ape2022-take14.herokuapp.com/api";


    namespace authConst {
      export const API_URL = `${BASE_API_URL}/auth`;
    }


    namespace roomConst {
      export const API_URL = `${BASE_API_URL}/rooms`;
    }


    const AUTH_API_URL = authConst.API_URL;


    class AuthService {
      login(userId: any, password: any) {
        return axios
          .post(AUTH_API_URL + "/signin", { userId, password })
          .then((response) => {
            if (response.data.token) {
              localStorage.setItem("user", JSON.stringify(response.data));
            }

            return response.data;
          })
          .catch((error) => {
            throw error;
          });
      }

      logout() {
        localStorage.removeItem("user");
      }
    }


    function authHeader() {
      const user = JSON.parse(<string>localStorage.getItem("user"));

      if (user && user.token) {
        return { Authorization: "Bearer " + user.token };
      } else {
        return { Authorization: "" };
      }
    }


    const API_URL = roomConst.API_URL;


    class RoomService {
      list(reservedDate: Date, page: number = 0) {
        const year = reservedDate.getFullYear();
        const month = ("0" + (reservedDate.getMonth() + 1)).slice(-2);
        const day = ("0" + reservedDate.getDate()).slice(-2);
        const url = `${API_URL}/${year}-${month}-${day}?page=${page}`;

        if (reservedDate) return axios.get(url, { headers: authHeader() });
        return axios.get(API_URL, { headers: authHeader() });
      }
    }


    $("#run_tdd").click(() => tryCatch(runTDD));


    (() => {
      describe("FizzBuzz", () => {
        let fizzBuzz;
        beforeEach(() => {
          fizzBuzz = FizzBuzz;
        });

        describe("三の倍数の場合", () => {
          it("3を渡したときは文字列Fizzを返す", () => {
            expect(fizzBuzz.generate(3)).toEqual("Fizz");
          });
        });
        describe("五の倍数の場合", () => {
          it("5を渡したときは文字列Buzzを返す", () => {
            expect(fizzBuzz.generate(5)).toEqual("Buzz");
          });
        });

        describe("三と五の倍数の場合", () => {
          it("15を渡したら文字列FizzBuzzを返す", () => {
            expect(fizzBuzz.generate(15)).toEqual("FizzBuzz");
          });
        });
        describe("その他の場合", () => {
          it("1を渡したら文字列1を返す", () => {
            expect(fizzBuzz.generate(1)).toEqual("1");
          });
          it("2を渡したら文字列2を返す", () => {
            expect(fizzBuzz.generate(2)).toEqual("2");
          });
        });
        describe("1 から 100 までの数の配列を返す", () => {
          let result;
          beforeEach(() => {
            result = fizzBuzz.generateList();
          });
          it("配列の初めは文字列の1を返す", () => {
            expect(result[0]).toEqual("1");
          });

          it("配列の最後は文字列のBuzzを返す", () => {
            expect(result[result.length - 1]).toEqual("Buzz");
          });

          it("配列の2番目は文字列のFizzを返す", () => {
            expect(result[2]).toEqual("Fizz");
          });

          it("配列の4番目は文字列のBuzzを返す", () => {
            expect(result[4]).toEqual("Buzz");
          });

          it("配列の14番目は文字列のFizzBuzzを返す", () => {
            expect(result[14]).toEqual("FizzBuzz");
          });
        });
      });
    })();


    class FizzBuzz {
      static get MAX_NUMBER() {
        return 100;
      }
      static get FIZZ() {
        return "Fizz";
      }
      static get BUZZ() {
        return "Buzz";
      }

      static generate(number): string {
        const isFizzBuzz = number % 3 === 0 && number % 5 === 0;
        const isFizz = number % 3 === 0;
        const isBuzz = number % 5 === 0;

        if (isFizzBuzz) return `${this.FIZZ}${this.BUZZ}`;
        if (isFizz) return this.FIZZ;
        if (isBuzz) return this.BUZZ;

        return number.toString();
      }

      static generateList(): string[] {
        let array = [];
        for (let i = 0; i < this.MAX_NUMBER; i++) {
          array.push(FizzBuzz.generate(i + 1));
        }
        return array;
      }
    }


    async function runTDD() {
      await Excel.run(async (context) => {
        const result = FizzBuzz.generateList();
        renderFizzBuzzSheet(context, result);
        await context.sync();
      });
    }


    const renderFizzBuzzSheet = (context: Excel.RequestContext, data: string[])
    => {
      console.log(data);

      context.workbook.worksheets.getItemOrNullObject("FizzBuzz").delete();
      const sheet = context.workbook.worksheets.add("FizzBuzz");

      let fizzBuzzTable = sheet.tables.add("A1", true);
      fizzBuzzTable.name = "FizzBuzzTable";
      fizzBuzzTable.getHeaderRowRange().values = [["Result"]];
      const newData = data.map((item) => [item]);
      fizzBuzzTable.rows.add(null, newData);

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


    $("#run_clean").click(() => tryCatch(ヒャッハー));


    async function ヒャッハー() {
      const 汚物は消毒だ = (context: Excel.RequestContext, sheet: string) => {
        context.workbook.worksheets.getItemOrNullObject(sheet).delete();
      };

      await Excel.run(async (context) => {
        const 汚物 = [
          "全選択",
          "全点数選択",
          "性別選択",
          "年齢選択",
          "点数選択",
          "FriendTable",
          "会議室予約一覧",
          "FizzBuzz"
        ];
        汚物.forEach((i) => 汚物は消毒だ(context, i));
        await context.sync();
      });
    }
  language: typescript
template:
  content: "<div id=\"mocha\">\n\t<script src=\"https://unpkg.com/chai/chai.js\"></script>\n\t<script src=\"https://unpkg.com/mocha/mocha.js\"></script>\n\n\t<button id=\"run_sql\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">SQLぽく使えるからってぜんぜん活用する気ないんだからねっ!</span>\n  </button>\n\n\t<button id=\"run_db\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">ストレージに保存できるからってぜんぜん活用する気ないんだからねっ！</span>\n  </button>\n\n\t<button id=\"run_api\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">外部サービスとAPI連携できるからってぜんぜん活用する気ないんだからねっ！</span>\n  </button>\n\n\t<div class=\"btn\">\n\t\t<a href=\"https://application-programing-excercise-2022-aoko1cvqx-k2works.vercel.app/\"\n\t\t\ttarget=\"_blank\">会議室予約システム</a>\n\t</div>\n\n\t<button id=\"run_tdd\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">テスト駆動開発ができるからってぜんぜん活用する気ないんだからねっ！</span>\n\t</button>\n</div>\n\n<script class=\"mocha-init\">\n\tmocha.setup('bdd');\n\t//mocha.checkLeaks();\n</script>\n<script src=\"test.array.js\"></script>\n<script src=\"test.object.js\"></script>\n<script src=\"test.xhr.js\"></script>\n<script class=\"mocha-exec\">\n\tmocha.run();\n</script>\n\n<div id=\"mocha\">\n\t<button id=\"run_clean\" class=\"ms-Button clean\">\n\t\t    <span class=\"ms-Button-label\">おわり</span>\n\t</button>\n</div>\n\n\t<div id=\"mocha\">\n\t\t<a href=\"https://gist.github.com/k2works/b41fc6cc166931baf78ffe7df6b0fe8d\" target=\"_blank\">公開コード</a>\n\t</div>"
  language: html
style:
  content: "section.samples {\n    margin-top: 20px;\n}\n\nsection.samples .ms-Button, section.setup .ms-Button {\n    display: block;\n    margin-bottom: 5px;\n    margin-left: 20px;\n    min-width: 80px;\n}\n\na {\n  text-decoration: none;\n}\n\n.btn a {\n  display: flex;\n\tjustify-content: center;\n\talign-items: center;\n\tposition: relative;\n\twidth: 300px;\n\theight: 60px;\n\tcolor: #333;\n\tfont-size: 18px;\n\tfont-weight: 700;\n\ttext-decoration: none;\n  background-color: #7dca65;\n  border-radius: 20px;\n\tborder: 2px solid #325328;\n}\n\n.btn a::after {\n  content: '';\n\tposition: absolute;\n\ttop: 50%;\n\tright: 20px;\n\ttransform: rotate(45deg) translateY(-50%);\n\twidth: 6px;\n\theight: 6px;\n\tborder-width: 2px 2px 0 0;\n\tborder-style: solid;\n\tborder-color: #325328;\n}"
  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/dexie/3.2.2/dexie.min.js
  https://cdnjs.cloudflare.com/ajax/libs/axios/1.2.1/axios.min.js

  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