Skip to content

Latest commit

 

History

History
262 lines (212 loc) · 10.8 KB

chart.md

File metadata and controls

262 lines (212 loc) · 10.8 KB

Chart Object (JavaScript API for Excel)

Represents a chart object in a workbook.

Properties

Property Type Description Req. Set
categoryLabelLevel int Returns or sets a ChartCategoryLabelLevel enumeration constant referring to 1.8
chartType string Represents the type of the chart. Possible values are: ColumnClustered, ColumnStacked, ColumnStacked100, BarClustered, BarStacked, BarStacked100, LineStacked, LineStacked100, LineMarkers, LineMarkersStacked, LineMarkersStacked100, PieOfPie, etc.. 1.7
height double Represents the height, in points, of the chart object. 1.1
id string The unique id of chart. Read-only. 1.7
left double The distance, in points, from the left side of the chart to the worksheet origin. 1.1
name string Represents the name of a chart object. 1.1
plotVisibleOnly bool True if only visible cells are plotted. False if both visible and hidden cells are plotted. ReadWrite. 1.8
seriesNameLevel int Returns or sets a ChartSeriesNameLevel enumeration constant referring to 1.8
showAllFieldButtons bool Represents whether to display all field buttons on a PivotChart. 1.7
showDataLabelsOverMaximum bool Represents whether to to show the data labels when the value is greater than the maximum value on the value axis. 1.8
style int Returns or sets the chart style for the chart. ReadWrite. 1.8
top double Represents the distance, in points, from the top edge of the object to the top of row 1 (on a worksheet) or the top of the chart area (on a chart). 1.1
width double Represents the width, in points, of the chart object. 1.1

See property access examples.

Relationships

Relationship Type Description Req. Set
axes ChartAxes Represents chart axes. Read-only. 1.1
dataLabels ChartDataLabels Represents the datalabels on the chart. Read-only. 1.1
displayBlanksAs ChartDisplayBlanksAs Returns or sets the way that blank cells are plotted on a chart. ReadWrite. 1.8
format ChartAreaFormat Encapsulates the format properties for the chart area. Read-only. 1.1
legend ChartLegend Represents the legend for the chart. Read-only. 1.1
pivotOptions ChartPivotOptions Encapsulates the options for the pivot chart. Read-only. beta
plotArea ChartPlotArea Represents the plotArea for the chart. Read-only. 1.8
plotBy ChartPlotBy Returns or sets the way columns or rows are used as data series on the chart. ReadWrite. 1.8
series ChartSeriesCollection Represents either a single series or collection of series in the chart. Read-only. 1.1
title ChartTitle Represents the title of the specified chart, including the text, visibility, position, and formating of the title. Read-only. 1.1
worksheet Worksheet The worksheet containing the current chart. Read-only. 1.2

Methods

Method Return Type Description Req. Set
activate() void Activate the chart in the Excel UI. beta
delete() void Deletes the chart object. 1.1
getImage(height: number, width: number, fittingMode: string) System.IO.Stream Renders the chart as a base64-encoded image by scaling the chart to fit the specified dimensions. 1.2
setData(sourceData: Range, seriesBy: string) void Resets the source data for the chart. 1.1
setPosition(startCell: Range or string, endCell: Range or string) void Positions the chart relative to cells on the worksheet. 1.1

Method Details

activate()

Activate the chart in the Excel UI.

Syntax

chartObject.activate();

Parameters

None

Returns

void

delete()

Deletes the chart object.

Syntax

chartObject.delete();

Parameters

None

Returns

void

Examples

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

getImage(height: number, width: number, fittingMode: string)

Renders the chart as a base64-encoded image by scaling the chart to fit the specified dimensions.

Syntax

chartObject.getImage(height, width, fittingMode);

Parameters

Parameter Type Description
height number Optional. (Optional) The desired height of the resulting image.
width number Optional. (Optional) The desired width of the resulting image.
fittingMode string Optional. (Optional) The method used to scale the chart to the specified to the specified dimensions (if both height and width are set). Possible values are: Fit, FitAndCenter, Fill

Returns

System.IO.Stream

Examples

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

setData(sourceData: Range, seriesBy: string)

Resets the source data for the chart.

Syntax

chartObject.setData(sourceData, seriesBy);

Parameters

Parameter Type Description
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. Can be one of the following: Auto (default), Rows, and Columns. Possible values are: Auto, Columns, Rows

Returns

void

Examples

Set the sourceData to be "A1:B4" and seriesBy to be "Columns"

Excel.run(function (ctx) { 
	var chart = ctx.workbook.worksheets.getItem("Sheet1").charts.getItem("Chart1");	
	var sourceData = "A1:B4";
	chart.setData(sourceData, "Columns");
	return ctx.sync(); 
}).catch(function(error) {
		console.log("Error: " + error);
		if (error instanceof OfficeExtension.Error) {
			console.log("Debug info: " + JSON.stringify(error.debugInfo));
		}
});

setPosition(startCell: Range or string, endCell: Range or string)

Positions the chart relative to cells on the worksheet.

Syntax

chartObject.setPosition(startCell, endCell);

Parameters

Parameter Type Description
startCell Range or string The start cell. This is where the chart will be moved to. The start cell is the top-left or top-right cell, depending on the user's right-to-left display settings.
endCell Range or string Optional. (Optional) The end cell. If specified, the chart's width and height will be set to fully cover up this cell/range.

Returns

void

Examples

Excel.run(function (ctx) { 
	var sheetName = "Charts";
	var rangeSelection = "A1:B4";
	var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeSelection);
	var sourceData = sheetName + "!" + "A1:B4";
	var chart = ctx.workbook.worksheets.getItem(sheetName).charts.add("pie", range, "auto");
	chart.width = 500;
	chart.height = 300;
	chart.setPosition("C2", null);
	return ctx.sync(); 
}).catch(function(error) {
		console.log("Error: " + error);
		if (error instanceof OfficeExtension.Error) {
			console.log("Debug info: " + JSON.stringify(error.debugInfo));
		}
});

Property access examples

Get a chart named "Chart1"

Excel.run(function (ctx) { 
	var chart = ctx.workbook.worksheets.getItem("Sheet1").charts.getItem("Chart1");	
	chart.load('name');
	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));
		}
});

Update a chart including renaming, positioning and resizing.

Excel.run(function (ctx) { 
	var chart = ctx.workbook.worksheets.getItem("Sheet1").charts.getItem("Chart1");	
	chart.name="New Name";
	chart.top = 100;
	chart.left = 100;
	chart.height = 200;
	chart.width = 200;
	return ctx.sync(); 
}).catch(function(error) {
		console.log("Error: " + error);
		if (error instanceof OfficeExtension.Error) {
			console.log("Debug info: " + JSON.stringify(error.debugInfo));
		}
});

Rename the chart to new name, resize the chart to 200 points in both height and weight. Move Chart1 to 100 points to the top and left.

Excel.run(function (ctx) { 
	var chart = ctx.workbook.worksheets.getItem("Sheet1").charts.getItem("Chart1");
	chart.name="New Name";	
	chart.top = 100;
	chart.left = 100;
	chart.height =200;
	chart.width =200;
	return ctx.sync(); 
}).catch(function(error) {
		console.log("Error: " + error);
		if (error instanceof OfficeExtension.Error) {
			console.log("Debug info: " + JSON.stringify(error.debugInfo));
		}
});