Shared with Script Lab

IgniteDemo.yaml

name: IgniteDemo
description: ''
author: lumine2008
host: EXCEL
api_set: {}
script:
    content: |
        $("#run").click(() => tryCatch(insertData));
        $("#pivot").click(() => tryCatch(createPivot));
        $("#refresh").click(() => tryCatch(refreshPivot));
        $("#delete").click(() => tryCatch(deletePivot));

        $("#yearSelect").change(combo);


        //hold individual tickets
        var trips = [];
        // holds number of rows (and *4 number of cells)
        var cont = 0;
        var myRange = null //holds range of inserted cells
        var NumYears = 3;

        async function insertData() {
            document.getElementById("result").innerHTML = "Running...";
            document.getElementById("result2").innerHTML = "Running...";
            cont = 0;
            trips = [];
            generateArray();
            //   console.log(trips);
            await Excel.run(async (context) => {
                var timer3 = Date.now();
                myRange = "A1:" + "G" + (cont + 1);
                context.workbook.worksheets.getActiveWorksheet().getRange(myRange).values = trips;
                await context.sync();

                document.getElementById("result2").innerHTML = "<H4> Cells inserted in: "
                    + addCommas((Date.now() - timer3)) + "ms</H4>";
            });
        }


        async function createPivot() {

            await Excel.run(async (context) => {
                var mySheet = context.workbook.worksheets.getActiveWorksheet();
                console.log(myRange);
                var myPivot = mySheet.pivotTables.add("noShows", myRange, mySheet.getRange("I1"));
                var timer3 = Date.now();

                await context.sync();

                var routesH = myPivot.hierarchies.getItem("Route");
                var yearH = myPivot.hierarchies.getItem("Year");
                var monthH = myPivot.hierarchies.getItem("Month");
                var dayH = myPivot.hierarchies.getItem("Day");
                var DateH = myPivot.hierarchies.getItem("Date");

                var noShowsH = myPivot.hierarchies.getItem("NoShow");
                myPivot.columnHierarchies.add(routesH);
                myPivot.rowHierarchies.add(yearH);
                myPivot.rowHierarchies.add(monthH);
                myPivot.rowHierarchies.add(dayH);
                  myPivot.dataHierarchies.add(noShowsH)
                await context.sync();

            });


        }

        async function refreshPivot() {

            await Excel.run(async (context) => {
                context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("noShows").refresh()

                await context.sync();

            });


        }

        async function deletePivot() {

            await Excel.run(async (context) => {
                var mySheet = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("noShows").delete();
                await context.sync();

            });


        }






        function generateArray() {
            var timer1 = Date.now();

            trips.push(["Route", "Date", "NoShow", "TicketId", "Year", "Month", "Day"]);

            for (var routesCount = 0; routesCount < 4; routesCount++) {
                for (var year = 0; year < NumYears; year++) {
                    for (var month = 0; month < 2; month++) {
                        for (var day = 0; day < 5; day++) {
                            for (var passengers = 0; passengers < 300; passengers++) {
                                var route = getRoute(routesCount);

                                var myDate = getDate(year, month, day);
                                var myNoShow = getRandom(month, day, year, route);
                                var tempTrip = [route, myDate, myNoShow, 1, (2018 - year), ((9 - month)) == 9 ? "Sep" : "Aug", (26 - day)];
                                cont = cont + 1;

                                trips.push(tempTrip);

                            }

                        }
                    }
                }
            }
            // console.log(cont);
            var completedIn = Date.now() - timer1;
            document.getElementById("result").innerHTML = "<H4> Data ready! " + addCommas((cont + 1) * 7) + " cells in: " + addCommas(completedIn) + "ms <H4>";


        }

        function getDate(year, month, day) {
            year = 2018 - year;
            month = 9 - month;
            day = 26 - day

            var myDate = month + "/" + day + "/" + year;
            return (myDate);
        }

        function getRandom(month, day, year, route) {
            var rnd = Math.random();
            if ((route == "SEA->MCO") && (year == 0) && (month == 0) && (day == 3 || day == 4)) {
                if (rnd < 0.4)
                    return 1
                else
                    return 0;

            }

            else {
                if (rnd < 0.01)
                    return 1
                else
                    return 0;
            }
        }

        function getRoute(route) {
            if (route == 0) {
                return ("MCO->SEA");
            }
            else if (route == 1) {
                return ("MCO->SFO");

            }
            else if (route == 2) {
                return ("SEA->MCO");

            }
            else if (route == 3) {
                return ("SFO->MCO");

            }

        }


        /** Default helper for invoking an action and handling errors. */
        async function tryCatch(callback) {
            try {
                await callback();
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        function addCommas(nStr) {
            nStr += '';
            x = nStr.split('.');
            x1 = x[0];
            x2 = x.length > 1 ? '.' + x[1] : '';
            var rgx = /(\d+)(\d{3})/;
            while (rgx.test(x1)) {
                x1 = x1.replace(rgx, '$1' + ',' + '$2');
            }
            return x1 + x2;
        }

        function combo() {
            var theinput = document.getElementById("yearSelect");
            var idx = theinput.selectedIndex;
            NumYears = theinput.options[idx].innerHTML;


        }
    language: typescript
template:
    content: |-
        <div class="ms-font-l" > Welcome to Ticket Simulator
        <br><br>


        <span class="ms-Button-label">Years:</span>
        <select id="yearSelect">
        <option>3</option>
        <option>5</option>
        <option>10</option>
        <option>20</option>
        <option>30</option>
        <option>40</option>
        <option>50</option>
        </select>

        <br><br>

        <button id="run" class="ms-Button">
            <span class="ms-Button-label">Insert Data</span>
        </button>
        <BR><BR>
        <button id="pivot" class="ms-Button">
            <span class="ms-Button-label">Create Pivot</span>
        </button>
        <BR>
        <BR>
        <button id="refresh" class="ms-Button">
            <span class="ms-Button-label">Refresh Pivot</span>
        </button>

        <BR>
        <BR>
        <button id="delete" class="ms-Button">
            <span class="ms-Button-label">Delete Pivot</span>
        </button>
        <br><Br>
        </i><div class= "ms-font-m-plus" id="result">
        </div>

        <div  class= "ms-font-m-plus" id="result2">
        </div>
    language: html
style:
    content: |
        /* Your style goes here */
    language: css
libraries: |
    https://appsforoffice.microsoft.com/lib/beta/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

    @microsoft/[email protected]/dist/office.helpers.min.js
    @microsoft/[email protected]/dist/office.helpers.d.ts

    [email protected]
    @types/jquery