Example: Sparklines (Advanced)
This example shows the majority of options that can be applied to sparklines.
Sparklines are small charts that fit in a single cell and are used to show trends in data.
See the Working with Sparklines method for more details.
###############################################################################
#
# Example of how to add sparklines to an XlsxWriter file with Python.
#
# Sparklines are small charts that fit in a single cell and are
# used to show trends in data. This example shows the majority of
# options that can be applied to sparklines.
#
# Copyright 2013-2019, John McNamara, jmcnamara@cpan.org
#
import xlsxwriter
workbook = xlsxwriter.Workbook('sparklines2.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
row = 1
# Set the columns widths to make the output clearer.
worksheet1.set_column('A:A', 14)
worksheet1.set_column('B:B', 50)
worksheet1.set_zoom(150)
# Headings.
worksheet1.write('A1', 'Sparkline', bold)
worksheet1.write('B1', 'Description', bold)
###############################################################################
#
text = 'A default "line" sparkline.'
worksheet1.add_sparkline('A2', {'range': 'Sheet2!A1:J1'})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'A default "column" sparkline.'
worksheet1.add_sparkline('A3', {'range': 'Sheet2!A2:J2',
'type': 'column'})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'A default "win/loss" sparkline.'
worksheet1.add_sparkline('A4', {'range': 'Sheet2!A3:J3',
'type': 'win_loss'})
worksheet1.write(row, 1, text)
row += 2
###############################################################################
#
text = 'Line with markers.'
worksheet1.add_sparkline('A6', {'range': 'Sheet2!A1:J1',
'markers': True})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Line with high and low points.'
worksheet1.add_sparkline('A7', {'range': 'Sheet2!A1:J1',
'high_point': True,
'low_point': True})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Line with first and last point markers.'
worksheet1.add_sparkline('A8', {'range': 'Sheet2!A1:J1',
'first_point': True,
'last_point': True})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Line with negative point markers.'
worksheet1.add_sparkline('A9', {'range': 'Sheet2!A1:J1',
'negative_points': True})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Line with axis.'
worksheet1.add_sparkline('A10', {'range': 'Sheet2!A1:J1',
'axis': True})
worksheet1.write(row, 1, text)
row += 2
###############################################################################
#
text = 'Column with default style (1).'
worksheet1.add_sparkline('A12', {'range': 'Sheet2!A2:J2',
'type': 'column'})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Column with style 2.'
worksheet1.add_sparkline('A13', {'range': 'Sheet2!A2:J2',
'type': 'column',
'style': 2})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Column with style 3.'
worksheet1.add_sparkline('A14', {'range': 'Sheet2!A2:J2',
'type': 'column',
'style': 3})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Column with style 4.'
worksheet1.add_sparkline('A15', {'range': 'Sheet2!A2:J2',
'type': 'column',
'style': 4})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Column with style 5.'
worksheet1.add_sparkline('A16', {'range': 'Sheet2!A2:J2',
'type': 'column',
'style': 5})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Column with style 6.'
worksheet1.add_sparkline('A17', {'range': 'Sheet2!A2:J2',
'type': 'column',
'style': 6})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Column with a user defined color.'
worksheet1.add_sparkline('A18', {'range': 'Sheet2!A2:J2',
'type': 'column',
'series_color': '#E965E0'})
worksheet1.write(row, 1, text)
row += 2
###############################################################################
#
text = 'A win/loss sparkline.'
worksheet1.add_sparkline('A20', {'range': 'Sheet2!A3:J3',
'type': 'win_loss'})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'A win/loss sparkline with negative points highlighted.'
worksheet1.add_sparkline('A21', {'range': 'Sheet2!A3:J3',
'type': 'win_loss',
'negative_points': True})
worksheet1.write(row, 1, text)
row += 2
###############################################################################
#
text = 'A left to right column (the default).'
worksheet1.add_sparkline('A23', {'range': 'Sheet2!A4:J4',
'type': 'column',
'style': 20})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'A right to left column.'
worksheet1.add_sparkline('A24', {'range': 'Sheet2!A4:J4',
'type': 'column',
'style': 20,
'reverse': True})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
text = 'Sparkline and text in one cell.'
worksheet1.add_sparkline('A25', {'range': 'Sheet2!A4:J4',
'type': 'column',
'style': 20})
worksheet1.write(row, 0, 'Growth')
worksheet1.write(row, 1, text)
row += 2
###############################################################################
#
text = 'A grouped sparkline. Changes are applied to all three.'
worksheet1.add_sparkline('A27', {'location': ['A27', 'A28', 'A29'],
'range': ['Sheet2!A5:J5',
'Sheet2!A6:J6',
'Sheet2!A7:J7'],
'markers': True})
worksheet1.write(row, 1, text)
row += 1
###############################################################################
#
# Create a second worksheet with data to plot.
#
worksheet2.set_column('A:J', 11)
data = [
# Simple line data.
[-2, 2, 3, -1, 0, -2, 3, 2, 1, 0],
# Simple column data.
[30, 20, 33, 20, 15, 5, 5, 15, 10, 15],
# Simple win/loss data.
[1, 1, -1, -1, 1, -1, 1, 1, 1, -1],
# Unbalanced histogram.
[5, 6, 7, 10, 15, 20, 30, 50, 70, 100],
# Data for the grouped sparkline example.
[-2, 2, 3, -1, 0, -2, 3, 2, 1, 0],
[3, -1, 0, -2, 3, 2, 1, 0, 2, 1],
[0, -2, 3, 2, 1, 0, 1, 2, 3, 1],
]
# Write the sample data to the worksheet.
worksheet2.write_row('A1', data[0])
worksheet2.write_row('A2', data[1])
worksheet2.write_row('A3', data[2])
worksheet2.write_row('A4', data[3])
worksheet2.write_row('A5', data[4])
worksheet2.write_row('A6', data[5])
worksheet2.write_row('A7', data[6])
workbook.close()