The following are the available vertical alignments:
| Vertical alignment |
|---|
| top |
| vcenter |
| bottom |
| vjustify |
| vdistributed |
As in Excel, vertical and horizontal alignments can be combined:
cell_format = workbook.add_format()
cell_format.set_align('center')
cell_format.set_align('vcenter')
worksheet.set_row(0, 70)
worksheet.set_column('A:A', 30)
worksheet.write(0, 0, 'Some Text', cell_format)
Text can be aligned across two or more adjacent cells using the
'center_across'
property. However, for genuine merged cells it is better
to use the merge_range()
worksheet method.
The 'vjustify'
(vertical justify) option can be used to provide automatic
text wrapping in a cell. The height of the cell will be adjusted to
accommodate the wrapped text. To specify where the text wraps use the
set_text_wrap()
method.
set_center_across
()Center text across adjacent cells.
Text can be aligned across two or more adjacent cells using the
set_center_across()
method. This is an alias for the
set_align('center_across')
method call.
Only the leftmost cell should contain the text. The other cells in the range should be blank but should include the formatting:
cell_format = workbook.add_format()
cell_format.set_center_across()
worksheet.write(1, 1, 'Center across selection', cell_format)
worksheet.write_blank(1, 2, '', cell_format)
For actual merged cells it is better to use the merge_range()
worksheet
method.
set_text_wrap
()Wrap text in a cell.
Turn text wrapping on for text in a cell:
cell_format = workbook.add_format()
cell_format.set_text_wrap()
worksheet.write(0, 0, "Some long text to wrap in a cell", cell_format)
If you wish to control where the text is wrapped you can add newline characters to the string:
worksheet.write(2, 0, "It's\na bum\nwrap", cell_format)
Excel will adjust the height of the row to accommodate the wrapped text. A
similar effect can be obtained without newlines using the
set_align('vjustify')
method.
set_rotation
(angle)Set the rotation of the text in a cell.
| Parameters: | angle (int) – Rotation angle in the range -90 to 90 and 270. |
|---|
Set the rotation of the text in a cell. The rotation can be any angle in the range -90 to 90 degrees:
cell_format = workbook.add_format()
cell_format.set_rotation(30)
worksheet.write(0, 0, 'This text is rotated', cell_format)
The angle 270 is also supported. This indicates text where the letters run from top to bottom.
set_reading_order
(direction)Set the reading order for the text in a cell.
| Parameters: | direction (int) – Reading order direction. |
|---|
Set the text reading direction. This is useful when creating Arabic, Hebrew or
other near or far eastern worksheets. It can be used in conjunction with the
Worksheet right_to_left()
method to also change the direction of the
worksheet.
format_left_to_right = workbook.add_format()
format_left_to_right.set_reading_order(1)
format_right_to_left = workbook.add_format()
format_right_to_left.set_reading_order(2)
worksheet.right_to_left()
worksheet.write('A1', u'نص عربي / English text') # Default direction.
worksheet.write('A2', u'نص عربي / English text', format_left_to_right)
worksheet.write('A3', u'نص عربي / English text', format_right_to_left)
set_indent
(level)Set the cell text indentation level.
| Parameters: | level (int) – Indentation level. |
|---|
This method can be used to indent text in a cell. The argument, which should be an integer, is taken as the level of indentation:
cell_format1 = workbook.add_format()
cell_format2 = workbook.add_format()
cell_format1.set_indent(1)
cell_format2.set_indent(2)
worksheet.write('A1', 'This text is indented 1 level', cell_format1)
worksheet.write('A2', 'This text is indented 2 levels', cell_format2)
Indentation is a horizontal alignment property. It will override any other horizontal properties but it can be used in conjunction with vertical properties.
set_shrink
()Turn on the text “shrink to fit” for a cell.
This method can be used to shrink text so that it fits in a cell:
cell_format = workbook.add_format()
cell_format.set_shrink()
worksheet.write(0, 0, 'Honey, I shrunk the text!', cell_format)
set_text_justlast
()Turn on the justify last text property.
Only applies to Far Eastern versions of Excel.
set_pattern
(index)| Parameters: | index (int) – Pattern index. 0 - 18. |
|---|
Set the background pattern of a cell.
The most common pattern is 1 which is a solid fill of the background color.
set_bg_color
(color)Set the color of the background pattern in a cell.
| Parameters: | color (string) – The cell font color. |
|---|
The set_bg_color()
method can be used to set the background color of a
pattern. Patterns are defined via the set_pattern()
method. If a pattern
hasn’t been defined then a solid fill pattern is used as the default.
Here is an example of how to set up a solid fill in a cell:
cell_format = workbook.add_format()
cell_format.set_pattern(1) # This is optional when using a solid fill.
cell_format.set_bg_color('green')
worksheet.write('A1', 'Ray', cell_format)
The color can be a Html style #RRGGBB
string or a limited number of named
colors, see Working with Colors.
set_fg_color
(color)Set the color of the foreground pattern in a cell.
| Parameters: | color (string) – The cell font color. |
|---|
The set_fg_color()
method can be used to set the foreground color of a
pattern.
The color can be a Html style #RRGGBB
string or a limited number of named
colors, see Working with Colors.
set_border
(style)Set the cell border style.
| Parameters: | style (int) – Border style index. Default is 1. |
|---|
Individual border elements can be configured using the following methods with the same parameters:
A cell border is comprised of a border on the bottom, top, left and right.
These can be set to the same value using set_border()
or individually
using the relevant method calls shown above.
The following shows the border styles sorted by XlsxWriter index number:
| Index | Name | Weight | Style |
|---|---|---|---|
| 0 | None | 0 | |
| 1 | Continuous | 1 | ----------- |
| 2 | Continuous | 2 | ----------- |
| 3 | Dash | 1 | - - - - - - |
| 4 | Dot | 1 | . . . . . . |
| 5 | Continuous | 3 | ----------- |
| 6 | Double | 3 | =========== |
| 7 | Continuous | 0 | ----------- |
| 8 | Dash | 2 | - - - - - - |
| 9 | Dash Dot | 1 | - . - . - . |
| 10 | Dash Dot | 2 | - . - . - . |
| 11 | Dash Dot Dot | 1 | - . . - . . |
| 12 | Dash Dot Dot | 2 | - . . - . . |
| 13 | SlantDash Dot | 2 | / - . / - . |
The following shows the borders in the order shown in the Excel Dialog:
| Index | Style | Index | Style |
|---|---|---|---|
| 0 | None | 12 | - . . - . . |
| 7 | ----------- |
13 | / - . / - . |
| 4 | . . . . . . |
10 | - . - . - . |
| 11 | - . . - . . |
8 | - - - - - - |
| 9 | - . - . - . |
2 | ----------- |
| 3 | - - - - - - |
5 | ----------- |
| 1 | ----------- |
6 | =========== |
set_bottom
(style)Set the cell bottom border style.
| Parameters: | style (int) – Border style index. Default is 1. |
|---|
Set the cell bottom border style. See set_border()
for details on the
border styles.
set_top
(style)Set the cell top border style.
| Parameters: | style (int) – Border style index. Default is 1. |
|---|
Set the cell top border style. See set_border()
for details on the
border styles.
set_left
(style)Set the cell left border style.
| Parameters: | style (int) – Border style index. Default is 1. |
|---|
Set the cell left border style. See set_border()
for details on the
border styles.
set_right
(style)Set the cell right border style.
| Parameters: | style (int) – Border style index. Default is 1. |
|---|
Set the cell right border style. See set_border()
for details on the
border styles.
set_border_color
(color)Set the color of the cell border.
| Parameters: | color (string) – The cell border color. |
|---|
Individual border elements can be configured using the following methods with the same parameters:
Set the color of the cell borders. A cell border is comprised of a border on
the bottom, top, left and right. These can be set to the same color using
set_border_color()
or individually using the relevant method calls shown
above.
The color can be a Html style #RRGGBB
string or a limited number of named
colors, see Working with Colors.
set_bottom_color
(color)Set the color of the bottom cell border.
| Parameters: | color (string) – The cell border color. |
|---|
See set_border_color()
for details on the border colors.
set_top_color
(color)Set the color of the top cell border.
| Parameters: | color (string) – The cell border color. |
|---|
See set_border_color()
for details on the border colors.
set_left_color
(color)Set the color of the left cell border.
| Parameters: | color (string) – The cell border color. |
|---|
See set_border_color()
for details on the border colors.
set_right_color
(color)Set the color of the right cell border.
| Parameters: | color (string) – The cell border color. |
|---|
See set_border_color()
for details on the border colors.
set_diag_border
(style)Set the diagonal cell border style.
| Parameters: | style (int) – Border style index. Default is 1. |
|---|
Set the style for a diagonal border. The style is the same as those used in
set_border()
.
set_diag_type
(style)Set the diagonal cell border type.
| Parameters: | style (int) – Border type, 1-3. No default. |
|---|
Set the type of the diagonal border. The style should be one of the following values:
set_diag_color
(color)Set the color of the diagonal cell border.
| Parameters: | color (string) – The cell border color. |
|---|
See set_border_color()
for details on the border colors.
The Chart
module is a base class for modules that implement charts in
XlsxWriter. The information in this section is applicable to all of the
available chart subclasses, such as Area, Bar, Column, Doughnut, Line, Pie,
Scatter, Stock and Radar.
A chart object is created via the Workbook add_chart()
method where the
chart type is specified:
chart = workbook.add_chart({'type': 'column'})
It is then inserted into a worksheet as an embedded chart using the
insert_chart()
Worksheet method:
worksheet.insert_chart('A7', chart)
Or it can be set in a chartsheet using the set_chart()
Chartsheet method:
chartsheet = workbook.add_chartsheet()
# ...
chartsheet.set_chart(chart)
The following is a small working example or adding an embedded chart:
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 chart. In simplest case we add one or more 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()
The supported chart types are:
area
: Creates an Area (filled line) style chart.bar
: Creates a Bar style (transposed histogram) chart.column
: Creates a column style (histogram) chart.line
: Creates a Line style chart.pie
: Creates a Pie style chart.doughnut
: Creates a Doughnut style chart.scatter
: Creates a Scatter style chart.stock
: Creates a Stock style chart.radar
: Creates a Radar style chart.Chart subtypes are also supported for some chart types:
workbook.add_chart({'type': 'bar', 'subtype': 'stacked'})
The available subtypes are:
area
stacked
percent_stacked
bar
stacked
percent_stacked
column
stacked
percent_stacked
scatter
straight_with_markers
straight
smooth_with_markers
smooth
line
stacked
percent_stacked
radar
with_markers
filled
Methods that are common to all chart types are documented below. See Working with Charts for chart specific information.
add_series
(options)Add a data series to a chart.
| Parameters: | options (dict) – A dictionary of chart series options. |
|---|
In Excel a chart series is a collection of information that defines which data is plotted such as values, axis labels and formatting.
For an XlsxWriter chart object the add_series()
method is used to set the
properties for a series:
chart.add_series({
'categories': '=Sheet1!$A$1:$A$5',
'values': '=Sheet1!$B$1:$B$5',
'line': {'color': 'red'},
})
# Or using a list of values instead of category/value formulas:
# [sheetname, first_row, first_col, last_row, last_col]
chart.add_series({
'categories': ['Sheet1', 0, 0, 4, 0],
'values': ['Sheet1', 0, 1, 4, 1],
'line': {'color': 'red'},
})
As shown above the categories
and values
can take either a range
formula such as =Sheet1!$A$2:$A$7
or, more usefully when generating the
range programmatically, a list with zero indexed row/column values.
The series options that can be set are:
values
: This is the most important property of a series and is the only
mandatory option for every chart object. This option links the chart with
the worksheet data that it displays. The data range can be set using a
formula as shown in the first example above or using a list of values as
shown in the second example.
categories
: This sets the chart category labels. The category is more
or less the same as the X axis. In most chart types the categories
property is optional and the chart will just assume a sequential series from
1..n
.
name
: Set the name for the series. The name is displayed in the formula
bar. For non-Pie/Doughnut charts it is also displayed in the legend. The
name property is optional and if it isn’t supplied it will default to
Series 1..n
. The name can also be a formula such as =Sheet1!$A$1
or
a list with a sheetname, row and column such as ['Sheet1', 0, 0]
.
line
: Set the properties of the series line type such as color and
width. See Chart formatting: Line.
border
: Set the border properties of the series such as color and
style. See Chart formatting: Border.
fill
: Set the solid fill properties of the series such as color. See
Chart formatting: Solid Fill.
pattern
: Set the pattern fill properties of the series. See
Chart formatting: Pattern Fill.
gradient
: Set the gradient fill properties of the series. See
Chart formatting: Gradient Fill.
marker
: Set the properties of the series marker such as style and
color. See Chart series option: Marker.
trendline
: Set the properties of the series trendline such as linear,
polynomial and moving average types. See
Chart series option: Trendline.
smooth
: Set the smooth property of a line series.
y_error_bars
: Set vertical error bounds for a chart series. See
Chart series option: Error Bars.
x_error_bars
: Set horizontal error bounds for a chart series. See
Chart series option: Error Bars.
data_labels
: Set data labels for the series. See
Chart series option: Data Labels.
points
: Set properties for individual points in a series. See
Chart series option: Points.
invert_if_negative
: Invert the fill color for negative values. Usually
only applicable to column and bar charts.
overlap
: Set the overlap between series in a Bar/Column chart. The
range is +/- 100. The default is 0:
chart.add_series({
'categories': '=Sheet1!$A$1:$A$5',
'values': '=Sheet1!$B$1:$B$5',
'overlap': 10,
})
Note, it is only necessary to apply the overlap
property to one series
in the chart.
gap
: Set the gap between series in a Bar/Column chart. The range is 0
to 500. The default is 150:
chart.add_series({
'categories': '=Sheet1!$A$1:$A$5',
'values': '=Sheet1!$B$1:$B$5',
'gap': 200,
})
Note, it is only necessary to apply the gap
property to one series in
the chart.
More than one series can be added to a chart. In fact, some chart types such as
stock
require it. The series numbering and order in the Excel chart will
be the same as the order in which they are added in XlsxWriter.
It is also possible to specify non-contiguous ranges:
chart.add_series({
'categories': '=(Sheet1!$A$1:$A$9,Sheet1!$A$14:$A$25)',
'values': '=(Sheet1!$B$1:$B$9,Sheet1!$B$14:$B$25)',
})
set_x_axis
(options)Set the chart X axis options.
| Parameters: | options (dict) – A dictionary of axis options. |
|---|
The set_x_axis()
method is used to set properties of the X axis:
chart.set_x_axis({
'name': 'Earnings per Quarter',
'name_font': {'size': 14, 'bold': True},
'num_font': {'italic': True },
})
The options that can be set are:
name
name_font
name_layout
num_font
num_format
line
fill
pattern
gradient
min
max
minor_unit
major_unit
interval_unit
interval_tick
crossing
position_axis
reverse
log_base
label_position
label_align
major_gridlines
minor_gridlines
visible
date_axis
text_axis
minor_unit_type
major_unit_type
minor_tick_mark
major_tick_mark
display_units
display_units_visible
These options are explained below. Some properties are only applicable to value, category or date axes (this is noted in each case). See Chart Value and Category Axes for an explanation of Excel’s distinction between the axis types.
name
: Set the name (also known as title or caption) for the axis. The
name is displayed below the X axis. (Applicable to category, date and value
axes.):
chart.set_x_axis({'name': 'Earnings per Quarter'})
This property is optional. The default is to have no axis name.
The name can also be a formula such as =Sheet1!$A$1
or a list with a
sheetname, row and column such as ['Sheet1', 0, 0]
.
name_font
: Set the font properties for the axis name. (Applicable to
category, date and value axes.):
chart.set_x_axis({'name_font': {'bold': True, 'italic': True}})
See the Chart Fonts section for more details on font properties.
name_layout
: Set the (x, y)
position of the axis caption in chart
relative units. (Applicable to category, date and value axes.):
chart.set_x_axis({
'name': 'X axis',
'name_layout': {
'x': 0.34,
'y': 0.85,
}
})
See the Chart Layout section for more details.
num_font
: Set the font properties for the axis numbers. (Applicable to
category, date and value axes.):
chart.set_x_axis({'name_font': {'bold': True, 'italic': True}})
See the Chart Fonts section for more details on font properties.
num_format
: Set the number format for the axis. (Applicable to
category, date and value axes.):
chart.set_x_axis({'num_format': '#,##0.00'})
chart.set_y_axis({'num_format': '0.00%'})
The number format is similar to the Worksheet Cell Format num_format
apart from the fact that a format index cannot be used. An explicit format
string must be used as shown above. See set_num_format()
for more
information.
line
: Set the properties of the axis line type such as color and
width. See Chart formatting: Line:
chart.set_x_axis({'line': {'none': True}})
fill
: Set the solid fill properties of the axis such as color. See
Chart formatting: Solid Fill. Note, in Excel the axis fill is applied to
the area of the numbers of the axis and not to the area of the axis bounding
box. That background is set from the chartarea fill.
pattern
: Set the pattern fill properties of the axis. See
Chart formatting: Pattern Fill.
gradient
: Set the gradient fill properties of the axis. See
Chart formatting: Gradient Fill.
min
: Set the minimum value for the axis range. (Applicable to value and
date axes only.):
chart.set_x_axis({'min': 3, 'max': 6})
max
: Set the maximum value for the axis range. (Applicable to value and
date axes only.)
minor_unit
: Set the increment of the minor units in the axis range.
(Applicable to value and date axes only.):
chart.set_x_axis({'minor_unit': 0.4, 'major_unit': 2})
major_unit
: Set the increment of the major units in the axis range.
(Applicable to value and date axes only.)
interval_unit
: Set the interval unit for a category axis. Should be an
integer value. (Applicable to category axes only.):
chart.set_x_axis({'interval_unit': 5})
interval_tick
: Set the tick interval for a category axis. Should be an
integer value. (Applicable to category axes only.):
chart.set_x_axis({'interval_tick': 2})
crossing
: Set the position where the y axis will cross the x axis.
(Applicable to all axes.)
The crossing
value can either be the string 'max'
to set the
crossing at the maximum axis value or a numeric value:
chart.set_x_axis({'crossing': 3})
chart.set_y_axis({'crossing': 'max'})
For category axes the numeric value must be an integer to represent the category number that the axis crosses at. For value and date axes it can have any value associated with the axis. See also Chart Value and Category Axes.
If crossing is omitted (the default) the crossing will be set automatically by Excel based on the chart data.
position_axis
: Position the axis on or between the axis tick marks.
(Applicable to category axes only.)
There are two allowable values on_tick
and between
:
chart.set_x_axis({'position_axis': 'on_tick'})
chart.set_x_axis({'position_axis': 'between'})
reverse
: Reverse the order of the axis categories or values.
(Applicable to category, date and value axes.):
chart.set_x_axis({'reverse': True})
log_base
: Set the log base of the axis range. (Applicable to value axes
only.):
chart.set_y_axis({'log_base': 10})
label_position
: Set the “Axis labels” position for the axis. The
following positions are available:
next_to (the default)
high
low
none
For example:
chart.set_x_axis({'label_position': 'high'})
chart.set_y_axis({'label_position': 'low'})
label_align
: Align the “Axis labels” the axis. (Applicable to category
axes only.)
The following Excel alignments are available:
center (the default)
right
left
For example:
chart.set_x_axis({'label_align': 'left'})
major_gridlines
: Configure the major gridlines for the axis. The
available properties are:
visible
line
For example:
chart.set_x_axis({
'major_gridlines': {
'visible': True,
'line': {'width': 1.25, 'dash_type': 'dash'}
},
})
The visible
property is usually on for the X axis but it depends on the
type of chart.
The line
property sets the gridline properties such as color and
width. See Chart Formatting.
minor_gridlines
: This takes the same options as major_gridlines
above.
The minor gridline visible
property is off by default for all chart
types.
visible
: Configure the visibility of the axis:
chart.set_y_axis({'visible': False})
Axes are visible by default.
date_axis
: This option is used to treat a category axis with date or
time data as a Date Axis. (Applicable to date category axes only.):
chart.set_x_axis({'date_axis': True})
This option also allows you to set max
and min
values for a
category axis which isn’t allowed by Excel for non-date category axes.
See Date Category Axes for more details.
text_axis
: This option is used to treat a category axis explicitly
as a Text Axis. (Applicable to category axes only.):
chart.set_x_axis({'text_axis': True})
minor_unit_type
: For date_axis
axes, see above, this option is used
to set the type of the minor units. (Applicable to date category axes
only.):
chart.set_x_axis({
'date_axis': True,
'minor_unit': 4,
'minor_unit_type': 'months',
})
major_unit_type
: Same as minor_unit_type
, see above, but for major
axes unit types.
minor_tick_mark
: Set the axis minor tick mark type/position to one of
the following values:
none
inside
outside
cross (inside and outside)
For example:
chart.set_x_axis({'major_tick_mark': 'none',
'minor_tick_mark': 'inside'})
major_tick_mark
: Same as minor_tick_mark
, see above, but for major
axes ticks.
display_units
: Set the display units for the axis. This can be useful if
the axis numbers are very large but you don’t want to represent them in
scientific notation. The available display units are:
hundreds
thousands
ten_thousands
hundred_thousands
millions
ten_millions
hundred_millions
billions
trillions
Applicable to value axes only.:
chart.set_x_axis({'display_units': 'thousands'})
chart.set_y_axis({'display_units': 'millions'})
display_units_visible
: Control the visibility of the display units
turned on by the previous option. This option is on by default. (Applicable
to value axes only.):
chart.set_x_axis({'display_units': 'hundreds',
'display_units_visible': False})
set_y_axis
(options)Set the chart Y axis options.
| Parameters: | options (dict) – A dictionary of axis options. |
|---|
The set_y_axis()
method is used to set properties of the Y axis.
The properties that can be set are the same as for set_x_axis
, see above.
set_x2_axis
(options)Set the chart secondary X axis options.
| Parameters: | options (dict) – A dictionary of axis options. |
|---|
The set_x2_axis()
method is used to set properties of the secondary X axis,
see chart_secondary_axes()
.
The properties that can be set are the same as for set_x_axis
, see above.
The default properties for this axis are:
'label_position': 'none',
'crossing': 'max',
'visible': False,
set_y2_axis
(options)Set the chart secondary Y axis options.
| Parameters: | options (dict) – A dictionary of axis options. |
|---|
The set_y2_axis()
method is used to set properties of the secondary Y axis,
see chart_secondary_axes()
.
The properties that can be set are the same as for set_x_axis
, see above.
The default properties for this axis are:
'major_gridlines': {'visible': True}
combine
(chart)Combine two charts of different types.
| Parameters: | chart – A chart object created with add_chart() . |
|---|
The chart combine()
method is used to combine two charts of different
types, for example a column and line chart:
# Create a primary chart.
column_chart = workbook.add_chart({'type': 'column'})
column_chart.add_series({...})
# Create a secondary chart.
line_chart = workbook.add_chart({'type': 'line'})
line_chart.add_series({...})
# Combine the charts.
column_chart.combine(line_chart)
See the Combined Charts section for more details.
The set_size()
method is used to set the dimensions of the chart. The size
properties that can be set are:
width
height
x_scale
y_scale
x_offset
y_offset
The width
and height
are in pixels. The default chart width x height is
480 x 288 pixels. The size of the chart can be modified by setting the
width
and height
or by setting the x_scale
and y_scale
:
chart.set_size({'width': 720, 'height': 576})
# Same as:
chart.set_size({'x_scale': 1.5, 'y_scale': 2})
The x_offset
and y_offset
position the top left corner of the chart in
the cell that it is inserted into.
Note: the x_offset
and y_offset
parameters can also be set via the
insert_chart()
method:
worksheet.insert_chart('E2', chart, {'x_offset': 25, 'y_offset': 10})
set_title
(options)Set the chart title options.
| Parameters: | options (dict) – A dictionary of chart size options. |
|---|
The set_title()
method is used to set properties of the chart title:
chart.set_title({'name': 'Year End Results'})
The properties that can be set are:
name
: Set the name (title) for the chart. The name is displayed above
the chart. The name can also be a formula such as =Sheet1!$A$1
or a list
with a sheetname, row and column such as ['Sheet1', 0, 0]
. The name
property is optional. The default is to have no chart title.
name_font
: Set the font properties for the chart title. See
Chart Fonts.
overlay
: Allow the title to be overlaid on the chart. Generally used
with the layout property below.
layout
: Set the (x, y)
position of the title in chart relative
units:
chart.set_title({
'name': 'Title',
'overlay': True,
'layout': {
'x': 0.42,
'y': 0.14,
}
})
See the Chart Layout section for more details.
none
: By default Excel adds an automatic chart title to charts with a
single series and a user defined series name. The none
option turns this
default title off. It also turns off all other set_title()
options:
chart.set_title({'none': True})
set_legend
(options)Set the chart legend options.
| Parameters: | options (dict) – A dictionary of chart legend options. |
|---|
The set_legend()
method is used to set properties of the chart legend. For
example it can be used to turn off the default chart legend:
chart.set_legend({'none': True})
The options that can be set are:
none
position
font
border
fill
pattern
gradient
delete_series
layout
none
: In Excel chart legends are on by default. The none
option
turns off the chart legend:
chart.set_legend({'none': True})
For backward compatibility, it is also possible to turn off the legend via
the position
property:
chart.set_legend({'position': 'none'})
position
: Set the position of the chart legend:
chart.set_legend({'position': 'bottom'})
The default legend position is right
. The available positions are:
top
bottom
left
right
overlay_left
overlay_right
none
font
: Set the font properties of the chart legend:
chart.set_legend({'font': {'size': 9, 'bold': True}})
See the Chart Fonts section for more details on font properties.
border
: Set the border properties of the legend such as color and
style. See Chart formatting: Border.
fill
: Set the solid fill properties of the legend such as color. See
Chart formatting: Solid Fill.
pattern
: Set the pattern fill properties of the legend. See
Chart formatting: Pattern Fill.
gradient
: Set the gradient fill properties of the legend. See
Chart formatting: Gradient Fill.
delete_series
: This allows you to remove one or more series from the
legend (the series will still display on the chart). This property takes
a list as an argument and the series are zero indexed:
# Delete/hide series index 0 and 2 from the legend.
chart.set_legend({'delete_series': [0, 2]})
layout
: Set the (x, y)
position of the legend in chart relative
units:
chart.set_legend({
'layout': {
'x': 0.80,
'y': 0.37,
'width': 0.12,
'height': 0.25,
}
})
See the Chart Layout section for more details.
set_chartarea
(options)Set the chart area options.
| Parameters: | options (dict) – A dictionary of chart area options. |
|---|
The set_chartarea()
method is used to set the properties of the chart area.
In Excel the chart area is the background area behind the chart:
chart.set_chartarea({
'border': {'none': True},
'fill': {'color': 'red'}
})
The properties that can be set are:
border
: Set the border properties of the chartarea such as color and
style. See Chart formatting: Border.fill
: Set the solid fill properties of the chartarea such as color. See
Chart formatting: Solid Fill.pattern
: Set the pattern fill properties of the chartarea. See
Chart formatting: Pattern Fill.gradient
: Set the gradient fill properties of the chartarea. See
Chart formatting: Gradient Fill.set_plotarea
(options)Set the plot area options.
| Parameters: | options (dict) – A dictionary of plot area options. |
|---|
The set_plotarea()
method is used to set properties of the plot area of a
chart. In Excel the plot area is the area between the axes on which the chart
series are plotted:
chart.set_plotarea({
'border': {'color': 'red', 'width': 2, 'dash_type': 'dash'},
'fill': {'color': '#FFFFC2'}
})
The properties that can be set are:
border
: Set the border properties of the plotarea such as color and
style. See Chart formatting: Border.
fill
: Set the solid fill properties of the plotarea such as color. See
Chart formatting: Solid Fill.
pattern
: Set the pattern fill properties of the plotarea. See
Chart formatting: Pattern Fill.
gradient
: Set the gradient fill properties of the plotarea. See
Chart formatting: Gradient Fill.
layout
: Set the (x, y)
position of the plotarea in chart relative
units:
chart.set_plotarea({
'layout': {
'x': 0.13,
'y': 0.26,
'width': 0.73,
'height': 0.57,
}
})
See the Chart Layout section for more details.
set_style
(style_id)Set the chart style type.
| Parameters: | style_id (int) – An index representing the chart style. |
|---|
The set_style()
method is used to set the style of the chart to one of the
48 built-in styles available on the ‘Design’ tab in Excel:
chart.set_style(37)
The style index number is counted from 1 on the top left. The default style is 2.
Note
In Excel 2013 the Styles section of the ‘Design’ tab in Excel shows what
were referred to as ‘Layouts’ in previous versions of Excel. These layouts
are not defined in the file format. They are a collection of modifications
to the base chart type. They can be replicated using the XlsxWriter Chart
API but they cannot be defined by the set_style()
method.
set_table
(options)Set properties for an axis data table.
| Parameters: | options (dict) – A dictionary of axis table options. |
|---|
The set_table()
method adds a data table below the horizontal axis with the
data used to plot the chart:
chart.set_table()
The available options, with default values are:
'horizontal': True # Display vertical lines in the table.
'vertical': True # Display horizontal lines in the table.
'outline': True # Display an outline in the table.
'show_keys': False # Show the legend keys with the table data.
'font': {} # Standard chart font properties.
For example:
chart.set_table({'show_keys': True})
The data table can only be shown with Bar, Column, Line, Area and stock charts. See the Chart Fonts section for more details on font properties.
set_up_down_bars
(options)Set properties for the chart up-down bars.
| Parameters: | options (dict) – A dictionary of options. |
|---|
The set_up_down_bars()
method adds Up-Down bars to Line charts to indicate
the difference between the first and last data series:
chart.set_up_down_bars()
It is possible to format the up and down bars to add fill
, pattern
or
gradient
and border
properties if required. See
Chart Formatting:
chart.set_up_down_bars({
'up': {
'fill': {'color': '#00B050'},
'border': {'color': 'black'}
},
'down': {
'fill': {'color': 'red'},
'border': {'color': 'black'},
},
})
Up-down bars can only be applied to Line charts and to Stock charts (by default).
set_drop_lines
(options)Set properties for the chart drop lines.
| Parameters: | options (dict) – A dictionary of options. |
|---|
The set_drop_lines()
method adds Drop Lines to charts to show the Category
value of points in the data:
chart.set_drop_lines()
It is possible to format the Drop Line line
properties if required. See
Chart Formatting:
chart.set_drop_lines({'line': {'color': 'red',
'dash_type': 'square_dot'}})
Drop Lines are only available in Line, Area and Stock charts.
set_high_low_lines
(options)Set properties for the chart high-low lines.
| Parameters: | options (dict) – A dictionary of options. |
|---|
The set_high_low_lines()
method adds High-Low lines to charts to show the
maximum and minimum values of points in a Category:
chart.set_high_low_lines()
It is possible to format the High-Low Line line
properties if required. See
Chart Formatting:
chart.set_high_low_lines({
'line': {
'color': 'red',
'dash_type': 'square_dot'
}
})
High-Low Lines are only available in Line and Stock charts.
show_blanks_as
(option)Set the option for displaying blank data in a chart.
| Parameters: | option (string) – A string representing the display option. |
|---|
The show_blanks_as()
method controls how blank data is displayed in a
chart:
chart.show_blanks_as('span')
The available options are:
'gap' # Blank data is shown as a gap. The default.
'zero' # Blank data is displayed as zero.
'span' # Blank data is connected with a line.
set_rotation
(rotation)Set the Pie/Doughnut chart rotation.
| Parameters: | rotation (int) – The angle of rotation. |
|---|
The set_rotation()
method is used to set the rotation of the first segment
of a Pie/Doughnut chart. This has the effect of rotating the entire chart:
chart->set_rotation(90)
The angle of rotation must be in the range 0 <= rotation <= 360
.
This option is only available for Pie/Doughnut charts.
set_hole_size
(size)Set the Doughnut chart hole size.
| Parameters: | size (int) – The hole size as a percentage. |
|---|
The set_hole_size()
method is used to set the hole size of a Doughnut
chart:
chart->set_hole_size(33)
The value of the hole size must be in the range 10 <= size <= 90
.
This option is only available for Doughnut charts.
See also Working with Charts and Chart Examples.
In Excel a chartsheet is a worksheet that only contains a chart.
The Chartsheet class has some of the functionality of data Worksheets such as tab selection, headers, footers, margins and print properties but its primary purpose is to display a single chart. This makes it different from ordinary data worksheets which can have one or more embedded charts.
Like a data worksheet a chartsheet object isn’t instantiated directly. Instead
a new chartsheet is created by calling the add_chartsheet()
method
from a Workbook object:
workbook = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet() # Required for the chart data.
chartsheet = workbook.add_chartsheet()
#...
workbook.close()
A chartsheet object functions as a worksheet and not as a chart. In order to have it display data a Chart object must be created and added to the chartsheet:
chartsheet = workbook.add_chartsheet()
chart = workbook.add_chart({'type': 'bar'})
# Configure the chart.
chartsheet.set_chart(chart)
The data for the chartsheet chart must be contained on a separate worksheet. That is why it is always created in conjunction with at least one data worksheet, as shown above.
set_chart
(chart)Add a chart to a chartsheet.
| Parameters: | chart – A chart object. |
|---|
The set_chart()
method is used to insert a chart into a chartsheet. A chart
object is created via the Workbook add_chart()
method where the chart
type is specified:
chart = workbook.add_chart({type, 'column'})
chartsheet.set_chart(chart)
Only one chart can be added to an individual chartsheet.
See The Chart Class, Working with Charts and Chart Examples.
The following The Worksheet Class methods are also available through a chartsheet:
activate()
select()
hide()
set_first_sheet()
protect()
set_zoom()
set_tab_color()
set_landscape()
set_portrait()
set_paper()
set_margins()
set_header()
set_footer()
get_name()
For example:
chartsheet.set_tab_color('#FF9900')
The set_zoom()
method can be used to modify the displayed size of the
chart.
See Example: Chartsheet.
The Exception class contains the various exceptions that can be raised by XlsxWriter. In general XlsxWriter only raised exceptions for un-recoverable errors or for errors that would lead to file corruption such as creating two worksheets with the same name.
The hierarchy of exceptions in XlsxWriter is:
XlsxWriterException(Exception)
XlsxFileError(XlsxWriterException)
FileCreateError(XlsxFileError)
UndefinedImageSize(XlsxFileError)
UndefinedImageSize(XlsxFileError)
FileSizeError(XlsxFileError)
XlsxInputError(XlsxWriterException)
DuplicateTableName(XlsxInputError)
InvalidWorksheetName(XlsxInputError)
DuplicateWorksheetName(XlsxInputError)
XlsxWriterException
Base exception for XlsxWriter.
XlsxFileError
Base exception for all file related errors.
XlsxInputError
Base exception for all input data related errors.
FileCreateError
This exception is raised if there is a file permission, or IO error, when writing the xlsx file to disk. This can be caused by an non-existent directory or (in Windows) if the file is already open in Excel:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet = workbook.add_worksheet()
# The file exception.xlsx is already open in Excel.
workbook.close()
Raises:
xlsxwriter.exceptions.FileCreateError:
[Errno 13] Permission denied: 'exception.xlsx'
This exception can be caught in a try
block where you can instruct the
user to close the open file before overwriting it:
while True:
try:
workbook.close()
except xlsxwriter.exceptions.FileCreateError as e:
# For Python 3 use input() instead of raw_input().
decision = raw_input("Exception caught in workbook.close(): %s\n"
"Please close the file if it is open in Excel.\n"
"Try to write file again? [Y/n]: " % e)
if decision != 'n':
continue
break
See also Example: Catch exception on closing.
UndefinedImageSize
This exception is raised if an image added via insert_image()
doesn’t
contain height or width information. The exception is raised during Workbook
close()
:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet = workbook.add_worksheet()
worksheet.insert_image('A1', 'logo.png')
workbook.close()
Raises:
xlsxwriter.exceptions.UndefinedImageSize:
logo.png: no size data found in image file.
Note
This is a relatively rare error that is most commonly caused by XlsxWriter failing to parse the dimensions of the image rather than the image not containing the information. In these cases you should raise a GitHub issue with the image attached, or provided via a link.
UnsupportedImageFormat
This exception is raised if if an image added via insert_image()
isn’t
one of the supported file formats: PNG, JPEG, BMP, WMF or EMF. The exception
is raised during Workbook close()
:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet = workbook.add_worksheet()
worksheet.insert_image('A1', 'logo.xyz')
workbook.close()
Raises:
xlsxwriter.exceptions.UnsupportedImageFormat:
logo.xyz: Unknown or unsupported image file format.
Note
If the image type is one of the supported types, and you are sure that the file format is correct, then the exception may be caused by XlsxWriter failing to parse the type of the image correctly. In these cases you should raise a GitHub issue with the image attached, or provided via a link.
FileSizeError
This exception is raised if one of the XML files that is part of the xlsx file, or the xlsx file itself, exceeds 4GB in size:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet = workbook.add_worksheet()
# Write lots of data to create a very big file.
workbook.close()
Raises:
xlsxwriter.exceptions.FileSizeError:
Filesize would require ZIP64 extensions. Use workbook.use_zip64().
As noted in the exception message, files larger than 4GB can be created by
turning on the zipfile.py ZIP64 extensions using the use_zip64()
method.
EmptyChartSeries
This exception is raised if a chart is added to a worksheet without a data
series. The exception is raised during Workbook close()
:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet = workbook.add_worksheet()
chart = workbook.add_chart({'type': 'column'})
worksheet.insert_chart('A7', chart)
workbook.close()
Raises:
xlsxwriter.exceptions.EmptyChartSeries:
Chart1 must contain at least one data series. See chart.add_series().
DuplicateTableName
This exception is raised if a duplicate worksheet table name in used via
add_table()
. The exception is raised during Workbook close()
:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet = workbook.add_worksheet()
worksheet.add_table('B1:F3', {'name': 'SalesData'})
worksheet.add_table('B4:F7', {'name': 'SalesData'})
workbook.close()
Raises:
xlsxwriter.exceptions.DuplicateTableName:
Duplicate name 'SalesData' used in worksheet.add_table().
InvalidWorksheetName
This exception is raised during Workbook add_worksheet()
if a
worksheet name is too long or contains restricted characters.
For example with a 32 character worksheet name:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
name = 'name_that_is_longer_than_thirty_one_characters'
worksheet = workbook.add_worksheet(name)
workbook.close()
Raises:
xlsxwriter.exceptions.InvalidWorksheetName:
Excel worksheet name 'name_that_is_longer_than_thirty_one_characters'
must be <= 31 chars.
Or for a worksheet name containing one of the Excel restricted characters,
i.e. [ ] : * ? / \
:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet = workbook.add_worksheet('Data[Jan]')
workbook.close()
Raises:
xlsxwriter.exceptions.InvalidWorksheetName:
Invalid Excel character '[]:*?/\' in sheetname 'Data[Jan]'.
Or for a worksheet name start or ends with an apostrophe:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet = workbook.add_worksheet("'Sheet1'")
workbook.close()
Raises:
xlsxwriter.exceptions.InvalidWorksheetName:
Sheet name cannot start or end with an apostrophe "'Sheet1'".
DuplicateWorksheetName
This exception is raised during Workbook add_worksheet()
if a
worksheet name has already been used. As with Excel the check is case
insensitive:
import xlsxwriter
workbook = xlsxwriter.Workbook('exception.xlsx')
worksheet1 = workbook.add_worksheet('Sheet1')
worksheet2 = workbook.add_worksheet('sheet1')
workbook.close()
Raises:
xlsxwriter.exceptions.DuplicateWorksheetName:
Sheetname 'sheet1', with case ignored, is already in use.
XlsxWriter supports two forms of notation to designate the position of cells: Row-column notation and A1 notation.
Row-column notation uses a zero based index for both row and column while A1 notation uses the standard Excel alphanumeric sequence of column letter and 1-based row. For example:
(0, 0) # Row-column notation.
('A1') # The same cell in A1 notation.
(6, 2) # Row-column notation.
('C7') # The same cell in A1 notation.
Row-column notation is useful if you are referring to cells programmatically:
for row in range(0, 5):
worksheet.write(row, 0, 'Hello')
A1 notation is useful for setting up a worksheet manually and for working with formulas:
worksheet.write('H1', 200)
worksheet.write('H2', '=H1+1')
In general when using the XlsxWriter module you can use A1 notation anywhere you can use row-column notation. This also applies to methods that take a range of cells:
worksheet.merge_range(2, 1, 3, 3, 'Merged Cells', merge_format)
worksheet.merge_range('B3:D4', 'Merged Cells', merge_format)
XlsxWriter supports Excel’s worksheet limits of 1,048,576 rows by 16,384 columns.
Note
In Excel you can specify row or column ranges such as 1:1
for all of the
first row or A:A
for all of the first column. In XlsxWriter these can be
set by specifying the full cell range for the row or column:
worksheet.print_area('A1:XFD1') # Same as 1:1
worksheet.print_area('A1:A1048576') # Same as A:A
This is actually how Excel stores ranges such as 1:1
and A:A
internally.
These ranges can also be specified using row-column notation, as explained above:
worksheet.print_area(0, 0, 0, 16383) # Same as 1:1
worksheet.print_area(0, 0, 1048575, 0) # Same as A:A
To select the entire worksheet range you can specify
A1:XFD1048576
.
When dealing with Excel cell references it is important to distinguish between relative and absolute cell references in Excel.
Relative cell references change when they are copied while Absolute references maintain fixed row and/or column references. In Excel absolute references are prefixed by the dollar symbol as shown below:
'A1' # Column and row are relative.
'$A1' # Column is absolute and row is relative.
'A$1' # Column is relative and row is absolute.
'$A$1' # Column and row are absolute.
See the Microsoft Office documentation for more information on relative and absolute references.
Some functions such as conditional_format()
may require absolute
references, depending on the range being specified.
It is also possible to define and use “Defined names/Named ranges” in
workbooks and worksheets, see define_name()
:
workbook.define_name('Exchange_rate', '=0.96')
worksheet.write('B3', '=B2*Exchange_rate')
See also Example: Defined names/Named ranges.
The XlsxWriter
utility
module contains several helper functions for
dealing with A1 notation as shown below. These functions can be imported as
follows:
from xlsxwriter.utility import xl_rowcol_to_cell
cell = xl_rowcol_to_cell(1, 2) # C2
xl_rowcol_to_cell
(row, col[, row_abs, col_abs])Convert a zero indexed row and column cell reference to a A1 style string.
| Parameters: | |
|---|---|
| Return type: | A1 style string. |
The xl_rowcol_to_cell()
function converts a zero indexed row and column
cell values to an A1
style string:
cell = xl_rowcol_to_cell(0, 0) # A1
cell = xl_rowcol_to_cell(0, 1) # B1
cell = xl_rowcol_to_cell(1, 0) # A2
The optional parameters row_abs
and col_abs
can be used to indicate
that the row or column is absolute:
str = xl_rowcol_to_cell(0, 0, col_abs=True) # $A1
str = xl_rowcol_to_cell(0, 0, row_abs=True) # A$1
str = xl_rowcol_to_cell(0, 0, row_abs=True, col_abs=True) # $A$1
xl_cell_to_rowcol
(cell_str)Convert a cell reference in A1 notation to a zero indexed row and column.
| Parameters: | cell_str (string) – A1 style string, absolute or relative. |
|---|---|
| Return type: | Tuple of ints for (row, col) |
The xl_cell_to_rowcol()
function converts an Excel cell reference in A1
notation to a zero based row and column. The function will also handle Excel’s
absolute, $
, cell notation:
(row, col) = xl_cell_to_rowcol('A1') # (0, 0)
(row, col) = xl_cell_to_rowcol('B1') # (0, 1)
(row, col) = xl_cell_to_rowcol('C2') # (1, 2)
(row, col) = xl_cell_to_rowcol('$C2') # (1, 2)
(row, col) = xl_cell_to_rowcol('C$2') # (1, 2)
(row, col) = xl_cell_to_rowcol('$C$2') # (1, 2)
xl_col_to_name
(col[, col_abs])Convert a zero indexed column cell reference to a string.
| Parameters: | |
|---|---|
| Return type: | Column style string. |
The xl_col_to_name()
converts a zero based column reference to a string:
column = xl_col_to_name(0) # A
column = xl_col_to_name(1) # B
column = xl_col_to_name(702) # AAA
The optional parameter col_abs
can be used to indicate if the column is
absolute:
column = xl_col_to_name(0, False) # A
column = xl_col_to_name(0, True) # $A
column = xl_col_to_name(1, True) # $B
xl_range
(first_row, first_col, last_row, last_col)Converts zero indexed row and column cell references to a A1:B1 range string.
| Parameters: | |
|---|---|
| Return type: | A1:B1 style range string. |
The xl_range()
function converts zero based row and column cell references
to an A1:B1
style range string:
cell_range = xl_range(0, 0, 9, 0) # A1:A10
cell_range = xl_range(1, 2, 8, 2) # C2:C9
cell_range = xl_range(0, 0, 3, 4) # A1:E4
xl_range_abs
(first_row, first_col, last_row, last_col)Converts zero indexed row and column cell references to a $A$1:$B$1 absolute range string.
| Parameters: | |
|---|---|
| Return type: | $A$1:$B$1 style range string. |
The xl_range_abs()
function converts zero based row and column cell
references to an absolute $A$1:$B$1
style range string:
cell_range = xl_range_abs(0, 0, 9, 0) # $A$1:$A$10
cell_range = xl_range_abs(1, 2, 8, 2) # $C$2:$C$9
cell_range = xl_range_abs(0, 0, 3, 4) # $A$1:$E$4
The following sections explain how to write various types of data to an Excel worksheet using XlsxWriter.
The worksheet
write()
method is the most common means of writing
Python data to cells based on its type:
import xlsxwriter
workbook = xlsxwriter.Workbook('write_data.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, 1234) # Writes an int
worksheet.write(1, 0, 1234.56) # Writes a float
worksheet.write(2, 0, 'Hello') # Writes a string
worksheet.write(3, 0, None) # Writes None
worksheet.write(4, 0, True) # Writes a bool
workbook.close()
The write()
method uses the type()
of the data to determine which
specific method to use for writing the data. These methods then map some basic
Python types to corresponding Excel types. The mapping is as follows:
| Python type | Excel type | Worksheet methods |
|---|---|---|
int |
Number | write() , write_number() |
long |
||
float |
||
Decimal |
||
Fraction |
||
basestring |
String | write() , write_string() |
str |
||
unicode |
||
None |
String (blank) | write() , write_blank() |
datetime.date |
Number | write() , write_datetime() |
datetime.datetime |
||
datetime.time |
||
datetime.timedelta |
||
bool |
Boolean | write() , write_boolean() |
The write()
method also handles a few other Excel types that are
encoded as Python strings in XlsxWriter:
| Pseudo-type | Excel type | Worksheet methods |
|---|---|---|
| formula string | Formula | write() , write_formula() |
| url string | URL | write() , write_url() |
It should be noted that Excel has a very limited set of types to map to. The
Python types that the write()
method can handle can be extended as
explained in the Writing user defined types section below.
Unicode data in Excel is encoded as UTF-8. XlsxWriter also supports writing UTF-8 data. This generally requires that your source file in also UTF-8 encoded:
# _*_ coding: utf-8
# Python 2 example.
worksheet.write('A1', u'Some UTF-8 text')
See Example: Simple Unicode with Python 2 and Example: Simple Unicode with Python 3 for more complete examples.
Alternatively, you can read data from an encoded file, convert it to UTF-8 during reading and then write the data to an Excel file. See Example: Unicode - Polish in UTF-8 and Example: Unicode - Shift JIS.
Writing compound data types such as lists with XlsxWriter is done the same way
it would be in any other Python program: with a loop. The Python
enumerate()
function is also very useful in this context:
import xlsxwriter
workbook = xlsxwriter.Workbook('write_list.xlsx')
worksheet = workbook.add_worksheet()
my_list = [1, 2, 3, 4, 5]
for row_num, data in enumerate(my_list):
worksheet.write(row_num, 0, data)
workbook.close()
Or if you wanted to write this horizontally as a row:
import xlsxwriter
workbook = xlsxwriter.Workbook('write_list.xlsx')
worksheet = workbook.add_worksheet()
my_list = [1, 2, 3, 4, 5]
for col_num, data in enumerate(my_list):
worksheet.write(0, col_num, data)
workbook.close()
For a list of lists structure you would use two loop levels:
import xlsxwriter
workbook = xlsxwriter.Workbook('write_list.xlsx')
worksheet = workbook.add_worksheet()
my_list = [[1, 1, 1, 1, 1],
[2, 2, 2, 2, 1],
[3, 3, 3, 3, 1],
[4, 4, 4, 4, 1],
[5, 5, 5, 5, 1]]
for row_num, row_data in enumerate(my_list):
for col_num, col_data in enumerate(row_data):
worksheet.write(row_num, col_num, col_data)
workbook.close()
The worksheet class has two utility functions called
write_row()
and write_column()
which are basically a loop around
the write()
method:
import xlsxwriter
workbook = xlsxwriter.Workbook('write_list.xlsx')
worksheet = workbook.add_worksheet()
my_list = [1, 2, 3, 4, 5]
worksheet.write_row(0, 1, my_list)
worksheet.write_column(1, 0, my_list)
workbook.close()
Unlike lists there is no single simple way to write a Python dictionary to an Excel worksheet using Xlsxwriter. The method will depend of the structure of the data in the dictionary. Here is a simple example for a simple data structure:
import xlsxwriter
workbook = xlsxwriter.Workbook('write_dict.xlsx')
worksheet = workbook.add_worksheet()
my_dict = {'Bob': [10, 11, 12],
'Ann': [20, 21, 22],
'May': [30, 31, 32]}
col_num = 0
for key, value in my_dict.items():
worksheet.write(0, col_num, key)
worksheet.write_column(1, col_num, value)
col_num += 1
workbook.close()
The best way to deal with dataframes or complex data structure is to use Python Pandas. Pandas is a Python data analysis library. It can read, filter and re-arrange small and large data sets and output them in a range of formats including Excel.
To use XlsxWriter with Pandas you specify it as the Excel writer engine:
import pandas as pd
# Create a Pandas dataframe from the 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()
The output from this would look like the following:
For more information on using Pandas with XlsxWriter see Working with Python Pandas and XlsxWriter.
As shown in the the first section above, the worksheet write()
method
maps the main Python data types to Excel’s data types. If you want to write an
unsupported type then you can either avoid write()
and map the user type
in your code to one of the more specific write methods or you can extend it
using the add_write_handler()
method. This can occasionally be more
convenient then adding a lot of if/else logic to your code.
As an example, say you wanted to modify write()
to automatically write
uuid
types as strings. You would start by creating a function that
takes the uuid, converts it to a string and then writes it using
write_string()
:
def write_uuid(worksheet, row, col, uuid, format=None):
return worksheet.write_string(row, col, str(uuid), format)
You could then add a handler that matches the uuid
type and calls your
user defined function:
# match, action()
worksheet.add_write_handler(uuid.UUID, write_uuid)
Then you can use write()
without further modification:
my_uuid = uuid.uuid3(uuid.NAMESPACE_DNS, 'python.org')
# Write the UUID. This would raise a TypeError without the handler.
worksheet.write('A1', my_uuid)
Multiple callback functions can be added using add_write_handler()
but
only one callback action is allowed per type. However, it is valid to use the
same callback function for different types:
worksheet.add_write_handler(int, test_number_range)
worksheet.add_write_handler(float, test_number_range)
The write()
method is mainly a large if()
statement that checks the
type()
of the input value and call the appropriate worksheet method to
write the data. The add_write_handler()
method works by injecting
additional type checks and associated actions into this if()
statement.
Here is a simplified version of the write()
method:
def write(self, row, col, *args):
# The first arg should be the token for all write calls.
token = args[0]
# Get the token type.
token_type = type(token)
# Check for any user defined type handlers with callback functions.
if token_type in self.write_handlers:
write_handler = self.write_handlers[token_type]
function_return = write_handler(self, row, col, *args)
# If the return value is None then the callback has returned
# control to this function and we should continue as
# normal. Otherwise we return the value to the caller and exit.
if function_return is None:
pass
else:
return function_return
# Check for standard Python types, if we haven't returned already.
if token_type is bool:
return self.write_boolean(row, col, *args)
# Etc. ...
Functions used in the add_write_handler()
method should have the
following method signature/parameters:
def my_function(worksheet, row, col, token, format=None):
return worksheet.write_string(row, col, token, format)
The function will be passed a worksheet instance, an
integer row
and col
value, a token
that matches the type added to
add_write_handler()
and some additional parameters. Usually the
additional parameter(s) will only be a cell format
instance. However, if you need to handle other additional parameters, such as
those passed to write_url()
then you can have more generic handling
like this:
def my_function(worksheet, row, col, token, *args):
return worksheet.write_string(row, col, token, *args)
Note, you don’t have to explicitly handle A1
style cell ranges. These will
be converted to row and column values prior to your function being called.
You can also make use of the row
and col
parameters to control the
logic of the function. Say for example you wanted to hide/replace user
passwords with ‘****’ when writing string data. If your data was
structured so that the password data was in the second column, apart from the
header row, you could write a handler function like this:
def hide_password(worksheet, row, col, string, format=None):
if col == 1 and row > 0:
return worksheet.write_string(row, col, '****', format)
else:
return worksheet.write_string(row, col, string, format)
Functions used in the add_write_handler()
method should return one of
the following values:
None
: to indicate that control is return to the parent write()
method to continue as normal. This is used if your handler function logic
decides that you don’t need to handle the matched token.write_xxx()
function. This is generally 0
for no error and a negative number errors. This causes an immediate return
from the calling write()
method with the return value that was passed
back.For example, say you wanted to ignore NaN
values in your data since Excel
doesn’t support them. You could create a handler function like the following
that matched against floats and which wrote a blank cell if it was a NaN
or else just returned to write()
to continue as normal:
def ignore_nan(worksheet, row, col, number, format=None):
if math.isnan(number):
return worksheet.write_blank(row, col, None, format)
else:
# Return control to the calling write() method.
return None
If you wanted to just drop the NaN
values completely and not add any
formatting to the cell you could just return 0, for no error:
def ignore_nan(worksheet, row, col, number, format=None):
if math.isnan(number):
return 0
else:
# Return control to the calling write() method.
return None
See the following, more complete, examples of handling user data types:
In general a formula in Excel can be used directly in the
write_formula()
method:
worksheet.write_formula('A1', '=10*B1 + C1')
However, there are a few potential issues and differences that the user should be aware of. These are explained in the following sections.
Excel stores formulas in the format of the US English version, regardless of the language or locale of the end-user’s version of Excel. Therefore all formula function names written using XlsxWriter must be in English:
worksheet.write_formula('A1', '=SUM(1, 2, 3)') # OK
worksheet.write_formula('A2', '=SOMME(1, 2, 3)') # French. Error on load.
Also, formulas must be written with the US style separator/range operator which is a comma (not semi-colon). Therefore a formula with multiple values should be written as follows:
worksheet.write_formula('A1', '=SUM(1, 2, 3)') # OK
worksheet.write_formula('A2', '=SUM(1; 2; 3)') # Semi-colon. Error on load.
If you have a non-English version of Excel you can use the following multi-lingual formula translator to help you convert the formula. It can also replace semi-colons with commas.
Excel 2010 and later added functions which weren’t defined in the original
file specification. These functions are referred to by Microsoft as future
functions. Examples of these functions are ACOT
, CHISQ.DIST.RT
,
CONFIDENCE.NORM
, STDEV.P
, STDEV.S
and WORKDAY.INTL
.
When written using write_formula()
these functions need to be fully
qualified with a _xlfn.
(or other) prefix as they are shown the list
below. For example:
worksheet.write_formula('A1', '=_xlfn.STDEV.S(B1:B10)')
They will appear without the prefix in Excel:
The following list is taken from MS XLSX extensions documentation on future functions.
_xlfn.ACOT
_xlfn.ACOTH
_xlfn.AGGREGATE
_xlfn.ARABIC
_xlfn.BASE
_xlfn.BETA.DIST
_xlfn.BETA.INV
_xlfn.BINOM.DIST
_xlfn.BINOM.DIST.RANGE
_xlfn.BINOM.INV
_xlfn.BITAND
_xlfn.BITLSHIFT
_xlfn.BITOR
_xlfn.BITRSHIFT
_xlfn.BITXOR
_xlfn.CEILING.MATH
_xlfn.CEILING.PRECISE
_xlfn.CHISQ.DIST
_xlfn.CHISQ.DIST.RT
_xlfn.CHISQ.INV
_xlfn.CHISQ.INV.RT
_xlfn.CHISQ.TEST
_xlfn.COMBINA
_xlfn.CONCAT
_xlfn.CONFIDENCE.NORM
_xlfn.CONFIDENCE.T
_xlfn.COT
_xlfn.COTH
_xlfn.COVARIANCE.P
_xlfn.COVARIANCE.S
_xlfn.CSC
_xlfn.CSCH
_xlfn.DAYS
_xlfn.DECIMAL
ECMA.CEILING
_xlfn.ERF.PRECISE
_xlfn.ERFC.PRECISE
_xlfn.EXPON.DIST
_xlfn.F.DIST
_xlfn.F.DIST.RT
_xlfn.F.INV
_xlfn.F.INV.RT
_xlfn.F.TEST
_xlfn.FILTERXML
_xlfn.FLOOR.MATH
_xlfn.FLOOR.PRECISE
_xlfn.FORECAST.ETS
_xlfn.FORECAST.ETS.CONFINT
_xlfn.FORECAST.ETS.SEASONALITY
_xlfn.FORECAST.ETS.STAT
_xlfn.FORECAST.LINEAR
_xlfn.FORMULATEXT
_xlfn.GAMMA
_xlfn.GAMMA.DIST
_xlfn.GAMMA.INV
_xlfn.GAMMALN.PRECISE
_xlfn.GAUSS
_xlfn.HYPGEOM.DIST
_xlfn.IFNA
_xlfn.IFS
_xlfn.IMCOSH
_xlfn.IMCOT
_xlfn.IMCSC
_xlfn.IMCSCH
_xlfn.IMSEC
_xlfn.IMSECH
_xlfn.IMSINH
_xlfn.IMTAN
_xlfn.ISFORMULA
ISO.CEILING
_xlfn.ISOWEEKNUM
_xlfn.LOGNORM.DIST
_xlfn.LOGNORM.INV
_xlfn.MAXIFS
_xlfn.MINIFS
_xlfn.MODE.MULT
_xlfn.MODE.SNGL
_xlfn.MUNIT
_xlfn.NEGBINOM.DIST
NETWORKDAYS.INTL
_xlfn.NORM.DIST
_xlfn.NORM.INV
_xlfn.NORM.S.DIST
_xlfn.NORM.S.INV
_xlfn.NUMBERVALUE
_xlfn.PDURATION
_xlfn.PERCENTILE.EXC
_xlfn.PERCENTILE.INC
_xlfn.PERCENTRANK.EXC
_xlfn.PERCENTRANK.INC
_xlfn.PERMUTATIONA
_xlfn.PHI
_xlfn.POISSON.DIST
_xlfn.QUARTILE.EXC
_xlfn.QUARTILE.INC
_xlfn.QUERYSTRING
_xlfn.RANK.AVG
_xlfn.RANK.EQ
_xlfn.RRI
_xlfn.SEC
_xlfn.SECH
_xlfn.SHEET
_xlfn.SHEETS
_xlfn.SKEW.P
_xlfn.STDEV.P
_xlfn.STDEV.S
_xlfn.SWITCH
_xlfn.T.DIST
_xlfn.T.DIST.2T
_xlfn.T.DIST.RT
_xlfn.T.INV
_xlfn.T.INV.2T
_xlfn.T.TEST
_xlfn.TEXTJOIN
_xlfn.UNICHAR
_xlfn.UNICODE
_xlfn.VAR.P
_xlfn.VAR.S
_xlfn.WEBSERVICE
_xlfn.WEIBULL.DIST
WORKDAY.INTL
_xlfn.XOR
_xlfn.Z.TEST
Worksheet tables can be added with XlsxWriter using the add_table()
method:
worksheet.add_table('B3:F7', {options})
By default tables are named Table1
, Table2
, etc., in the order that
they are added. However it can also be set by the user using the name
parameter:
worksheet.add_table('B3:F7', {'name': 'SalesData'})
If you need to know the name of the table, for example to use it in a formula, you can get it as follows:
table = worksheet.add_table('B3:F7')
table_name = table.name
When used in a formula a table name such as TableX
should be referred to
as TableX[]
(like a Python list):
worksheet.write_formula('A5', '=VLOOKUP("Sales", Table1[], 2, FALSE')
If there is an error in the syntax of a formula it is usually displayed in
Excel as #NAME?
. Alternatively you may get a warning from Excel when the
file is loaded. If you encounter an error like this you can debug it as
follows:
@
symbols added then it is probably an array function and should
be written using write_array_formula()
.Finally if you have completed all the previous steps and still get a
#NAME?
error you can examine a valid Excel file to see what the correct
syntax should be. To do this you should create a valid formula in Excel and
save the file. You can then examine the XML in the unzipped file.
The following shows how to do that using Linux unzip
and libxml’s xmllint to format the XML for clarity:
$ unzip myfile.xlsx -d myfile
$ xmllint --format myfile/xl/worksheets/sheet1.xml | grep '</f>'
<f>SUM(1, 2, 3)</f>
XlsxWriter doesn’t calculate the result of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.
This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas will only display the 0 results. Examples of such applications are Excel Viewer, PDF Converters, and some mobile device applications.
If required, it is also possible to specify the calculated result of the
formula using the optional value
parameter for write_formula()
:
worksheet.write_formula('A1', '=2+2', num_format, 4)
The value
parameter can be a number, a string, a bool or one of the
following Excel error codes:
#DIV/0!
#N/A
#NAME?
#NULL!
#NUM!
#REF!
#VALUE!
It is also possible to specify the calculated result of an array formula
created with write_array_formula()
:
# Specify the result for a single cell range.
worksheet.write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}', cell_format, 2005)
However, using this parameter only writes a single value to the upper left
cell in the result array. For a multi-cell array formula where the results are
required, the other result values can be specified by using write_number()
to write to the appropriate cell:
# Specify the results for a multi cell range.
worksheet.write_array_formula('A1:A3', '{=TREND(C1:C3,B1:B3)}', cell_format, 15)
worksheet.write_number('A2', 12, cell_format)
worksheet.write_number('A3', 14, cell_format)
Dates and times in Excel are represented by real numbers, for example “Jan 1 2013 12:00 PM” is represented by the number 41275.5.
The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day.
A date or time in Excel is just like any other number. To display the number as a date you must apply an Excel number format to it. Here are some examples:
import xlsxwriter
workbook = xlsxwriter.Workbook('date_examples.xlsx')
worksheet = workbook.add_worksheet()
# Widen column A for extra visibility.
worksheet.set_column('A:A', 30)
# A number to convert to a date.
number = 41333.5
# Write it as a number without formatting.
worksheet.write('A1', number) # 41333.5
format2 = workbook.add_format({'num_format': 'dd/mm/yy'})
worksheet.write('A2', number, format2) # 28/02/13
format3 = workbook.add_format({'num_format': 'mm/dd/yy'})
worksheet.write('A3', number, format3) # 02/28/13
format4 = workbook.add_format({'num_format': 'd-m-yyyy'})
worksheet.write('A4', number, format4) # 28-2-2013
format5 = workbook.add_format({'num_format': 'dd/mm/yy hh:mm'})
worksheet.write('A5', number, format5) # 28/02/13 12:00
format6 = workbook.add_format({'num_format': 'd mmm yyyy'})
worksheet.write('A6', number, format6) # 28 Feb 2013
format7 = workbook.add_format({'num_format': 'mmm d yyyy hh:mm AM/PM'})
worksheet.write('A7', number, format7) # Feb 28 2013 12:00 PM
workbook.close()
To make working with dates and times a little easier the XlsxWriter module
provides a write_datetime()
method to write dates in standard library
datetime
format.
Specifically it supports datetime objects of type datetime.datetime
,
datetime.date
, datetime.time
and datetime.timedelta
.
There are many way to create datetime objects, for example the
datetime.datetime.strptime()
method:
date_time = datetime.datetime.strptime('2013-01-23', '%Y-%m-%d')
See the datetime
documentation for other date/time creation methods.
As explained above you also need to create and apply a number format to format the date/time:
date_format = workbook.add_format({'num_format': 'd mmmm yyyy'})
worksheet.write_datetime('A1', date_time, date_format)
# Displays "23 January 2013"
Here is a longer example that displays the same date in a several different formats:
from datetime import datetime
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('datetimes.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
# Expand the first columns so that the dates are visible.
worksheet.set_column('A:B', 30)
# Write the column headers.
worksheet.write('A1', 'Formatted date', bold)
worksheet.write('B1', 'Format', bold)
# Create a datetime object to use in the examples.
date_time = datetime.strptime('2013-01-23 12:30:05.123',
'%Y-%m-%d %H:%M:%S.%f')
# Examples date and time formats.
date_formats = (
'dd/mm/yy',
'mm/dd/yy',
'dd m yy',
'd mm yy',
'd mmm yy',
'd mmmm yy',
'd mmmm yyy',
'd mmmm yyyy',
'dd/mm/yy hh:mm',
'dd/mm/yy hh:mm:ss',
'dd/mm/yy hh:mm:ss.000',
'hh:mm',
'hh:mm:ss',
'hh:mm:ss.000',
)
# Start from first row after headers.
row = 1
# Write the same date and time using each of the above formats.
for date_format_str in date_formats:
# Create a format for the date or time.
date_format = workbook.add_format({'num_format': date_format_str,
'align': 'left'})
# Write the same date using different formats.
worksheet.write_datetime(row, 0, date_time, date_format)
# Also write the format string for comparison.
worksheet.write_string(row, 1, date_format_str)
row += 1
workbook.close()
In certain circumstances you may wish to apply a default date format when
writing datetime objects, for example, when handling a row of data with
write_row()
.
In these cases it is possible to specify a default date format string using the
Workbook()
constructor default_date_format
option:
workbook = xlsxwriter.Workbook('datetimes.xlsx', {'default_date_format':
'dd/mm/yy'})
worksheet = workbook.add_worksheet()
date_time = datetime.now()
worksheet.write_datetime(0, 0, date_time) # Formatted as 'dd/mm/yy'
workbook.close()
Excel doesn’t support timezones in datetimes/times so there isn’t any fail-safe
way that XlsxWriter can map a Python timezone aware datetime into an Excel
datetime. As such the user should handle the timezones in some way that makes
sense according to their requirements. Usually this will require some
conversion to a timezone adjusted time and the removal of the tzinfo
from
the datetime object so that it can be passed to write_datetime()
:
utc_datetime = datetime(2016, 9, 23, 14, 13, 21, tzinfo=utc)
naive_datetime = utc_datetime.replace(tzinfo=None)
worksheet.write_datetime(row, 0, naive_datetime, date_format)
Alternatively the Workbook()
constructor option remove_timezone
can
be used to strip the timezone from datetime values passed to
write_datetime()
. The default is False
. To enable this option use:
workbook = xlsxwriter.Workbook(filename, {'remove_timezone': True})
When Working with Python Pandas and XlsxWriter you can pass the argument as follows:
writer = pd.ExcelWriter('pandas_example.xlsx',
engine='xlsxwriter',
options={'remove_timezone': True})
Throughout XlsxWriter colors are specified using a Html style #RRGGBB
value. For example with a Format object:
cell_format.set_font_color('#FF0000')
For backward compatibility a limited number of color names are supported:
cell_format.set_font_color('red')
The color names and corresponding #RRGGBB
value are shown below:
| Color name | RGB color code |
|---|---|
| black | #000000 |
| blue | #0000FF |
| brown | #800000 |
| cyan | #00FFFF |
| gray | #808080 |
| green | #008000 |
| lime | #00FF00 |
| magenta | #FF00FF |
| navy | #000080 |
| orange | #FF6600 |
| pink | #FF00FF |
| purple | #800080 |
| red | #FF0000 |
| silver | #C0C0C0 |
| white | #FFFFFF |
| yellow | #FFFF00 |
This section explains how to work with some of the options and features of The Chart Class.
The majority of the examples in this section are based on a variation of the following program:
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_line.xlsx')
worksheet = workbook.add_worksheet()
# Add the worksheet data to be plotted.
data = [10, 40, 50, 20, 10, 50]
worksheet.write_column('A1', data)
# Create a new chart object.
chart = workbook.add_chart({'type': 'line'})
# Add a series to the chart.
chart.add_series({'values': '=Sheet1!$A$1:$A$6'})
# Insert the chart into the worksheet.
worksheet.insert_chart('C1', chart)
workbook.close()
See also Chart Examples.
When working with charts it is important to understand how Excel differentiates between a chart axis that is used for series categories and a chart axis that is used for series values.
In the majority of Excel charts the X axis is the category axis and each of the values is evenly spaced and sequential. The Y axis is the value axis and points are displayed according to their value:
Excel treats these two types of axis differently and exposes different properties for each. For example, here are the properties for a category axis:
Here are properties for a value axis:
As such, some of the XlsxWriter axis properties can be set for a value
axis, some can be set for a category axis and some properties can be set for
both. For example reverse
can be set for either category or value axes while the
min
and max
properties can only be set for value axes (and Date Axes).
The documentation calls out the type of axis to which properties apply.
For a Bar chart the Category and Value axes are reversed:
A Scatter chart (but not a Line chart) has 2 value axes:
Date Category Axes are a special type of category axis that give them
some of the properties of values axes such as min
and max
when used
with date or time values.
This following sections detail the more complex options of the
add_series()
Chart method:
marker
trendline
y_error_bars
x_error_bars
data_labels
points
smooth
The marker format specifies the properties of the markers used to distinguish series on a chart. In general only Line and Scatter chart types and trendlines use markers.
The following properties can be set for marker
formats in a chart:
type
size
border
fill
pattern
gradient
The type
property sets the type of marker that is used with a series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'marker': {'type': 'diamond'},
})
The following type
properties can be set for marker
formats in a chart.
These are shown in the same order as in the Excel format dialog:
automatic
none
square
diamond
triangle
x
star
short_dash
long_dash
circle
plus
The automatic
type is a special case which turns on a marker using the
default marker style for the particular series number:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'marker': {'type': 'automatic'},
})
If automatic
is on then other marker properties such as size, border or
fill cannot be set.
The size
property sets the size of the marker and is generally used in
conjunction with type
:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'marker': {'type': 'diamond', 'size': 7},
})
Nested border
and fill
properties can also be set for a marker:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'marker': {
'type': 'square',
'size': 8,
'border': {'color': 'black'},
'fill': {'color': 'red'},
},
})
A trendline can be added to a chart series to indicate trends in the data such as a moving average or a polynomial fit.
The following properties can be set for trendlines in a chart series:
type
order (for polynomial trends)
period (for moving average)
forward (for all except moving average)
backward (for all except moving average)
name
line
intercept (for exponential, linear and polynomial only)
display_equation (for all except moving average)
display_r_squared (for all except moving average)
The type
property sets the type of trendline in the series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'trendline': {'type': 'linear'},
})
The available trendline
types are:
exponential
linear
log
moving_average
polynomial
power
A polynomial
trendline can also specify the order
of the polynomial.
The default value is 2:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'trendline': {
'type': 'polynomial',
'order': 3,
},
})
A moving_average
trendline can also specify the period
of the moving
average. The default value is 2:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'trendline': {
'type': 'moving_average',
'period': 2,
},
})
The forward
and backward
properties set the forecast period of the
trendline:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'trendline': {
'type': 'polynomial',
'order': 2,
'forward': 0.5,
'backward': 0.5,
},
})
The name
property sets an optional name for the trendline that will appear
in the chart legend. If it isn’t specified the Excel default name will be
displayed. This is usually a combination of the trendline type and the series
name:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'trendline': {
'type': 'polynomial',
'name': 'My trend name',
'order': 2,
},
})
The intercept
property sets the point where the trendline crosses the Y
(value) axis:
chart.add_series({
'values': '=Sheet1!$B$1:$B$6',
'trendline': {'type': 'linear',
'intercept': 0.8,
},
})
The display_equation
property displays the trendline equation on the
chart:
chart.add_series({
'values': '=Sheet1!$B$1:$B$6',
'trendline': {'type': 'linear',
'display_equation': True,
},
})
The display_r_squared
property displays the R squared value of the
trendline on the chart:
chart.add_series({
'values': '=Sheet1!$B$1:$B$6',
'trendline': {'type': 'linear',
'display_r_squared': True,
},
})
Several of these properties can be set in one go:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'trendline': {
'type': 'polynomial',
'name': 'My trend name',
'order': 2,
'forward': 0.5,
'backward': 0.5,
'display_equation': True,
'line': {
'color': 'red',
'width': 1,
'dash_type': 'long_dash',
},
},
})
Trendlines cannot be added to series in a stacked chart or pie chart, doughnut chart, radar chart or (when implemented) to 3D or surface charts.
Error bars can be added to a chart series to indicate error bounds in the data.
The error bars can be vertical y_error_bars
(the most common type) or
horizontal x_error_bars
(for Bar and Scatter charts only).
The following properties can be set for error bars in a chart series:
type
value (for all types except standard error and custom)
plus_values (for custom only)
minus_values (for custom only)
direction
end_style
line
The type
property sets the type of error bars in the series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'y_error_bars': {'type': 'standard_error'},
})
The available error bars types are available:
fixed
percentage
standard_deviation
standard_error
custom
All error bar types, except for standard_error
and custom
must also
have a value associated with it for the error bounds:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'y_error_bars': {
'type': 'percentage',
'value': 5,
},
})
The custom
error bar type must specify plus_values
and minus_values
which should either by a Sheet1!$A$1:$A$6
type range formula or a list of
values:
chart.add_series({
'categories': '=Sheet1!$A$1:$A$6',
'values': '=Sheet1!$B$1:$B$6',
'y_error_bars': {
'type': 'custom',
'plus_values': '=Sheet1!$C$1:$C$6',
'minus_values': '=Sheet1!$D$1:$D$6',
},
})
# or
chart.add_series({
'categories': '=Sheet1!$A$1:$A$6',
'values': '=Sheet1!$B$1:$B$6',
'y_error_bars': {
'type': 'custom',
'plus_values': [1, 1, 1, 1, 1],
'minus_values': [2, 2, 2, 2, 2],
},
})
Note, as in Excel the items in the minus_values
do not need to be negative.
The direction
property sets the direction of the error bars. It should be
one of the following:
plus # Positive direction only.
minus # Negative direction only.
both # Plus and minus directions, The default.
The end_style
property sets the style of the error bar end cap. The options
are 1 (the default) or 0 (for no end cap):
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'y_error_bars': {
'type': 'fixed',
'value': 2,
'end_style': 0,
'direction': 'minus'
},
})
Data labels can be added to a chart series to indicate the values of the plotted data points.
The following properties can be set for data_labels
formats in a chart:
value
category
series_name
position
leader_lines
percentage
separator
legend_key
num_format
font
border
fill
pattern
gradient
custom
The value
property turns on the Value data label for a series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True},
})
By default data labels are displayed in Excel with only the values shown. However, it is possible to configure other display options, as shown below.
The category
property turns on the Category Name data label for a series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'category': True},
})
The series_name
property turns on the Series Name data label for a
series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'series_name': True},
})
Here is an example with all three data label types shown:
The position
property is used to position the data label for a series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'series_name': True, 'position': 'above'},
})
In Excel the allowable data label positions vary for different chart types. The allowable positions are:
| Position | Line, Scatter, Stock | Bar, Column | Pie, Doughnut | Area, Radar |
|---|---|---|---|---|
| center | Yes | Yes | Yes | Yes* |
| right | Yes* | |||
| left | Yes | |||
| above | Yes | |||
| below | Yes | |||
| inside_base | Yes | |||
| inside_end | Yes | Yes | ||
| outside_end | Yes* | Yes | ||
| best_fit | Yes* |
Note: The * indicates the default position for each chart type in Excel, if a position isn’t specified by the user.
The percentage
property is used to turn on the display of data labels as a
Percentage for a series. In Excel the percentage
data label option is
only available for Pie and Doughnut chart variants:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'percentage': True},
})
The leader_lines
property is used to turn on Leader Lines for the data
label of a series. It is mainly used for pie charts:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True, 'leader_lines': True},
})
Note
Even when leader lines are turned on they aren’t automatically visible in Excel or XlsxWriter. Due to an Excel limitation (or design) leader lines only appear if the data label is moved manually or if the data labels are very close and need to be adjusted automatically.
The separator
property is used to change the separator between multiple
data label items:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True, 'category': True,
'series_name': True, 'separator': "\n"},
})
The separator value must be one of the following strings:
','
';'
'.'
'\n'
' '
The legend_key
property is used to turn on the Legend Key for the data
label of a series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True, 'legend_key': True},
})
The num_format
property is used to set the number format for the data
labels of a series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True, 'num_format': '#,##0.00'},
})
The number format is similar to the Worksheet Cell Format num_format
apart from the fact that a format index cannot be used. An explicit format
string must be used as shown above. See set_num_format()
for more
information.
The font
property is used to set the font of the data labels of a series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {
'value': True,
'font': {'name': 'Consolas', 'color': 'red'}
},
})
The font
property is also used to rotate the data labels of a series:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {
'value': True,
'font': {'rotation': 45}
},
})
See Chart Fonts.
Standard chart formatting such as border
and fill
can also be added to
the data labels:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True,
'border': {'color': 'red'},
'fill': {'color': 'yellow'}},
})
The custom
property is used to set properties for individual data
labels. This is explained in detail in the next section.
The custom
data label property is used to set the properties of individual
data labels in a series. The most common use for this is to set custom text or
number labels:
custom_labels = [
{'value': 'Jan'},
{'value': 'Feb'},
{'value': 'Mar'},
{'value': 'Apr'},
{'value': 'May'},
{'value': 'Jun'},
]
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True, 'custom': custom_labels},
})
As shown in the previous examples th custom
property should be a list of
dicts. Any property dict that is set to None
or not included in the list
will be assigned the default data label value:
custom_labels = [
None,
{'value': 'Feb'},
{'value': 'Mar'},
{'value': 'Apr'},
]
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True, 'custom': custom_labels},
})
The property elements of the custom
lists should be dicts with the
following allowable keys/sub-properties:
value
font
delete
The value
property should be a string, number or formula string that refers to
a cell from which the value will be taken:
custom_labels = [
{'value': '=Sheet1!$C$1'},
{'value': '=Sheet1!$C$2'},
{'value': '=Sheet1!$C$3'},
{'value': '=Sheet1!$C$4'},
{'value': '=Sheet1!$C$5'},
{'value': '=Sheet1!$C$6'},
]
The font
property is used to set the font of the custom data label of a series:
custom_labels = [
{'value': '=Sheet1!$C$1', 'font': {'color': 'red'}},
{'value': '=Sheet1!$C$2', 'font': {'color': 'red'}},
{'value': '=Sheet1!$C$3', 'font': {'color': 'red'}},
{'value': '=Sheet1!$C$4', 'font': {'color': 'red'}},
{'value': '=Sheet1!$C$5', 'font': {'color': 'red'}},
{'value': '=Sheet1!$C$6', 'font': {'color': 'red'}},
]
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True, 'custom': custom_labels},
})
See Chart Fonts for details on the available font properties.
The delete
property can be used to delete labels in a series. This can be
useful if you want to highlight one or more cells in the series, for example
the maximum and the minimum:
custom_labels = [
None,
{'delete': True},
{'delete': True},
{'delete': True},
{'delete': True},
None,
]
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'data_labels': {'value': True, 'custom': custom_labels},
})
Standard chart formatting such as border
and fill
can also be added to
the custom data labels:
custom_labels = [
{'value': 'Jan', 'border': {'color': 'blue'}},
{'value': 'Feb'},
{'value': 'Mar'},
{'value': 'Apr'},
{'value': 'May'},
{'value': 'Jun', 'fill': {'color': 'green'}},
]
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'marker': {'type': 'circle'},
'data_labels': {'value': True,
'custom': custom_labels,
'border': {'color': 'red'},
'fill': {'color': 'yellow'}},
})
In general formatting is applied to an entire series in a chart. However, it is occasionally required to format individual points in a series. In particular this is required for Pie/Doughnut charts where each segment is represented by a point.
In these cases it is possible to use the points
property of
add_series()
:
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_pie.xlsx')
worksheet = workbook.add_worksheet()
chart = workbook.add_chart({'type': 'pie'})
data = [
['Pass', 'Fail'],
[90, 10],
]
worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])
chart.add_series({
'categories': '=Sheet1!$A$1:$A$2',
'values': '=Sheet1!$B$1:$B$2',
'points': [
{'fill': {'color': 'green'}},
{'fill': {'color': 'red'}},
],
})
worksheet.insert_chart('C3', chart)
workbook.close()
The points
property takes a list of format options (see the “Chart
Formatting” section below). To assign default properties to points in a series
pass None
values in the array ref:
# Format point 3 of 3 only.
chart.add_series({
'values': '=Sheet1!A1:A3',
'points': [
None,
None,
{'fill': {'color': '#990000'}},
],
})
# Format point 1 of 3 only.
chart.add_series({
'values': '=Sheet1!A1:A3',
'points': [
{'fill': {'color': '#990000'}},
],
})
The smooth
option is used to set the smooth property of a line series. It
is only applicable to the line
and scatter
chart types:
chart.add_series({
'categories': '=Sheet1!$A$1:$A$6',
'values': '=Sheet1!$B$1:$B$6',
'smooth': True,
})
The following chart formatting properties can be set for any chart object that they apply to (and that are supported by XlsxWriter) such as chart lines, column fill areas, plot area borders, markers, gridlines and other chart elements:
line
border
fill
pattern
gradient
Chart formatting properties are generally set using dicts:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {'color': 'red'},
})
In some cases the format properties can be nested. For example a marker
may
contain border
and fill
sub-properties:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {'color': 'blue'},
'marker': {'type': 'square',
'size,': 5,
'border': {'color': 'red'},
'fill': {'color': 'yellow'}
},
})
The line format is used to specify properties of line objects that appear in a chart such as a plotted line on a chart or a border.
The following properties can be set for line
formats in a chart:
none
color
width
dash_type
transparency
The none
property is uses to turn the line
off (it is always on by
default except in Scatter charts). This is useful if you wish to plot a series
with markers but without a line:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {'none': True},
'marker': {'type': 'automatic'},
})
The color
property sets the color of the line
:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {'color': 'red'},
})
The available colors are shown in the main XlsxWriter documentation. It is
also possible to set the color of a line with a Html style #RRGGBB
string
or a limited number of named colors, see Working with Colors:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {'color': '#FF9900'},
})
The width
property sets the width of the line
. It should be specified
in increments of 0.25 of a point as in Excel:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {'width': 3.25},
})
The dash_type
property sets the dash style of the line:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {'dash_type': 'dash_dot'},
})
The following dash_type
values are available. They are shown in the order
that they appear in the Excel dialog:
solid
round_dot
square_dot
dash
dash_dot
long_dash
long_dash_dot
long_dash_dot_dot
The default line style is solid
.
The transparency
property sets the transparency of the line color in the
integer range 1 - 100. The color must be set for transparency to work, it
doesn’t work with an automatic/default color:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {'color': 'yellow', 'transparency': 50},
})
More than one line
property can be specified at a time:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'line': {
'color': 'red',
'width': 1.25,
'dash_type': 'square_dot',
},
})
The border
property is a synonym for line
.
It can be used as a descriptive substitute for line
in chart types such as
Bar and Column that have a border and fill style rather than a line style. In
general chart objects with a border
property will also have a fill
property.
The solid fill format is used to specify filled areas of chart objects such as the interior of a column or the background of the chart itself.
The following properties can be set for fill
formats in a chart:
none
color
transparency
The none
property is used to turn the fill
property off (it is
generally on by default):
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'fill': {'none': True},
'border': {'color': 'black'}
})
The color
property sets the color of the fill
area:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'fill': {'color': 'red'}
})
The available colors are shown in the main XlsxWriter documentation. It is
also possible to set the color of a fill with a Html style #RRGGBB
string
or a limited number of named colors, see Working with Colors:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'fill': {'color': '#FF9900'}
})
The transparency
property sets the transparency of the solid fill color in
the integer range 1 - 100. The color must be set for transparency to work, it
doesn’t work with an automatic/default color:
chart.set_chartarea({'fill': {'color': 'yellow', 'transparency': 50}})
The fill
format is generally used in conjunction with a border
format
which has the same properties as a line
format:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'fill': {'color': 'red'},
'border': {'color': 'black'}
})
The pattern fill format is used to specify pattern filled areas of chart objects such as the interior of a column or the background of the chart itself.
The following properties can be set for pattern
fill formats in a chart:
pattern: the pattern to be applied (required)
fg_color: the foreground color of the pattern (required)
bg_color: the background color (optional, defaults to white)
For example:
chart.set_plotarea({
'pattern': {
'pattern': 'percent_5',
'fg_color': 'red',
'bg_color': 'yellow',
}
})
The following patterns can be applied:
percent_5
percent_10
percent_20
percent_25
percent_30
percent_40
percent_50
percent_60
percent_70
percent_75
percent_80
percent_90
light_downward_diagonal
light_upward_diagonal
dark_downward_diagonal
dark_upward_diagonal
wide_downward_diagonal
wide_upward_diagonal
light_vertical
light_horizontal
narrow_vertical
narrow_horizontal
dark_vertical
dark_horizontal
dashed_downward_diagonal
dashed_upward_diagonal
dashed_horizontal
dashed_vertical
small_confetti
large_confetti
zigzag
wave
diagonal_brick
horizontal_brick
weave
plaid
divot
dotted_grid
dotted_diamond
shingle
trellis
sphere
small_grid
large_grid
small_check
large_check
outlined_diamond
solid_diamond
The foreground color, fg_color
, is a required parameter and can be a Html
style #RRGGBB
string or a limited number of named colors, see
Working with Colors.
The background color, bg_color
, is optional and defaults to white.
If a pattern fill is used on a chart object it overrides the solid fill properties of the object.
The gradient fill format is used to specify gradient filled areas of chart objects such as the interior of a column or the background of the chart itself.
The following properties can be set for gradient
fill formats in a chart:
colors: a list of colors
positions: an optional list of positions for the colors
type: the optional type of gradient fill
angle: the optional angle of the linear fill
The colors
property sets a list of colors that define the gradient
:
chart.set_plotarea({
'gradient': {'colors': ['#FFEFD1', '#F0EBD5', '#B69F66']}
})
Excel allows between 2 and 10 colors in a gradient but it is unlikely that you will require more than 2 or 3.
As with solid or pattern fill it is also possible to set the colors of a
gradient with a Html style #RRGGBB
string or a limited number of named
colors, see Working with Colors:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'gradient': {'colors': ['red', 'green']}
})
The positions
defines an optional list of positions, between 0 and 100, of
where the colors in the gradient are located. Default values are provided for
colors
lists of between 2 and 4 but they can be specified if required:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'gradient': {
'colors': ['#DDEBCF', '#156B13'],
'positions': [10, 90],
}
})
The type
property can have one of the following values:
linear (the default)
radial
rectangular
path
For example:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'gradient': {
'colors': ['#DDEBCF', '#9CB86E', '#156B13'],
'type': 'radial'
}
})
If type
isn’t specified it defaults to linear
.
For a linear
fill the angle of the gradient can also be specified:
chart.add_series({
'values': '=Sheet1!$A$1:$A$6',
'gradient': {'colors': ['#DDEBCF', '#9CB86E', '#156B13'],
'angle': 45}
})
The default angle is 90 degrees.
If gradient fill is used on a chart object it overrides the solid fill and pattern fill properties of the object.
The following font properties can be set for any chart object that they apply to (and that are supported by XlsxWriter) such as chart titles, axis labels, axis numbering and data labels:
name
size
bold
italic
underline
rotation
color
These properties correspond to the equivalent Worksheet cell Format object properties. See the The Format Class section for more details about Format properties and how to set them.
The following explains the available font properties:
name
: Set the font name:
chart.set_x_axis({'num_font': {'name': 'Arial'}})
size
: Set the font size:
chart.set_x_axis({'num_font': {'name': 'Arial', 'size': 9}})
bold
: Set the font bold property:
chart.set_x_axis({'num_font': {'bold': True}})
italic
: Set the font italic property:
chart.set_x_axis({'num_font': {'italic': True}})
underline
: Set the font underline property:
chart.set_x_axis({'num_font': {'underline': True}})
rotation
: Set the font rotation, angle, property in the integer
range -90 to 90 deg, and 270-271 deg:
chart.set_x_axis({'num_font': {'rotation': 45}})
The font rotation angle is useful for displaying axis data such as dates in a more compact format.
There are 2 special case angles outside the range -90 to 90:
color
: Set the font color property. Can be a color index, a color name
or HTML style RGB color:
chart.set_x_axis({'num_font': {'color': 'red' }})
chart.set_y_axis({'num_font': {'color': '#92D050'}})
Here is an example of Font formatting in a Chart program:
chart.set_title({
'name': 'Test Results',
'name_font': {
'name': 'Calibri',
'color': 'blue',
},
})
chart.set_x_axis({
'name': 'Month',
'name_font': {
'name': 'Courier New',
'color': '#92D050'
},
'num_font': {
'name': 'Arial',
'color': '#00B0F0',
},
})
chart.set_y_axis({
'name': 'Units',
'name_font': {
'name': 'Century',
'color': 'red'
},
'num_font': {
'bold': True,
'italic': True,
'underline': True,
'color': '#7030A0',
},
})
chart.set_legend({'font': {'bold': 1, 'italic': 1}})
The position of the chart in the worksheet is controlled by the
set_size()
method.
It is also possible to change the layout of the following chart sub-objects:
plotarea
legend
title
x_axis caption
y_axis caption
Here are some examples:
chart.set_plotarea({
'layout': {
'x': 0.13,
'y': 0.26,
'width': 0.73,
'height': 0.57,
}
})
chart.set_legend({
'layout': {
'x': 0.80,
'y': 0.37,
'width': 0.12,
'height': 0.25,
}
})
chart.set_title({
'name': 'Title',
'overlay': True,
'layout': {
'x': 0.42,
'y': 0.14,
}
})
chart.set_x_axis({
'name': 'X axis',
'name_layout': {
'x': 0.34,
'y': 0.85,
}
})
See set_plotarea()
, set_legend()
, set_title()
and
set_x_axis()
,
Note
It is only possible to change the width and height for the plotarea
and legend
objects. For the other text based objects the width and
height are changed by the font dimensions.
The layout units must be a float in the range 0 < x <= 1
and are expressed
as a percentage of the chart dimensions as shown below:
From this the layout units are calculated as follows:
layout:
x = a / W
y = b / H
width = w / W
height = h / H
These units are cumbersome and can vary depending on other elements in the chart such as text lengths. However, these are the units that are required by Excel to allow relative positioning. Some trial and error is generally required.
Note
The plotarea
origin is the top left corner in the plotarea itself and
does not take into account the axes.
Date Category Axes are category axes that display time or date information. 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})
In general you should also specify a number format for a date axis although Excel will usually default to the same format as the data being plotted:
chart.set_x_axis({
'date_axis': True,
'num_format': 'dd/mm/yyyy',
})
Excel doesn’t normally allow minimum and maximum values to be set for category
axes. However, date axes are an exception. The min
and max
values
should be set as Excel times or dates:
chart.set_x_axis({
'date_axis': True,
'min': date(2013, 1, 2),
'max': date(2013, 1, 9),
'num_format': 'dd/mm/yyyy',
})
For date axes it is also possible to set the type of the major and minor units:
chart.set_x_axis({
'date_axis': True,
'minor_unit': 4,
'minor_unit_type': 'months',
'major_unit': 1,
'major_unit_type': 'years',
'num_format': 'dd/mm/yyyy',
})
It is possible to add a secondary axis of the same type to a chart by setting
the y2_axis
or x2_axis
property of the series:
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_secondary_axis.xlsx')
worksheet = workbook.add_worksheet()
data = [
[2, 3, 4, 5, 6, 7],
[10, 40, 50, 20, 10, 50],
]
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
chart = workbook.add_chart({'type': 'line'})
# Configure a series with a secondary axis.
chart.add_series({
'values': '=Sheet1!$A$2:$A$7',
'y2_axis': True,
})
# Configure a primary (default) Axis.
chart.add_series({
'values': '=Sheet1!$B$2:$B$7',
})
chart.set_legend({'position': 'none'})
chart.set_y_axis({'name': 'Primary Y axis'})
chart.set_y2_axis({'name': 'Secondary Y axis'})
worksheet.insert_chart('D2', chart)
workbook.close()
It is also possible to have a secondary, combined, chart either with a shared or secondary axis, see below.
It is also possible to combine two different chart types, for example a column
and line chart to create a Pareto chart using the Chart combine()
method:
The combined charts can share the same Y axis like the following example:
# Usual setup to create workbook and add data...
# Create a new column chart. This will use this as the primary chart.
column_chart = workbook.add_chart({'type': 'column'})
# Configure the data series for the primary chart.
column_chart.add_series({
'name': '=Sheet1!B1',
'categories': '=Sheet1!A2:A7',
'values': '=Sheet1!B2:B7',
})
# Create a new column chart. This will use this as the secondary chart.
line_chart = workbook.add_chart({'type': 'line'})
# Configure the data series for the secondary chart.
line_chart.add_series({
'name': '=Sheet1!C1',
'categories': '=Sheet1!A2:A7',
'values': '=Sheet1!C2:C7',
})
# Combine the charts.
column_chart.combine(line_chart)
# Add a chart title and some axis labels. Note, this is done via the
# primary chart.
column_chart.set_title({ 'name': 'Combined chart - same Y axis'})
column_chart.set_x_axis({'name': 'Test number'})
column_chart.set_y_axis({'name': 'Sample length (mm)'})
# Insert the chart into the worksheet
worksheet.insert_chart('E2', column_chart)
The secondary chart can also be placed on a secondary axis using the methods shown in the previous section.
In this case it is just necessary to add a y2_axis
parameter to the series
and, if required, add a title using set_y2_axis()
. The following are
the additions to the previous example to place the secondary chart on the
secondary axis:
# ...
line_chart.add_series({
'name': '=Sheet1!C1',
'categories': '=Sheet1!A2:A7',
'values': '=Sheet1!C2:C7',
'y2_axis': True,
})
# Add a chart title and some axis labels.
# ...
column_chart.set_y2_axis({'name': 'Target length (mm)'})
The examples above use the concept of a primary and secondary chart. The primary chart is the chart that defines the primary X and Y axis. It is also used for setting all chart properties apart from the secondary data series. For example the chart title and axes properties should be set via the primary chart.
See also Example: Combined Chart and Example: Pareto Chart for more detailed examples.
There are some limitations on combined charts:
The examples shown above and in general the most common type of charts in Excel are embedded charts.
However, it is also possible to create “Chartsheets” which are worksheets that are comprised of a single chart:
See The Chartsheet Class for details.
Charts can by created from Worksheet Tables. However, Excel
has a limitation where the data series name
, if specified, must refer to a
cell within the table (usually one of the headers).
To workaround this Excel limitation you can specify a user defined name in the table and refer to that from the chart:
import xlsxwriter
workbook = xlsxwriter.Workbook('chart_pie.xlsx')
worksheet = workbook.add_worksheet()
data = [
['Apple', 60],
['Cherry', 30],
['Pecan', 10],
]
worksheet.add_table('A1:B4', {'data': data,
'columns': [{'header': 'Types'},
{'header': 'Number'}]}
)
chart = workbook.add_chart({'type': 'pie'})
chart.add_series({
'name': '=Sheet1!$A$1',
'categories': '=Sheet1!$A$2:$A$4',
'values': '=Sheet1!$B$2:$B$4',
})
worksheet.insert_chart('D2', chart)
workbook.close()
The following chart features aren’t supported in XlsxWriter:
See Chart Examples.
XlsxWriter positions worksheet objects such as images, charts and textboxes in worksheets by calculating precise co-ordinates based on the object size, it’s DPI (for images) and any scaling that the user specifies. It also takes into account the heights and widths of the rows and columns that the object crosses. In this way objects maintain their original sizes even if the rows or columns underneath change size or are hidden.
For example:
import xlsxwriter
workbook = xlsxwriter.Workbook('image.xlsx')
worksheet = workbook.add_worksheet()
# Original image.
worksheet.insert_image('B2', 'logo.png')
# Same size as original, despite row/col changes.
worksheet.insert_image('E8', 'logo.png')
# Make column F narrower.
worksheet.set_column('F:F', 2)
# Hide row 10 (zero indexed).
worksheet.set_row(9, None, None, {'hidden': True})
workbook.close()
As can be seen the inserted image sizes are the same even though the second image crosses changed rows and columns.
However, there are two cases where the image scale may change with row or columns changes. These are explained in the next two sections.
The scaling of a image may be affected if is crosses a row that has its default height changed due to a font that is larger than the default font size or that has text wrapping turned on. In these cases Excel will automatically calculate a row height based on the text when it loads the file. Since this row height isn’t available to XlsxWriter when it creates the file the object may appear as if it is sized incorrectly. For example:
import xlsxwriter
workbook = xlsxwriter.Workbook('image.xlsx')
worksheet = workbook.add_worksheet()
wrap_format = workbook.add_format({'text_wrap': True})
worksheet.write('A9', 'Some text that wraps', wrap_format)
worksheet.insert_image('B2', 'logo.png')
worksheet.insert_image('B8', 'logo.png')
workbook.close()
As can be seen the second inserted image is distorted, compared to the first,
due to the row being scaled automatically. To avoid this you should explicitly
set the height of the row using set_row()
if it crosses an inserted
object.
Excel supports three options for “Object Positioning” within a worksheet:
Image, chart and textbox objects in XlsxWriter emulate these options using the
object_position
parameter:
worksheet.insert_image('B3', 'python.png', {'object_position': 1})
Where object_position
has one of the following allowable values:
Option 4 appears in Excel as Option 1. However, the worksheet object is sized to take hidden rows or columns into account. This allows the user to hide an image in a cell, possibly as part of an autofilter. For example:
import xlsxwriter
workbook = xlsxwriter.Workbook('image.xlsx')
worksheet = workbook.add_worksheet()
worksheet.insert_image('B2', 'logo.png')
worksheet.insert_image('B9', 'logo.png', {'object_position': 4})
# Hide some rows.
for row in range(1, 13):
worksheet.set_row(row, None, None, {'hidden': True})
workbook.close()
In this example the first inserted image is visible over the hidden rows whilst the second image is hidden with the rows. Unhiding the rows in Excel would reveal the second image.
When an image is imported into Excel the DPI (dots per inch) resolution of the image is taken into account. Excel sizes the image according to a base DPI of 96. Therefore an image with a DPI of 72 may appear slightly larger when imported into Excel while an image with a DPI of 200 may appear twice as small. XlsxWriter also reads the DPI of the images that the user inserts into a worksheet and stores the image dimensions in the same way that Excel does. If it cannot determine the DPI of the image it uses a default of 96.
A lot of work has gone into ensuring that XlsxWriter inserts images into worksheets in exactly the same way that Excel does, even though the required calculations and units are arcane. There are over 80 test cases that check image insertion against files created in Excel to ensure that XlsxWriter’s handling of images is correct.
As such, before reporting any issues with image handling in XlsxWriter please check how the same image is handled in Excel (not OpenOffice, LibreOffice or other third party applications). If you do report an issue please use the XlsxWriter Issue tracker is on GitHub and attach the image that demonstrates the issue.
An autofilter in Excel is a way of filtering a 2D range of data based on some simple criteria.
The first step is to apply an autofilter to a cell range in a worksheet using
the autofilter()
method:
worksheet.autofilter('A1:D11')
As usual you can also use Row-Column notation:
worksheet.autofilter(0, 0, 10, 3) # Same as above.
The autofilter()
defines the cell range that the filter applies to and
creates drop-down selectors in the heading row. In order to filter out data it
is necessary to apply some criteria to the columns using either the
filter_column()
or filter_column_list()
methods.
The filter_column
method is used to filter columns in a autofilter range
based on simple criteria:
worksheet.filter_column('A', 'x > 2000')
worksheet.filter_column('B', 'x > 2000 and x < 5000')
It isn’t sufficient to just specify the filter condition. You must also hide
any rows that don’t match the filter condition. Rows are hidden using the
set_row()
hidden
parameter. XlsxWriter
cannot filter rows
automatically since this isn’t part of the file format.
The following is an example of how you might filter a data range to match an autofilter criteria:
# Set the autofilter.
worksheet.autofilter('A1:D51')
# Add the filter criteria. The placeholder "Region" in the filter is
# ignored and can be any string that adds clarity to the expression.
worksheet.filter_column(0, 'Region == East')
# Hide the rows that don't match the filter criteria.
row = 1
for row_data in (data):
region = row_data[0]
# Check for rows that match the filter.
if region == 'East':
# Row matches the filter, display the row as normal.
pass
else:
# We need to hide rows that don't match the filter.
worksheet.set_row(row, options={'hidden': True})
worksheet.write_row(row, 0, row_data)
# Move on to the next worksheet row.
row += 1
The filter_column()
method can be used to filter columns in a autofilter
range based on simple conditions:
worksheet.filter_column('A', 'x > 2000')
The column
parameter can either be a zero indexed column number or a string
column name.
The following operators are available for setting the filter criteria:
Operator
==
!=
>
<
>=
<=
and
or
An expression can comprise a single statement or two statements separated by
the and
and or
operators. For example:
'x < 2000'
'x > 2000'
'x == 2000'
'x > 2000 and x < 5000'
'x == 2000 or x == 5000'
Filtering of blank or non-blank data can be achieved by using a value of
Blanks
or NonBlanks
in the expression:
'x == Blanks'
'x == NonBlanks'
Excel also allows some simple string matching operations:
'x == b*' # begins with b
'x != b*' # doesn't begin with b
'x == *b' # ends with b
'x != *b' # doesn't end with b
'x == *b*' # contains b
'x != *b*' # doesn't contains b
You can also use '*'
to match any character or number and '?'
to match
any single character or number. No other regular expression quantifier is
supported by Excel’s filters. Excel’s regular expression characters can be
escaped using '~'
.
The placeholder variable x
in the above examples can be replaced by any
simple string. The actual placeholder name is ignored internally so the
following are all equivalent:
'x < 2000'
'col < 2000'
'Price < 2000'
A filter condition can only be applied to a column in a range specified by the
autofilter()
method.
Prior to Excel 2007 it was only possible to have either 1 or 2 filter
conditions such as the ones shown above in the filter_column()
method.
Excel 2007 introduced a new list style filter where it is possible to specify 1 or more ‘or’ style criteria. For example if your column contained data for the months of the year you could filter the data based on certain months:
The filter_column_list()
method can be used to represent these types of
filters:
worksheet.filter_column_list('A', ['March', 'April', 'May'])
One or more criteria can be selected:
worksheet.filter_column_list('A', ['March'])
worksheet.filter_column_list('B', [100, 110, 120, 130])
To filter blanks as part of the list use Blanks as a list item:
worksheet.filter_column_list('A', ['March', 'April', 'May', 'Blanks'])
As explained above, it isn’t sufficient to just specify filters. You must also hide any rows that don’t match the filter condition.
See Example: Applying Autofilters for a full example of all these features.
Data validation is a feature of Excel which allows you to restrict the data that a user enters in a cell and to display associated help and warning messages. It also allows you to restrict input to values in a dropdown list.
A typical use case might be to restrict data in a cell to integer values in a certain range, to provide a help message to indicate the required value and to issue a warning if the input data doesn’t meet the stated criteria. In XlsxWriter we could do that as follows:
worksheet.data_validation('B25', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 100,
'input_title': 'Enter an integer:',
'input_message': 'between 1 and 100'})
If the user inputs a value that doesn’t match the specified criteria an error message is displayed:
For more information on data validation see the Microsoft support article “Description and examples of data validation in Excel”: http://support.microsoft.com/kb/211485.
The following sections describe how to use the data_validation()
method and
its various options.
The data_validation()
method is used to construct an Excel data
validation.
The data validation can be applied to a single cell or a range of cells. As usual you can use A1 or Row/Column notation, see Working with Cell Notation.
With Row/Column notation you must specify all four cells in the range:
(first_row, first_col, last_row, last_col)
. If you need to refer to a
single cell set the last_ values equal to the first_ values. With A1
notation you can refer to a single cell or a range of cells:
worksheet.data_validation(0, 0, 4, 1, {...})
worksheet.data_validation('B1', {...})
worksheet.data_validation('C1:E5', {...})
The options parameter in data_validation()
must be a dictionary containing
the parameters that describe the type and style of the data validation. The
main parameters are:
validate |
||
criteria |
||
value |
minimum |
source |
maximum |
||
ignore_blank |
||
dropdown |
||
input_title |
||
input_message |
||
show_input |
||
error_title |
||
error_message |
||
error_type |
||
show_error |
These parameters are explained in the following sections. Most of the
parameters are optional, however, you will generally require the three main
options validate
, criteria
and value
:
worksheet.data_validation('A1', {'validate': 'integer',
'criteria': '>',
'value': 100})
The validate
parameter is used to set the type of data that you wish to
validate:
worksheet.data_validation('A1', {'validate': 'integer',
'criteria': '>',
'value': 100})
It is always required and it has no default value. Allowable values are:
integer
decimal
list
date
time
length
custom
any
TRUE/FALSE
value.The criteria
parameter is used to set the criteria by which the data in the
cell is validated. It is almost always required except for the list
,
custom
and any
validate options. It has no default value:
worksheet.data_validation('A1', {'validate': 'integer',
'criteria': '>',
'value': 100})
Allowable values are:
between |
|
not between |
|
equal to |
== |
not equal to |
!= |
greater than |
> |
less than |
< |
greater than or equal to |
>= |
less than or equal to |
<= |
You can either use Excel’s textual description strings, in the first column above, or the more common symbolic alternatives. The following are equivalent:
worksheet.data_validation('A1', {'validate': 'integer',
'criteria': '>',
'value': 100})
worksheet.data_validation('A1', {'validate': 'integer',
'criteria': 'greater than',
'value': 100})
The list
, custom
and any
validate options don’t require a
criteria
. If you specify one it will be ignored:
worksheet.data_validation('B13', {'validate': 'list',
'source': ['open', 'high', 'close']})
worksheet.data_validation('B23', {'validate': 'custom',
'value': '=AND(F5=50,G5=60)'})
The value
parameter is used to set the limiting value to which the
criteria
is applied. It is always required and it has no default value.
You can also use the synonyms minimum
or source
to make the validation
a little clearer and closer to Excel’s description of the parameter:
# Using 'value'.
worksheet.data_validation('A1', {'validate': 'integer',
'criteria': 'greater than',
'value': 100})
# Using 'minimum'.
worksheet.data_validation('B11', {'validate': 'decimal',
'criteria': 'between',
'minimum': 0.1,
'maximum': 0.5})
# Using 'source'.
worksheet.data_validation('B10', {'validate': 'list',
'source': '=$E$4:$G$4'})
# Using 'source' with a string list.
worksheet.data_validation('B13', {'validate': 'list',
'source': ['open', 'high', 'close']})
Note, when using the list
validation with a list of strings, like in the
last example above, Excel stores the strings internally as a Comma Separated
Variable string. The total length for this string, including commas, cannot
exceed the Excel limit of 255 characters. For longer sets of data you should
use a range reference like the prior example above.
The maximum
parameter is used to set the upper limiting value when the
criteria
is either 'between'
or 'not between'
:
worksheet.data_validation('B11', {'validate': 'decimal',
'criteria': 'between',
'minimum': 0.1,
'maximum': 0.5})
The ignore_blank
parameter is used to toggle on and off the ‘Ignore blank’
option in the Excel data validation dialog. When the option is on the data
validation is not applied to blank data in the cell. It is on by default:
worksheet.data_validation('B5', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 10,
'ignore_blank': False,
})
The dropdown
parameter is used to toggle on and off the ‘In-cell dropdown’
option in the Excel data validation dialog. When the option is on a dropdown
list will be shown for list
validations. It is on by default.
The input_title
parameter is used to set the title of the input message
that is displayed when a cell is entered. It has no default value and is only
displayed if the input message is displayed. See the input_message
parameter below.
The maximum title length is 32 characters.
The input_message
parameter is used to set the input message that is
displayed when a cell is entered. It has no default value:
worksheet.data_validation('B25', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 100,
'input_title': 'Enter an integer:',
'input_message': 'between 1 and 100'})
The input message generated from the above example is:
The message can be split over several lines using newlines. The maximum message length is 255 characters.
The show_input
parameter is used to toggle on and off the ‘Show input
message when cell is selected’ option in the Excel data validation dialog.
When the option is off an input message is not displayed even if it has been
set using input_message
. It is on by default.
The error_title
parameter is used to set the title of the error message
that is displayed when the data validation criteria is not met. The default
error title is ‘Microsoft Excel’. The maximum title length is 32 characters.
The error_message
parameter is used to set the error message that is
displayed when a cell is entered. The default error message is “The value you
entered is not valid. A user has restricted values that can be entered into
the cell.”. A non-default error message can be displayed as follows:
worksheet.data_validation('B27', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 100,
'input_title': 'Enter an integer:',
'input_message': 'between 1 and 100',
'error_title': 'Input value not valid!',
'error_message': 'It should be an integer between 1 and 100'})
Which give the following message:
The message can be split over several lines using newlines. The maximum message length is 255 characters.
The error_type
parameter is used to specify the type of error dialog that
is displayed. There are 3 options:
'stop'
'warning'
'information'
The default is 'stop'
.
The show_error
parameter is used to toggle on and off the ‘Show error alert
after invalid data is entered’ option in the Excel data validation dialog.
When the option is off an error message is not displayed even if it has been
set using error_message
. It is on by default.
Example 1. Limiting input to an integer greater than a fixed value:
worksheet.data_validation('A1', {'validate': 'integer',
'criteria': '>',
'value': 0,
})
Example 2. Limiting input to an integer greater than a fixed value where the value is referenced from a cell:
worksheet.data_validation('A2', {'validate': 'integer',
'criteria': '>',
'value': '=E3',
})
Example 3. Limiting input to a decimal in a fixed range:
worksheet.data_validation('A3', {'validate': 'decimal',
'criteria': 'between',
'minimum': 0.1,
'maximum': 0.5,
})
Example 4. Limiting input to a value in a dropdown list:
worksheet.data_validation('A4', {'validate': 'list',
'source': ['open', 'high', 'close'],
})
Example 5. Limiting input to a value in a dropdown list where the list is specified as a cell range:
worksheet.data_validation('A5', {'validate': 'list',
'source': '=$E$4:$G$4',
})
Example 6. Limiting input to a date in a fixed range:
from datetime import date
worksheet.data_validation('A6', {'validate': 'date',
'criteria': 'between',
'minimum': date(2013, 1, 1),
'maximum': date(2013, 12, 12),
})
Example 7. Displaying a message when the cell is selected:
worksheet.data_validation('A7', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 100,
'input_title': 'Enter an integer:',
'input_message': 'between 1 and 100',
})
Conditional formatting is a feature of Excel which allows you to apply a format to a cell or a range of cells based on certain criteria.
For example the following rules are used to highlight cells in the conditional_format.py example:
worksheet.conditional_format('B3:K12', {'type': 'cell',
'criteria': '>=',
'value': 50,
'format': format1})
worksheet.conditional_format('B3:K12', {'type': 'cell',
'criteria': '<',
'value': 50,
'format': format2})
Which gives criteria like this:
And output which looks like this:
It is also possible to create color scales and data bars:
The conditional_format()
worksheet method is used to apply formatting
based on user defined criteria to an XlsxWriter file.
The conditional format can be applied to a single cell or a range of cells. As usual you can use A1 or Row/Column notation (Working with Cell Notation).
With Row/Column notation you must specify all four cells in the range:
(first_row, first_col, last_row, last_col)
. If you need to refer to a
single cell set the last_*
values equal to the first_*
values. With A1
notation you can refer to a single cell or a range of cells:
worksheet.conditional_format(0, 0, 4, 1, {...})
worksheet.conditional_format('B1', {...})
worksheet.conditional_format('C1:E5', {...})
The options parameter in conditional_format()
must be a dictionary
containing the parameters that describe the type and style of the conditional
format. The main parameters are:
type
format
criteria
value
minimum
maximum
Other, less commonly used parameters are:
min_type
mid_type
max_type
min_value
mid_value
max_value
min_color
mid_color
max_color
bar_color
bar_only
bar_solid
bar_negative_color
bar_border_color
bar_negative_border_color
bar_negative_color_same
bar_negative_border_color_same
bar_no_border
bar_direction
bar_axis_position
bar_axis_color
data_bar_2010
icon_style
icons
reverse_icons
icons_only
stop_if_true
multi_range
The conditional format options that can be used with conditional_format()
are explained in the following sections.
The type
option is a required parameter and it has no default value.
Allowable type
values and their associated parameters are:
| Type | Parameters |
|---|---|
| cell | criteria |
| value | |
| minimum | |
| maximum | |
| format | |
| date | criteria |
| value | |
| minimum | |
| maximum | |
| format | |
| time_period | criteria |
| format | |
| text | criteria |
| value | |
| format | |
| average | criteria |
| format | |
| duplicate | format |
| unique | format |
| top | criteria |
| value | |
| format | |
| bottom | criteria |
| value | |
| format | |
| blanks | format |
| no_blanks | format |
| errors | format |
| no_errors | format |
| formula | criteria |
| format | |
| 2_color_scale | min_type |
| max_type | |
| min_value | |
| max_value | |
| min_color | |
| max_color | |
| 3_color_scale | min_type |
| mid_type | |
| max_type | |
| min_value | |
| mid_value | |
| max_value | |
| min_color | |
| mid_color | |
| max_color | |
| data_bar | min_type |
| max_type | |
| min_value | |
| max_value | |
| bar_only | |
| bar_color | |
| bar_solid* | |
| bar_negative_color* | |
| bar_border_color* | |
| bar_negative_border_color* | |
| bar_negative_color_same* | |
| bar_negative_border_color_same* | |
| bar_no_border* | |
| bar_direction* | |
| bar_axis_position* | |
| bar_axis_color* | |
| data_bar_2010* | |
| icon_set | icon_style |
| reverse_icons | |
| icons | |
| icons_only |
Note
Data bar parameters marked with (*) are only available in Excel 2010 and later. Files that use these properties can still be opened in Excel 2007 but the data bars will be displayed without them.
This is the most common conditional formatting type. It is used when a format is applied to a cell based on a simple criterion.
For example using a single cell and the greater than
criteria:
worksheet.conditional_format('A1', {'type': 'cell',
'criteria': 'greater than',
'value': 5,
'format': red_format})
Or, using a range and the between
criteria:
worksheet.conditional_format('C1:C4', {'type': 'cell',
'criteria': 'between',
'minimum': 20,
'maximum': 30,
'format': green_format})
Other types are shown below, after the other main options.
The criteria