# Create some data to write.
my_data = [
['Name', 'Password', 'City'],
['Sara', '$5%^6&', 'Rome'],
['Michele', '123abc', 'Milano'],
['Maria', 'juvexme', 'Torino'],
['Paolo', 'qwerty', 'Fano']
]
# Write the data. Note that write_row() calls write() so this will work as
# expected.
for row_num, row_data in enumerate(my_data):
worksheet.write_row(row_num, 0, row_data)
workbook.close()
Example of how to add sparklines to a XlsxWriter worksheet.
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 a Python XlsxWriter file.
#
# Sparklines are small charts that fit in a single cell and are
# used to show trends in data.
#
# See sparklines2.py for examples of more complex sparkline formatting.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('sparklines1.xlsx')
worksheet = workbook.add_worksheet()
# Some sample data to plot.
data = [
[-2, 2, 3, -1, 0],
[30, 20, 33, 20, 15],
[1, -1, -1, 1, -1],
]
# Write the sample data to the worksheet.
worksheet.write_row('A1', data[0])
worksheet.write_row('A2', data[1])
worksheet.write_row('A3', data[2])
# Add a line sparkline (the default) with markers.
worksheet.add_sparkline('F1', {'range': 'Sheet1!A1:E1',
'markers': True})
# Add a column sparkline with non-default style.
worksheet.add_sparkline('F2', {'range': 'Sheet1!A2:E2',
'type': 'column',
'style': 12})
# Add a win/loss sparkline with negative values highlighted.
worksheet.add_sparkline('F3', {'range': 'Sheet1!A3:E3',
'type': 'win_loss',
'negative_points': True})
workbook.close()
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-2020, John McNamara<EMAIL_ADDRESS>#
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()
A simple example of adding cell comments to a worksheet. For more details see Working with Cell Comments.
###############################################################################
#
# An example of writing cell comments to a worksheet using Python and
# XlsxWriter.
#
# For more advanced comment options see comments2.py.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('comments1.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello')
worksheet.write_comment('A1', 'This is a comment')
workbook.close()
Another example of adding cell comments to a worksheet. This example demonstrates most of the available comment formatting options. For more details see Working with Cell Comments.
###############################################################################
#
# An example of writing cell comments to a worksheet using Python and
# XlsxWriter.
#
# Each of the worksheets demonstrates different features of cell comments.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('comments.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet4 = workbook.add_worksheet()
worksheet5 = workbook.add_worksheet()
worksheet6 = workbook.add_worksheet()
worksheet7 = workbook.add_worksheet()
worksheet8 = workbook.add_worksheet()
text_wrap = workbook.add_format({'text_wrap': 1, 'valign': 'top'})
###############################################################################
#
# Example 1. Demonstrates a simple cell comments without formatting.
#
# Set up some formatting.
worksheet1.set_column('C:C', 25)
worksheet1.set_row(2, 50)
# Simple ASCII string.
cell_text = 'Hold the mouse over this cell to see the comment.'
comment = 'This is a comment.'
worksheet1.write('C3', cell_text, text_wrap)
worksheet1.write_comment('C3', comment)
###############################################################################
#
# Example 2. Demonstrates visible and hidden comments.
#
# Set up some formatting.
worksheet2.set_column('C:C', 25)
worksheet2.set_row(2, 50)
worksheet2.set_row(5, 50)
cell_text = 'This cell comment is visible.'
comment = 'Hello.'
worksheet2.write('C3', cell_text, text_wrap)
worksheet2.write_comment('C3', comment, {'visible': True})
cell_text = "This cell comment isn't visible (the default)."
worksheet2.write('C6', cell_text, text_wrap)
worksheet2.write_comment('C6', comment)
###############################################################################
#
# Example 3. Demonstrates visible and hidden comments set at the worksheet
# level.
#
# Set up some formatting.
worksheet3.set_column('C:C', 25)
worksheet3.set_row(2, 50)
worksheet3.set_row(5, 50)
worksheet3.set_row(8, 50)
# Make all comments on the worksheet visible.
worksheet3.show_comments()
cell_text = 'This cell comment is visible, explicitly.'
comment = 'Hello.'
worksheet3.write('C3', cell_text, text_wrap)
worksheet3.write_comment('C3', comment, {'visible': True})
cell_text = 'This cell comment is also visible because of show_comments().'
worksheet3.write('C6', cell_text, text_wrap)
worksheet3.write_comment('C6', comment)
cell_text = 'However, we can still override it locally.'
worksheet3.write('C9', cell_text, text_wrap)
worksheet3.write_comment('C9', comment, {'visible': False})
###############################################################################
#
# Example 4. Demonstrates changes to the comment box dimensions.
#
# Set up some formatting.
worksheet4.set_column('C:C', 25)
worksheet4.set_row(2, 50)
worksheet4.set_row(5, 50)
worksheet4.set_row(8, 50)
worksheet4.set_row(15, 50)
worksheet4.set_row(18, 50)
worksheet4.show_comments()
cell_text = 'This cell comment is default size.'
comment = 'Hello.'
worksheet4.write('C3', cell_text, text_wrap)
worksheet4.write_comment('C3', comment)
cell_text = 'This cell comment is twice as wide.'
worksheet4.write('C6', cell_text, text_wrap)
worksheet4.write_comment('C6', comment, {'x_scale': 2})
cell_text = 'This cell comment is twice as high.'
worksheet4.write('C9', cell_text, text_wrap)
worksheet4.write_comment('C9', comment, {'y_scale': 2})
cell_text = 'This cell comment is scaled in both directions.'
worksheet4.write('C16', cell_text, text_wrap)
worksheet4.write_comment('C16', comment, {'x_scale': 1.2, 'y_scale': 0.5})
cell_text = 'This cell comment has width and height specified in pixels.'
worksheet4.write('C19', cell_text, text_wrap)
worksheet4.write_comment('C19', comment, {'width': 200, 'height': 50})
###############################################################################
#
# Example 5. Demonstrates changes to the cell comment position.
#
worksheet5.set_column('C:C', 25)
worksheet5.set_row(2, 50)
worksheet5.set_row(5, 50)
worksheet5.set_row(8, 50)
worksheet5.set_row(11, 50)
worksheet5.show_comments()
cell_text = 'This cell comment is in the default position.'
comment = 'Hello.'
worksheet5.write('C3', cell_text, text_wrap)
worksheet5.write_comment('C3', comment)
cell_text = 'This cell comment has been moved to another cell.'
worksheet5.write('C6', cell_text, text_wrap)
worksheet5.write_comment('C6', comment, {'start_cell': 'E4'})
cell_text = 'This cell comment has been moved to another cell.'
worksheet5.write('C9', cell_text, text_wrap)
worksheet5.write_comment('C9', comment, {'start_row': 8, 'start_col': 4})
cell_text = 'This cell comment has been shifted within its default cell.'
worksheet5.write('C12', cell_text, text_wrap)
worksheet5.write_comment('C12', comment, {'x_offset': 30, 'y_offset': 12})
###############################################################################
#
# Example 6. Demonstrates changes to the comment background color.
#
worksheet6.set_column('C:C', 25)
worksheet6.set_row(2, 50)
worksheet6.set_row(5, 50)
worksheet6.set_row(8, 50)
worksheet6.show_comments()
cell_text = 'This cell comment has a different color.'
comment = 'Hello.'
worksheet6.write('C3', cell_text, text_wrap)
worksheet6.write_comment('C3', comment, {'color': 'green'})
cell_text = 'This cell comment has the default color.'
worksheet6.write('C6', cell_text, text_wrap)
worksheet6.write_comment('C6', comment)
cell_text = 'This cell comment has a different color.'
worksheet6.write('C9', cell_text, text_wrap)
worksheet6.write_comment('C9', comment, {'color': '#CCFFCC'})
###############################################################################
#
# Example 7. Demonstrates how to set the cell comment author.
#
worksheet7.set_column('C:C', 30)
worksheet7.set_row(2, 50)
worksheet7.set_row(5, 50)
author = ''
cell = 'C3'
cell_text = ("Move the mouse over this cell and you will see 'Cell commented "
"by (blank)' in the status bar at the bottom")
comment = 'Hello.'
worksheet7.write(cell, cell_text, text_wrap)
worksheet7.write_comment(cell, comment)
author = 'Python'
cell = 'C6'
cell_text = ("Move the mouse over this cell and you will see 'Cell commented "
"by Python' in the status bar at the bottom")
worksheet7.write(cell, cell_text, text_wrap)
worksheet7.write_comment(cell, comment, {'author': author})
###############################################################################
#
# Example 8. Demonstrates the need to explicitly set the row height.
#
# Set up some formatting.
worksheet8.set_column('C:C', 25)
worksheet8.set_row(2, 80)
worksheet8.show_comments()
cell_text = ('The height of this row has been adjusted explicitly using '
'set_row(). The size of the comment box is adjusted '
'accordingly by XlsxWriter.')
comment = 'Hello.'
worksheet8.write('C3', cell_text, text_wrap)
worksheet8.write_comment('C3', comment)
cell_text = ('The height of this row has been adjusted by Excel due to the '
'text wrap property being set. Unfortunately this means that '
'the height of the row is unknown to XlsxWriter at run time '
"and thus the comment box is stretched as well.\n\n"
'Use set_row() to specify the row height explicitly to avoid '
'this problem.')
worksheet8.write('C6', cell_text, text_wrap)
worksheet8.write_comment('C6', comment)
workbook.close()
The following is an example of how to insert and format textboxes in a
worksheet, see insert_textbox()
and Working with Textboxes for
more details.
#######################################################################
#
# An example of inserting textboxes into an Excel worksheet using
# Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('textbox.xlsx')
worksheet = workbook.add_worksheet()
row = 4
col = 1
# The examples below show different textbox options and formatting. In each
# example the text describes the formatting.
# Example
text = 'A simple textbox with some text'
worksheet.insert_textbox(row, col, text)
row += 10
# Example
text = 'A textbox with changed dimensions'
options = {
'width': 256,
'height': 100,
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'A textbox with an offset in the cell'
options = {
'x_offset': 10,
'y_offset': 10,
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'A textbox with scaling'
options = {
'x_scale': 1.5,
'y_scale': 0.8,
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'A textbox with some long text that wraps around onto several lines'
worksheet.insert_textbox(row, col, text)
row += 10
# Example
text = 'A textbox\nwith some\nnewlines\n\nand paragraphs'
worksheet.insert_textbox(row, col, text)
row += 10
# Example
text = 'A textbox with a solid fill background'
options = {
'fill': {'color': 'red'},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'A textbox with a no fill background'
options = {
'fill': {'none': True},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'A textbox with a gradient fill background'
options = {
'gradient': {'colors': ['#DDEBCF',
'#9CB86E',
'#156B13']},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'A textbox with a user defined border line'
options = {
'border': {'color': 'red',
'width': 3,
'dash_type': 'round_dot'},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'A textbox with no border line'
options = {
'border': {'none': True},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Default alignment: top - left'
worksheet.insert_textbox(row, col, text)
row += 10
# Example
text = 'Alignment: top - center'
options = {
'align': {'horizontal': 'center'},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Alignment: middle - center'
options = {
'align': {'vertical': 'middle',
'horizontal': 'center'},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Font properties: bold'
options = {
'font': {'bold': True},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Font properties: various'
options = {
'font': {'bold': True},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Font properties: various'
options = {
'font': {'bold': True,
'italic': True,
'underline': True,
'name': 'Arial',
'color': 'red',
'size': 12}
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Some text in a textbox with formatting'
options = {
'font': {'color': 'white'},
'align': {'vertical': 'middle',
'horizontal': 'center'
},
'gradient': {'colors': ['red', 'blue']},
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = ''
options = {
'textlink': '=$F$185',
}
worksheet.write('F185', 'Text in a cell')
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Text rotated up'
options = {
'text_rotation': 90
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Text rotated down'
options = {
'text_rotation': -90
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Text rotated vertically'
options = {
'text_rotation': 270
}
worksheet.insert_textbox(row, col, text, options)
row += 10
# Example
text = 'Textbox with hyperlink'
options = {
'url': 'https://github.com/jmcnamara',
'tip': 'GitHub'
}
worksheet.insert_textbox(row, col, text, options)
row += 10
workbook.close()
Examples of how use XlsxWriter to generate Excel outlines and grouping. See also Working with Outlines and Grouping.
###############################################################################
#
# Example of how use Python and XlsxWriter to generate Excel outlines and
# grouping.
#
# Excel allows you to group rows or columns so that they can be hidden or
# displayed with a single mouse click. This feature is referred to as outlines.
#
# Outlines can reduce complex data down to a few salient sub-totals or
# summaries.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Create a new workbook and add some worksheets
workbook = xlsxwriter.Workbook('outline.xlsx')
worksheet1 = workbook.add_worksheet('Outlined Rows')
worksheet2 = workbook.add_worksheet('Collapsed Rows')
worksheet3 = workbook.add_worksheet('Outline Columns')
worksheet4 = workbook.add_worksheet('Outline levels')
# Add a general format
bold = workbook.add_format({'bold': 1})
###############################################################################
#
# Example 1: A worksheet with outlined rows. It also includes SUBTOTAL()
# functions so that it looks like the type of automatic outlines that are
# generated when you use the Excel Data->SubTotals menu item.
#
# For outlines the important parameters are 'level' and 'hidden'. Rows with
# the same 'level' are grouped together. The group will be collapsed if
# 'hidden' is enabled. The parameters 'height' and 'cell_format' are assigned
# default values if they are None.
#
worksheet1.set_row(1, None, None, {'level': 2})
worksheet1.set_row(2, None, None, {'level': 2})
worksheet1.set_row(3, None, None, {'level': 2})
worksheet1.set_row(4, None, None, {'level': 2})
worksheet1.set_row(5, None, None, {'level': 1})
worksheet1.set_row(6, None, None, {'level': 2})
worksheet1.set_row(7, None, None, {'level': 2})
worksheet1.set_row(8, None, None, {'level': 2})
worksheet1.set_row(9, None, None, {'level': 2})
worksheet1.set_row(10, None, None, {'level': 1})
# Adjust the column width for clarity
worksheet1.set_column('A:A', 20)
# Add the data, labels and formulas
worksheet1.write('A1', 'Region', bold)
worksheet1.write('A2', 'North')
worksheet1.write('A3', 'North')
worksheet1.write('A4', 'North')
worksheet1.write('A5', 'North')
worksheet1.write('A6', 'North Total', bold)
worksheet1.write('B1', 'Sales', bold)
worksheet1.write('B2', 1000)
worksheet1.write('B3', 1200)
worksheet1.write('B4', 900)
worksheet1.write('B5', 1200)
worksheet1.write('B6', '=SUBTOTAL(9,B2:B5)', bold)
worksheet1.write('A7', 'South')
worksheet1.write('A8', 'South')
worksheet1.write('A9', 'South')
worksheet1.write('A10', 'South')
worksheet1.write('A11', 'South Total', bold)
worksheet1.write('B7', 400)
worksheet1.write('B8', 600)
worksheet1.write('B9', 500)
worksheet1.write('B10', 600)
worksheet1.write('B11', '=SUBTOTAL(9,B7:B10)', bold)
worksheet1.write('A12', 'Grand Total', bold)
worksheet1.write('B12', '=SUBTOTAL(9,B2:B10)', bold)
###############################################################################
#
# Example 2: A worksheet with outlined rows. This is the same as the
# previous example except that the rows are collapsed.
# Note: We need to indicate the rows that contains the collapsed symbol '+'
# with the optional parameter, 'collapsed'. The group will be then be
# collapsed if 'hidden' is True.
#
worksheet2.set_row(1, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(2, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(3, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(4, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(5, None, None, {'level': 1, 'hidden': True})
worksheet2.set_row(6, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(7, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(8, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(9, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(10, None, None, {'level': 1, 'hidden': True})
worksheet2.set_row(11, None, None, {'collapsed': True})
# Adjust the column width for clarity
worksheet2.set_column('A:A', 20)
# Add the data, labels and formulas
worksheet2.write('A1', 'Region', bold)
worksheet2.write('A2', 'North')
worksheet2.write('A3', 'North')
worksheet2.write('A4', 'North')
worksheet2.write('A5', 'North')
worksheet2.write('A6', 'North Total', bold)
worksheet2.write('B1', 'Sales', bold)
worksheet2.write('B2', 1000)
worksheet2.write('B3', 1200)
worksheet2.write('B4', 900)
worksheet2.write('B5', 1200)
worksheet2.write('B6', '=SUBTOTAL(9,B2:B5)', bold)
worksheet2.write('A7', 'South')
worksheet2.write('A8', 'South')
worksheet2.write('A9', 'South')
worksheet2.write('A10', 'South')
worksheet2.write('A11', 'South Total', bold)
worksheet2.write('B7', 400)
worksheet2.write('B8', 600)
worksheet2.write('B9', 500)
worksheet2.write('B10', 600)
worksheet2.write('B11', '=SUBTOTAL(9,B7:B10)', bold)
worksheet2.write('A12', 'Grand Total', bold)
worksheet2.write('B12', '=SUBTOTAL(9,B2:B10)', bold)
###############################################################################
#
# Example 3: Create a worksheet with outlined columns.
#
data = [
['Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Total'],
['North', 50, 20, 15, 25, 65, 80, '=SUM(B2:G2)'],
['South', 10, 20, 30, 50, 50, 50, '=SUM(B3:G3)'],
['East', 45, 75, 50, 15, 75, 100, '=SUM(B4:G4)'],
['West', 15, 15, 55, 35, 20, 50, '=SUM(B5:G5)']]
# Add bold format to the first row.
worksheet3.set_row(0, None, bold)
# Set column formatting and the outline level.
worksheet3.set_column('A:A', 10, bold)
worksheet3.set_column('B:G', 5, None, {'level': 1})
worksheet3.set_column('H:H', 10)
# Write the data and a formula
for row, data_row in enumerate(data):
worksheet3.write_row(row, 0, data_row)
worksheet3.write('H6', '=SUM(H2:H5)', bold)
###############################################################################
#
# Example 4: Show all possible outline levels.
#
levels = [
'Level 1', 'Level 2', 'Level 3', 'Level 4', 'Level 5', 'Level 6',
'Level 7', 'Level 6', 'Level 5', 'Level 4', 'Level 3', 'Level 2',
'Level 1']
worksheet4.write_column('A1', levels)
worksheet4.set_row(0, None, None, {'level': 1})
worksheet4.set_row(1, None, None, {'level': 2})
worksheet4.set_row(2, None, None, {'level': 3})
worksheet4.set_row(3, None, None, {'level': 4})
worksheet4.set_row(4, None, None, {'level': 5})
worksheet4.set_row(5, None, None, {'level': 6})
worksheet4.set_row(6, None, None, {'level': 7})
worksheet4.set_row(7, None, None, {'level': 6})
worksheet4.set_row(8, None, None, {'level': 5})
worksheet4.set_row(9, None, None, {'level': 4})
worksheet4.set_row(10, None, None, {'level': 3})
worksheet4.set_row(11, None, None, {'level': 2})
worksheet4.set_row(12, None, None, {'level': 1})
workbook.close()
Examples of how use XlsxWriter to generate Excel outlines and grouping. These examples focus mainly on collapsed outlines. See also Working with Outlines and Grouping.
###############################################################################
#
# Example of how to use Python and XlsxWriter to generate Excel outlines and
# grouping.
#
# These examples focus mainly on collapsed outlines. See also the
# outlines.py example program for more general examples.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Create a new workbook and add some worksheets
workbook = xlsxwriter.Workbook('outline_collapsed.xlsx')
worksheet1 = workbook.add_worksheet('Outlined Rows')
worksheet2 = workbook.add_worksheet('Collapsed Rows 1')
worksheet3 = workbook.add_worksheet('Collapsed Rows 2')
worksheet4 = workbook.add_worksheet('Collapsed Rows 3')
worksheet5 = workbook.add_worksheet('Outline Columns')
worksheet6 = workbook.add_worksheet('Collapsed Columns')
# Add a general format
bold = workbook.add_format({'bold': 1})
# This function will generate the same data and sub-totals on each worksheet.
# Used in the first 4 examples.
#
def create_sub_totals(worksheet):
# Adjust the column width for clarity.
worksheet.set_column('A:A', 20)
# Add the data, labels and formulas.
worksheet.write('A1', 'Region', bold)
worksheet.write('A2', 'North')
worksheet.write('A3', 'North')
worksheet.write('A4', 'North')
worksheet.write('A5', 'North')
worksheet.write('A6', 'North Total', bold)
worksheet.write('B1', 'Sales', bold)
worksheet.write('B2', 1000)
worksheet.write('B3', 1200)
worksheet.write('B4', 900)
worksheet.write('B5', 1200)
worksheet.write('B6', '=SUBTOTAL(9,B2:B5)', bold)
worksheet.write('A7', 'South')
worksheet.write('A8', 'South')
worksheet.write('A9', 'South')
worksheet.write('A10', 'South')
worksheet.write('A11', 'South Total', bold)
worksheet.write('B7', 400)
worksheet.write('B8', 600)
worksheet.write('B9', 500)
worksheet.write('B10', 600)
worksheet.write('B11', '=SUBTOTAL(9,B7:B10)', bold)
worksheet.write('A12', 'Grand Total', bold)
worksheet.write('B12', '=SUBTOTAL(9,B2:B10)', bold)
###############################################################################
#
# Example 1: A worksheet with outlined rows. It also includes SUBTOTAL()
# functions so that it looks like the type of automatic outlines that are
# generated when you use the Excel Data->SubTotals menu item.
#
worksheet1.set_row(1, None, None, {'level': 2})
worksheet1.set_row(2, None, None, {'level': 2})
worksheet1.set_row(3, None, None, {'level': 2})
worksheet1.set_row(4, None, None, {'level': 2})
worksheet1.set_row(5, None, None, {'level': 1})
worksheet1.set_row(6, None, None, {'level': 2})
worksheet1.set_row(7, None, None, {'level': 2})
worksheet1.set_row(8, None, None, {'level': 2})
worksheet1.set_row(9, None, None, {'level': 2})
worksheet1.set_row(10, None, None, {'level': 1})
# Write the sub-total data that is common to the row examples.
create_sub_totals(worksheet1)
###############################################################################
#
# Example 2: Create a worksheet with collapsed outlined rows.
# This is the same as the example 1 except that the all rows are collapsed.
# Note: We need to indicate the rows that contains the collapsed symbol '+'
# with the optional parameter, 'collapsed'.
#
worksheet2.set_row(1, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(2, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(3, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(4, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(5, None, None, {'level': 1, 'hidden': True})
worksheet2.set_row(6, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(7, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(8, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(9, None, None, {'level': 2, 'hidden': True})
worksheet2.set_row(10, None, None, {'level': 1, 'hidden': True})
worksheet2.set_row(11, None, None, {'collapsed': True})
# Write the sub-total data that is common to the row examples.
create_sub_totals(worksheet2)
###############################################################################
#
# Example 3: Create a worksheet with collapsed outlined rows.
# Same as the example 1 except that the two sub-totals are collapsed.
#
worksheet3.set_row(1, None, None, {'level': 2, 'hidden': True})
worksheet3.set_row(2, None, None, {'level': 2, 'hidden': True})
worksheet3.set_row(3, None, None, {'level': 2, 'hidden': True})
worksheet3.set_row(4, None, None, {'level': 2, 'hidden': True})
worksheet3.set_row(5, None, None, {'level': 1, 'collapsed': True})
worksheet3.set_row(6, None, None, {'level': 2, 'hidden': True})
worksheet3.set_row(7, None, None, {'level': 2, 'hidden': True})
worksheet3.set_row(8, None, None, {'level': 2, 'hidden': True})
worksheet3.set_row(9, None, None, {'level': 2, 'hidden': True})
worksheet3.set_row(10, None, None, {'level': 1, 'collapsed': True})
# Write the sub-total data that is common to the row examples.
create_sub_totals(worksheet3)
###############################################################################
#
# Example 4: Create a worksheet with outlined rows.
# Same as the example 1 except that the two sub-totals are collapsed.
#
worksheet4.set_row(1, None, None, {'level': 2, 'hidden': True})
worksheet4.set_row(2, None, None, {'level': 2, 'hidden': True})
worksheet4.set_row(3, None, None, {'level': 2, 'hidden': True})
worksheet4.set_row(4, None, None, {'level': 2, 'hidden': True})
worksheet4.set_row(5, None, None, {'level': 1, 'hidden': True,
'collapsed': True})
worksheet4.set_row(6, None, None, {'level': 2, 'hidden': True})
worksheet4.set_row(7, None, None, {'level': 2, 'hidden': True})
worksheet4.set_row(8, None, None, {'level': 2, 'hidden': True})
worksheet4.set_row(9, None, None, {'level': 2, 'hidden': True})
worksheet4.set_row(10, None, None, {'level': 1, 'hidden': True,
'collapsed': True})
worksheet4.set_row(11, None, None, {'collapsed': True})
# Write the sub-total data that is common to the row examples.
create_sub_totals(worksheet4)
###############################################################################
#
# Example 5: Create a worksheet with outlined columns.
#
data = [
['Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Total'],
['North', 50, 20, 15, 25, 65, 80, '=SUM(B2:G2)'],
['South', 10, 20, 30, 50, 50, 50, '=SUM(B3:G3)'],
['East', 45, 75, 50, 15, 75, 100, '=SUM(B4:G4)'],
['West', 15, 15, 55, 35, 20, 50, '=SUM(B5:G5)']]
# Add bold format to the first row.
worksheet5.set_row(0, None, bold)
# Set column formatting and the outline level.
worksheet5.set_column('A:A', 10, bold)
worksheet5.set_column('B:G', 5, None, {'level': 1})
worksheet5.set_column('H:H', 10)
# Write the data and a formula.
for row, data_row in enumerate(data):
worksheet5.write_row(row, 0, data_row)
worksheet5.write('H6', '=SUM(H2:H5)', bold)
###############################################################################
#
# Example 6: Create a worksheet with collapsed outlined columns.
# This is the same as the previous example except with collapsed columns.
#
# Reuse the data from the previous example.
# Add bold format to the first row.
worksheet6.set_row(0, None, bold)
# Set column formatting and the outline level.
worksheet6.set_column('A:A', 10, bold)
worksheet6.set_column('B:G', 5, None, {'level': 1, 'hidden': True})
worksheet6.set_column('H:H', 10, None, {'collapsed': True})
# Write the data and a formula.
for row, data_row in enumerate(data):
worksheet6.write_row(row, 0, data_row)
worksheet6.write('H6', '=SUM(H2:H5)', bold)
workbook.close()
This program is an example setting document properties. See the
set_properties()
workbook method for more details.
##############################################################################
#
# An example of adding document properties to a XlsxWriter file.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('doc_properties.xlsx')
worksheet = workbook.add_worksheet()
workbook.set_properties({
'title': 'This is an example spreadsheet',
'subject': 'With document properties',
'author': 'John McNamara',
'manager': 'Dr. Heinz Doofenshmirtz',
'company': 'of Wolves',
'category': 'Example spreadsheets',
'keywords': 'Sample, Example, Properties',
'comments': 'Created with Python and XlsxWriter',
'status': 'Quo',
})
worksheet.set_column('A:A', 70)
worksheet.write('A1', "Select 'Workbook Properties' to see properties.")
workbook.close()
To write Unicode text in UTF-8 to a xlsxwriter file in Python 2:
u''
to indicate a Unicode string.###############################################################################
# _*_ coding: utf-8
#
# A simple Unicode spreadsheet in Python 2 using the XlsxWriter Python module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
# To write Unicode text in UTF-8 to a xlsxwriter file in Python 2:
#
# 1. Encode the file as UTF-8.
# 2. Include the "coding" directive at the start of the file.
# 3. Use u'' to indicate a Unicode string.
import xlsxwriter
workbook = xlsxwriter.Workbook('unicode_python2.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('B3', u'Это фраза на русском!')
workbook.close()
To write Unicode text in UTF-8 to a xlsxwriter file in Python 3:
###############################################################################
#
#
# A simple Unicode spreadsheet in Python 3 using the XlsxWriter Python module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
# To write Unicode text in UTF-8 to a xlsxwriter file in Python 3:
#
# 1. Encode the file as UTF-8.
#
#
import xlsxwriter
workbook = xlsxwriter.Workbook('unicode_python3.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('B3', 'Это фраза на русском!')
workbook.close()
This program is an example of reading in data from a UTF-8 encoded text file and converting it to a worksheet.
The main trick is to ensure that the data read in is converted to UTF-8 within the Python program. The XlsxWriter module will then take care of writing the encoding to the Excel file.
The encoding of the input data shouldn’t matter once it can be converted
to UTF-8 via the codecs
module.
##############################################################################
#
# A simple example of converting some Unicode text to an Excel file using
# the XlsxWriter Python module.
#
# This example generates a spreadsheet with some Polish text from a file
# with UTF8 encoded text.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import codecs
import xlsxwriter
# Open the input file with the correct encoding.
textfile = codecs.open('unicode_polish_utf8.txt', 'r', 'utf-8')
# Create an new Excel file and convert the text data.
workbook = xlsxwriter.Workbook('unicode_polish_utf8.xlsx')
worksheet = workbook.add_worksheet()
# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 50)
# Start from the first cell.
row = 0
col = 0
# Read the text file and write it to the worksheet.
for line in textfile:
# Ignore the comments in the text file.
if line.startswith('#'):
continue
# Write any other lines to the worksheet.
worksheet.write(row, col, line.rstrip("\n"))
row += 1
workbook.close()
This program is an example of reading in data from a Shift JIS encoded text file and converting it to a worksheet.
The main trick is to ensure that the data read in is converted to UTF-8 within the Python program. The XlsxWriter module will then take care of writing the encoding to the Excel file.
The encoding of the input data shouldn’t matter once it can be converted
to UTF-8 via the codecs
module.
##############################################################################
#
# A simple example of converting some Unicode text to an Excel file using
# the XlsxWriter Python module.
#
# This example generates a spreadsheet with some Japanese text from a file
# with Shift-JIS encoded text.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import codecs
import xlsxwriter
# Open the input file with the correct encoding.
textfile = codecs.open('unicode_shift_jis.txt', 'r', 'shift_jis')
# Create an new Excel file and convert the text data.
workbook = xlsxwriter.Workbook('unicode_shift_jis.xlsx')
worksheet = workbook.add_worksheet()
# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 50)
# Start from the first cell.
row = 0
col = 0
# Read the text file and write it to the worksheet.
for line in textfile:
# Ignore the comments in the text file.
if line.startswith('#'):
continue
# Write any other lines to the worksheet.
worksheet.write(row, col, line.rstrip("\n"))
row += 1
workbook.close()
This program is an example of setting worksheet tab colors. See the
set_tab_color()
method for more details.
#######################################################################
#
# Example of how to set Excel worksheet tab colors using Python
# and the XlsxWriter module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('tab_colors.xlsx')
# Set up some worksheets.
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet4 = workbook.add_worksheet()
# Set tab colors
worksheet1.set_tab_color('red')
worksheet2.set_tab_color('green')
worksheet3.set_tab_color('#FF9900') # Orange
# worksheet4 will have the default color.
workbook.close()
Example of how to set diagonal borders in a cell.
See set_diag_border()
, set_diag_type()
and set_diag_border()
for details.
##############################################################################
#
# A simple formatting example that demonstrates how to add diagonal cell
# borders with XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('diag_border.xlsx')
worksheet = workbook.add_worksheet()
format1 = workbook.add_format({'diag_type': 1})
format2 = workbook.add_format({'diag_type': 2})
format3 = workbook.add_format({'diag_type': 3})
format4 = workbook.add_format({
'diag_type': 3,
'diag_border': 7,
'diag_color': 'red',
})
worksheet.write('B3', 'Text', format1)
worksheet.write('B6', 'Text', format2)
worksheet.write('B9', 'Text', format3)
worksheet.write('B12', 'Text', format4)
workbook.close()
This program is an example cell locking and formula hiding in an Excel
worksheet using the protect()
worksheet method and the Format
set_locked()
property.
Note, that Excel’s behavior is that all cells are locked once you set the default protection. Therefore you need to explicitly unlock cells rather than explicitly lock them.
########################################################################
#
# Example of cell locking and formula hiding in an Excel worksheet
# using Python and the XlsxWriter module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('protection.xlsx')
worksheet = workbook.add_worksheet()
# Create some cell formats with protection properties.
unlocked = workbook.add_format({'locked': False})
hidden = workbook.add_format({'hidden': True})
# Format the columns to make the text more visible.
worksheet.set_column('A:A', 40)
# Turn worksheet protection on.
worksheet.protect()
# Write a locked, unlocked and hidden cell.
worksheet.write('A1', 'Cell B1 is locked. It cannot be edited.')
worksheet.write('A2', 'Cell B2 is unlocked. It can be edited.')
worksheet.write('A3', "Cell B3 is hidden. The formula isn't visible.")
worksheet.write_formula('B1', '=1+2') # Locked by default.
worksheet.write_formula('B2', '=1+2', unlocked)
worksheet.write_formula('B3', '=1+2', hidden)
workbook.close()
This program is an example of how to hide a worksheet using the
hide()
method.
#######################################################################
#
# Example of how to hide a worksheet with XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('hide_sheet.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet1.set_column('A:A', 30)
worksheet2.set_column('A:A', 30)
worksheet3.set_column('A:A', 30)
# Hide Sheet2. It won't be visible until it is unhidden in Excel.
worksheet2.hide()
worksheet1.write('A1', 'Sheet2 is hidden')
worksheet2.write('A1', "Now it's my turn to find you!")
worksheet3.write('A1', 'Sheet2 is hidden')
workbook.close()
This program is an example of how to hide rows and columns in XlsxWriter.
An individual row can be hidden using the set_row()
method:
worksheet.set_row(0, None, None, {'hidden': True})
However, in order to hide a large number of rows, for example all the rows
after row 8, we need to use an Excel optimization to hide rows without setting
each one, (of approximately 1 million rows). To do this we use the
set_default_row()
method.
Columns don’t require this optimization and can be hidden using
set_column()
.
###############################################################################
#
# Example of how to hide rows and columns in XlsxWriter. In order to
# hide rows without setting each one, (of approximately 1 million rows),
# Excel uses an optimizations to hide all rows that don't have data.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('hide_row_col.xlsx')
worksheet = workbook.add_worksheet()
# Write some data.
worksheet.write('D1', 'Some hidden columns.')
worksheet.write('A8', 'Some hidden rows.')
# Hide all rows without data.
worksheet.set_default_row(hide_unused_rows=True)
# Set the height of empty rows that we do want to display even if it is
# the default height.
for row in range(1, 7):
worksheet.set_row(row, 15)
# Columns can be hidden explicitly. This doesn't increase the file size..
worksheet.set_column('G:XFD', None, None, {'hidden': True})
workbook.close()
Example of how to subclass the Workbook and Worksheet objects.
We also override the default worksheet.write()
method to show how that is
done.
##############################################################################
#
# Example of how to subclass the Workbook and Worksheet objects. We also
# override the default worksheet.write() method to show how that is done.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
from xlsxwriter.workbook import Workbook
from xlsxwriter.worksheet import Worksheet
from xlsxwriter.worksheet import convert_cell_args
from xlsxwriter.compatibility import str_types
class MyWorksheet(Worksheet):
"""
Subclass of the XlsxWriter Worksheet class to override the default
write() method.
"""
@convert_cell_args
def write(self, row, col, *args):
data = args[0]
# Reverse strings to demonstrate the overridden method.
if isinstance(data, str_types):
data = data[::-1]
return self.write_string(row, col, data)
else:
# Call the parent version of write() as usual for other data.
return super(MyWorksheet, self).write(row, col, *args)
class MyWorkbook(Workbook):
"""
Subclass of the XlsxWriter Workbook class to override the default
Worksheet class with our custom class.
"""
def add_worksheet(self, name=None):
# Overwrite add_worksheet() to create a MyWorksheet object.
worksheet = super(MyWorkbook, self).add_worksheet(name, MyWorksheet)
return worksheet
# Create a new MyWorkbook object.
workbook = MyWorkbook('inheritance1.xlsx')
# The code from now on will be the same as a normal "Workbook" program.
worksheet = workbook.add_worksheet()
# Write some data to test the subclassing.
worksheet.write('A1', 'Hello')
worksheet.write('A2', 'World')
worksheet.write('A3', 123)
worksheet.write('A4', 345)
workbook.close()
Example of how to subclass the Workbook and Worksheet objects. See also the simpler inheritance1.py example.
In this example we see an approach to implementing a simulated autofit in a
user application. This works by overriding the write_string()
method to
track the maximum width string in each column and then set the column widths
when closing the workbook.
Some notes on this:
excel_string_width()
function. One possibility is to use the PIL
ImageFont() method
and convert the pixel width back to a character width.set_column()
calls in close()
will override any others set by the
user. They also don’t set any column formats.##############################################################################
#
# Example of how to subclass the Workbook and Worksheet objects. See also the
# simpler inheritance1.py example.
#
# In this example we see an approach to implementing a simulated autofit in a
# user application. This works by overriding the write_string() method to
# track the maximum width string in each column and then set the column
# widths.
#
# Note: THIS ISN'T A FULLY FUNCTIONAL AUTOFIT EXAMPLE. It is only a proof or
# concept or a framework to try out solutions.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
from xlsxwriter.workbook import Workbook
from xlsxwriter.worksheet import Worksheet
from xlsxwriter.worksheet import convert_cell_args
def excel_string_width(str):
"""
Calculate the length of the string in Excel character units. This is only
an example and won't give accurate results. It will need to be replaced
by something more rigorous.
"""
string_width = len(str)
if string_width == 0:
return 0
else:
return string_width * 1.1
class MyWorksheet(Worksheet):
"""
Subclass of the XlsxWriter Worksheet class to override the default
write_string() method.
"""
@convert_cell_args
def write_string(self, row, col, string, cell_format=None):
# Overridden write_string() method to store the maximum string width
# seen in each column.
# Check that row and col are valid and store max and min values.
if self._check_dimensions(row, col):
return -1
# Set the min width for the cell. In some cases this might be the
# default width of 8.43. In this case we use 0 and adjust for all
# string widths.
min_width = 0
# Check if it the string is the largest we have seen for this column.
string_width = excel_string_width(string)
if string_width > min_width:
max_width = self.max_column_widths.get(col, min_width)
if string_width > max_width:
self.max_column_widths[col] = string_width
# Now call the parent version of write_string() as usual.
return super(MyWorksheet, self).write_string(row, col, string,
cell_format)
class MyWorkbook(Workbook):
"""
Subclass of the XlsxWriter Workbook class to override the default
Worksheet class with our custom class.
"""
def add_worksheet(self, name=None):
# Overwrite add_worksheet() to create a MyWorksheet object.
# Also add an Worksheet attribute to store the column widths.
worksheet = super(MyWorkbook, self).add_worksheet(name, MyWorksheet)
worksheet.max_column_widths = {}
return worksheet
def close(self):
# We apply the stored column widths for each worksheet when we close
# the workbook. This will override any other set_column() values that
# may have been applied. This could be handled in the application code
# below, instead.
for worksheet in self.worksheets():
for column, width in worksheet.max_column_widths.items():
worksheet.set_column(column, column, width)
return super(MyWorkbook, self).close()
# Create a new MyWorkbook object.
workbook = MyWorkbook('inheritance2.xlsx')
# The code from now on will be the same as a normal "Workbook" program.
worksheet = workbook.add_worksheet()
# Write some data to test column fitting.
worksheet.write('A1', 'F')
worksheet.write('B3', 'Foo')
worksheet.write('C1', 'F')
worksheet.write('C2', 'Fo')
worksheet.write('C3', 'Foo')
worksheet.write('C4', 'Food')
worksheet.write('D1', 'This is a longer string')
# Write a string in row-col notation.
worksheet.write(0, 4, 'Hello World')
# Write a number.
worksheet.write(0, 5, 123456)
workbook.close()
This program is an example of how to add a button connected to a VBA macro to a worksheet.
See Working with VBA Macros for more details.
#######################################################################
#
# An example of adding macros to an XlsxWriter file using a VBA project
# file extracted from an existing Excel xlsm file.
#
# The vba_extract.py utility supplied with XlsxWriter can be used to extract
# the vbaProject.bin file.
#
# An embedded macro is connected to a form button on the worksheet.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Note the file extension should be .xlsm.
workbook = xlsxwriter.Workbook('macros.xlsm')
worksheet = workbook.add_worksheet()
worksheet.set_column('A:A', 30)
# Add the VBA project binary.
workbook.add_vba_project('./vbaProject.bin')
# Show text for the end user.
worksheet.write('A3', 'Press the button to say hello.')
# Add a button tied to a macro in the VBA project.
worksheet.insert_button('B3', {'macro': 'say_hello',
'caption': 'Press Me',
'width': 80,
'height': 30})
workbook.close()
The following are some of the examples included in the examples directory of the XlsxWriter distribution.
Example of a simple column chart with 3 data series:
See the The Chart Class and Working with Charts for more details.
#######################################################################
#
# An example of a simple Excel chart with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()
# Create a new Chart object.
chart = workbook.add_chart({'type': 'column'})
# Write some data to add to plot on the chart.
data = [
[1, 2, 3, 4, 5],
[2, 4, 6, 8, 10],
[3, 6, 9, 12, 15],
]
worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])
worksheet.write_column('C1', data[2])
# Configure the charts. In simplest case we just add some data series.
chart.add_series({'values': '=Sheet1!$A$1:$A$5'})
chart.add_series({'values': '=Sheet1!$B$1:$B$5'})
chart.add_series({'values': '=Sheet1!$C$1:$C$5'})
# Insert the chart into the worksheet.
worksheet.insert_chart('A7', chart)
workbook.close()
Example of creating Excel Area charts.
Chart 1 in the following example is a default area chart:
Chart 2 is a stacked area chart:
Chart 3 is a percentage stacked area chart:
#######################################################################
#
# An example of creating Excel Area charts with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_area.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[40, 40, 50, 30, 25, 50],
[30, 25, 30, 10, 5, 10],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#######################################################################
#
# Create an area chart.
#
chart1 = workbook.add_chart({'type': 'area'})
# Configure the first series.
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure a second series. Note use of alternative syntax to define ranges.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 6, 0],
'values': ['Sheet1', 1, 2, 6, 2],
})
# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart1.set_style(11)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a stacked area chart sub-type.
#
chart2 = workbook.add_chart({'type': 'area', 'subtype': 'stacked'})
# Configure the first series.
chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart2.set_title ({'name': 'Stacked Chart'})
chart2.set_x_axis({'name': 'Test number'})
chart2.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart2.set_style(12)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a percent stacked area chart sub-type.
#
chart3 = workbook.add_chart({'type': 'area', 'subtype': 'percent_stacked'})
# Configure the first series.
chart3.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart3.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart3.set_title ({'name': 'Percent Stacked Chart'})
chart3.set_x_axis({'name': 'Test number'})
chart3.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart3.set_style(13)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating Excel Bar charts.
Chart 1 in the following example is a default bar chart:
Chart 2 is a stacked bar chart:
Chart 3 is a percentage stacked bar chart:
#######################################################################
#
# An example of creating Excel Bar charts with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_bar.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#######################################################################
#
# Create a new bar chart.
#
chart1 = workbook.add_chart({'type': 'bar'})
# Configure the first series.
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure a second series. Note use of alternative syntax to define ranges.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 6, 0],
'values': ['Sheet1', 1, 2, 6, 2],
})
# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart1.set_style(11)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a stacked chart sub-type.
#
chart2 = workbook.add_chart({'type': 'bar', 'subtype': 'stacked'})
# Configure the first series.
chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart2.set_title ({'name': 'Stacked Chart'})
chart2.set_x_axis({'name': 'Test number'})
chart2.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart2.set_style(12)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a percentage stacked chart sub-type.
#
chart3 = workbook.add_chart({'type': 'bar', 'subtype': 'percent_stacked'})
# Configure the first series.
chart3.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart3.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart3.set_title ({'name': 'Percent Stacked Chart'})
chart3.set_x_axis({'name': 'Test number'})
chart3.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart3.set_style(13)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating Excel Column charts.
Chart 1 in the following example is a default column chart:
Chart 2 is a stacked column chart:
Chart 3 is a percentage stacked column chart:
#######################################################################
#
# An example of creating Excel Column charts with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_column.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#######################################################################
#
# Create a new column chart.
#
chart1 = workbook.add_chart({'type': 'column'})
# Configure the first series.
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure a second series. Note use of alternative syntax to define ranges.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 6, 0],
'values': ['Sheet1', 1, 2, 6, 2],
})
# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart1.set_style(11)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a stacked chart sub-type.
#
chart2 = workbook.add_chart({'type': 'column', 'subtype': 'stacked'})
# Configure the first series.
chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart2.set_title ({'name': 'Stacked Chart'})
chart2.set_x_axis({'name': 'Test number'})
chart2.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart2.set_style(12)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a percentage stacked chart sub-type.
#
chart3 = workbook.add_chart({'type': 'column', 'subtype': 'percent_stacked'})
# Configure the first series.
chart3.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart3.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart3.set_title ({'name': 'Percent Stacked Chart'})
chart3.set_x_axis({'name': 'Test number'})
chart3.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart3.set_style(13)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating an Excel line charts. The X axis of a line chart is a category axis with fixed point spacing. For a line chart with arbitrary point spacing see the Scatter chart type.
Chart 1 in the following example is a default line chart:
Chart 2 is a stacked line chart:
Chart 3 is a percentage stacked line chart:
#######################################################################
#
# An example of creating Excel Line charts with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_line.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
# Create a new chart object. In this case an embedded chart.
chart1 = workbook.add_chart({'type': 'line'})
# Configure the first series.
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure a second series. Note use of alternative syntax to define ranges.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 6, 0],
'values': ['Sheet1', 1, 2, 6, 2],
})
# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style. Colors with white outline and shadow.
chart1.set_style(10)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a stacked line chart sub-type.
#
chart2 = workbook.add_chart({'type': 'line', 'subtype': 'stacked'})
# Configure the first series.
chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart2.set_title ({'name': 'Stacked Chart'})
chart2.set_x_axis({'name': 'Test number'})
chart2.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart2.set_style(12)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a percent stacked line chart sub-type.
#
chart3 = workbook.add_chart({'type': 'line', 'subtype': 'percent_stacked'})
# Configure the first series.
chart3.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart3.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart3.set_title ({'name': 'Percent Stacked Chart'})
chart3.set_x_axis({'name': 'Test number'})
chart3.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart3.set_style(13)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating Excel Pie charts. Chart 1 in the following example is:
Chart 2 shows how to set segment colors.
It is possible to define chart colors for most types of XlsxWriter charts via
the add_series()
method. However, Pie charts are a special case since
each segment is represented as a point and as such it is necessary to assign
formatting to each point in the series.
Chart 3 shows how to rotate the segments of the chart:
#######################################################################
#
# An example of creating Excel Pie charts with Python and XlsxWriter.
#
# The demo also shows how to set segment colors. It is possible to
# define chart colors for most types of XlsxWriter charts
# via the add_series() method. However, Pie/Doughnut charts are a special
# case since each segment is represented as a point so it is necessary to
# assign formatting to each point in the series.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_pie.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Category', 'Values']
data = [
['Apple', 'Cherry', 'Pecan'],
[60, 30, 10],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
#######################################################################
#
# Create a new chart object.
#
chart1 = workbook.add_chart({'type': 'pie'})
# Configure the series. Note the use of the list syntax to define ranges:
chart1.add_series({
'name': 'Pie sales data',
'categories': ['Sheet1', 1, 0, 3, 0],
'values': ['Sheet1', 1, 1, 3, 1],
})
# Add a title.
chart1.set_title({'name': 'Popular Pie Types'})
# Set an Excel chart style. Colors with white outline and shadow.
chart1.set_style(10)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('C2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a Pie chart with user defined segment colors.
#
# Create an example Pie chart like above.
chart2 = workbook.add_chart({'type': 'pie'})
# Configure the series and add user defined segment colors.
chart2.add_series({
'name': 'Pie sales data',
'categories': '=Sheet1!$A$2:$A$4',
'values': '=Sheet1!$B$2:$B$4',
'points': [
{'fill': {'color': '#5ABA10'}},
{'fill': {'color': '#FE110E'}},
{'fill': {'color': '#CA5C05'}},
],
})
# Add a title.
chart2.set_title({'name': 'Pie Chart with user defined colors'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('C18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a Pie chart with rotation of the segments.
#
# Create an example Pie chart like above.
chart3 = workbook.add_chart({'type': 'pie'})
# Configure the series.
chart3.add_series({
'name': 'Pie sales data',
'categories': '=Sheet1!$A$2:$A$4',
'values': '=Sheet1!$B$2:$B$4',
})
# Add a title.
chart3.set_title({'name': 'Pie Chart with segment rotation'})
# Change the angle/rotation of the first segment.
chart3.set_rotation(90)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('C34', chart3, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating Excel Doughnut charts. Chart 1 in the following example is:
Chart 4 shows how to set segment colors and other options.
It is possible to define chart colors for most types of XlsxWriter charts via
the add_series()
method. However, Pie/Doughnut charts are a special
case since each segment is represented as a point and as such it is necessary
to assign formatting to each point in the series.
#######################################################################
#
# An example of creating Excel Doughnut charts with Python and XlsxWriter.
#
# The demo also shows how to set segment colors. It is possible to
# define chart colors for most types of XlsxWriter charts
# via the add_series() method. However, Pie/Doughnut charts are a special
# case since each segment is represented as a point so it is necessary to
# assign formatting to each point in the series.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_doughnut.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Category', 'Values']
data = [
['Glazed', 'Chocolate', 'Cream'],
[50, 35, 15],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
#######################################################################
#
# Create a new chart object.
#
chart1 = workbook.add_chart({'type': 'doughnut'})
# Configure the series. Note the use of the list syntax to define ranges:
chart1.add_series({
'name': 'Doughnut sales data',
'categories': ['Sheet1', 1, 0, 3, 0],
'values': ['Sheet1', 1, 1, 3, 1],
})
# Add a title.
chart1.set_title({'name': 'Popular Doughnut Types'})
# Set an Excel chart style. Colors with white outline and shadow.
chart1.set_style(10)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('C2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a Doughnut chart with user defined segment colors.
#
# Create an example Doughnut chart like above.
chart2 = workbook.add_chart({'type': 'doughnut'})
# Configure the series and add user defined segment colors.
chart2.add_series({
'name': 'Doughnut sales data',
'categories': '=Sheet1!$A$2:$A$4',
'values': '=Sheet1!$B$2:$B$4',
'points': [
{'fill': {'color': '#FA58D0'}},
{'fill': {'color': '#61210B'}},
{'fill': {'color': '#F5F6CE'}},
],
})
# Add a title.
chart2.set_title({'name': 'Doughnut Chart with user defined colors'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('C18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a Doughnut chart with rotation of the segments.
#
# Create an example Doughnut chart like above.
chart3 = workbook.add_chart({'type': 'doughnut'})
# Configure the series.
chart3.add_series({
'name': 'Doughnut sales data',
'categories': '=Sheet1!$A$2:$A$4',
'values': '=Sheet1!$B$2:$B$4',
})
# Add a title.
chart3.set_title({'name': 'Doughnut Chart with segment rotation'})
# Change the angle/rotation of the first segment.
chart3.set_rotation(90)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('C34', chart3, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a Doughnut chart with user defined hole size and other options.
#
# Create an example Doughnut chart like above.
chart4 = workbook.add_chart({'type': 'doughnut'})
# Configure the series.
chart4.add_series({
'name': 'Doughnut sales data',
'categories': '=Sheet1!$A$2:$A$4',
'values': '=Sheet1!$B$2:$B$4',
'points': [
{'fill': {'color': '#FA58D0'}},
{'fill': {'color': '#61210B'}},
{'fill': {'color': '#F5F6CE'}},
],
})
# Set a 3D style.
chart4.set_style(26)
# Add a title.
chart4.set_title({'name': 'Doughnut Chart with options applied'})
# Change the angle/rotation of the first segment.
chart4.set_rotation(28)
# Change the hole size.
chart4.set_hole_size(33)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('C50', chart4, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating Excel Scatter charts.
Chart 1 in the following example is a default scatter chart:
Chart 2 is a scatter chart with straight lines and markers:
Chart 3 is a scatter chart with straight lines and no markers:
Chart 4 is a scatter chart with smooth lines and markers:
Chart 5 is a scatter chart with smooth lines and no markers:
#######################################################################
#
# An example of creating Excel Scatter charts with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_scatter.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#######################################################################
#
# Create a new scatter chart.
#
chart1 = workbook.add_chart({'type': 'scatter'})
# Configure the first series.
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series. Note use of alternative syntax to define ranges.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 6, 0],
'values': ['Sheet1', 1, 2, 6, 2],
})
# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart1.set_style(11)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a scatter chart sub-type with straight lines and markers.
#
chart2 = workbook.add_chart({'type': 'scatter',
'subtype': 'straight_with_markers'})
# Configure the first series.
chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart2.set_title ({'name': 'Straight line with markers'})
chart2.set_x_axis({'name': 'Test number'})
chart2.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart2.set_style(12)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a scatter chart sub-type with straight lines and no markers.
#
chart3 = workbook.add_chart({'type': 'scatter',
'subtype': 'straight'})
# Configure the first series.
chart3.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart3.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart3.set_title ({'name': 'Straight line'})
chart3.set_x_axis({'name': 'Test number'})
chart3.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart3.set_style(13)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a scatter chart sub-type with smooth lines and markers.
#
chart4 = workbook.add_chart({'type': 'scatter',
'subtype': 'smooth_with_markers'})
# Configure the first series.
chart4.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart4.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart4.set_title ({'name': 'Smooth line with markers'})
chart4.set_x_axis({'name': 'Test number'})
chart4.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart4.set_style(14)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D50', chart4, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a scatter chart sub-type with smooth lines and no markers.
#
chart5 = workbook.add_chart({'type': 'scatter',
'subtype': 'smooth'})
# Configure the first series.
chart5.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart5.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart5.set_title ({'name': 'Smooth line'})
chart5.set_x_axis({'name': 'Test number'})
chart5.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart5.set_style(15)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D66', chart5, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating Excel Column charts.
Chart 1 in the following example is a default radar chart:
Chart 2 in the following example is a radar chart with markers:
Chart 3 in the following example is a filled radar chart:
#######################################################################
#
# An example of creating Excel Radar charts with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_radar.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[30, 60, 70, 50, 40, 30],
[25, 40, 50, 30, 50, 40],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#######################################################################
#
# Create a new radar chart.
#
chart1 = workbook.add_chart({'type': 'radar'})
# Configure the first series.
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series. Note use of alternative syntax to define ranges.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 6, 0],
'values': ['Sheet1', 1, 2, 6, 2],
})
# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart1.set_style(11)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a radar chart with markers chart sub-type.
#
chart2 = workbook.add_chart({'type': 'radar', 'subtype': 'with_markers'})
# Configure the first series.
chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart2.set_title ({'name': 'Radar Chart With Markers'})
chart2.set_x_axis({'name': 'Test number'})
chart2.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart2.set_style(12)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a filled radar chart sub-type.
#
chart3 = workbook.add_chart({'type': 'radar', 'subtype': 'filled'})
# Configure the first series.
chart3.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart3.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart3.set_title ({'name': 'Filled Radar Chart'})
chart3.set_x_axis({'name': 'Test number'})
chart3.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart3.set_style(13)
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating and Excel HiLow-Close Stock chart.
Chart 1 in the following example is:
#######################################################################
#
# An example of creating Excel Stock charts with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
from datetime import datetime
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_stock.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})
chart = workbook.add_chart({'type': 'stock'})
# Add the worksheet data that the charts will refer to.
headings = ['Date', 'High', 'Low', 'Close']
data = [
['2007-01-01', '2007-01-02', '2007-01-03', '2007-01-04', '2007-01-05'],
[27.2, 25.03, 19.05, 20.34, 18.5],
[23.49, 19.55, 15.12, 17.84, 16.34],
[25.45, 23.05, 17.32, 20.45, 17.34],
]
worksheet.write_row('A1', headings, bold)
for row in range(5):
date = datetime.strptime(data[0][row], "%Y-%m-%d")
worksheet.write(row + 1, 0, date, date_format)
worksheet.write(row + 1, 1, data[1][row])
worksheet.write(row + 1, 2, data[2][row])
worksheet.write(row + 1, 3, data[3][row])
worksheet.set_column('A:D', 11)
# Add a series for each of the High-Low-Close columns.
chart.add_series({
'categories': '=Sheet1!$A$2:$A$6',
'values': '=Sheet1!$B$2:$B$6',
})
chart.add_series({
'categories': '=Sheet1!$A$2:$A$6',
'values': '=Sheet1!$C$2:$C$6',
})
chart.add_series({
'categories': '=Sheet1!$A$2:$A$6',
'values': '=Sheet1!$D$2:$D$6',
})
# Add a chart title and some axis labels.
chart.set_title ({'name': 'High-Low-Close'})
chart.set_x_axis({'name': 'Date'})
chart.set_y_axis({'name': 'Share price'})
worksheet.insert_chart('E9', chart)
workbook.close()
An example showing all 48 default chart styles available in Excel 2007 using
the chart set_style()
method.
Note, these styles are not the same as the styles available in Excel 2013.
#######################################################################
#
# An example showing all 48 default chart styles available in Excel 2007
# using Python and XlsxWriter. Note, these styles are not the same as
# the styles available in Excel 2013.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_styles.xlsx')
# Show the styles for all of these chart types.
chart_types = ['column', 'area', 'line', 'pie']
for chart_type in chart_types:
# Add a worksheet for each chart type.
worksheet = workbook.add_worksheet(chart_type.title())
worksheet.set_zoom(30)
style_number = 1
# Create 48 charts, each with a different style.
for row_num in range(0, 90, 15):
for col_num in range(0, 64, 8):
chart = workbook.add_chart({'type': chart_type})
chart.add_series({'values': '=Data!$A$1:$A$6'})
chart.set_title ({'name': 'Style %d' % style_number})
chart.set_legend({'none': True})
chart.set_style(style_number)
worksheet.insert_chart(row_num, col_num , chart)
style_number += 1
# Create a worksheet with data for the charts.
data_worksheet = workbook.add_worksheet('Data')
data = [10, 40, 50, 20, 10, 50]
data_worksheet.write_column('A1', data)
data_worksheet.hide()
workbook.close()
Example of creating an Excel chart with pattern fills, in the columns.
#######################################################################
#
# An example of an Excel chart with patterns using Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_pattern.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Shingle', 'Brick']
data = [
[105, 150, 130, 90 ],
[50, 120, 100, 110],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
# Create a new Chart object.
chart = workbook.add_chart({'type': 'column'})
# Configure the charts. Add two series with patterns. The gap is used to make
# the patterns more visible.
chart.add_series({
'name': '=Sheet1!$A$1',
'values': '=Sheet1!$A$2:$A$5',
'pattern': {
'pattern': 'shingle',
'fg_color': '#804000',
'bg_color': '#c68c53'
},
'border': {'color': '#804000'},
'gap': 70,
})
chart.add_series({
'name': '=Sheet1!$B$1',
'values': '=Sheet1!$B$2:$B$5',
'pattern': {
'pattern': 'horizontal_brick',
'fg_color': '#b30000',
'bg_color': '#ff6666'
},
'border': {'color': '#b30000'},
})
# Add a chart title and some axis labels.
chart.set_title ({'name': 'Cladding types'})
chart.set_x_axis({'name': 'Region'})
chart.set_y_axis({'name': 'Number of houses'})
# Insert the chart into the worksheet.
worksheet.insert_chart('D2', chart)
workbook.close()
Example of creating an Excel chart with gradient fills, in the columns and in the plot area.
#######################################################################
#
# An example of creating an Excel charts with gradient fills using
# Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_gradient.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
# Create a new column chart.
chart = workbook.add_chart({'type': 'column'})
# Configure the first series, including a gradient.
chart.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'gradient': {'colors': ['#963735', '#F1DCDB']}
})
# Configure the second series, including a gradient.
chart.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
'gradient': {'colors': ['#E36C0A', '#FCEADA']}
})
# Set a gradient for the plotarea.
chart.set_plotarea({
'gradient': {'colors': ['#FFEFD1', '#F0EBD5', '#B69F66']}
})
# Add some axis labels.
chart.set_x_axis({'name': 'Test number'})
chart.set_y_axis({'name': 'Sample length (mm)'})
# Turn off the chart legend.
chart.set_legend({'none': True})
# Insert the chart into the worksheet.
worksheet.insert_chart('E2', chart)
workbook.close()
Example of creating an Excel Line chart with a secondary axis. Note, the primary and secondary chart type are the same. The next example shows a secondary chart of a different type.
#######################################################################
#
# An example of creating an Excel Line chart with a secondary axis
# using Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_secondary_axis.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Aliens', 'Humans']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
# Create a new chart object. In this case an embedded chart.
chart = workbook.add_chart({'type': 'line'})
# Configure a series with a secondary axis
chart.add_series({
'name': '=Sheet1!$A$1',
'values': '=Sheet1!$A$2:$A$7',
'y2_axis': 1,
})
chart.add_series({
'name': '=Sheet1!$B$1',
'values': '=Sheet1!$B$2:$B$7',
})
chart.set_legend({'position': 'right'})
# Add a chart title and some axis labels.
chart.set_title({'name': 'Survey results'})
chart.set_x_axis({'name': 'Days', })
chart.set_y_axis({'name': 'Population', 'major_gridlines': {'visible': 0}})
chart.set_y2_axis({'name': 'Laser wounds'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating combined Excel charts with two chart types.
In the first example we create a combined column and line chart that share the same X and Y axes.
In the second example we create a similar combined column and line chart except that the secondary chart has a secondary Y axis.
#######################################################################
#
# An example of a Combined chart in XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
from xlsxwriter.workbook import Workbook
workbook = Workbook('chart_combined.xlsx')
worksheet = workbook.add_worksheet()
# Add a format for the headings.
bold = workbook.add_format({'bold': True})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#
# In the first example we will create a combined column and line chart.
# They will share the same X and Y axes.
#
# Create a new column chart. This will use this as the primary chart.
column_chart1 = workbook.add_chart({'type': 'column'})
# Configure the data series for the primary chart.
column_chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Create a new column chart. This will use this as the secondary chart.
line_chart1 = workbook.add_chart({'type': 'line'})
# Configure the data series for the secondary chart.
line_chart1.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Combine the charts.
column_chart1.combine(line_chart1)
# Add a chart title and some axis labels. Note, this is done via the
# primary chart.
column_chart1.set_title({ 'name': 'Combined chart - same Y axis'})
column_chart1.set_x_axis({'name': 'Test number'})
column_chart1.set_y_axis({'name': 'Sample length (mm)'})
# Insert the chart into the worksheet
worksheet.insert_chart('E2', column_chart1)
#
# In the second example we will create a similar combined column and line
# chart except that the secondary chart will have a secondary Y axis.
#
# Create a new column chart. This will use this as the primary chart.
column_chart2 = workbook.add_chart({'type': 'column'})
# Configure the data series for the primary chart.
column_chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Create a new column chart. This will use this as the secondary chart.
line_chart2 = workbook.add_chart({'type': 'line'})
# Configure the data series for the secondary chart. We also set a
# secondary Y axis via (y2_axis). This is the only difference between
# this and the first example, apart from the axis label below.
line_chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
'y2_axis': True,
})
# Combine the charts.
column_chart2.combine(line_chart2)
# Add a chart title and some axis labels.
column_chart2.set_title({ 'name': 'Combine chart - secondary Y axis'})
column_chart2.set_x_axis({ 'name': 'Test number'})
column_chart2.set_y_axis({ 'name': 'Sample length (mm)'})
# Note: the y2 properties are on the secondary chart.
line_chart2.set_y2_axis({'name': 'Target length (mm)'})
# Insert the chart into the worksheet
worksheet.insert_chart('E18', column_chart2)
workbook.close()
Example of creating a Pareto chart with a secondary chart and axis.
#######################################################################
#
# An example of creating of a Pareto chart with Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_pareto.xlsx')
worksheet = workbook.add_worksheet()
# Formats used in the workbook.
bold = workbook.add_format({'bold': True})
percent_format = workbook.add_format({'num_format': '0.0%'})
# Widen the columns for visibility.
worksheet.set_column('A:A', 15)
worksheet.set_column('B:C', 10)
# Add the worksheet data that the charts will refer to.
headings = ['Reason', 'Number', 'Percentage']
reasons = [
'Traffic', 'Child care', 'Public Transport', 'Weather',
'Overslept', 'Emergency',
]
numbers = [60, 40, 20, 15, 10, 5]
percents = [0.44, 0.667, 0.8, 0.9, 0.967, 1]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', reasons)
worksheet.write_column('B2', numbers)
worksheet.write_column('C2', percents, percent_format)
# Create a new column chart. This will be the primary chart.
column_chart = workbook.add_chart({'type': 'column'})
# Add a series.
column_chart.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Add a chart title.
column_chart.set_title({'name': 'Reasons for lateness'})
# Turn off the chart legend.
column_chart.set_legend({'position': 'none'})
# Set the title and scale of the Y axes. Note, the secondary axis is set from
# the primary chart.
column_chart.set_y_axis({
'name': 'Respondents (number)',
'min': 0,
'max': 120
})
column_chart.set_y2_axis({'max': 1})
# Create a new line chart. This will be the secondary chart.
line_chart = workbook.add_chart({'type': 'line'})
# Add a series, on the secondary axis.
line_chart.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
'marker': {'type': 'automatic'},
'y2_axis': 1,
})
# Combine the charts.
column_chart.combine(line_chart)
# Insert the chart into the worksheet.
worksheet.insert_chart('F2', column_chart)
workbook.close()
A Gauge Chart isn’t a native chart type in Excel. It is constructed by combining a doughnut chart and a pie chart and by using some non-filled elements. This example follows the following online example of how to create a Gauge Chart in Excel: https://www.excel-easy.com/examples/gauge-chart.html
#######################################################################
#
# An example of creating a Gauge Chart in Excel with Python and XlsxWriter.
#
# A Gauge Chart isn't a native chart type in Excel. It is constructed by
# combining a doughnut chart and a pie chart and by using some non-filled
# elements. This example follows the following online example of how to create
# a Gauge Chart in Excel: https://www.excel-easy.com/examples/gauge-chart.html
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_gauge.xlsx')
worksheet = workbook.add_worksheet()
chart_doughnut = workbook.add_chart({'type': 'doughnut'})
chart_pie = workbook.add_chart({'type': 'pie'})
# Add some data for the Doughnut and Pie charts. This is set up so the
# gauge goes from 0-100. It is initially set at 75%.
worksheet.write_column('H2', ['Donut', 25, 50, 25, 100])
worksheet.write_column('I2', ['Pie', 75, 1, '=200-I4-I3'])
# Configure the doughnut chart as the background for the gauge.
chart_doughnut.add_series({
'name': '=Sheet1!$H$2',
'values': '=Sheet1!$H$3:$H$6',
'points': [
{'fill': {'color': 'green'}},
{'fill': {'color': 'yellow'}},
{'fill': {'color': 'red'}},
{'fill': {'none': True}}],
})
# Rotate chart so the gauge parts are above the horizontal.
chart_doughnut.set_rotation(270)
# Turn off the chart legend.
chart_doughnut.set_legend({'none': True})
# Turn off the chart fill and border.
chart_doughnut.set_chartarea({
'border': {'none': True},
'fill': {'none': True},
})
# Configure the pie chart as the needle for the gauge.
chart_pie.add_series({
'name': '=Sheet1!$I$2',
'values': '=Sheet1!$I$3:$I$6',
'points': [
{'fill': {'none': True}},
{'fill': {'color': 'black'}},
{'fill': {'none': True}}],
})
# Rotate the pie chart/needle to align with the doughnut/gauge.
chart_pie.set_rotation(270)
# Combine the pie and doughnut charts.
chart_doughnut.combine(chart_pie)
# Insert the chart into the worksheet.
worksheet.insert_chart('A1', chart_doughnut)
workbook.close()
Example of creating a clustered Excel chart where there are two levels of category on the X axis.
The categories in clustered charts are 2D ranges, instead of the more normal 1D ranges. The series are shown as formula strings for clarity but you can also use the a list syntax.
#######################################################################
#
# A demo of a clustered category chart in XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
from xlsxwriter.workbook import Workbook
workbook = Workbook('chart_clustered.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Types', 'Sub Type', 'Value 1', 'Value 2', 'Value 3']
data = [
['Type 1', 'Sub Type A', 5000, 8000, 6000],
['', 'Sub Type B', 2000, 3000, 4000],
['', 'Sub Type C', 250, 1000, 2000],
['Type 2', 'Sub Type D', 6000, 6000, 6500],
['', 'Sub Type E', 500, 300, 200],
]
worksheet.write_row('A1', headings, bold)
for row_num, row_data in enumerate(data):
worksheet.write_row(row_num + 1, 0, row_data)
# Create a new chart object. In this case an embedded chart.
chart = workbook.add_chart({'type': 'column'})
# Configure the series. Note, that the categories are 2D ranges (from column A
# to column B). This creates the clusters. The series are shown as formula
# strings for clarity but you can also use the list syntax. See the docs.
chart.add_series({
'categories': '=Sheet1!$A$2:$B$6',
'values': '=Sheet1!$C$2:$C$6',
})
chart.add_series({
'categories': '=Sheet1!$A$2:$B$6',
'values': '=Sheet1!$D$2:$D$6',
})
chart.add_series({
'categories': '=Sheet1!$A$2:$B$6',
'values': '=Sheet1!$E$2:$E$6',
})
# Set the Excel chart style.
chart.set_style(37)
# Turn off the legend.
chart.set_legend({'position': 'none'})
# Insert the chart into the worksheet.
worksheet.insert_chart('G3', chart)
workbook.close()
Date Category Axes are a special case of Category axes in Excel which give them some of the properties of Values axes.
For example, Excel doesn’t normally allow minimum and maximum values to be set for category axes. However, date axes are an exception.
In XlsxWriter Date Category Axes are set using the date_axis
option in
set_x_axis()
or set_y_axis()
:
chart.set_x_axis({'date_axis': True})
If used, the min
and max
values should be set as Excel times or dates.
#######################################################################
#
# An example of creating an Excel charts with a date axis using
# Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
from datetime import date
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_date_axis.xlsx')
worksheet = workbook.add_worksheet()
chart = workbook.add_chart({'type': 'line'})
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})
# Widen the first column to display the dates.
worksheet.set_column('A:A', 12)
# Some data to be plotted in the worksheet.
dates = [date(2013, 1, 1),
date(2013, 1, 2),
date(2013, 1, 3),
date(2013, 1, 4),
date(2013, 1, 5),
date(2013, 1, 6),
date(2013, 1, 7),
date(2013, 1, 8),
date(2013, 1, 9),
date(2013, 1, 10)]
values = [10, 30, 20, 40, 20, 60, 50, 40, 30, 30]
# Write the date to the worksheet.
worksheet.write_column('A1', dates, date_format)
worksheet.write_column('B1', values)
# Add a series to the chart.
chart.add_series({
'categories': '=Sheet1!$A$1:$A$10',
'values': '=Sheet1!$B$1:$B$10',
})
# Configure the X axis as a Date axis and set the max and min limits.
chart.set_x_axis({
'date_axis': True,
'min': date(2013, 1, 2),
'max': date(2013, 1, 9),
})
# Turn off the legend.
chart.set_legend({'none': True})
# Insert the chart into the worksheet.
worksheet.insert_chart('D2', chart)
workbook.close()
Example of creating charts with data tables.
Chart 1 in the following example is a column chart with default data table:
Chart 2 is a column chart with default data table with legend keys:
#######################################################################
#
# An example of creating Excel Column charts with data tables using
# Python and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_data_table.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#######################################################################
#
# Create a column chart with a data table.
#
chart1 = workbook.add_chart({'type': 'column'})
# Configure the first series.
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series. Note use of alternative syntax to define ranges.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 6, 0],
'values': ['Sheet1', 1, 2, 6, 2],
})
# Add a chart title and some axis labels.
chart1.set_title({'name': 'Chart with Data Table'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})
# Set a default data table on the X-Axis.
chart1.set_table()
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Create a column chart with a data table and legend keys.
#
chart2 = workbook.add_chart({'type': 'column'})
# Configure the first series.
chart2.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure second series.
chart2.add_series({
'name': '=Sheet1!$C$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title and some axis labels.
chart2.set_title({'name': 'Data Table with legend keys'})
chart2.set_x_axis({'name': 'Test number'})
chart2.set_y_axis({'name': 'Sample length (mm)'})
# Set a data table on the X-Axis with the legend keys shown.
chart2.set_table({'show_keys': True})
# Hide the chart legend since the keys are shown on the data table.
chart2.set_legend({'position': 'none'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
workbook.close()
A demo of an various Excel chart data tools that are available via an XlsxWriter chart. These include, Trendlines, Data Labels, Error Bars, Drop Lines, High-Low Lines and Up-Down Bars.
Chart 1 in the following example is a chart with trendlines:
Chart 2 is a chart with data labels and markers:
Chart 3 is a chart with error bars:
Chart 4 is a chart with up-down bars:
Chart 5 is a chart with hi-low lines:
Chart 6 is a chart with drop lines:
#######################################################################
#
# A demo of an various Excel chart data tools that are available via
# an XlsxWriter chart.
#
# These include, Trendlines, Data Labels, Error Bars, Drop Lines,
# High-Low Lines and Up-Down Bars.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_data_tools.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Data 1', 'Data 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#######################################################################
#
# Trendline example.
#
# Create a Line chart.
chart1 = workbook.add_chart({'type': 'line'})
# Configure the first series with a polynomial trendline.
chart1.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'trendline': {
'type': 'polynomial',
'order': 3,
},
})
# Configure the second series with a moving average trendline.
chart1.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
'trendline': {'type': 'linear'},
})
# Add a chart title.
chart1.set_title({'name': 'Chart with Trendlines'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Data Labels and Markers example.
#
# Create a Line chart.
chart2 = workbook.add_chart({'type': 'line'})
# Configure the first series.
chart2.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': 1},
'marker': {'type': 'automatic'},
})
# Configure the second series.
chart2.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title.
chart2.set_title({'name': 'Chart with Data Labels and Markers'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Error Bars example.
#
# Create a Line chart.
chart3 = workbook.add_chart({'type': 'line'})
# Configure the first series.
chart3.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'y_error_bars': {'type': 'standard_error'},
})
# Configure the second series.
chart3.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title.
chart3.set_title({'name': 'Chart with Error Bars'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Up-Down Bars example.
#
# Create a Line chart.
chart4 = workbook.add_chart({'type': 'line'})
# Add the Up-Down Bars.
chart4.set_up_down_bars()
# Configure the first series.
chart4.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure the second series.
chart4.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title.
chart4.set_title({'name': 'Chart with Up-Down Bars'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D50', chart4, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# High-Low Lines example.
#
# Create a Line chart.
chart5 = workbook.add_chart({'type': 'line'})
# Add the High-Low lines.
chart5.set_high_low_lines()
# Configure the first series.
chart5.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure the second series.
chart5.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title.
chart5.set_title({'name': 'Chart with High-Low Lines'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D66', chart5, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Drop Lines example.
#
# Create a Line chart.
chart6 = workbook.add_chart({'type': 'line'})
# Add Drop Lines.
chart6.set_drop_lines()
# Configure the first series.
chart6.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure the second series.
chart6.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$C$2:$C$7',
})
# Add a chart title.
chart6.set_title({'name': 'Chart with Drop Lines'})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D82', chart6, {'x_offset': 25, 'y_offset': 10})
workbook.close()
A demo of some of the Excel chart data labels options that are available via an XlsxWriter chart. These include custom labels with user text or text taken from cells in the worksheet. See also Chart series option: Data Labels and Chart series option: Custom Data Labels.
Chart 1 in the following example is a chart with standard data labels:
Chart 2 is a chart with Category and Value data labels:
Chart 3 is a chart with data labels with a user defined font:
Chart 4 is a chart with standard data labels and formatting:
Chart 5 is a chart with custom string data labels:
Chart 6 is a chart with custom data labels referenced from worksheet cells:
Chart 7 is a chart with a mix of custom and default labels. The None
items
will get the default value. We also set a font for the custom items as an
extra example:
Chart 8 is a chart with some deleted custom labels and defaults (set with
None
values). This allows us to highlight certain values such as the
minimum and maximum:
Chart 9 is a chart with custom string data labels and formatting:
#######################################################################
#
# A demo of an various Excel chart data label features that are available
# via an XlsxWriter chart.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_data_labels.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Data', 'Text']
data = [
[2, 3, 4, 5, 6, 7],
[20, 10, 20, 30, 40, 30],
['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
#######################################################################
#
# Example with standard data labels.
#
# Create a Column chart.
chart1 = workbook.add_chart({'type': 'column'})
# Configure the data series and add the data labels.
chart1.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True},
})
# Add a chart title.
chart1.set_title({'name': 'Chart with standard data labels'})
# Turn off the chart legend.
chart1.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D2', chart1, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Example with value and category data labels.
#
# Create a Column chart.
chart2 = workbook.add_chart({'type': 'column'})
# Configure the data series and add the data labels.
chart2.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True, 'category': True},
})
# Add a chart title.
chart2.set_title({'name': 'Category and Value data labels'})
# Turn off the chart legend.
chart2.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D18', chart2, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Example with standard data labels with different font.
#
# Create a Column chart.
chart3 = workbook.add_chart({'type': 'column'})
# Configure the data series and add the data labels.
chart3.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True,
'font': {'bold': True,
'color': 'red',
'rotation': -30}},
})
# Add a chart title.
chart3.set_title({'name': 'Data labels with user defined font'})
# Turn off the chart legend.
chart3.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D34', chart3, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Example with standard data labels and formatting.
#
# Create a Column chart.
chart4 = workbook.add_chart({'type': 'column'})
# Configure the data series and add the data labels.
chart4.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True,
'border': {'color': 'red'},
'fill': {'color': 'yellow'}},
})
# Add a chart title.
chart4.set_title({'name': 'Data labels with formatting'})
# Turn off the chart legend.
chart4.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D50', chart4, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Example with custom string data labels.
#
# Create a Column chart.
chart5 = workbook.add_chart({'type': 'column'})
# Some custom labels.
custom_labels = [
{'value': 'Amy'},
{'value': 'Bea'},
{'value': 'Eva'},
{'value': 'Fay'},
{'value': 'Liv'},
{'value': 'Una'},
]
# Configure the data series and add the data labels.
chart5.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True, 'custom': custom_labels},
})
# Add a chart title.
chart5.set_title({'name': 'Chart with custom string data labels'})
# Turn off the chart legend.
chart5.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D66', chart5, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Example with custom data labels from cells.
#
# Create a Column chart.
chart6 = workbook.add_chart({'type': 'column'})
# Some custom labels.
custom_labels = [
{'value': '=Sheet1!$C$2'},
{'value': '=Sheet1!$C$3'},
{'value': '=Sheet1!$C$4'},
{'value': '=Sheet1!$C$5'},
{'value': '=Sheet1!$C$6'},
{'value': '=Sheet1!$C$7'},
]
# Configure the data series and add the data labels.
chart6.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True, 'custom': custom_labels},
})
# Add a chart title.
chart6.set_title({'name': 'Chart with custom data labels from cells'})
# Turn off the chart legend.
chart6.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D82', chart6, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Example with custom and default data labels.
#
# Create a Column chart.
chart7 = workbook.add_chart({'type': 'column'})
# The following is used to get a mix of default and custom labels. The 'None'
# items will get the default value. We also set a font for the custom items
# as an extra example.
custom_labels = [
{'value': '=Sheet1!$C$2', 'font': {'color': 'red'}},
None,
{'value': '=Sheet1!$C$4', 'font': {'color': 'red'}},
{'value': '=Sheet1!$C$5', 'font': {'color': 'red'}},
]
# Configure the data series and add the data labels.
chart7.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True, 'custom': custom_labels},
})
# Add a chart title.
chart7.set_title({'name': 'Mixed custom and default data labels'})
# Turn off the chart legend.
chart7.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D98', chart7, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Example with deleted custom data labels.
#
# Create a Column chart.
chart8 = workbook.add_chart({'type': 'column'})
# Some deleted custom labels and defaults (set with None values). This allows
# us to highlight certain values such as the minimum and maximum.
custom_labels = [
{'delete': True},
None,
{'delete': True},
{'delete': True},
None,
{'delete': True},
]
# Configure the data series and add the data labels.
chart8.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True, 'custom': custom_labels},
})
# Add a chart title.
chart8.set_title({'name': 'Chart with deleted data labels'})
# Turn off the chart legend.
chart8.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D114', chart8, {'x_offset': 25, 'y_offset': 10})
#######################################################################
#
# Example with custom string data labels and formatting.
#
# Create a Column chart.
chart9 = workbook.add_chart({'type': 'column'})
# Some custom labels.
custom_labels = [
{'value': 'Amy', 'border': {'color': 'blue'}},
{'value': 'Bea'},
{'value': 'Eva'},
{'value': 'Fay'},
{'value': 'Liv'},
{'value': 'Una', 'fill': {'color': 'green'}},
]
# Configure the data series and add the data labels.
chart9.add_series({
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
'data_labels': {'value': True,
'custom': custom_labels,
'border': {'color': 'red'},
'fill': {'color': 'yellow'}},
})
# Add a chart title.
chart9.set_title({'name': 'Chart with custom labels and formatting'})
# Turn off the chart legend.
chart9.set_legend({'none': True})
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart('D130', chart9, {'x_offset': 25, 'y_offset': 10})
workbook.close()
Example of creating an Excel Bar chart on a chartsheet.
#######################################################################
#
# An example of creating an Excel chart in a chartsheet with Python
# and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('chartsheet.xlsx')
# Add a worksheet to hold the data.
worksheet = workbook.add_worksheet()
# Add a chartsheet. A worksheet that only holds a chart.
chartsheet = workbook.add_chartsheet()
# Add a format for the headings.
bold = workbook.add_format({'bold': 1})
# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
[30, 60, 70, 50, 40, 30],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])
# Create a new bar chart.
chart1 = workbook.add_chart({'type': 'bar'})
# Configure the first series.
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$7',
'values': '=Sheet1!$B$2:$B$7',
})
# Configure a second series. Note use of alternative syntax to define ranges.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 6, 0],
'values': ['Sheet1', 1, 2, 6, 2],
})
# Add a chart title and some axis labels.
chart1.set_title ({'name': 'Results of sample analysis'})
chart1.set_x_axis({'name': 'Test number'})
chart1.set_y_axis({'name': 'Sample length (mm)'})
# Set an Excel chart style.
chart1.set_style(11)
# Add the chart to the chartsheet.
chartsheet.set_chart(chart1)
# Display the chartsheet as the active sheet when the workbook is opened.
chartsheet.activate();
workbook.close()
The following are some of the examples included in the examples directory of the XlsxWriter distribution.
They show how to use XlsxWriter with Pandas.
A simple example of converting a Pandas dataframe to an Excel file using Pandas and XlsxWriter. See Working with Python Pandas and XlsxWriter for more details.
##############################################################################
#
# A simple example of converting a Pandas dataframe to an xlsx file using
# Pandas and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import pandas as pd
# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')
# Close the Pandas Excel writer and output the Excel file.
writer.save()
An example of writing multiple dataframes to worksheets using Pandas and XlsxWriter.
##############################################################################
#
# An example of writing multiple dataframes to worksheets using Pandas and
# XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import pandas as pd
# Create some Pandas dataframes from some data.
df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})
df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_multiple.xlsx', engine='xlsxwriter')
# Write each dataframe to a different worksheet.
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
df3.to_excel(writer, sheet_name='Sheet3')
# Close the Pandas Excel writer and output the Excel file.
writer.save()
An example of positioning dataframes in a worksheet using Pandas and XlsxWriter. It also demonstrates how to write a dataframe without the header and index.
##############################################################################
#
# An example of positioning dataframes in a worksheet using Pandas and
# XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import pandas as pd
# Create some Pandas dataframes from some data.
df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})
df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})
df4 = pd.DataFrame({'Data': [41, 42, 43, 44]})
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_positioning.xlsx', engine='xlsxwriter')
# Position the dataframes in the worksheet.
df1.to_excel(writer, sheet_name='Sheet1') # Default position, cell A1.
df2.to_excel(writer, sheet_name='Sheet1', startcol=3)
df3.to_excel(writer, sheet_name='Sheet1', startrow=6)
# It is also possible to write the dataframe without the header and index.
df4.to_excel(writer, sheet_name='Sheet1',
startrow=7, startcol=4, header=False, index=False)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
A simple example of converting a Pandas dataframe to an Excel file with a chart using Pandas and XlsxWriter.
##############################################################################
#
# An example of converting a Pandas dataframe to an xlsx file with a chart
# using Pandas and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import pandas as pd
# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_chart.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')
# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Create a chart object.
chart = workbook.add_chart({'type': 'column'})
# Configure the series of the chart from the dataframe data.
chart.add_series({'values': '=Sheet1!$B$2:$B$8'})
# Insert the chart into the worksheet.
worksheet.insert_chart('D2', chart)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
An example of converting a Pandas dataframe to an Excel file with a conditional formatting using Pandas and XlsxWriter.
##############################################################################
#
# An example of converting a Pandas dataframe to an xlsx file with a
# conditional formatting using Pandas and XlsxWriter.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import pandas as pd
# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')
# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Apply a conditional format to the cell range.