Shared with Script Lab

Bing Maps - Tech Summit 2018.yaml

name: Bing Maps - Tech Summit 2018
description: ''
author: XiangGaoMSFT
host: EXCEL
api_set: {}
script:
    content: |+
        var map;

        $(function () {
            initialize();
        })

        async function initialize() {
            await resetMap();
            await loadSampleData();

            $("#registerEvents").click(() => tryCatch(registerEvents));
            $("#unRegisterEvents").click(() => tryCatch(unRegisterEvents));
        }

        async function resetMap() {
            map = new Microsoft.Maps.Map(document.getElementById("myMap"), {
                credentials: 'AiLjvaOSxb3eaqTWhEEIxMkvtGIxg1WfN5aysNTO5YN7IiRav_3kWefHXaNZSfYS',
                center: new Microsoft.Maps.Location(47.60357, -122.32945),
                zoom: 6
            });
        }
        var sheetMap;

        async function loadSampleData() {
            var values = [
                ['Country', 'Capital', 'Latitude', 'Longitude'],
                ['China', 'Beijing', '39.905897', '116.426269'],
                ['United Kingdom', 'London', '51.519159', '-0.125637'],
                ['France', 'Paris', '48.855224', '2.355298'],
                ['Russia', 'Moscow', '55.764156', '37.631101'],
                ['Japan', 'Tokyo', '35.729022', '139.728920'],
                ['Israel', 'Jerusalem', '31.768282', '35.212436'],
                ['Germany', 'Berlin', '52.520551', '13.405500']
            ];

            await Excel.run(async (ctx) => {
                sheetMap = ctx.workbook.worksheets.getItemOrNullObject('Bing Maps');

                await ctx.sync();
                if (sheetMap.isNullObject) {
                    sheetMap = ctx.workbook.worksheets.add('Bing Maps');

                    sheetMap.getRange("A1:D8").values = values;
                    sheetMap.getRange("A1:D8").format.autofitColumns();
                    sheetMap.getRange("A1:D1").format.fill.color = '0070B0';
                    sheetMap.getRange("A1:D1").format.font.color = 'FFFFFF';
                    sheetMap.getRange("A1:D1").format.font.bold = true;
                }
                sheetMap.activate();

                await ctx.sync();
            })
        }

        var dictHandlers = [];

        async function registerEvents() {
            await Excel.run(async (context) => {
                var worksheetBingMaps = context.workbook.worksheets.getItem("Bing Maps");

                dictHandlers.push(worksheetBingMaps.onSelectionChanged.add(onBingMapsSelectionChanged));
                dictHandlers.push(worksheetBingMaps.onActivated.add(onWorksheetActivated));
                dictHandlers.push(worksheetBingMaps.onDeactivated.add(onWorksheetDeactivated));

                await context.sync();
                console.log("Event registered succcessfully on Bing Maps worksheet");
            });
        }

        async function unRegisterEvents() {
            for (var i = 0; i < dictHandlers.length; i++) {
                await Excel.run(dictHandlers[i].context, async function (context) {
                    dictHandlers[i].remove();
                });
            }

            console.log("All " + dictHandlers.length + " events are unregistered.");
            dictHandlers = [];
        }

        async function onBingMapsSelectionChanged(event) {
            console.log(JSON.stringify(event));
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getItem(event.worksheetId);
                var range = worksheet.getRange(event.address);
                range.load();
                await context.sync();

                if (range.columnIndex < 4) {
                    range = range.getOffsetRange(0, 0 - range.columnIndex).getAbsoluteResizedRange(1, 4);
                    range.load("values");
                    await context.sync();
                    var country = range.values[0][0];
                    var capital = range.values[0][1];
                    var latitude = range.values[0][2];
                    var longitude = range.values[0][3];
                    displayMap(country, capital, latitude, longitude);
                }
            })
        }


        function displayMap(country, capital, latitude, longitude) {
            if (map == null) resetMap();

            map.setView({
                center: new Microsoft.Maps.Location(latitude, longitude),
            });

            var center = map.getCenter();
            var infobox = new Microsoft.Maps.Infobox(center, {
                title: capital,
                description: country
            });
            infobox.setMap(map);
        }

        async function onWorksheetActivated(event) {
            console.log(JSON.stringify(event));
            $("#myMap").show();
        }

        async function onWorksheetDeactivated(event) {
            console.log(JSON.stringify(event));
            $("#myMap").hide();
        }

        /** 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);
            }
        }

    language: typescript
template:
    content: "<script type='text/javascript' src='https://www.bing.com/api/maps/mapcontrol'></script>\n\n<div class='buttonGroup'>\n\t<button class=\"ms-Button ms-Button--compound\" id=\"registerEvents\">\n        <span class=\"ms-Button-icon\">\n            <i class=\"ms-Icon ms-Icon--plus\"></i>\n        </span>\n        <span class=\"ms-Button-label\">Register</span>\n    </button>\n\n\t<button class=\"ms-Button ms-Button--compound\" id=\"unRegisterEvents\">\n        <span class=\"ms-Button-icon\">\n            <i class=\"ms-Icon ms-Icon--plus\"></i>\n        </span>\n        <span class=\"ms-Button-label\">UnRegister</span>\n    </button>\n</div>\n\n<div id='myMap' style='width: 100vw; height: 90vh;'></div>"
    language: html
style:
    content: |
        .buttonGroup {
            height: 90px;
        }

        .buttonGroup .ms-Button {
            float: left;
            margin: 10px;
        }
    language: css
libraries: |
    https://appsforoffice.microsoft.com/lib/1/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