EX 4.EXCEL.yaml

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

    const field1 = document.getElementById("field1") as HTMLInputElement;

    const field2 = document.getElementById("field2") as HTMLInputElement;

    const button1 = document.getElementById("showBestStudent") as
    HTMLButtonElement;

    const button2 = document.getElementById("showWorstStudent") as
    HTMLButtonElement;

    const button3 = document.getElementById("showAverageGrade") as
    HTMLButtonElement;

    const result = document.getElementById("result") as HTMLDivElement;


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

      await Excel.run(async (context) => {
        if (!isFormValid()) return;

        const sheet = context.workbook.worksheets.getActiveWorksheet();
        sheet.name = "Alunos";

        let range = sheet.getUsedRange(true);
        range.load("columnCount");
        await context.sync();

        if (range.columnCount > 1) {
          range = range.getRowsBelow();
        } else {
          range = sheet.getRange("A1:B1");
        }

        range.values = [[field1.value, field2.value]];
        await context.sync();

        field1.value = field2.value = "";
        field1.focus();
      });
    });


    function isFormValid() {
      field1.className = field2.className = "";
      result.innerHTML = "";

      if (!field1.value) {
        result.innerHTML = '<p class="negative">Por favor, informe um nome para adicionar.</p>';
        field1.className = "negative";
        field1.focus();
        return false;
      }

      const n = parseFloat(field2.value);
      if (isNaN(n) || n < 0 || n > 10) {
        result.innerHTML = '<p class="negative">Por favor, informe uma nota válida para adicionar.</p>';
        field2.className = "negative";
        field2.focus();
        return false;
      }

      return true;
    }


    button1.addEventListener("click", async () => {
      field1.className = field2.className = "";
      result.innerHTML = "";

      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        let range = sheet.getRange("A1:B1").getExtendedRange("Down");
        range.load("values");
        await context.sync();

        if (!range.values.length) {


          result.innerHTML = '<p class="negative">Nenhum aluno cadastrado.</p>';
          field1.focus();
          return;
        }

        range.values.sort((a, b) => b[1] - a[1]);

        const best = range.values[0];

        result.innerHTML = `<p>Parabéns para <b>${best[0]}</b>,
          melhor aluno da turma com nota <b>${best[1]}</b>!</p>`;
      });
    });


    button2.addEventListener("click", async () => {
      field1.className = field2.className = "";
      result.innerHTML = "";

      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        let range = sheet.getRange("A1:B1").getExtendedRange("Down");
        range.load("values");
        await context.sync();

        if (!range.values.length) {
          result.innerHTML = '<p class="negative">Nenhum aluno cadastrado.</p>';
          field1.focus();
          return;
        }

        range.values.sort((a, b) => a[1] - b[1]);

        const worst = range.values[0];

        result.innerHTML = `<p>Nossos pêsames para <b>${worst[0]}</b>,
          pior aluno da turma com nota <b>${worst[1]}</b>!</p>`;
      });
    });


    button3.addEventListener("click", async () => {
      field1.className = field2.className = "";
      result.innerHTML = "";

      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        let range = sheet.getRange("A1:B1").getExtendedRange("Down");
        range.load("values");
        await context.sync();

        if (!range.values.length) {
          result.innerHTML = '<p class="negative">Nenhum aluno cadastrado.</p>';
          field1.focus();
          return;
        }

        const sum = range.values.reduce((sum, line) => sum + line[1], 0);

        const average = sum / range.values.length;

        result.innerHTML = `<p>A média aritmética desta turma foi:
          <b>${average}</b>.</p>`;
      });
    });
  language: typescript
template:
  content: "<form id=\"form\" novalidate>\r\n\t<input id=\"field1\" placeholder=\"Nome do aluno\" autocomplete=\"off\" required>\r\n\t<input id=\"field2\" type=\"number\" placeholder=\"Nota final\" autocomplete=\"off\" required>\r\n\t<button>Adicionar</button>\r\n</form>\r\n\r\n<div class=\"buttons\">\r\n\t<button id=\"showBestStudent\">Melhor aluno</button>\r\n\t<button id=\"showWorstStudent\">Pior aluno</button>\r\n\t<button id=\"showAverageGrade\">Média aritmética da turma</button>\r\n</div>\r\n\r\n<div id=\"result\"></div>"
  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