1#######################################################################
2#
3# An example of a Combined chart in XlsxWriter.
4#
5# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
6#
7from xlsxwriter.workbook import Workbook
8
9workbook  = Workbook('chart_combined.xlsx')
10worksheet = workbook.add_worksheet()
11
12# Add a format for the headings.
13bold = workbook.add_format({'bold': True})
14
15# Add the worksheet data that the charts will refer to.
16headings = ['Number', 'Batch 1', 'Batch 2']
17data = [
18    [2, 3, 4, 5, 6, 7],
19    [10, 40, 50, 20, 10, 50],
20    [30, 60, 70, 50, 40, 30],
21]
22
23worksheet.write_row('A1', headings, bold)
24worksheet.write_column('A2', data[0])
25worksheet.write_column('B2', data[1])
26worksheet.write_column('C2', data[2])
27
28#
29# In the first example we will create a combined column and line chart.
30# They will share the same X and Y axes.
31#
32
33# Create a new column chart. This will use this as the primary chart.
34column_chart1 = workbook.add_chart({'type': 'column'})
35
36# Configure the data series for the primary chart.
37column_chart1.add_series({
38    'name':       '=Sheet1!$B$1',
39    'categories': '=Sheet1!$A$2:$A$7',
40    'values':     '=Sheet1!$B$2:$B$7',
41})
42
43# Create a new column chart. This will use this as the secondary chart.
44line_chart1 = workbook.add_chart({'type': 'line'})
45
46# Configure the data series for the secondary chart.
47line_chart1.add_series({
48    'name':       '=Sheet1!$C$1',
49    'categories': '=Sheet1!$A$2:$A$7',
50    'values':     '=Sheet1!$C$2:$C$7',
51})
52
53# Combine the charts.
54column_chart1.combine(line_chart1)
55
56# Add a chart title and some axis labels. Note, this is done via the
57# primary chart.
58column_chart1.set_title({ 'name': 'Combined chart - same Y axis'})
59column_chart1.set_x_axis({'name': 'Test number'})
60column_chart1.set_y_axis({'name': 'Sample length (mm)'})
61
62# Insert the chart into the worksheet
63worksheet.insert_chart('E2', column_chart1)
64
65#
66# In the second example we will create a similar combined column and line
67# chart except that the secondary chart will have a secondary Y axis.
68#
69
70# Create a new column chart. This will use this as the primary chart.
71column_chart2 = workbook.add_chart({'type': 'column'})
72
73# Configure the data series for the primary chart.
74column_chart2.add_series({
75    'name':       '=Sheet1!$B$1',
76    'categories': '=Sheet1!$A$2:$A$7',
77    'values':     '=Sheet1!$B$2:$B$7',
78})
79
80# Create a new column chart. This will use this as the secondary chart.
81line_chart2 = workbook.add_chart({'type': 'line'})
82
83# Configure the data series for the secondary chart. We also set a
84# secondary Y axis via (y2_axis). This is the only difference between
85# this and the first example, apart from the axis label below.
86line_chart2.add_series({
87    'name':       '=Sheet1!$C$1',
88    'categories': '=Sheet1!$A$2:$A$7',
89    'values':     '=Sheet1!$C$2:$C$7',
90    'y2_axis':    True,
91})
92
93# Combine the charts.
94column_chart2.combine(line_chart2)
95
96# Add a chart title and some axis labels.
97column_chart2.set_title({  'name': 'Combine chart - secondary Y axis'})
98column_chart2.set_x_axis({ 'name': 'Test number'})
99column_chart2.set_y_axis({ 'name': 'Sample length (mm)'})
100
101# Note: the y2 properties are on the secondary chart.
102line_chart2.set_y2_axis({'name': 'Target length (mm)'})
103
104# Insert the chart into the worksheet
105worksheet.insert_chart('E18', column_chart2)
106
107workbook.close()
108