1#######################################################################
2#
3# A demo of an various Excel chart data label features that are available
4# via an XlsxWriter chart.
5#
6# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
7#
8import xlsxwriter
9
10workbook = xlsxwriter.Workbook('chart_data_labels.xlsx')
11worksheet = workbook.add_worksheet()
12bold = workbook.add_format({'bold': 1})
13
14# Add the worksheet data that the charts will refer to.
15headings = ['Number', 'Data', 'Text']
16
17data = [
18    [2, 3, 4, 5, 6, 7],
19    [20, 10, 20, 30, 40, 30],
20    ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'],
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#
30# Example with standard data labels.
31#
32
33# Create a Column chart.
34chart1 = workbook.add_chart({'type': 'column'})
35
36# Configure the data series and add the data labels.
37chart1.add_series({
38    'categories': '=Sheet1!$A$2:$A$7',
39    'values':     '=Sheet1!$B$2:$B$7',
40    'data_labels': {'value': True},
41})
42
43# Add a chart title.
44chart1.set_title({'name': 'Chart with standard data labels'})
45
46# Turn off the chart legend.
47chart1.set_legend({'none': True})
48
49# Insert the chart into the worksheet (with an offset).
50worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
51
52#######################################################################
53#
54# Example with value and category data labels.
55#
56
57# Create a Column chart.
58chart2 = workbook.add_chart({'type': 'column'})
59
60# Configure the data series and add the data labels.
61chart2.add_series({
62    'categories': '=Sheet1!$A$2:$A$7',
63    'values':     '=Sheet1!$B$2:$B$7',
64    'data_labels': {'value': True, 'category': True},
65})
66
67# Add a chart title.
68chart2.set_title({'name': 'Category and Value data labels'})
69
70# Turn off the chart legend.
71chart2.set_legend({'none': True})
72
73# Insert the chart into the worksheet (with an offset).
74worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
75
76#######################################################################
77#
78# Example with standard data labels with different font.
79#
80
81# Create a Column chart.
82chart3 = workbook.add_chart({'type': 'column'})
83
84# Configure the data series and add the data labels.
85chart3.add_series({
86    'categories': '=Sheet1!$A$2:$A$7',
87    'values':     '=Sheet1!$B$2:$B$7',
88    'data_labels': {'value': True,
89                    'font': {'bold': True,
90                             'color': 'red',
91                             'rotation': -30}},
92})
93
94# Add a chart title.
95chart3.set_title({'name': 'Data labels with user defined font'})
96
97# Turn off the chart legend.
98chart3.set_legend({'none': True})
99
100# Insert the chart into the worksheet (with an offset).
101worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
102
103#######################################################################
104#
105# Example with standard data labels and formatting.
106#
107
108# Create a Column chart.
109chart4 = workbook.add_chart({'type': 'column'})
110
111# Configure the data series and add the data labels.
112chart4.add_series({
113    'categories': '=Sheet1!$A$2:$A$7',
114    'values':     '=Sheet1!$B$2:$B$7',
115    'data_labels': {'value': True,
116                    'border': {'color': 'red'},
117                    'fill':   {'color': 'yellow'}},
118})
119
120# Add a chart title.
121chart4.set_title({'name': 'Data labels with formatting'})
122
123# Turn off the chart legend.
124chart4.set_legend({'none': True})
125
126# Insert the chart into the worksheet (with an offset).
127worksheet.insert_chart('D50', chart4, {'x_offset': 25, 'y_offset': 10})
128
129#######################################################################
130#
131# Example with custom string data labels.
132#
133
134# Create a Column chart.
135chart5 = workbook.add_chart({'type': 'column'})
136
137# Some custom labels.
138custom_labels = [
139    {'value': 'Amy'},
140    {'value': 'Bea'},
141    {'value': 'Eva'},
142    {'value': 'Fay'},
143    {'value': 'Liv'},
144    {'value': 'Una'},
145]
146
147# Configure the data series and add the data labels.
148chart5.add_series({
149    'categories': '=Sheet1!$A$2:$A$7',
150    'values':     '=Sheet1!$B$2:$B$7',
151    'data_labels': {'value': True, 'custom': custom_labels},
152})
153
154# Add a chart title.
155chart5.set_title({'name': 'Chart with custom string data labels'})
156
157# Turn off the chart legend.
158chart5.set_legend({'none': True})
159
160# Insert the chart into the worksheet (with an offset).
161worksheet.insert_chart('D66', chart5, {'x_offset': 25, 'y_offset': 10})
162
163#######################################################################
164#
165# Example with custom data labels from cells.
166#
167
168# Create a Column chart.
169chart6 = workbook.add_chart({'type': 'column'})
170
171# Some custom labels.
172custom_labels = [
173    {'value': '=Sheet1!$C$2'},
174    {'value': '=Sheet1!$C$3'},
175    {'value': '=Sheet1!$C$4'},
176    {'value': '=Sheet1!$C$5'},
177    {'value': '=Sheet1!$C$6'},
178    {'value': '=Sheet1!$C$7'},
179]
180
181# Configure the data series and add the data labels.
182chart6.add_series({
183    'categories': '=Sheet1!$A$2:$A$7',
184    'values':     '=Sheet1!$B$2:$B$7',
185    'data_labels': {'value': True, 'custom': custom_labels},
186})
187
188# Add a chart title.
189chart6.set_title({'name': 'Chart with custom data labels from cells'})
190
191# Turn off the chart legend.
192chart6.set_legend({'none': True})
193
194# Insert the chart into the worksheet (with an offset).
195worksheet.insert_chart('D82', chart6, {'x_offset': 25, 'y_offset': 10})
196
197#######################################################################
198#
199# Example with custom and default data labels.
200#
201
202# Create a Column chart.
203chart7 = workbook.add_chart({'type': 'column'})
204
205# The following is used to get a mix of default and custom labels. The 'None'
206# items will get the default value. We also set a font for the custom items
207# as an extra example.
208custom_labels = [
209    {'value': '=Sheet1!$C$2', 'font': {'color': 'red'}},
210    None,
211    {'value': '=Sheet1!$C$4', 'font': {'color': 'red'}},
212    {'value': '=Sheet1!$C$5', 'font': {'color': 'red'}},
213]
214
215# Configure the data series and add the data labels.
216chart7.add_series({
217    'categories': '=Sheet1!$A$2:$A$7',
218    'values':     '=Sheet1!$B$2:$B$7',
219    'data_labels': {'value': True, 'custom': custom_labels},
220})
221
222# Add a chart title.
223chart7.set_title({'name': 'Mixed custom and default data labels'})
224
225# Turn off the chart legend.
226chart7.set_legend({'none': True})
227
228# Insert the chart into the worksheet (with an offset).
229worksheet.insert_chart('D98', chart7, {'x_offset': 25, 'y_offset': 10})
230
231
232#######################################################################
233#
234# Example with deleted custom data labels.
235#
236
237# Create a Column chart.
238chart8 = workbook.add_chart({'type': 'column'})
239
240# Some deleted custom labels and defaults (set with None values). This allows
241# us to highlight certain values such as the minimum and maximum.
242custom_labels = [
243    {'delete': True},
244    None,
245    {'delete': True},
246    {'delete': True},
247    None,
248    {'delete': True},
249]
250
251# Configure the data series and add the data labels.
252chart8.add_series({
253    'categories': '=Sheet1!$A$2:$A$7',
254    'values':     '=Sheet1!$B$2:$B$7',
255    'data_labels': {'value': True, 'custom': custom_labels},
256})
257
258# Add a chart title.
259chart8.set_title({'name': 'Chart with deleted data labels'})
260
261# Turn off the chart legend.
262chart8.set_legend({'none': True})
263
264# Insert the chart into the worksheet (with an offset).
265worksheet.insert_chart('D114', chart8, {'x_offset': 25, 'y_offset': 10})
266
267#######################################################################
268#
269# Example with custom string data labels and formatting.
270#
271
272# Create a Column chart.
273chart9 = workbook.add_chart({'type': 'column'})
274
275# Some custom labels.
276custom_labels = [
277    {'value': 'Amy', 'border': {'color': 'blue'}},
278    {'value': 'Bea'},
279    {'value': 'Eva'},
280    {'value': 'Fay'},
281    {'value': 'Liv'},
282    {'value': 'Una', 'fill':   {'color': 'green'}},
283]
284
285# Configure the data series and add the data labels.
286chart9.add_series({
287    'categories': '=Sheet1!$A$2:$A$7',
288    'values':     '=Sheet1!$B$2:$B$7',
289    'data_labels': {'value': True,
290                    'custom': custom_labels,
291                    'border': {'color': 'red'},
292                    'fill':   {'color': 'yellow'}},
293
294})
295
296# Add a chart title.
297chart9.set_title({'name': 'Chart with custom labels and formatting'})
298
299# Turn off the chart legend.
300chart9.set_legend({'none': True})
301
302# Insert the chart into the worksheet (with an offset).
303worksheet.insert_chart('D130', chart9, {'x_offset': 25, 'y_offset': 10})
304
305workbook.close()
306