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