Add a new line for employee

Add New Employee Line.EXCEL.yaml

name: Add New Employee Line
description: Add a new line for employee
host: EXCEL
api_set: {}
script:
  content: >-
    $("#run").click(() => tryCatch(run));


    async function run() {
      await Excel.run(async (context) => {
        const mainBook = context.workbook.worksheets.getActiveWorksheet();

        let getDate = await getCellValue(mainBook, "A1", context);
        console.log(getDate);

        const numberOfEntriesRange = mainBook.getRange("A2");
        numberOfEntriesRange.load("values");
        await context.sync();
        const numberOfEntries = numberOfEntriesRange.values.toString();
        const entries = Number(numberOfEntries);
        console.log(entries);

        var placement = 3;
        console.log(placement);
        for (var i = 0; i <= entries; i++) {
          var position = placement + i;
          // console.log(`"position", ${position}`)
          const numberOfEntriesRange = mainBook.getRange("B" + String(position));
          numberOfEntriesRange.load("values");
          await context.sync();
          const payType = numberOfEntriesRange.values.toString();
          if (payType != "1" && payType != "2") {
            placement += 1;
            continue;
          } else {
            var namePosition = mainBook.getRange("B" + String(position) + ":P" + String(position));
            namePosition.load("values");
            await context.sync();
            const name = namePosition.values[0][1];
            // console.log(name);

            const nameBook = context.workbook.worksheets.getItem(name);
            var test = await getCellValue(nameBook, "B1", context);

            var start2 = performance.now();
            await InsertNewEmployeeLine(context, nameBook, namePosition, getDate);
            // await DeleteLine(context,nameBook);
            var duration = performance.now() - start2;

            console.log(duration);
          }
        }
        console.log("ENDED");

        // const range = context.workbook.getSelectedRange();
        // range.format.fill.color = "yellow";
        // range.load("address");

        // await context.sync();

        // console.log(`The range address was "${range.address}".`);
      });
    }


    /** 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 getCellValue(book: Excel.Worksheet, cell: string, context:
    Excel.RequestContext) {
      var p = book.getRange(cell);
      p.load("values");
      await context.sync();
      return p.values[0][0];
    }


    async function InsertNewEmployeeLine(
      context: Excel.RequestContext,
      book: Excel.Worksheet,
      rangeValue: Excel.Range,
      dateValue
    ) {
      book.getRange("9:9").insert("Down");

      rangeValue.load("values");
      await context.sync()
      var values = rangeValue.values[0];

      var range = book.getRange("A9");
      range.load("values");
      await context.sync()

      range.values = [[values[0]]];

      var s = values.splice(0,5);

      range = book.getRange("C9:L9");
      range.load("values");
      await context.sync();
      range.values = [values]

      range = book.getRange("B9")
      range.values = [[dateValue]];
      await context.sync();

      range = book.getRange("M9:P9");
      range.values = [
        [
          "=(SUM(D9,E9,G9)*WEEKS_AL_YEAR/WEEKS_PER_YEAR)",
          "=SUM(D9,E9,G9)*HOURS_PER_DAYS/HOURS_PER_WEEK*DAYS_PL_YEAR/WEEKS_PER_YEAR",
          "=B9+VLOOKUP(A9,Data!$A$13:$B$17,2)",
          "=IF(MONTH(O9)>=7,YEAR(O9),YEAR(O9)-1)"
        ]
      ];
      await context.sync();

      range = book.getRange("B9");
      range.numberFormat = [["dd/mm/yy"]];
      range = book.getRange("O9");
      range.numberFormat = [["dd/mm/yy"]];

      await context.sync();
      book.getRange("9:9").format.font.bold = false; 
      await context.sync();
    }


    async function DeleteLine(
      context: Excel.RequestContext,
      book: Excel.Worksheet) {
        book.getRange("9:9").delete("Up");
      }
  language: typescript
template:
  content: "<section class=\"ms-font-m\">\n\t<p class=\"ms-font-m\">This sample demonstrates basic Excel API calls.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<p class=\"ms-font-m\">Select some cells in the worksheet, then press <b>Highlight selected range</b>.</p>\n\t<button id=\"run\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Highlight selected range</span>\n    </button>\n</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]