1###############################################################################
2#
3# Example of how to add sparklines to an XlsxWriter file with Python.
4#
5# Sparklines are small charts that fit in a single cell and are
6# used to show trends in data. This example shows the majority of
7# options that can be applied to sparklines.
8#
9# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
10#
11import xlsxwriter
12
13workbook = xlsxwriter.Workbook('sparklines2.xlsx')
14worksheet1 = workbook.add_worksheet()
15worksheet2 = workbook.add_worksheet()
16bold = workbook.add_format({'bold': True})
17row = 1
18
19# Set the columns widths to make the output clearer.
20worksheet1.set_column('A:A', 14)
21worksheet1.set_column('B:B', 50)
22worksheet1.set_zoom(150)
23
24# Headings.
25worksheet1.write('A1', 'Sparkline', bold)
26worksheet1.write('B1', 'Description', bold)
27
28
29###############################################################################
30#
31text = 'A default "line" sparkline.'
32
33worksheet1.add_sparkline('A2', {'range': 'Sheet2!A1:J1'})
34
35worksheet1.write(row, 1, text)
36row += 1
37
38
39###############################################################################
40#
41text = 'A default "column" sparkline.'
42
43worksheet1.add_sparkline('A3', {'range': 'Sheet2!A2:J2',
44                                'type': 'column'})
45
46worksheet1.write(row, 1, text)
47row += 1
48
49
50###############################################################################
51#
52text = 'A default "win/loss" sparkline.'
53
54worksheet1.add_sparkline('A4', {'range': 'Sheet2!A3:J3',
55                                'type': 'win_loss'})
56
57worksheet1.write(row, 1, text)
58row += 2
59
60
61###############################################################################
62#
63text = 'Line with markers.'
64
65worksheet1.add_sparkline('A6', {'range': 'Sheet2!A1:J1',
66                                'markers': True})
67
68worksheet1.write(row, 1, text)
69row += 1
70
71
72###############################################################################
73#
74text = 'Line with high and low points.'
75
76worksheet1.add_sparkline('A7', {'range': 'Sheet2!A1:J1',
77                                'high_point': True,
78                                'low_point': True})
79
80worksheet1.write(row, 1, text)
81row += 1
82
83
84###############################################################################
85#
86text = 'Line with first and last point markers.'
87
88worksheet1.add_sparkline('A8', {'range': 'Sheet2!A1:J1',
89                                'first_point': True,
90                                'last_point': True})
91
92worksheet1.write(row, 1, text)
93row += 1
94
95
96###############################################################################
97#
98text = 'Line with negative point markers.'
99
100worksheet1.add_sparkline('A9', {'range': 'Sheet2!A1:J1',
101                                'negative_points': True})
102
103worksheet1.write(row, 1, text)
104row += 1
105
106
107###############################################################################
108#
109text = 'Line with axis.'
110
111worksheet1.add_sparkline('A10', {'range': 'Sheet2!A1:J1',
112                                 'axis': True})
113
114worksheet1.write(row, 1, text)
115row += 2
116
117
118###############################################################################
119#
120text = 'Column with default style (1).'
121
122worksheet1.add_sparkline('A12', {'range': 'Sheet2!A2:J2',
123                                 'type': 'column'})
124
125worksheet1.write(row, 1, text)
126row += 1
127
128
129###############################################################################
130#
131text = 'Column with style 2.'
132
133worksheet1.add_sparkline('A13', {'range': 'Sheet2!A2:J2',
134                                 'type': 'column',
135                                 'style': 2})
136
137worksheet1.write(row, 1, text)
138row += 1
139
140
141###############################################################################
142#
143text = 'Column with style 3.'
144
145worksheet1.add_sparkline('A14', {'range': 'Sheet2!A2:J2',
146                                 'type': 'column',
147                                 'style': 3})
148
149worksheet1.write(row, 1, text)
150row += 1
151
152
153###############################################################################
154#
155text = 'Column with style 4.'
156
157worksheet1.add_sparkline('A15', {'range': 'Sheet2!A2:J2',
158                                 'type': 'column',
159                                 'style': 4})
160
161worksheet1.write(row, 1, text)
162row += 1
163
164
165###############################################################################
166#
167text = 'Column with style 5.'
168
169worksheet1.add_sparkline('A16', {'range': 'Sheet2!A2:J2',
170                                 'type': 'column',
171                                 'style': 5})
172
173worksheet1.write(row, 1, text)
174row += 1
175
176
177###############################################################################
178#
179text = 'Column with style 6.'
180
181worksheet1.add_sparkline('A17', {'range': 'Sheet2!A2:J2',
182                                 'type': 'column',
183                                 'style': 6})
184
185worksheet1.write(row, 1, text)
186row += 1
187
188
189###############################################################################
190#
191text = 'Column with a user defined color.'
192
193worksheet1.add_sparkline('A18', {'range': 'Sheet2!A2:J2',
194                                 'type': 'column',
195                                 'series_color': '#E965E0'})
196
197worksheet1.write(row, 1, text)
198row += 2
199
200
201###############################################################################
202#
203text = 'A win/loss sparkline.'
204
205worksheet1.add_sparkline('A20', {'range': 'Sheet2!A3:J3',
206                                 'type': 'win_loss'})
207
208worksheet1.write(row, 1, text)
209row += 1
210
211
212###############################################################################
213#
214text = 'A win/loss sparkline with negative points highlighted.'
215
216worksheet1.add_sparkline('A21', {'range': 'Sheet2!A3:J3',
217                                 'type': 'win_loss',
218                                 'negative_points': True})
219
220worksheet1.write(row, 1, text)
221row += 2
222
223
224###############################################################################
225#
226text = 'A left to right column (the default).'
227
228worksheet1.add_sparkline('A23', {'range': 'Sheet2!A4:J4',
229                                 'type': 'column',
230                                 'style': 20})
231
232worksheet1.write(row, 1, text)
233row += 1
234
235
236###############################################################################
237#
238text = 'A right to left column.'
239
240worksheet1.add_sparkline('A24', {'range': 'Sheet2!A4:J4',
241                                 'type': 'column',
242                                 'style': 20,
243                                 'reverse': True})
244
245worksheet1.write(row, 1, text)
246row += 1
247
248
249###############################################################################
250#
251text = 'Sparkline and text in one cell.'
252
253worksheet1.add_sparkline('A25', {'range': 'Sheet2!A4:J4',
254                                 'type': 'column',
255                                 'style': 20})
256
257worksheet1.write(row, 0, 'Growth')
258worksheet1.write(row, 1, text)
259row += 2
260
261
262###############################################################################
263#
264text = 'A grouped sparkline. Changes are applied to all three.'
265
266worksheet1.add_sparkline('A27', {'location': ['A27', 'A28', 'A29'],
267                                 'range': ['Sheet2!A5:J5',
268                                           'Sheet2!A6:J6',
269                                           'Sheet2!A7:J7'],
270                                 'markers': True})
271
272worksheet1.write(row, 1, text)
273row += 1
274
275
276###############################################################################
277#
278# Create a second worksheet with data to plot.
279#
280worksheet2.set_column('A:J', 11)
281
282data = [
283
284    # Simple line data.
285    [-2, 2, 3, -1, 0, -2, 3, 2, 1, 0],
286
287    # Simple column data.
288    [30, 20, 33, 20, 15, 5, 5, 15, 10, 15],
289
290    # Simple win/loss data.
291    [1, 1, -1, -1, 1, -1, 1, 1, 1, -1],
292
293    # Unbalanced histogram.
294    [5, 6, 7, 10, 15, 20, 30, 50, 70, 100],
295
296    # Data for the grouped sparkline example.
297    [-2, 2, 3, -1, 0, -2, 3, 2, 1, 0],
298    [3, -1, 0, -2, 3, 2, 1, 0, 2, 1],
299    [0, -2, 3, 2, 1, 0, 1, 2, 3, 1],
300
301]
302
303# Write the sample data to the worksheet.
304worksheet2.write_row('A1', data[0])
305worksheet2.write_row('A2', data[1])
306worksheet2.write_row('A3', data[2])
307worksheet2.write_row('A4', data[3])
308worksheet2.write_row('A5', data[4])
309worksheet2.write_row('A6', data[5])
310worksheet2.write_row('A7', data[6])
311
312workbook.close()
313