Example of creating combined Excel charts with two chart types.
In the first example we create a combined column and line chart that share the same X and Y axes.
In the second example we create a similar combined column and line chart except that the secondary chart has a secondary Y axis.
#######################################################################
#
# An example of a Combined chart in XlsxWriter.
#
# SPDX-License-Identifier: BSD-2-Clause
# Copyright 2013-2023, John McNamara, jmcnamara@cpan.org
#
from xlsxwriter.workbook import Workbook
workbook = Workbook('chart_combined.xlsx')
worksheet = workbook.add_worksheet()
# Add a format for the headings.
bold = workbook.add_format({'bold': True})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#
# In the first example we will create a combined column and line chart.
# They will share the same X and Y axes.
#
# Create a new column chart. This will use this as the primary chart.
column_chart1 = workbook.add_chart({'type': 'column'})
# Configure the data series for the primary chart.
column_chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Create a new column chart. This will use this as the secondary chart.
line_chart1 = workbook.add_chart({'type': 'line'})
# Configure the data series for the secondary chart.
line_chart1.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Combine the charts.
column_chart1.combine(line_chart1)
# Add a chart title and some axis labels. Note, this is done via the
# primary chart.
column_chart1.set_title({ 'name': 'Combined chart - same Y axis'})
column_chart1.set_x_axis({'name': 'Test number'})
column_chart1.set_y_axis({'name': 'Sample length (mm)'})
# Insert the chart into the worksheet
worksheet.insert_chart('E2', column_chart1)
#
# In the second example we will create a similar combined column and line
# chart except that the secondary chart will have a secondary Y axis.
#
# Create a new column chart. This will use this as the primary chart.
column_chart2 = workbook.add_chart({'type': 'column'})
# Configure the data series for the primary chart.
column_chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Create a new column chart. This will use this as the secondary chart.
line_chart2 = workbook.add_chart({'type': 'line'})
# Configure the data series for the secondary chart. We also set a
# secondary Y axis via (y2_axis). This is the only difference between
# this and the first example, apart from the axis label below.
line_chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
'y2_axis': True,
})
# Combine the charts.
column_chart2.combine(line_chart2)
# Add a chart title and some axis labels.
column_chart2.set_title({ 'name': 'Combine chart - secondary Y axis'})
column_chart2.set_x_axis({ 'name': 'Test number'})
column_chart2.set_y_axis({ 'name': 'Sample length (mm)'})
# Note: the y2 properties are on the secondary chart.
line_chart2.set_y2_axis({'name': 'Target length (mm)'})
# Insert the chart into the worksheet
worksheet.insert_chart('E18', column_chart2)
workbook.close()