EX 5.EXCEL.yaml

name: EX 5
description: ''
host: EXCEL
api_set: {}
script:
  content: |
    const form = document.getElementById("form") as HTMLFormElement;

    form.addEventListener("submit", async (event) => {
      event.preventDefault();

      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        sheet.name = "Alunos";

        sheet.getRange().clear("Formats");

        const range = sheet.getRange("A1:B1").getExtendedRange("Down");

        let table = sheet.tables.getItemOrNullObject("Alunos");
        await context.sync();

        if (table.isNullObject) {
          table = sheet.tables.add(range, false);
          table.name = "Alunos";
        }

        table.style = "TableStyleMedium15";

        table.columns.getItemAt(0).name = "Nome";
        table.columns.getItemAt(1).name = "Nota";

        range.format.autofitColumns();

        table.sort.apply([{ key: 0, ascending: true }]);

        const rangeB = sheet.getRange("B2").getExtendedRange("Down");
        rangeB.load("numberFormat");
        await context.sync();

        rangeB.numberFormat = rangeB.numberFormat.map(() => ["#,##0.0"]);

        rangeB.format.horizontalAlignment = "Left";

        rangeB.conditionalFormats.clearAll();

        const conditionalRed = rangeB.conditionalFormats.add("CellValue");
        conditionalRed.cellValue.format.font.color = "Red";
        conditionalRed.cellValue.rule = { formula1: "=6", operator: "LessThan" };

        const conditionalBlue = rangeB.conditionalFormats.add("CellValue");
        conditionalBlue.cellValue.format.font.color = "Blue";
        conditionalBlue.cellValue.rule = { formula1: "=6", operator: "GreaterThanOrEqual" };

        await context.sync();
      });
    });
  language: typescript
template:
  content: "\n\n<form id=\"form\" novalidate>\n\t<button>Formatar tabela de alunos</button>\n</form>"
  language: html
style:
  content: ''
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  core-js@2.4.1/client/core.min.js
  @types/core-js