Snippets - Charts

link - docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-charts


Charts - Read all charts on active worksheet

Excel.run(function (context) { 
    var _charts = context.workbook.worksheets.getActiveWorksheet().charts.load("name");
    return context.sync().then(function () {
        for (var i = 0; i < _charts.items.length; i++) {
          console.log(_charts.items[i].name);
        }
        console.log("done");
    });
}).catch(function (error) {
   console.log(error);
});

Charts - Add Data

Excel.run(function (context) { 
    var _sheet = context.workbook.worksheets.getActiveWorksheet();
    var _rangeCategories = _sheet.getRange("A2:A5");
    _rangeCategories.values = [
                    [ "Category 1" ],
                    [ "Category 2" ],
                    [ "Category 3" ],
                    [ "Category 4" ]
    ];
    var _rangeSeries = _sheet.getRange("B1:D1");
    _rangeSeries.values = [
        [ "Series 1", "Series 2", "Series 3", ]
    ];
    var _rangeData = sheet.getRange("B2:D5");
    _rangeData.formulas = "=RAND()*17";
    _rangeData.numberFormat = "#0";
    return context.sync();
}).catch(function (error) {
   console.log(error);
});

Excel.run(function (context) { 
   context.workbook.worksheets.getItem("Sheet1").charts.add("ColumnClustered", "Sheet1!A1:D5", Excel.ChartSeriesBy.auto);
}).catch(function (error) {
   console.log(error);
});

Charts - Create New

Excel.run(function (context) { 
   var _range = context.workbook.names.getItem("MyChartData").getRange();
   var _chart = context.workbook.worksheets.getItem("Sheet1").charts.add(Excel.ChartType.pie, _range, Excel.ChartSeriesBy.auto);
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - Rename

Excel.run(function (context) { 
   context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0).name = "Chart1";
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - add axis title

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.axes.valueAxis.title.text = "Category";
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - show data labels

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getItem("Sheet1").charts.getItemAt(0);
   _chart.datalabels.visible = true;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - show data labels series name

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.dataLabels.showSeriesName = true;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - change data labels position

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.dataLabels.position = Excel.ChartDataLabelPosition.top;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - get chart by name

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItem("Chart1");
   _chart.load("name");
   return context.sync().then(function () {
      console.log(_chart.name);
   });
}).catch(function (error) {
   console.log(error);
});

Charts - get chart title

Excel.run(function (context) { 
   var _charttitle = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0).title.load("text");
   return context.sync().then(function() {
      console.log(_charttitle.text);
   });
}).catch(function (error) {
   console.log(error);
});

Charts - change chart background color

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.format.fill.setSolidColor("#FF0000");
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - change series border color

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet.charts.getItemAt(0);
   _chart.series.getItemAt(0).lineFormat.color = "#FF0000";
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - change series fill color

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.series.getItemAt(0).format.fill.setSolidColor("#FF0000");
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - configure axis

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.axes.valueAxis.maximum = 5;
   _chart.axes.valueAxis.minimum = 0;
   _chart.axes.valueAxis.majorUnit = 1;
   _chart.axes.valueAxis.minorUnit = 0.2;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - format chart title

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.title.format.font.bold = true;
   _chart.title.format.font.color = "#FF0000";
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - create and format chart

Excel.run(function (context) { 
    var _sheet = context.workbook.worksheets.getItem("Sheet1");
    var _range = _sheet.getRange("A1:B3");
    _range.values = [
                    ["", "Gender"],
                    ["Male", 12],
                    ["Female", 14]
    ];
    var _chart = sheet.charts.add("pie", _range, "auto");
    _chart.format.fill.setSolidColor("F8F8FF");
    _chart.title.text = "Class Demographics";
    _chart.title.format.font.bold = true;
    _chart.title.format.font.size = 18;
    _chart.title.format.font.color = "568568";
    _chart.legend.position = "right";
    _chart.legend.format.font.name = "Algerian";
    _chart.legend.format.font.size = 13;
    _chart.dataLabels.showPercentage = true;
    _chart.dataLabels.format.font.size = 15;
    _chart.dataLabels.format.font.color = "444444";
    var _points = _chart.series.getItemAt(0).points;
    _points.getItemAt(0).format.fill.setSolidColor("8FBC8F");
    _points.getItemAt(1).format.fill.setSolidColor("D87093");
    return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - resize chart

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getItem("Sheet1").charts.getItemAt(0);
   _chart.height = 200;
   _chart.width = 200;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - set chart position

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.top = 200;
   _chart.left = 200;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - set seriesby

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getItem("Sheet1").charts.getItemAt(0);
   _chart.setData("Sheet1!A1:B4", Excel.ChartSeriesBy.rows);
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - show legend

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.legend.visible = true;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - show majorgridlines

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.axes.valueAxis.majorGridlines.visible = true;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - set chart data source

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getItem("Sheet1").charts.getItemAt(0);
   _chart.setData("Sheet1!A1:B4", Excel.ChartSeriesBy.columns);
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - hide axis title

Excel.run(function (context) { 
   var _chart = context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0);
   _chart.axes.valueAxis.title.visible = false;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - delete chart

Excel.run(function (context) { 
   context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0).delete();
   return context.sync();
}).catch(function (error) {
   console.log(error);
});

Charts - hide chart title

Excel.run(function (context) { 
   context.workbook.worksheets.getActiveWorksheet().charts.getItemAt(0).title.visible = false;
   return context.sync();
}).catch(function (error) {
   console.log(error);
});



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext