Calcula Stats Navidad de coleccion

Calcula Stats Navidad .EXCEL.yaml

name: 'Calcula Stats Navidad '
description: Calcula Stats Navidad de coleccion
host: EXCEL
api_set: {}
script:
  content: |
    $("#run").click(() => tryCatch(run));
    const colecciones = {};
    function run() {
      return Excel.run(async function (context) {
        var range = context.workbook.getSelectedRange();
        range.format.fill.color = "yellow";
        range.load("text");
        await context.sync();
        var values = range.text;
        console.log(JSON.stringify(values));
        const data = {};

        for (let val of values) {
          const splt = val[0].trim().split(" ");
          for (let col of splt) {
            const colUpper = col.trim().toUpperCase();
            if (colUpper !== "") colecciones[colUpper] = 0;
          }
        }
        for (let val of values) {
          if (data[val[2]] == null) {
            data[val[2]] = {};
          }

          if (data[val[2]][val[1]] == null) {
            data[val[2]][val[1]] = { ...colecciones };
          }

          const splt = val[0].trim().split(" ");
          for (let col of splt) {
            const colUpper = col.trim().toUpperCase();

            if (colUpper !== "") data[val[2]][val[1]][colUpper]++;
          }
        }
        sendToWorksheet(data);

        console.log(JSON.stringify(data));
      });
    }

    function prepend(value, array) {
      var newArray = array.slice();
      newArray.unshift(value);
      return newArray;
    }

    function sendToWorksheet(data) {
      return Excel.run(async function (context) {
        const headers = [
          "AGENTE",
          "BUYER",
          "FABRICA",
          "CATEGORIA 2",
          "COD CATEGORIA",
        ];
        const output = [];

        const rowC = ["", ""];

        for (let fabrica of Object.keys(data)) {
          const rowParent = [...rowC];
          rowParent.push(fabrica);
          rowParent.push("");
          console.log("fabrica", fabrica);
          for (let codigo of Object.keys(data[fabrica])) {
            const row = [...rowParent];

            console.log("codigo", codigo);
            row.push(codigo);
            console.log("codigo Keys", Object.keys(data[fabrica][codigo]));
            for (let coleccion of Object.keys(data[fabrica][codigo])) {
              row.push(data[fabrica][codigo][coleccion]);
              if (headers.indexOf(coleccion) <= -1)
                headers.push(coleccion);
            }
            output.push(row);
          }
        }
        const outputFinal = prepend(headers, output);
        console.log(outputFinal);

        let sheet = context.workbook.worksheets.getLast();
        sheet.activate();
        // let range = sheet.getRange(`A${4}:W${4 + outputFinal.length}`);
        // console.log("Range printing", `A${4}:X${4 + outputFinal.length}`);
        // const range = sheet.getRange("A1"); 
        // range.format.fill.color = "yellow";
        var _celltop = sheet.getCell(0, 0);
        var _cellbottom = sheet.getCell(outputFinal.length - 1, outputFinal[0].length - 1);
        var range = _celltop.getBoundingRect(_cellbottom).insert('Down');  

        range.values = outputFinal;
        // range.values = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]; 
        range.format.autofitColumns();

        await context.sync();
      });
    }

    /** Default helper for invoking an action and handling errors. */
    function tryCatch(callback) {
      Promise.resolve()
        .then(callback)
        .catch(function (error) {
          // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
          console.error(error);
        });
    }
  language: typescript
template:
  content: |
    <section class="ms-font-m">
        <p class="ms-font-m">Calcula tus estadisticas por coleccion.</p>
    </section>

    <section class="samples ms-font-m">
        <h3>Try it out</h3>
        <p class="ms-font-m">Select some cells in the worksheet, then press <b>Run </b>.</p>
        <button id="run" class="ms-Button">
            <span class="ms-Button-label">Run</span>
        </button>
    </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

  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