rust_xlsxwriter roadmap: Charts

[jmcnamara]

The Chart feature has a roadmap of it own since it is has a lot of properties and methods (see the following for details on the features: https://xlsxwriter.readthedocs.io/chart.html).

I've added initial support for Charts via the Chart class and the worksheet.insert_chart() method. See also the Chart Examples in the user guide.

All Excel first generation chart types such as Area, Bar, Column, Line, Scatter, Pie, Doughnut and Radar are supported. Newer Excel 365 style charts such as Waterfall probably won't be supported (at least not until other major features have been completed).

The planned and completed features are.

• [x] Initial support for all first generation chart types.

Format options:

• [x] line: Set the properties of the series line type such as color and width.
• [x] border: Set the border properties of the series such as color and style.
• [x] fill: Set the solid fill properties of the series such as color.
• [x] pattern: Set the pattern fill properties of the series.
• [x] gradient: Set the gradient fill properties of the series.

Series option:

• [x] name: Set the name for the series.
• [x] format
• [x] marker: Set the properties of the series marker such as style and color.
• [x] trendline: Set the properties of the series trendline.
• [x] smooth: Set the smooth property of a line series.
• [x] y_error_bars: Set vertical error bounds for a chart series.
• [x] x_error_bars: Set horizontal error bounds for a chart series.
• [x] data_labels: Set data labels for the series.
• [x] points: Set properties for individual points in a series.
• [x] invert_if_negative: Invert the fill color for negative values.
• [x] invert_if_negative_color: Invert the fill color for negative values.
• [x] overlap: Set the overlap between series in a Bar/Column chart.
• [x] gap: Set the gap between series in a Bar/Column chart.

X-Axis `chart.set_x_axis() sub options:

• [x] name
• [x] name_font
• [x] layout
• [x] num_font
• [x] num_format
• [x] format
• [x] min
• [x] max
• [x] minor_unit
• [x] major_unit
• [x] interval_unit
• [x] interval_tick
• [x] crossing
• [x] position_axis
• [x] reverse
• [x] log_base
• [x] label_position
• [x] label_align
• [x] major_gridlines
• [x] minor_gridlines
• [x] hidden
• [x] date_axis
• [x] text_axis
• [x] minor_unit_type
• [x] major_unit_type
• [x] minor_tick_mark
• [x] major_tick_mark
• [x] display_units
• [x] display_units_visible

Other Chart options:

• [x] chart.set_name()
• [x] chart.set_y_axis()
• [x] chart.set_x2_axis()
• [x] chart.set_y2_axis()
• [x] chart.combine()
• [x] chart.set_size()
• [x] chart.set_title()
• [x] chart.set_legend()
• [x] chart.set_legend() - delete entries
• [x] chart.set_chartarea()
• [x] chart.set_plotarea()
• [x] chart.set_style()
• [x] chart.set_table()
• [x] chart.set_up_down_bars()
• [x] chart.set_drop_lines()
• [x] chart.set_high_low_lines()
• [x] chart.show_blanks_as()
• [x] chart.show_na_as_empty()
• [x] chart.show_hidden_data()
• [x] chart.set_rotation()
• [x] chart.set_hole_size()

[redtechtiger]

What's the status on chart.combine()? I would love to contribute to implementing it if there's any way I can help!

[jmcnamara]

What's the status on chart.combine()?

It is the next in line feature for Charts. I'll see if I can move it along in the next week or two.

[redtechtiger]

Any updates? :)

[jmcnamara]

Any updates? :)

No, unfortunately. I got sucked into some Polars work in the last few weeks. I'll post an update once there is something.

[jmcnamara]

What's the status on chart.combine()?

@redtechtiger

I have started work on this and there is some initial code upstream for the combine part. However, it is missing the secondary axes parts that make it more useful. I'll add those in the next few weeks.

In the meantime here is a working example using main:

use rust_xlsxwriter::{Chart, ChartType, Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();
    let worksheet = workbook.add_worksheet();

    // Add some test data for the chart(s).
    worksheet.write_column(0, 0, [20, 25, 10, 10, 20])?;
    worksheet.write_column(0, 1, [2, 7, 3, 6, 2])?;

    let mut chart1 = Chart::new(ChartType::Column);
    chart1.add_series().set_values(("Sheet1", 0, 0, 4, 0));

    let mut chart2 = Chart::new(ChartType::Line);
    chart2.add_series().set_values(("Sheet1", 0, 1, 4, 1));

    // Combine the Line chart into the Column chart.
    chart1.combine(&chart2);

    worksheet.insert_chart(0, 3, &chart1)?;

    workbook.save("chart.xlsx")?;

    Ok(())
}

[redtechtiger]

Looks great! I'll have a look once I get some time over. Since the secondary axis isn't implemented yet, I'm assuming a combined graph won't play well with very different scales of values for the two series?

[jmcnamara]

I'm assuming a combined graph won't play well with very different scales of values for the two series?

Correct. That needs secondary axis support which I'm working on and which actually doesn't need combined charts.

BTW, what is your use case. What type of chart do you need to generate?

[redtechtiger]

I somehow totally missed this. My bad!

BTW, what is your use case. What type of chart do you need to generate?

I'm combining a bar chart (let's say products sold per month) with a line chart (let's say orders fulfilled % per month). So the bar chart tends to vary significantly, while the line chart stays in the 0-100 range.

[jmcnamara]

@redtechtiger

I'm combining a bar chart (let's say products sold per month) with a line chart (let's say orders fulfilled % per month). So the bar chart tends to vary significantly, while the line chart stays in the 0-100 range.

This is now available on main. You can create a secondary axis chart like this:

use rust_xlsxwriter::{Chart, ChartType, Format, Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();
    let worksheet = workbook.add_worksheet();

    // Formats used in the workbook.
    let bold = Format::new().set_bold();
    let percent_format = Format::new().set_num_format("0%");

    // Add the worksheet data that the charts will refer to.
    let headings = ["Reason", "Number", "Percentage"];

    let reasons = [
        "Traffic",
        "Child care",
        "Public Transport",
        "Weather",
        "Overslept",
        "Emergency",
    ];

    let numbers = [60, 40, 20, 15, 10, 5];
    let percents = [0.440, 0.667, 0.800, 0.900, 0.967, 1.00];

    worksheet.write_row_with_format(0, 0, headings, &bold)?;
    worksheet.write_column(1, 0, reasons)?;
    worksheet.write_column(1, 1, numbers)?;
    worksheet.write_column_with_format(1, 2, percents, &percent_format)?;

    // Widen the columns for visibility.
    worksheet.set_column_width(0, 15)?;
    worksheet.set_column_width(1, 10)?;
    worksheet.set_column_width(2, 10)?;

    //
    // Create a new Column chart. This will be the primary chart.
    //
    let mut column_chart = Chart::new(ChartType::Column);

    // Configure a series on the primary axis.
    column_chart
        .add_series()
        .set_categories("Sheet1!$A$2:$A$7")
        .set_values("Sheet1!$B$2:$B$7");

    // Add a chart title.
    column_chart.title().set_name("Reasons for lateness");

    // Turn off the chart legend.
    column_chart.legend().set_hidden();

    // Set the  name and scale of the Y axes. Note, the secondary axis is set
    // from the primary chart.
    column_chart
        .y_axis()
        .set_name("Respondents (number)")
        .set_min(0)
        .set_max(120);

    column_chart.y2_axis().set_max(1);

    //
    // Create a new Line chart. This will be the secondary chart.
    //
    let mut line_chart = Chart::new(ChartType::Line);

    // Add a series on the secondary axis.
    line_chart
        .add_series()
        .set_categories("Sheet1!$A$2:$A$7")
        .set_values("Sheet1!$C$2:$C$7")
        .set_y2_axis(true);

    // Combine the charts.
    column_chart.combine(&line_chart);

    // Add the chart to the worksheet.
    worksheet.insert_chart(1, 5, &column_chart)?;

    workbook.save("chart_pareto.xlsx")?;

    Ok(())
}

Output:

I'll roll this into a release within the next week (hopefully).

[redtechtiger]

Awesome, I'll check it out in a bit! Thanks! :-)

[jmcnamara]

What's the status on chart.combine()

This is now available upstream in v0.65.0

[jmcnamara]

Note, I changed ChartSeries::set_y2_axis() to ChartSeries::set_secondary_axis() for API consistency in v0.66.0.

[jmcnamara]

Chart layout support has been added and will be release in v0.69.0.

"Chartsheets" are the last remaining chart feature to port. I'm going to move those back to the main Roadmap #1 and make charts as complete.

Chartsheets aren't used very much in practice these days (as far as I can see) so I will wait for a feature request before I implement them.

If you have any other chart feature requests or bug report please open a new issue.