libxlsxwriter
 All Data Structures Files Functions Variables Typedefs Enumerations Enumerator Macros Pages
Working with Charts

Table of Contents

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:

int main() {
lxw_workbook *workbook = new_workbook("chart_line.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
lxw_chart *chart;
/* Write some data for the chart. */
worksheet_write_number(worksheet, 0, 0, 10, NULL);
worksheet_write_number(worksheet, 1, 0, 40, NULL);
worksheet_write_number(worksheet, 2, 0, 50, NULL);
worksheet_write_number(worksheet, 3, 0, 20, NULL);
worksheet_write_number(worksheet, 4, 0, 10, NULL);
worksheet_write_number(worksheet, 5, 0, 50, NULL);
/* Create a chart object. */
chart = workbook_add_chart(workbook, LXW_CHART_LINE);
/* Configure the chart. */
series = chart_add_series(chart, NULL, "Sheet1!$A$1:$A$6");
(void)series; /* Do something with series in the real examples. */
/* Insert the chart into the worksheet. */
worksheet_insert_chart(worksheet, CELL("C1"), chart);
return workbook_close(workbook);
}

chart_working.png

Chart Value and Category Axes

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:

chart_axes01.png

Excel treats these two types of axis differently and exposes different properties for each. For example, here are the properties for a category axis:

chart_axes02.png

Here are properties for a value axis:

chart_axes03.png

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:

chart_axes04.png

A Scatter chart (but not a Line chart) has 2 value axes:

chart_axes05.png
Note
Date axes aren't supported yet.

Working with Chart Markers

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.

The chart_series_set_marker_type() function is used to specify the type of the series marker:

chart_marker1.png

The available marker types defined by lxw_chart_marker_type are:

The LXW_CHART_MARKER_NONE type can be used to turn off default markers:

chart_series_set_marker_none.png

The 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.

The chart_series_set_marker_size() function is used to specify the size of the series marker:

chart_series_set_marker_size.png

The chart_series_set_marker_line() and chart_series_set_marker_fill() functions can be used to set the line/border and fill properties of a chart marker:

chart_marker2.png

For more information on line/border and fill formatting see Chart formatting: Line and Chart formatting: Fill below.

Working with Chart Trendlines

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_trendline0.png

The chart_series_set_trendline() function turns on these trendlines for a data series:

series = chart_add_series(chart, NULL, "Sheet1!$A$1:$A$6");
chart_trendline2.png

The 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:

chart_trendline3.png

The allowable values for the the trendline type are:

Other trendline options, such as those shown in the following Excel dialog, can be set using the functions described below.

chart_trendline1.png

The chart_series_set_trendline_forecast() function sets the forward and backward forecast periods for the trendline:

chart_trendline4.png
Note
This feature isn't available for Moving Average in Excel.

The chart_series_set_trendline_equation() function displays the equation of the trendline on the chart:

chart_trendline5.png
Note
This feature isn't available for Moving Average in Excel.

The chart_series_set_trendline_r_squared() function displays the R-squared value for the trendline on the chart:

chart_trendline6.png
Note
This feature isn't available for Moving Average in Excel.

The chart_series_set_trendline_intercept() function sets the Y-axis intercept for the trendline:

chart_trendline7.png

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.

Note
The intercept feature is only available in Excel for Exponential, Linear and Polynomial trendline types.

The 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:

chart_trendline8.png

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:

// Delete the series name for the second series (=1 in zero base).
// The -1 value indicates the end of the array of values.
int16_t names[] = {1, -1};
chart_trendline9.png

The chart_series_set_trendline_line() function is used to set the line properties of a trendline:

chart_trendline10.png
Note
Trendlines cannot be added to series in a stacked chart, pie chart, doughnut chart or radar chart.

Working with Chart Error Bars

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:

chart_series_set_error_bars(series->y_error_bars, ...);
chart_series_set_error_bars(series->x_error_bars, ...);

The error bar properties that can be set in Excel are show in the following dialog:

chart_error_bars0.png

Some of these properties can be set using the functions discussed below.

The chart_series_set_error_bars() function sets the error bar type and value associated with the type:

"=Sheet1!$A$1:$A$5",
"=Sheet1!$B$1:$B$5");
chart_series_set_error_bars(series->y_error_bars,
chart_error_bars1.png

The error bar types that be used are:

Note
Custom error bars are not currently supported.

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.

The chart_series_set_error_bars_direction() function sets the direction of the error bars:

chart_error_bars2.png

The valid directions are:

The chart_series_set_error_bars_endcap() function sets the end cap type for the error bars:

chart_error_bars3.png

The valid values are:

The chart_series_set_error_bars_line() function sets the line properties for the error bars:

chart_error_bars4.png

Working with Chart Points

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:

#include "xlsxwriter.h"
/*
* Create a worksheet with an example Pie chart.
*/
int main() {
lxw_workbook *workbook = new_workbook("chart_pie_colors.xlsx");
lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);
/* Write some data for the chart. */
worksheet_write_string(worksheet, CELL("A1"), "Pass", NULL);
worksheet_write_string(worksheet, CELL("A2"), "Fail", NULL);
worksheet_write_number(worksheet, CELL("B1"), 90, NULL);
worksheet_write_number(worksheet, CELL("B2"), 10, NULL);
/* Create a pie chart. */
/* Add the data series to the chart. */
series = chart_add_series(chart, "=Sheet1!$A$1:$A$2",
"=Sheet1!$B$1:$B$2");
/* Create some fills for the chart points/segments. */
/* Add the fills to the point objects. */
lxw_chart_point red_point = {.fill = &red_fill };
lxw_chart_point green_point = {.fill = &green_fill};
/* Create an array of pointer to chart points. Note, the array should be
* NULL terminated. */
lxw_chart_point *points[] = {&green_point,
&red_point,
NULL};
/* Add the points to the series. */
chart_series_set_points(series, points);
/* Insert the chart into the worksheet. */
worksheet_insert_chart(worksheet, CELL("D2"), chart);
return workbook_close(workbook);
}

chart_points1.png

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:

lxw_chart_point red_point = {.fill = &red_fill };
lxw_chart_point green_point = {.fill = &green_fill};
lxw_chart_point *points[] = {&green_point,
&red_point,
NULL}; // Indicates the end of the list.
chart_series_set_points(series, points);

You can skip points in the series that you don't want to format by passing a zero-initialized lxw_chart_point:

lxw_chart_point default_point = {0, 0, 0};
lxw_chart_point red_point = {.fill = &red_fill, .line = &line};
lxw_chart_point green_point = {.fill = &green_fill, .line = &line};
lxw_chart_point *points[] = {&red_point,
&default_point,
&green_point,
&default_point,
&red_point,
NULL};
chart_series_set_points(series, points);
chart_points2.png

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:

lxw_chart_point red_point = {.fill = &red_fill};
lxw_chart_point default_point = {0, 0, 0};
lxw_chart_point *points[] = {&default_point, &default_point, &red_point, NULL};
chart_series_set_points(series2, points);
chart_points3.png

Working with Chart Data Labels

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.

The chart_series_set_labels() function is used to turn on data labels for a chart series:

chart_labels1.png

By default data labels are displayed in Excel with only the values shown:

chart_labels2.png

However, it is possible to configure other display options, as shown in the functions below.

The chart_series_set_labels_options() function is used to set the parameters that are displayed in the series data label:

chart_labels3.png

The 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:

For example:

chart_labels4.png

The chart_series_set_labels_position() function sets the position of the labels in the data series:

chart_labels5.png

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
LXW_CHART_LABEL_POSITION_CENTER Yes Yes Yes Yes (default)
LXW_CHART_LABEL_POSITION_RIGHT Yes (default)
LXW_CHART_LABEL_POSITION_LEFT Yes
LXW_CHART_LABEL_POSITION_ABOVE Yes
LXW_CHART_LABEL_POSITION_BELOW Yes
LXW_CHART_LABEL_POSITION_INSIDE_BASE Yes
LXW_CHART_LABEL_POSITION_INSIDE_END Yes Yes
LXW_CHART_LABEL_POSITION_OUTSIDE_END Yes (default) Yes
LXW_CHART_LABEL_POSITION_BEST_FIT Yes (default)

The 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:

Note
Even when leader lines are turned on they aren't automatically visible in Excel or XlsxWriter. Due to an Excel limitation (or design) leader lines only appear if the data label is moved manually or if the data labels are very close and need to be adjusted automatically.

The chart_series_set_labels_legend() function is used to set the legend key for a data series:

chart_labels6.png

The 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_labels7.png

The chart_series_set_labels_num_format() function is used to set the number format for data labels:

chart_labels8.png

The number format is similar to the Worksheet Cell Format num_format, see format_set_num_format().

The chart_series_set_labels_font() function is used to set the font for data labels:

lxw_chart_font font = {.name = "Consolas", .color = LXW_COLOR_RED};
chart_labels9.png

For more information see Chart formatting: Fonts below.

Chart Formatting

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:

For example:

chart_formatting2.png

These properties are explained in the subsections below.

Chart formatting: Fonts

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:

lxw_chart_font font = {.name = "Calibri",
.size = 10,
.bold = LXW_FALSE,
.italic = LXW_FALSE,
.underline = LXW_FALSE,
.rotation = 0,
.color = LXW_COLOR_BLACK};

The font properties are:

lxw_chart_font font = {.name = "Arial"};
lxw_chart_font font = {.name = "Arial", .size = 11};
lxw_chart_font font = {.rotation = 45};

This is useful for displaying axis data such as dates in a more compact format.

Here is a longer example with several chart formats:

lxw_chart_font font1 = {.name = "Calibri", .color = LXW_COLOR_BLUE};
lxw_chart_font font2 = {.name = "Courier", .color = 0x92D050};
lxw_chart_font font3 = {.name = "Arial", .color = 0x00B0F0};
lxw_chart_font font4 = {.name = "Century", .color = LXW_COLOR_RED};
lxw_chart_font font5 = {.rotation = -30};
.italic = LXW_TRUE,
.underline = LXW_TRUE,
.color = 0x7030A0};
/* Write the chart title with a font. */
chart_title_set_name(chart, "Test Results");
chart_title_set_name_font(chart, &font1);
/* Write the Y axis with a font. */
chart_axis_set_name(chart->y_axis, "Units");
/* Write the X axis with a font. */
chart_axis_set_name(chart->x_axis, "Month");
/* Display the chart legend at the bottom of the chart. */
chart_legend_set_font(chart, &font6);

chart_fonts.png

Chart formatting: Line

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:

.color = LXW_COLOR_BLACK,
.width = 2.25,

The 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:

chart_formatting3.png

The 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):

lxw_chart_line line = {.color = 0xFF9900};
chart_series_set_line(series, &line);
chart_formatting4.png

The width member sets the width of the line. It should be specified in increments of 0.25 of a point as in Excel:

lxw_chart_line line = {.width = 4.25};
chart_series_set_line(series, &line);
chart_formatting_width.png

The dash_type member sets the dash style of the line:

chart_formatting5.png

The following lxw_chart_line_dash_type values are available. They are shown in the order that they appear in the Excel dialog:

Define Excel name
LXW_CHART_LINE_DASH_SOLID Solid
LXW_CHART_LINE_DASH_ROUND_DOT Round Dot
LXW_CHART_LINE_DASH_SQUARE_DOT Square Dot
LXW_CHART_LINE_DASH_DASH Dash
LXW_CHART_LINE_DASH_DASH_DOT Dash Dot
LXW_CHART_LINE_DASH_LONG_DASH Long Dash
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 LXW_CHART_LINE_DASH_SOLID.

More than one line property can be specified at a time:

.width = 1.25,
chart_formatting_multiple.png

Chart formatting: Border

In Excel chart formatting the border property is a synonym for line when the object being formatting also has a fill property.

Anywhere you see border in an Excel chart dialog you can use the equivalent libxlsxwriter line function.

Chart formatting: Fill

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:

.color = LXW_COLOR_BLACK,
.transparency = 0};

The none property is used to turn the fill property off (it is generally on by default):

chart_fill1.png

The 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):

lxw_chart_fill fill = {.color = 0xFF9900};
chart_series_set_fill(series, &fill);
chart_fill2.png

The transparency member sets the transparency of the solid fill color in the integer range 1 - 100:

.transparency = 50};
chart_series_set_fill(series, &fill);
chart_fill3.png

The fill format is generally used in conjunction with a border which can be set via the line format:

chart_fill4.png

Chart formatting: Pattern

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:

.fg_color = LXW_COLOR_NONE,;
.bg_color = LXW_COLOR_WHITE};

Where the members are:

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.

For example:

.fg_color = 0x804000,
.bg_color = 0XC68C53};
.fg_color = 0XB30000,
.bg_color = 0XFF6666};
chart_series_set_pattern(series1, &pattern1);
chart_series_set_pattern(series2, &pattern2);
chart_pattern.png

The following patterns lxw_chart_pattern_type can be applied:

Note
If a pattern fill is used on a chart object it overrides the solid fill properties of the object.

Chart Limitations

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.

Next: Working with Memory and Performance