XLSXChart Class Reference

The XLSXChart class allows creating an Excel chart. More...

Public Member Functions
XLSXChartSeries	addSeries (String categories="", String values="")
	Add a data series to the current chart. More...
XLSXChartAxis	getAxis (String axisType)
	Get an axis from the chart. More...
String	getLastError ()
	Function to get the description of the last error that occurred. More...
boolean	setStyle (number styleId=2)
	Set the chart style type. More...
boolean	setTable ()
	Add a data table below the horizontal axis with the data used to plot the chart. More...
boolean	setTitle (String title)
	Set the title of the chart. More...
boolean	setTitleRange (String sheetname, number row, number col)
	Set a chart title formula using row and column values. More...

Detailed Description

The XLSXChart class allows creating an Excel chart.

An XLSXChart object represents an Excel chart. It provides functions for adding data series to the chart and for configuring the chart. An XLSXChart object isn't created directly. Instead it is created by calling the XLSXWriter.addChart(number type) function from an XLSXWriter object.

The basic procedure for adding a chart to a worksheet is:

1. Create the chart with XLSXWriter.addChart().
2. Add one or more data series to the chart which refers to data in the Excel file using XLSXChart.addSeries().
3. Configure the chart with the other available functions shown below.
4. Insert the chart into a worksheet using XLSXWorksheet.insertChart().

Since

DOCUMENTS 5.0e

Example:

var writer = new XLSXWriter("c:\\tmp\\chart.xlsx");
var worksheet1 = writer.addWorksheet("Worksheet1");

// Write some data to the worksheet.
writeWorksheetData(worksheet1);

// Create a chart object.
var chart = writer.addChart(writer.CHART_COLUMN);

// Configure the chart. In simplest case we just add some value data series. 
// The empty categories will default to 1 to 6 like in Excel.
var series1 = chart.addSeries("", "=Worksheet1!$A$1:$A$6");
var series2 = chart.addSeries("", "=Worksheet1!$B$1:$B$6");
var series3 = chart.addSeries("", "=Worksheet1!$C$1:$C$6");

chart.setTable(); // Turn on the data table
chart.setStyle(37);
chart.setTitle("Chart with Data Table");

// Insert the chart into the worksheet
worksheet1.insertChart(8, 1, chart);

if (!writer.save())
    throw writer.getLastError();

function writeWorksheetData(worksheet)
{
    var data = [
        // Three columns of data.
        [1,   2,   3],
        [2,   4,   6],
        [3,   6,   9],
        [4,   8,  12],
        [5,  10,  15],
        [6,  12,  18]
    ];

    var row, col;
    for (row = 0; row < 6; row++)
        for (col = 0; col < 3; col++)
            worksheet.writeCell("number", row, col, data[row][col]);
}

See also

XLSXWriter.addChart(number type)
XLSXChart.addSeries(String categories, String values)
XLSXWorksheet.insertChart(number row, number col, XLSXChart chart, number xOffset, number yOffset, number xScale, number yScale)
XLSXChartsheet.insertChart(XLSXChart chart )

Member Function Documentation

addSeries()

XLSXChartSeries XLSXChart::addSeries	(	String	categories = "",
		String	values = ""
	)

Add a data series to the current chart.

The series numbering and order in the Excel chart will be the same as the order in which they are added with this function.

Parameters

categories	Optional range of categories in the data series being a string formula like "=Worksheet1!$A$1:$A$6". The category is more or less the same as the X axis. In most Excel chart types the categories property is optional and the chart will just assume a sequential series from 1..n.
values	Optional range of values in the data series being a string formula like "=Worksheet1!$A$1:$A$6". This parameter links the chart with the worksheet data that it displays.

Returns

An XLSXChartSeries object if successful, null in case of any error.

Since

DOCUMENTS 5.0e

Example:

// The empty categories will default to 1 to 6 like in Excel.
var series1 = chart1.addSeries("", "=Worksheet1!$A$1:$A$6");
var series2 = chart1.addSeries("", "=Worksheet1!$B$1:$B$6");

// It is also possible to specify non-contiguous ranges:
chart2.addSeries("=(Sheet1!$A$1:$A$9,Sheet1!$A$14:$A$25)", "=(Sheet1!$B$1:$B$9,Sheet1!$B$14:$B$25)");

See also

XLSXChartSeries.setCategories(String sheetname, number firstRow, number firstCol, number lastRow, number lastCol)
XLSXChartSeries.setValues(String sheetname, number firstRow, number firstCol, number lastRow, number lastCol)

getAxis()

XLSXChartAxis XLSXChart::getAxis

(

String

axisType

)

Get an axis from the chart.

Parameters

axisType

The axis type: x or y.

Returns

An XLSXChartAxis object if successful, null in case of any error.

Since

DOCUMENTS 5.0e

Example:

var xAxis = chart.getAxis("x");

getLastError()

String XLSXChart::getLastError

(

)

Function to get the description of the last error that occurred.

Returns

Text of the last error as String

Since

DOCUMENTS 5.0e

setStyle()

boolean XLSXChart::setStyle

(

number

styleId = 2

)

Set the chart style type.

This function is used to set the style of the chart to one of the 48 built-in styles available on the "Design" tab in Excel 2007.

Parameters

styleId

Optional index representing the chart style, 1 - 48. The style index number is counted from 1 on the top left in the Excel dialog. The default style is 2.

Returns

true if successful, false in case of any error

Note

In Excel 2013 the Styles section of the "Design" tab in Excel shows what were referred to as "Layouts" in previous versions of Excel. These layouts are not defined in the file format. They are a collection of modifications to the base chart type. They can not be defined by this function.

Since

DOCUMENTS 5.0e

Example:

chart.setStyle(37);

setTable()

boolean XLSXChart::setTable

(

)

Add a data table below the horizontal axis with the data used to plot the chart.

Returns

true if successful, false in case of any error

Note

The data table can only be shown with Bar, Column, Line and Area charts.

Since

DOCUMENTS 5.0e

setTitle()

boolean XLSXChart::setTitle

(

String

title

)

Set the title of the chart.

Parameters

title

The chart title displayed above the chart. This parameter can also be a formula such as "=Worksheet1!$A$1" to point to a cell in the Excel file that contains the title. The Excel default is to have no chart title.

Returns

true if successful, false in case of any error

Since

DOCUMENTS 5.0e

Example:

chart1.setTitle("Test Results");
chart2.setTitle("=Worksheet1!$B$1");

See also

XLSXChart.setTitleRange(String sheetname, number row, number col);

setTitleRange()

boolean XLSXChart::setTitleRange	(	String	sheetname,
		number	row,
		number	col
	)

Set a chart title formula using row and column values.

This function can be used to set a chart title range and is an alternative to using XLSXChart.setTitle(String title) and a string formula.

Parameters

sheetname	The name of the worksheet that contains the cell range.
row	The zero indexed row number of the range.
col	The zero indexed column number of the range.

Returns

true if successful, false in case of any error

Since

DOCUMENTS 5.0e

Example:

chart2.setTitleRange("Worksheet1", 0, 1);