ChartCollection Object (JavaScript API for Excel)

A collection of all the chart objects on a worksheet.

Properties

Property Type Description Req. Set
count int Returns the number of charts in the worksheet. Read-only. 1.1
items Chart[] A collection of chart objects. Read-only. 1.1

See property access examples.

Relationships

None

Methods

Method Return Type Description Req. Set
add(type: string, sourceData: Range, seriesBy: string) Chart Creates a new chart. 1.1
getCount() int Returns the number of charts in the worksheet. 1.4
getItem(name: string) Chart Gets a chart using its name. If there are multiple charts with the same name, the first one will be returned. 1.1
getItemAt(index: number) Chart Gets a chart based on its position in the collection. 1.1
getItemOrNullObject(name: string) Chart Gets a chart using its name. If there are multiple charts with the same name, the first one will be returned. 1.4

Method Details

add(type: string, sourceData: Range, seriesBy: string)

Creates a new chart.

Syntax

chartCollectionObject.add(type, sourceData, seriesBy);

Parameters

Parameter Type Description
type string Represents the type of a chart. See below for possible values.
sourceData Range The Range object corresponding to the source data.
seriesBy string Optional. Specifies the way columns or rows are used as data series on the chart. Possible values are: Auto, Scalar, Matrix
Possible chart types

The following are the valid chart types:

ColumnClustered, ColumnStacked, ColumnStacked100, _3DColumnClustered, _3DColumnStacked, _3DColumnStacked100, BarClustered, BarStacked, BarStacked100, _3DBarClustered, _3DBarStacked, _3DBarStacked100, LineStacked, LineStacked100, LineMarkers, LineMarkersStacked, LineMarkersStacked100, PieOfPie, PieExploded, _3DPieExploded, BarOfPie, XYScatterSmooth, XYScatterSmoothNoMarkers, XYScatterLines, XYScatterLinesNoMarkers, AreaStacked, AreaStacked100, _3DAreaStacked, _3DAreaStacked100, DoughnutExploded, RadarMarkers, RadarFilled, Surface, SurfaceWireframe, SurfaceTopView, SurfaceTopViewWireframe, Bubble, Bubble3DEffect, StockHLC, StockOHLC, StockVHLC, StockVOHLC, CylinderColClustered, CylinderColStacked, CylinderColStacked100, CylinderBarClustered, CylinderBarStacked, CylinderBarStacked100, CylinderCol, ConeColClustered, ConeColStacked, ConeColStacked100, ConeBarClustered, ConeBarStacked, ConeBarStacked100, ConeCol, PyramidColClustered, PyramidColStacked, PyramidColStacked100, PyramidBarClustered, PyramidBarStacked, PyramidBarStacked100, PyramidCol, _3DColumn, Line, _3DLine, _3DPie, Pie, XYScatter, _3DArea, Area, Doughnut, Radar

Returns

Chart

Examples

Add a chart of chartType "ColumnClustered" on worksheet "Charts" with sourceData from Range "A1:B4" and seriresBy is set to be "auto".

Excel.run(function (ctx) { 
    var rangeSelection = "A1:B4";
    var range = ctx.workbook.worksheets.getItem(sheetName)
        .getRange(rangeSelection);
    var chart = ctx.workbook.worksheets.getItem(sheetName)
        .charts.add("ColumnClustered", range, "auto");  return ctx.sync().then(function() {
            console.log("New Chart Added");
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getCount()

Returns the number of charts in the worksheet.

Syntax

chartCollectionObject.getCount();

Parameters

None

Returns

int

getItem(name: string)

Gets a chart using its name. If there are multiple charts with the same name, the first one will be returned.

Syntax

chartCollectionObject.getItem(name);

Parameters

Parameter Type Description
name string Name of the chart to be retrieved.

Returns

Chart

Examples

Excel.run(function (ctx) { 
    var chartname = 'Chart1';
    var chart = ctx.workbook.worksheets.getItem("Sheet1").charts.getItem(chartname);
    return ctx.sync().then(function() {
            console.log(chart.height);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Examples

Excel.run(function (ctx) { 
    var chartId = 'SamplChartId';
    var chart = ctx.workbook.worksheets.getItem("Sheet1").charts.getItem(chartId);
    return ctx.sync().then(function() {
            console.log(chart.height);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Examples

Excel.run(function (ctx) { 
    var lastPosition = ctx.workbook.worksheets.getItem("Sheet1").charts.count - 1;
    var chart = ctx.workbook.worksheets.getItem("Sheet1").charts.getItemAt(lastPosition);
    return ctx.sync().then(function() {
            console.log(chart.name);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getItemAt(index: number)

Gets a chart based on its position in the collection.

Syntax

chartCollectionObject.getItemAt(index);

Parameters

Parameter Type Description
index number Index value of the object to be retrieved. Zero-indexed.

Returns

Chart

Examples

Excel.run(function (ctx) { 
    var lastPosition = ctx.workbook.worksheets.getItem("Sheet1").charts.count - 1;
    var chart = ctx.workbook.worksheets.getItem("Sheet1").charts.getItemAt(lastPosition);
    return ctx.sync().then(function() {
            console.log(chart.name);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getItemOrNullObject(name: string)

Gets a chart using its name. If there are multiple charts with the same name, the first one will be returned.

Syntax

chartCollectionObject.getItemOrNullObject(name);

Parameters

Parameter Type Description
name string Name of the chart to be retrieved.

Returns

Chart

Property access examples

Excel.run(function (ctx) { 
    var charts = ctx.workbook.worksheets.getItem("Sheet1").charts;
    charts.load('items');
    return ctx.sync().then(function() {
        for (var i = 0; i < charts.items.length; i++)
        {
            console.log(charts.items[i].name);
        }
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Get the number of charts

Excel.run(function (ctx) { 
    var charts = ctx.workbook.worksheets.getItem("Sheet1").charts;
    charts.load('count');
    return ctx.sync().then(function() {
        console.log("charts: Count= " + charts.count);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});
} });
rror: " + error); if (error instanceof OfficeExtension.Error) { console.log("Debug info: " + JSON.stringify(error.debugInfo)); } });

Get the number of charts

Excel.run(function (ctx) { 
    var charts = ctx.workbook.worksheets.getItem("Sheet1").charts;
    charts.load('count');
    return ctx.sync().then(function() {
        console.log("charts: Count= " + charts.count);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});