Shared with Script Lab

Chart 1.8 API + Report .yaml

name: 'Chart 1.8 API + Report '
description: ''
author: JuaneloJuanelo
host: EXCEL
api_set: {}
script:
    content: |
        $("#addSampleData").click(() => tryCatch(addSampleData));
        $("#CreateLineChart").click(() => tryCatch(CreateLineChart));
        $("#SplitCharts").click(() => tryCatch(SplitCharts));
        $("#SyncAxis").click(() => tryCatch(SyncAxis));
        $("#FormatAxis").click(() => tryCatch(FormatAxis));
        $("#SecondaryAxis").click(() => tryCatch(SecondaryAxis));
        $("#SetFormat").click(() => tryCatch(SetFormat));
        $("#InvertColor").click(() => tryCatch(InvertColor));
        $("#AddXYChart").click(() => tryCatch(AddXYChart));
        $("#ChangeDataLabels").click(() => tryCatch(ChangeDataLabels));
        $("#CleanCharts").click(() => tryCatch(CleanCharts));

        $("#report").click(() => tryCatch(createReport));

        var startHeight = 550;
        var orange = "#FF7C00";
        var blue = "#0000FF";
        var greyGreen = "#E4E1D2";
        var green = "#70ad47";
        var red = "#FF0000";

        async function CreateLineChart() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();
                var chart = worksheet.charts.add(Excel.ChartType.line, worksheet.getRange("A3:C35"));
                chart.name = "FirstLineChart";
                chart.top = startHeight;
                chart.left = 10;

                var legend = chart.legend;
                legend.visible = true;
                legend.position = Excel.ChartLegendPosition.bottom;
            });
        }

        async function SplitCharts() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();
                worksheet.charts.getItem("FirstLineChart").delete();
                var chartLemon = worksheet.charts.add(Excel.ChartType.line, worksheet.getRange("A3:B35"));
                chartLemon.name = "chartLemon";
                chartLemon.top = startHeight;
                chartLemon.left = 10;

                chartLemon.load("height,width");
                await context.sync();

                var chartOrange = worksheet.charts.add(Excel.ChartType.line, worksheet.getRange("C3:C35"));
                chartOrange.series.getItemAt(0).setXAxisValues(worksheet.getRange("A4:A35"));
                chartOrange.name = "chartOrange";
                chartOrange.series.getItemAt(0).format.line.color = orange;
                chartOrange.top = chartLemon.top + chartLemon.height + 10;
                chartOrange.left = 10;

                var legend = chartLemon.legend;
                legend.visible = true;
                legend.position = Excel.ChartLegendPosition.bottom;

                legend = chartOrange.legend;
                legend.visible = true;
                legend.position = Excel.ChartLegendPosition.bottom;
            });
        }

        async function SyncAxis() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();
                for (var i = 0; i <= 1; i++) {
                    var chart = worksheet.charts.getItemAt(i);
                    chart.axes.valueAxis.maximum = 35000;
                }
            });
        }

        async function FormatAxis() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();
                for (var i = 0; i <= 1; i++){
                    var chart = worksheet.charts.getItemAt(i);
                    var categoryAxis = chart.axes.categoryAxis;
                    categoryAxis.majorTimeUnitScale = Excel.ChartAxisTimeUnit.days;
                    categoryAxis.majorUnit = 7;
                    categoryAxis.numberFormat = "m/d;@";

                    chart.axes.valueAxis.numberFormat = "$ #, K";
                }
               
            });
        }

        async function SecondaryAxis() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();

                var chart = worksheet.charts.getItemAt(0);

                var series = chart.series.add("Weekend", 2);
                series.chartType = Excel.ChartType.area;
                series.setValues(worksheet.getRange("J4:J35"));

                //Set Series.AxisGroup to Secondary
                series.axisGroup = "Secondary";
                var axis = chart.axes.getItem("Value", "Secondary");
                axis.maximum = 1;
                axis.tickLabelPosition = "None";
                chart.title.text = "Lemon";

                chart = worksheet.charts.getItemAt(1);
                series.format.fill.setSolidColor(orange);
                

                series = chart.series.add("Weekend", 2);
                series.chartType = Excel.ChartType.area;
                series.setValues(worksheet.getRange("J4:J35"));

                //Set Series.AxisGroup to Secondary
                series.axisGroup = "Secondary";
                axis = chart.axes.getItem("Value", "Secondary");
                axis.maximum = 1;
                axis.tickLabelPosition = "None";

                series.format.fill.setSolidColor(blue);
                chart.title.text = "Orange";

            });
        }

        async function SetFormat() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();
                for (var i = 0; i <= 1; i++) {
                    var chart = worksheet.charts.getItemAt(i);
                    chart.title.top = 5;
                    chart.title.left = 10;
                    chart.legend.top = 30;
                    chart.legend.left = 10;
                    chart.legend.overlay = false;

                    chart.plotArea.top = 54;
                    chart.plotArea.height = 142;
                    
                    //Set PlotArea.Top/Height
                    chart.series.getItemAt(1).format.fill.setSolidColor(greyGreen);
                    chart.axes.valueAxis.majorGridlines.visible = false;
                    // chart.plotArea.load();
                    // await context.sync();
                    // console.log(chart.plotArea.top);
                    // console.log(chart.plotArea.height);
                }

            });
        }

        async function InvertColor() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();
                var chartLemon = worksheet.charts.add(Excel.ChartType.columnClustered, worksheet.getRange("L3:L35"));
                chartLemon.series.getItemAt(0).setXAxisValues(worksheet.getRange("A4:A35"));
                chartLemon.name = "chartLemonColumn";
                var chartOrange = worksheet.charts.add(Excel.ChartType.columnClustered, worksheet.getRange("M3:M35"));
                chartOrange.series.getItemAt(0).setXAxisValues(worksheet.getRange("A4:A35"));
                chartOrange.name = "chartOrangeColumn";

                chartLemon.axes.valueAxis.majorGridlines.visible = false;
                chartOrange.axes.valueAxis.majorGridlines.visible = false;

                chartLemon.load("height,width");
                await context.sync();

                chartLemon.top = startHeight;
                chartLemon.left = 10 + chartLemon.width + 20;
                chartOrange.top = chartLemon.top + chartLemon.height + 10;
                chartOrange.left = chartLemon.left;

                chartOrange.axes.valueAxis.maximum = 15000;
                chartOrange.axes.valueAxis.minimum = -25000;

                
                chartOrange.axes.categoryAxis.tickLabelPosition = Excel.ChartAxisTickLabelPosition.low;
                chartLemon.axes.categoryAxis.tickLabelPosition = Excel.ChartAxisTickLabelPosition.low;

                chartLemon.axes.valueAxis.numberFormat = "$ #, K";
                chartLemon.axes.categoryAxis.numberFormat = "m/d;@";
                chartOrange.axes.valueAxis.numberFormat = "$ #, K";
                chartOrange.axes.categoryAxis.numberFormat = "m/d;@";
                chartLemon.legend.visible = false;
                chartOrange.legend.visible = false;

                var point = chartOrange.series.getItemAt(0).points.getItemAt(11);
                point.dataLabel.showValue = true;
                point.dataLabel.showCategoryName = true;
                //TODO: Set Series.InvertColor/InvertIfNegative 
                chartLemon.series.getItemAt(0).invertIfNegative = true;
                chartOrange.series.getItemAt(0).invertIfNegative = true;
                // await ChangeColor(context, chartLemon);
                // await ChangeColor(context, chartOrange);
            });
        }

        async function ChangeColor(context: Excel.RequestContext, chartObject) {
            var points = chartObject.series.getItemAt(0).points;
            points.load("count");
            await context.sync();
            var pointCount = points.count;
            for (var i = 0; i < pointCount; i++) {
                var point = points.getItemAt(i);
                point.load("value");
                await context.sync();
                if (point.value > 0) {
                    point.format.fill.setSolidColor(green);
                }
                else {
                    point.format.fill.setSolidColor(red);
                }
            }
        }

        async function AddXYChart() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();
                var chartxy = worksheet.charts.add(Excel.ChartType.xyscatter, worksheet.getRange("B3:B35"));
                chartxy.series.getItemAt(0).setXAxisValues(worksheet.getRange("B4:B35"));
                chartxy.series.getItemAt(0).setValues(worksheet.getRange("D4:D35"));
                chartxy.series.add("Orange", 1);
                chartxy.series.getItemAt(1).setXAxisValues(worksheet.getRange("C4:C35"));
                chartxy.series.getItemAt(1).setValues(worksheet.getRange("D4:D35"));
                chartxy.name = ("ScatterChart");

                chartxy.load("height,width");
                await context.sync();

                chartxy.top = startHeight + 2 * (chartxy.height + 10);
                chartxy.left = 10;

                chartxy.dataLabels.showValue = true;
                chartxy.title.visible = false;
            });
        }

        async function ChangeDataLabels() {
            await Excel.run(async (context) => {
                var worksheet = context.workbook.worksheets.getActiveWorksheet();
                var chartxy = worksheet.charts.getItem("ScatterChart");
                chartxy.axes.categoryAxis.majorGridlines.visible = false;
                chartxy.axes.valueAxis.majorGridlines.visible = false;
                chartxy.axes.categoryAxis.majorGridlines.visible = true;
                chartxy.axes.valueAxis.majorGridlines.visible = true;
                // chartxy.axes.valueAxis.minimum = 60;
                chartxy.legend.visible = false;
                chartxy.dataLabels.showValue = false;
                //TODO: DataLabel value from cells
                for (var i = 0; i <= 1; i++){
                    var series = chartxy.series.getItemAt(i);
                    
                    series.setValues(worksheet.getRange("E4:E35"));

                    var points = series.points;
                    points.load("count");
                    await context.sync();

                    for (var j = 0; j < points.count; j++) {
                        var point = points.getItemAt(j);
                        //console.log(i + " " + j);
                        var rowIndex = j + 4;
                        var cell = worksheet.getCell(rowIndex, 7);
                        cell.load("values");
                        await context.sync();
                        //console.log(cell.values.toString());
                        if (cell.values.toString() == "Hot") {
                            //console.log(cell.values)
                            point.dataLabel.text = "Hot";
                            //point.dataLabel.showCategoryName = true;
                        }
                        //point.dataLabel.formula = "=Sales!$H$" + rowIndex;
                    }
                }
                console.log("done");
            });
        }

        async function addSampleData() {
            try {
                await Excel.run(async (context) => {
                    await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sales");
                    let sheet = context.workbook.worksheets.getItem("Sales");
                    let expensesTable = sheet.tables.add('A3:N3', true);
                    expensesTable.name = "SalesTable";
                    expensesTable.getHeaderRowRange().values = [["Date", "Lemon", "Orange", "Temperature", "Leaflets", "Price", "Weekday", "Hot Day", "Total Sale", "Weekend Sale", "Hot Day Sale", "Sales Change Lemon", "Sales Change Orange", "Weeknumber"]];
                    expensesTable.rows.add(null, [
                        ["7/1/2016", 9409, 4489, 70, 90, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/2/2016", 9604, 4489, 72, 90, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/3/2016", 12100, 5929, 71, 104, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/4/2016", 17956, 9801, 76, 98, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/5/2016", 25281, 13924, 78, 135, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/6/2016", 10609, 4761, 82, 90, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/6/2016", 10609, 4761, 82, 90, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/7/2016", 20449, 10201, 81, 135, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/8/2016", 15129, 7396, 82, 113, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/9/2016", 17956, 9025, 80, 126, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/10/2016", 19600, 9604, 82, 131, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/11/2016", 26244, 14400, 83, 135, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/12/2016", 16900, 9025, 84, 99, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/13/2016", 11881, 5625, 77, 99, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/14/2016", 14884, 7225, 78, 113, 0.25, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/15/2016", 9604, 3844, 75, 108, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/16/2016", 6561, 2500, 74, 90, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/17/2016", 13225, 5776, 77, 126, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/18/2016", 17161, 8464, 81, 122, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/19/2016", 14884, 7225, 78, 113, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/20/2016", 5041, 1764, 70, 120, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/21/2016", 6889, 2500, 77, 90, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/22/2016", 12544, 5625, 80, 108, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/23/2016", 14400, 6724, 81, 117, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/24/2016", 14641, 6724, 82, 117, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/25/2016", 24336, 12769, 84, 135, 0.5, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/26/2016", 30976, 16641, 83, 158, 0.35, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/27/2016", 10816, 4624, 80, 99, 0.35, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/28/2016", 9216, 3969, 82, 90, 0.35, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/29/2016", 10000, 4356, 81, 95, 0.35, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/30/2016", 7744, 3249, 82, 81, 0.35, 0, 0, 0, 0, 0, 0, 0, 0],
                        ["7/31/2016", 5776, 2209, 82, 68, 0.35, 0, 0, 0, 0, 0, 0, 0, 0]
                    ]);

                    let range = sheet.getRange("P4:Q5");
                    range.values = [["Temperature Data", ""],["Hot Day Limit", 82]];

                    let weekdayRange = sheet.getRange("G4:G35");
                    let data = [];
                    for (let i = 4; i < 36; i++) {
                        let item = [];
                        item.push('=IF(WEEKDAY([@Date],2)>5,"Weekend","Weekday")');
                        data.push(item);
                    }
                    weekdayRange.formulas = data;
                    //weekdayRange.format.autofitColumns();

                    let hotDayRange = sheet.getRange("H4:H35");
                    let data2 = [];
                    for (let i = 4; i < 36; i++) {
                        let item = [];
                        item.push('=IF([@Temperature]>$Q$5,"Hot","")');
                        data2.push(item);
                    }
                    hotDayRange.formulas = data2;
                    //hotDayRange.format.autofitColumns();

                    let totalSaleRange = sheet.getRange("I4:I35");
                    let data3 = [];
                    for (let i = 4; i < 36; i++) {
                        let item = [];
                        item.push('=[@Lemon]+[@Orange]');
                        data3.push(item);
                    }
                    totalSaleRange.formulas = data3;
                    //totalSaleRange.format.autofitColumns();

                    let weekendSaleRange = sheet.getRange("J4:J35");
                    let data4 = [];
                    for (let i = 4; i < 36; i++) {
                        let item = [];
                        item.push('=IF(WEEKDAY([@Date],2)>5,1,NA())');
                        data4.push(item);
                    }
                    weekendSaleRange.formulas = data4;
                    //weekendSaleRange.format.autofitColumns();

                    let hotDaySaleRange = sheet.getRange("K4:K35");
                    let data5 = [];
                    for (let i = 4; i < 36; i++) {
                        let item = [];
                        item.push('=IF([@[Hot Day]]="Normal",NA(),1)');
                        data5.push(item);
                    }
                    hotDaySaleRange.formulas = data5;
                    //hotDaySaleRange.format.autofitColumns();

                    let salesChangeLemonRange = sheet.getRange("L5:L35");
                    let data6 = [];
                    for (let i = 5; i < 36; i++) {
                        let item = [];
                        item.push('=[@Lemon]-OFFSET([@Lemon],-1,0)');
                        data6.push(item);
                    }
                    salesChangeLemonRange.formulas = data6;
                    //salesChangeLemonRange.format.autofitColumns();

                    let salesChangeOrangeRange = sheet.getRange("M5:M35");
                    let data7 = [];
                    for (let i = 5; i < 36; i++) {
                        let item = [];
                        item.push('=[@Orange]-OFFSET([@Orange],-1,0)');
                        data7.push(item);
                    }
                    salesChangeOrangeRange.formulas = data7;
                    //salesChangeOrangeRange.format.autofitColumns();

                    let weekNumberRange = sheet.getRange("N4:N35");
                    let data8 = [];
                    for (let i = 4; i < 36; i++) {
                        let item = [];
                        item.push('=WEEKNUM([@Date])');
                        data8.push(item);
                    }
                    weekNumberRange.formulas = data8;
                    weekNumberRange.format.autofitColumns();

                    let numRange1 = sheet.getRange("B4:B35");
                    let numRange2 = sheet.getRange("C4:C35");
                    let numRange3 = sheet.getRange("I4:I35");
                    let numRange4 = sheet.getRange("L4:L35");
                    let numRange5 = sheet.getRange("M4:M35");
                    let formatnumber = [];
                    let formatnumberitem = ["###,0"];
                    for (let i = 4; i < 36; i++) {
                        formatnumber.push(formatnumberitem);
                    }
                    numRange1.numberFormat = formatnumber;
                    numRange2.numberFormat = formatnumber;
                    numRange3.numberFormat = formatnumber;
                    numRange4.numberFormat = formatnumber;
                    numRange5.numberFormat = formatnumber;

                    sheet.activate();
                    //sheet.gridlines = false;

                    if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
                        sheet.getUsedRange().format.autofitColumns();
                        sheet.getUsedRange().format.autofitRows();
                    }
                    //sheet.onDataChanged.add(onDataChanged);
                    await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function CleanCharts() {
            await Excel.run(async (context) => {
                var charts = context.workbook.worksheets.getActiveWorksheet().charts;

                charts.load("count");
                await context.sync();

                var chartCount = charts.count;
                for (var i = 0; i < chartCount; i++){
                    charts.getItemAt(0).delete();
                }
            });
        }

        async function createReport() {
            console.log("entered here");
            await Excel.run(async (context) => {


                var pageLayout = context.workbook.worksheets.getActiveWorksheet().pageLayout;


                pageLayout.headersFooters.defaultForAllPages.centerHeader = "&B&36 My Little Shop Sales Report";

                pageLayout.headersFooters.defaultForAllPages.rightFooter = "&B&36&P";

                pageLayout.setPrintArea("A1:N90");


                context.workbook.worksheets.getActiveWorksheet().horizontalPageBreaks.add("A37");
                context.workbook.worksheets.getActiveWorksheet().horizontalPageBreaks.add("A71");
                pageLayout.paperSize = "Legal";
                pageLayout.orientation = "Landscape"
                pageLayout.centerHorizontally = true;
              

                pageLayout.printGridlines = false;

                pageLayout.setPrintMargins(Excel.PrintMarginUnit.inches, { left: 1, right: 1 });



                pageLayout.zoom = { scale: 75 };

                pageLayout.headersFooters.useSheetScale = true;
                

                await context.sync();
                console.log("Report REady");


            });
        }



        /** 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: "<button id=\"addSampleData\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\"> Add Sample Data</span>\n</button>\n<h3>\n    Sales Chart\n</h3>\n\n<button id=\"CreateLineChart\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\"> 1. Create Line Chart</span>\n</button>\n<p></p>\n<button id=\"SplitCharts\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">2. Split Charts</span>\n</button>\n<p></p>\n<button id=\"SyncAxis\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">3. Sync Axis </span>\n</button>\n<p></p>\n<button id=\"FormatAxis\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">4. Format Axis </span>\n</button>\n<p></p>\n<button id=\"SecondaryAxis\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">5. Secondary Axis </span>\n</button>\n<p></p>\n<button id=\"SetFormat\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">6. Set Format</span>\n</button>\n<h3>\n\tOther Analysis\n</h3>\n<button id=\"InvertColor\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">Sales Change</span>\n</button>\n<p></p>\n<button id=\"AddXYChart\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">Sales Base on Tempreture</span>\n</button>\n<p></p>\n<button id=\"ChangeDataLabels\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">Sales Base on Leaflets</span>\n</button>\n<h3>\n\tClear Charts\n</h3>\n<button id=\"CleanCharts\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">Clear Charts</span>\n</button>\n\n<h3>\n\tPrepare Report\n</h3>\n<button id=\"report\" class=\"ms-Button\">\n    <span class=\"ms-Button-label\">Prepare Report</span>\n</button>\n"
    language: html
style:
    content: |
        /* Your style goes here */
    language: css
libraries: |
    https://appsforoffice.microsoft.com/lib/beta/hosted/office.js
    https://appsforoffice.microsoft.com/lib/beta/hosted/office.d.ts


    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

    @microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js
    @microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts

    jquery@3.1.1
    @types/jquery