Permite preparar a planilha na estrutura correta antes de possibilitar realizar operações aritméticas básicas.

Operações Aritméticas com Preparação.EXCEL.yaml

name: Operações Aritméticas com Preparação
description: >-
  Permite preparar a planilha na estrutura correta antes de possibilitar
  realizar operações aritméticas básicas.
host: EXCEL
api_set: {}
script:
  content: "const prepare = document.getElementById(\"prepare\") as HTMLButtonElement;\r\nprepare.addEventListener(\"click\", async () => {\r\n  await Excel.run(async (context) => {\r\n    const sheet = context.workbook.worksheets.getActiveWorksheet();\r\n    sheet.getRange().clear();\r\n    sheet.name = \"Calculadora\";\r\n\r\n    const range = sheet.getRange(\"A1:B5\");\r\n    range.values = [[\"Calculadora\", \"\"], [\"Número 1\", \"\"], [\"Número 2\", \"\"], [\"Operação\", \"\"], [\"Resultado\", \"\"]];\r\n    range.format.borders.getItem(\"InsideHorizontal\").color = \"Black\";\r\n    range.format.borders.getItem(\"InsideVertical\").color = \"Black\";\r\n    range.format.borders.getItem(\"EdgeTop\").color = \"Black\";\r\n    range.format.borders.getItem(\"EdgeLeft\").color = \"Black\";\r\n    range.format.borders.getItem(\"EdgeRight\").color = \"Black\";\r\n    range.format.borders.getItem(\"EdgeBottom\").color = \"Black\";\r\n\r\n    sheet.getRange(\"A1:A5\").format.font.bold = true;\r\n    sheet.getRange(\"A1:A5\").format.fill.color = \"#aaaaaa\";\r\n    sheet.getRange(\"A1:B1\").merge();\r\n    sheet.getRange(\"A1:B1\").format.horizontalAlignment = \"Center\";\r\n\r\n    const format = \"#,##0.00;-#,##0.00\";\r\n    sheet.getRange(\"B2:B3\").numberFormat = [[format], [format]];\r\n    sheet.getRange(\"B5\").numberFormat = [[format]];\r\n\r\n    sheet.getRange(\"B2:B5\").format.horizontalAlignment = \"Left\";\r\n    sheet.getRange(\"B2:B5\").format.fill.color = \"White\";\r\n\r\n    sheet.getRange().format.autofitColumns();\r\n    sheet.getRange(\"A1\").format.columnWidth = 65;\r\n    sheet.getRange(\"B1\").format.columnWidth = 70;\r\n    await context.sync();\r\n  });\r\n});\r\n\r\nconst calc = document.getElementById(\"calc\") as HTMLButtonElement;\r\ncalc.addEventListener(\"click\", async () => {\r\n  await Excel.run(async (context) => {\r\n    const sheet = context.workbook.worksheets.getActiveWorksheet();\r\n\r\n    const range = sheet.getRange(\"B2:B5\");\r\n    range.load(\"values\");\r\n    await context.sync();\r\n\r\n    const n1 = parseFloat(range.values[0][0]);\r\n    const n2 = parseFloat(range.values[1][0]);\r\n    const op = range.values[2][0];\r\n    let r;\r\n\r\n    if (op === \"Soma\") {\r\n      r = n1 + n2;\r\n    } else if (op === \"Subtração\") {\r\n      r = n1 - n2;\r\n    } else if (op === \"Multiplicação\") {\r\n      r = n1 * n2;\r\n    } else if (op === \"Divisão\") {\r\n      r = n1 / n2;\r\n    }\r\n\r\n    if (isNaN(r)) {\r\n      r = \"Erro\";\r\n    }\r\n\r\n    range.values = [[n1], [n2], [op], [r]];\r\n    await context.sync();\r\n  });\r\n});\r\n"
  language: typescript
template:
  content: |-
    <p>Primeiro prepare a planilha usando este botão:</p>
    <button id="prepare">Preparar</button>

    <p>Preencha as células B2, B3 e B4 e veja o resultado na B5.</p>
    <button id="calc">Calcular</button>
  language: html
style:
  content: ''
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  [email protected]/client/core.min.js
  @types/core-js