This section explains how to work with some of the options and features of The Chart object.
The majority of the examples in this section are based on a variation of the following program:
When working with charts it is important to understand how Excel differentiates between a chart axis that is used for series categories and a chart axis that is used for series values.
In the majority of Excel charts the X axis is the category axis and each of the values is evenly spaced and sequential. The Y axis is the value axis and points are displayed according to their value:
Excel treats these two types of axis differently and exposes different properties for each. For example, here are the properties for a category axis:
Here are properties for a value axis:
As such, some of the
libxlsxwriter axis properties can be set for a value axis, some can be set for a category axis and some properties can be set for both. The documentation calls out the type of axis to which functions apply and the API emits a warning if the wrong type is used.
Some category axes, such as the one in the column chart example above, use numbers as the categories. However, these numbers can't be treated like value axes. In particular you can't use chart_axis_set_max() and chart_axis_set_min() for category axes, as you can see in the respective format dialogs above.
For a Bar chart the Category and Value axes are reversed:
A Scatter chart (but not a Line chart) has 2 value axes:
In Excel a chart marker is used to distinguish data points in a plotted series. In general only Line and Scatter and Radar chart types use markers. The libxlsxwriter chart types that can have markers are:
The chart types with
MARKERS in the name have markers with default colors and shapes turned on by default but it is possible to change them using the functions shown below.
chart_series_set_marker_type() function is used to specify the type of the series marker:
The available marker types defined by lxw_chart_marker_type are:
LXW_CHART_MARKER_NONE type can be used to turn off default markers:
LXW_CHART_MARKER_AUTOMATIC type is a special case which turns on a marker using the default marker style for the particular series. If automatic is on then other marker properties such as size, line or fill cannot be set.
chart_series_set_marker_size() function is used to specify the size of the series marker:
A trendline can be added to a chart series to indicate trends in the data such as a moving average or a polynomial fit. The trendlines types are shown in the following Excel dialog:
chart_series_set_trendline() function turns on these trendlines for a data series:
value parameter corresponds to order for a polynomial trendline and period for a Moving Average trendline. It both cases it must be >= 2. The
value parameter is ignored for all other trendlines:
The allowable values for the the trendline
valueparameter corresponds to order.
valueparameter corresponds to period.
Other trendline options, such as those shown in the following Excel dialog, can be set using the functions described below.
chart_series_set_trendline_forecast() function sets the forward and backward forecast periods for the trendline:
chart_series_set_trendline_equation() function displays the equation of the trendline on the chart:
chart_series_set_trendline_r_squared() function displays the R-squared value for the trendline on the chart:
chart_series_set_trendline_intercept() function sets the Y-axis intercept for the trendline:
As can be seen from the equation on the chart the intercept point (when X=0) is the same as the value set in the equation.
chart_series_set_trendline_name() function sets the name of the trendline that is displayed in the chart legend. In the examples above the trendlines are displayed with default names like "Linear (Series 1)" and "2 per Mov. Avg. (Series 1)". If these names are too verbose or not descriptive enough you can set your own trendline name:
It is often preferable to turn off the trendline caption in the legend. This is done in Excel by deleting the trendline name from the legend. In libxlsxwriter this is done using the
chart_legend_delete_series() function to delete the zero based series numbers:
chart_series_set_trendline_line() function is used to set the line properties of a trendline:
Error bars can be added to a chart series to indicate error bounds in the data.
The error bars can be vertical
y_error_bars (the most common type) or horizontal
x_error_bars (for Bar and Scatter charts only). For convenience these can be accessed from a series as follows:
The error bar properties that can be set in Excel are show in the following dialog:
Some of these properties can be set using the functions discussed below.
chart_series_set_error_bars() function sets the error bar type and value associated with the type:
The error bar types that be used are:
All error bar types, apart from Standard error, should have a valid value to set the error range:
For the Standard error type the value is ignored.
chart_series_set_error_bars_direction() function sets the direction of the error bars:
The valid directions are:
chart_series_set_error_bars_endcap() function sets the end cap type for the error bars:
The valid values are:
chart_series_set_error_bars_line() function sets the line properties for the error bars:
In general formatting is applied to an entire series in a chart. However, it is occasionally required to format individual points within a series.
In Excel charts "points" have a different meaning depending on the type of chart:
The most common use case is to format segments of a pie chart like this example:
The lxw_chart_point objects can be used to set the following properties for a chart point:
These properties are explained in the Chart Formatting subsections below.
The points should be passed as a NULL terminated array of pointers to lxw_chart_point objects:
You can skip points in the series that you don't want to format by passing a zero-initialized lxw_chart_point:
The array of lxw_chart_point objects pointers corresponds to the order of the points in the series starting from the first point. However, it does not have to extend to the entire range of the series. It can be NULL terminated at any point in the series, such as in the previous example.
For Bar/Column/Area charts "points" refer to bars/areas within the chart:
Data labels can be added to a chart series to indicate the values of the plotted data points. The functions described below turn on and set data label properties.
chart_series_set_labels() function is used to turn on data labels for a chart series:
By default data labels are displayed in Excel with only the values shown:
However, it is possible to configure other display options, as shown in the functions below.
chart_series_set_labels_options() function is used to set the parameters that are displayed in the series data label:
chart_series_set_labels_separator() function is used to change the separator between multiple data label items. The default options is a comma separator as shown in the previous example.
The available options are:
chart_series_set_labels_position() function sets the position of the labels in the data series:
In Excel the allowable data label positions vary for different chart types. The allowable, and default, positions are:
|Position||Line, Scatter||Bar, Column||Pie, Doughnut||Area, Radar|
chart_series_set_labels_leader_line() function is used to turn on leader lines for the data label of a series. It is mainly used for pie or doughnut charts:
chart_series_set_labels_legend() function is used to set the legend key for a data series:
chart_series_set_labels_percentage() function is used to turn on the display of data labels as a percentage for a series. It is mainly used for pie charts:
chart_series_set_labels_num_format() function is used to set the number format for data labels:
The number format is similar to the Worksheet Cell Format num_format, see
chart_series_set_labels_font() function is used to set the font for data labels:
For more information see Chart formatting: Fonts below.
The following chart formatting properties can be set for any chart object that they apply to (and that are supported by libxlsxwriter) such as chart lines, column fill areas and other chart elements:
These properties are explained in the subsections below.
Font properties can be set for several chart objects such as chart titles, axis labels, and axis numbering.
A chart font lxw_chart_font struct with default properties is:
The font properties are:
name: Set the font name:
size: Set the font size:
bold: Set the font bold property:
italic: Set the font italic property:
underline: Set the font underline property:
rotation: Set the font rotation, angle, property in the range -90 to 90 deg:
This is useful for displaying axis data such as dates in a more compact format.
color: Set the font color property. It can be a HTML style RGB color or a limited number of named colors (see Working with Colors for more information):
Here is a longer example with several chart formats:
The line format is used to specify properties of line objects that appear in a chart such as a plotted line on a chart or a border.
A chart line lxw_chart_line struct with default properties is:
none member is uses to turn the line off (it is always on by default except in Scatter charts). This is useful if you wish to plot a series with markers without a line, or a column fill without a border:
color member sets the color of the line. It can be a HTML style RGB color or a limited number of named colors (see Working with Colors for more information):
width member sets the width of the line. It should be specified in increments of 0.25 of a point as in Excel:
dash_type member sets the dash style of the line:
The following lxw_chart_line_dash_type values are available. They are shown in the order that they appear in the Excel dialog:
|LXW_CHART_LINE_DASH_LONG_DASH_DOT||Long Dash Dot|
|LXW_CHART_LINE_DASH_LONG_DASH_DOT_DOT||Long Dash Dot Dot|
The default line style is
More than one line property can be specified at a time:
In Excel chart formatting the
border property is a synonym for
line when the object being formatting also has a
Anywhere you see
border in an Excel chart dialog you can use the equivalent libxlsxwriter
The fill format is used to specify properties of chart objects that internal boundaries such as a column chart.
A chart fill lxw_chart_fill struct with default properties is:
The none property is used to turn the fill property off (it is generally on by default):
color member sets the color of the fill area. It can be a HTML style RGB color or a limited number of named colors (see Working with Colors for more information):
transparency member sets the transparency of the solid fill color in the integer range 1 - 100:
fill format is generally used in conjunction with a border which can be set via the
The pattern fill is used to specify pattern filled areas of chart objects such as the interior of a column or the background of the chart itself.
A chart pattern lxw_chart_pattern struct with default properties is:
Where the members are:
pattern: The pattern to be applied (required).
fg_color: The foreground color of the pattern (required).
bg_color: The background color (optional, defaults to white).
The foreground color,
fg_color, is a required parameter and can be HTML style RGB color or a limited number of named colors (see Working with Colors for more information):
The background color,
bg_color, is optional and defaults to LXW_COLOR_WHITE.
The following patterns lxw_chart_pattern_type can be applied:
The following chart features aren't currently supported in libxlsxwriter but will be in time. See the GitHub Chart Feature Requests.
If required these features are all available in the Perl and Python versions of this library.