1#######################################################################
2#
3# A demo of a clustered category chart in XlsxWriter.
4#
5# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
6#
7from xlsxwriter.workbook import Workbook
8
9workbook = Workbook('chart_clustered.xlsx')
10worksheet = workbook.add_worksheet()
11bold = workbook.add_format({'bold': 1})
12
13# Add the worksheet data that the charts will refer to.
14headings = ['Types', 'Sub Type', 'Value 1', 'Value 2', 'Value 3']
15data = [
16    ['Type 1', 'Sub Type A', 5000,      8000,      6000],
17    ['',       'Sub Type B', 2000,      3000,      4000],
18    ['',       'Sub Type C', 250,       1000,      2000],
19    ['Type 2', 'Sub Type D', 6000,      6000,      6500],
20    ['',       'Sub Type E', 500,       300,        200],
21]
22
23worksheet.write_row('A1', headings, bold)
24
25for row_num, row_data in enumerate(data):
26    worksheet.write_row(row_num + 1, 0, row_data)
27
28# Create a new chart object. In this case an embedded chart.
29chart = workbook.add_chart({'type': 'column'})
30
31# Configure the series. Note, that the categories are 2D ranges (from column A
32# to column B). This creates the clusters. The series are shown as formula
33# strings for clarity but you can also use the list syntax. See the docs.
34chart.add_series({
35    'categories': '=Sheet1!$A$2:$B$6',
36    'values':     '=Sheet1!$C$2:$C$6',
37})
38
39chart.add_series({
40    'categories': '=Sheet1!$A$2:$B$6',
41    'values':     '=Sheet1!$D$2:$D$6',
42})
43
44chart.add_series({
45    'categories': '=Sheet1!$A$2:$B$6',
46    'values':     '=Sheet1!$E$2:$E$6',
47})
48
49# Set the Excel chart style.
50chart.set_style(37)
51
52# Turn off the legend.
53chart.set_legend({'position': 'none'})
54
55# Insert the chart into the worksheet.
56worksheet.insert_chart('G3', chart)
57
58workbook.close()
59