parameter is used to set the criteria by which the cell data
will be evaluated. It has no default value. The most common criteria as
applied to {'type': 'cell'}
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.
Additional criteria which are specific to other conditional format types are shown in the relevant sections below.
The value
is generally used along with the criteria
parameter to set
the rule by which the cell data will be evaluated:
worksheet.conditional_format('A1', {'type': 'cell',
'criteria': 'equal to',
'value': 5,
'format': red_format})
If the type
is cell
and the value
is a string then it should be
double quoted, as required by Excel:
worksheet.conditional_format('A1', {'type': 'cell',
'criteria': 'equal to',
'value': '"Failed"',
'format': red_format})
The value
property can also be an cell reference:
worksheet.conditional_format('A1', {'type': 'cell',
'criteria': 'equal to',
'value': '$C$1',
'format': red_format})
Note
In general any value
property that refers to a cell reference should
use an absolute reference, especially if the
conditional formatting is applied to a range of values. Without an absolute
cell reference the conditional format will not be applied correctly by
Excel from the first cell in the formatted range.
The format
parameter is used to specify the format that will be applied to
the cell when the conditional formatting criterion is met. The format is
created using the add_format()
method in the same way as cell formats:
format1 = workbook.add_format({'bold': 1, 'italic': 1})
worksheet.conditional_format('A1', {'type': 'cell',
'criteria': '>',
'value': 5,
'format': format1})
Note
In Excel, a conditional format is superimposed over the existing cell format and not all cell format properties can be modified. Properties that cannot be modified in a conditional format are font name, font size, superscript and subscript, diagonal borders, all alignment properties and all protection properties.
Excel specifies some default formats to be used with conditional formatting. These can be replicated using the following XlsxWriter formats:
# Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
'font_color': '#9C0006'})
# Light yellow fill with dark yellow text.
format2 = workbook.add_format({'bg_color': '#FFEB9C',
'font_color': '#9C6500'})
# Green fill with dark green text.
format3 = workbook.add_format({'bg_color': '#C6EFCE',
'font_color': '#006100'})
See also The Format Class.
The minimum
parameter is used to set the lower limiting value when the
criteria
is either 'between'
or 'not between'
:
worksheet.conditional_format('A1', {'type': 'cell',
'criteria': 'between',
'minimum': 2,
'maximum': 6,
'format': format1,
})
The maximum
parameter is used to set the upper limiting value when the
criteria
is either 'between'
or 'not between'
. See the previous
example.
The date
type is similar the cell
type and uses the same criteria and
values. However, the value
, minimum
and maximum
properties are
specified as a datetime object as shown in Working with Dates and Time:
date = datetime.datetime.strptime('2011-01-01', "%Y-%m-%d")
worksheet.conditional_format('A1:A4', {'type': 'date',
'criteria': 'greater than',
'value': date,
'format': format1})
The time_period
type is used to specify Excel’s “Dates Occurring” style
conditional format:
worksheet.conditional_format('A1:A4', {'type': 'time_period',
'criteria': 'yesterday',
'format': format1})
The period is set in the criteria
and can have one of the following values:
'criteria': 'yesterday',
'criteria': 'today',
'criteria': 'last 7 days',
'criteria': 'last week',
'criteria': 'this week',
'criteria': 'next week',
'criteria': 'last month',
'criteria': 'this month',
'criteria': 'next month'
The text
type is used to specify Excel’s “Specific Text” style conditional
format. It is used to do simple string matching using the criteria
and
value
parameters:
worksheet.conditional_format('A1:A4', {'type': 'text',
'criteria': 'containing',
'value': 'foo',
'format': format1})
The criteria
can have one of the following values:
'criteria': 'containing',
'criteria': 'not containing',
'criteria': 'begins with',
'criteria': 'ends with',
The value
parameter should be a string or single character.
The average
type is used to specify Excel’s “Average” style conditional
format:
worksheet.conditional_format('A1:A4', {'type': 'average',
'criteria': 'above',
'format': format1})
The type of average for the conditional format range is specified by the
criteria
:
'criteria': 'above',
'criteria': 'below',
'criteria': 'equal or above',
'criteria': 'equal or below',
'criteria': '1 std dev above',
'criteria': '1 std dev below',
'criteria': '2 std dev above',
'criteria': '2 std dev below',
'criteria': '3 std dev above',
'criteria': '3 std dev below',
The duplicate
type is used to highlight duplicate cells in a range:
worksheet.conditional_format('A1:A4', {'type': 'duplicate',
'format': format1})
The unique
type is used to highlight unique cells in a range:
worksheet.conditional_format('A1:A4', {'type': 'unique',
'format': format1})
The top
type is used to specify the top n
values by number or
percentage in a range:
worksheet.conditional_format('A1:A4', {'type': 'top',
'value': 10,
'format': format1})
The criteria
can be used to indicate that a percentage condition is
required:
worksheet.conditional_format('A1:A4', {'type': 'top',
'value': 10,
'criteria': '%',
'format': format1})
The bottom
type is used to specify the bottom n
values by number or
percentage in a range.
It takes the same parameters as top
, see above.
The blanks
type is used to highlight blank cells in a range:
worksheet.conditional_format('A1:A4', {'type': 'blanks',
'format': format1})
The no_blanks
type is used to highlight non blank cells in a range:
worksheet.conditional_format('A1:A4', {'type': 'no_blanks',
'format': format1})
The errors
type is used to highlight error cells in a range:
worksheet.conditional_format('A1:A4', {'type': 'errors',
'format': format1})
The no_errors
type is used to highlight non error cells in a range:
worksheet.conditional_format('A1:A4', {'type': 'no_errors',
'format': format1})
The formula
type is used to specify a conditional format based on a user
defined formula:
worksheet.conditional_format('A1:A4', {'type': 'formula',
'criteria': '=$A$1>5',
'format': format1})
The formula is specified in the criteria
.
Formulas must be written with the US style separator/range operator which is a
comma (not semi-colon) and should follow the same rules as
write_formula()
. See Non US Excel functions and syntax for a full explanation:
# This formula will cause an Excel error on load due to
# non-English language and use of semi-colons.
worksheet.conditional_format('A2:C9' ,
{'type': 'formula',
'criteria': '=ODER($B2<$C2;UND($B2="";$C2>HEUTE()))',
'format': format1
})
# This is the correct syntax.
worksheet.conditional_format('A2:C9' ,
{'type': 'formula',
'criteria': '=OR($B2<$C2,AND($B2="",$C2>TODAY()))',
'format': format1
})
Also, any cell or range references in the formula should
be absolute references if they are applied to the full
range of the conditional format. See the note in the value
section above.
The 2_color_scale
type is used to specify Excel’s “2 Color Scale” style
conditional format:
worksheet.conditional_format('A1:A12', {'type': '2_color_scale'})
This conditional type can be modified with min_type
, max_type
,
min_value
, max_value
, min_color
and max_color
, see below.
The 3_color_scale
type is used to specify Excel’s “3 Color Scale” style
conditional format:
worksheet.conditional_format('A1:A12', {'type': '3_color_scale'})
This conditional type can be modified with min_type
, mid_type
,
max_type
, min_value
, mid_value
, max_value
, min_color
,
mid_color
and max_color
, see below.
The data_bar
type is used to specify Excel’s “Data Bar” style conditional
format:
worksheet.conditional_format('A1:A12', {'type': 'data_bar'})
This conditional type can be modified with the following parameters, which are explained in the sections below. These properties were available in the original xlsx file specification used in Excel 2007:
min_type
max_type
min_value
max_value
bar_color
bar_only
In Excel 2010 additional data bar properties were added such as solid (non-gradient) bars and control over how negative values are displayed. These properties can be set using the following parameters:
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
Files that use these Excel 2010 properties can still be opened in Excel 2007 but the data bars will be displayed without them.
The icon_set
type is used to specify a conditional format with a set of
icons such as traffic lights or arrows:
worksheet.conditional_format('A1:C1', {'type': 'icon_set',
'icon_style': '3_traffic_lights'})
The icon set style is specified by the icon_style
parameter. Valid options are:
3_arrows
3_arrows_gray
3_flags
3_signs
3_symbols
3_symbols_circled
3_traffic_lights
3_traffic_lights_rimmed
4_arrows
4_arrows_gray
4_ratings
4_red_to_black
4_traffic_lights
5_arrows
5_arrows_gray
5_quarters
5_ratings
The criteria, type and value of each icon can be specified using the icon
array of dicts with optional criteria
, type
and value
parameters:
worksheet.conditional_format(
'A1:D1',
{'type': 'icon_set',
'icon_style': '4_red_to_black',
'icons': [{'criteria': '>=', 'type': 'number', 'value': 90},
{'criteria': '<', 'type': 'percentile', 'value': 50},
{'criteria': '<=', 'type': 'percent', 'value': 25}]}
)
The icons criteria
parameter should be either >=
or <
. The default
criteria
is >=
.
The icons type
parameter should be one of the following values:
number
percentile
percent
formula
The default type
is percent
.
The icons value
parameter can be a value or formula:
worksheet.conditional_format('A1:D1',
{'type': 'icon_set',
'icon_style': '4_red_to_black',
'icons': [{'value': 90},
{'value': 50},
{'value': 25}]})
Note: The icons
parameters should start with the highest value and with
each subsequent one being lower.
The default value
is (n * 100) / number_of_icons
. The lowest number
icon in an icon set has properties defined by Excel. Therefore in a n
icon
set, there is no n-1
hash of parameters.
The order of the icons can be reversed using the reverse_icons
parameter:
worksheet.conditional_format('A1:C1',
{'type': 'icon_set',
'icon_style': '3_arrows',
'reverse_icons': True})
The icons can be displayed without the cell value using the icons_only
parameter:
worksheet.conditional_format('A1:C1',
{'type': 'icon_set',
'icon_style': '3_flags',
'icons_only': True})
The min_type
and max_type
properties are available when the conditional
formatting type is 2_color_scale
, 3_color_scale
or data_bar
. The
mid_type
is available for 3_color_scale
. The properties are used as
follows:
worksheet.conditional_format('A1:A12', {'type': '2_color_scale',
'min_type': 'percent',
'max_type': 'percent'})
The available min/mid/max types are:
min (for min_type only)
num
percent
percentile
formula
max (for max_type only)
Used for 3_color_scale
. Same as min_type
, see above.
Same as min_type
, see above.
The min_value
and max_value
properties are available when the
conditional formatting type is 2_color_scale
, 3_color_scale
or
data_bar
. The mid_value
is available for 3_color_scale
. The
properties are used as follows:
worksheet.conditional_format('A1:A12', {'type': '2_color_scale',
'min_value': 10,
'max_value': 90})
Used for 3_color_scale
. Same as min_value
, see above.
Same as min_value
, see above.
The min_color
and max_color
properties are available when the
conditional formatting type is 2_color_scale
, 3_color_scale
or
data_bar
. The mid_color
is available for 3_color_scale
. The
properties are used as follows:
worksheet.conditional_format('A1:A12', {'type': '2_color_scale',
'min_color': '#C5D9F1',
'max_color': '#538ED5'})
The color can be a Html style #RRGGBB
string or a limited number named
colors, see Working with Colors.
Used for 3_color_scale
. Same as min_color
, see above.
Same as min_color
, see above.
The bar_color
parameter sets the fill color for data bars:
worksheet.conditional_format('F3:F14', {'type': 'data_bar',
'bar_color': '#63C384'})
The color can be a Html style #RRGGBB
string or a limited number named
colors, see Working with Colors.
The bar_only
property displays a bar data but not the data in the cells:
worksheet.conditional_format('D3:D14', {'type': 'data_bar',
'bar_only': True})
See the image above.
The bar_solid
property turns on a solid (non-gradient) fill for data
bars:
worksheet.conditional_format('H3:H14', {'type': 'data_bar',
'bar_solid': True})
See the image above.
Note, this property is only visible in Excel 2010 and later.
The bar_negative_color
property sets the color fill for the negative
portion of a data bar:
worksheet.conditional_format('F3:F14', {'type': 'data_bar',
'bar_negative_color': '#63C384'})
The color can be a Html style #RRGGBB
string or a limited number named
colors, see Working with Colors.
Note, this property is only visible in Excel 2010 and later.
The bar_border_color
property sets the color for the border line of a data
bar:
worksheet.conditional_format('F3:F14', {'type': 'data_bar',
'bar_border_color': '#63C384'})
The color can be a Html style #RRGGBB
string or a limited number named
colors, see Working with Colors.
Note, this property is only visible in Excel 2010 and later.
The bar_negative_border_color
property sets the color for the border of
the negative portion of a data bar:
worksheet.conditional_format('F3:F14', {'type': 'data_bar',
'bar_negative_border_color': '#63C384'})
The color can be a Html style #RRGGBB
string or a limited number named
colors, see Working with Colors.
Note, this property is only visible in Excel 2010 and later.
The bar_negative_color_same
property sets the fill color for the negative
portion of a data bar to be the same as the fill color for the positive
portion of the data bar:
worksheet.conditional_format('F3:F14', {'type': 'data_bar',
'bar_negative_color_same': True})
Note, this property is only visible in Excel 2010 and later.
The bar_negative_border_color_same
property sets the border color for the
negative portion of a data bar to be the same as the border color for the
positive portion of the data bar:
worksheet.conditional_format('F3:F14', {'type': 'data_bar',
'bar_negative_border_color_same': True})
See the image above.
Note, this property is only visible in Excel 2010 and later.
The bar_no_border
property turns off the border for data bars:
worksheet.conditional_format('F3:F14', {'type': 'data_bar',
'bar_no_border': True})
Note, this property is only visible in Excel 2010 and later, however the default in Excel 2007 is to not have a border.
The bar_direction
property sets the direction for data bars. This property
can be either left
for left-to-right or right
for right-to-left. If
the property isn’t set then Excel will adjust the position automatically based
on the context:
worksheet.conditional_format('J3:J14', {'type': 'data_bar',
'bar_direction': 'right'})
Note, this property is only visible in Excel 2010 and later.
The bar_axis_position
property sets the position within the cells for the
axis that is shown in data bars when there are negative values to display. The
property can be either middle
or none
. If the property isn’t set then
Excel will position the axis based on the range of positive and negative
values:
worksheet.conditional_format('J3:J14', {'type': 'data_bar',
'bar_axis_position': 'middle'})
Note, this property is only visible in Excel 2010 and later.
The bar_axis_color
property sets the color for the axis that is shown in
data bars when there are negative values to display:
worksheet.conditional_format('J3:J14', {'type': 'data_bar',
'bar_axis_color': '#0070C0'})
Note, this property is only visible in Excel 2010 and later.
The data_bar_2010
property sets Excel 2010 style data bars even when Excel
2010 specific properties aren’t used. This can be used for consistency across
all the data bar formatting in a worksheet:
worksheet.conditional_format('L3:L14', {'type': 'data_bar',
'data_bar_2010': True})
The stop_if_true
parameter can be used to set the “stop if true” feature
of a conditional formatting rule when more than one rule is applied to a cell
or a range of cells. When this parameter is set then subsequent rules are not
evaluated if the current rule is true:
worksheet.conditional_format('A1',
{'type': 'cell',
'format': cell_format,
'criteria': '>',
'value': 20,
'stop_if_true': True
})
The multi_range
option is used to extend a conditional format over
non-contiguous ranges.
It is possible to apply the conditional format to different cell ranges in a
worksheet using multiple calls to conditional_format()
. However, as a
minor optimization it is also possible in Excel to apply the same conditional
format to different non-contiguous cell ranges.
This is replicated in conditional_format()
using the multi_range
option. The range must contain the primary range for the conditional format
and any others separated by spaces.
For example to apply one conditional format to two ranges, 'B3:K6'
and
'B9:K12'
:
worksheet.conditional_format('B3:K6', {'type': 'cell',
'criteria': '>=',
'value': 50,
'format': format1,
'multi_range': 'B3:K6 B9:K12'})
Highlight cells greater than an integer value:
worksheet.conditional_format('A1:F10', {'type': 'cell',
'criteria': 'greater than',
'value': 5,
'format': format1})
Highlight cells greater than a value in a reference cell:
worksheet.conditional_format('A1:F10', {'type': 'cell',
'criteria': 'greater than',
'value': 'H1',
'format': format1})
Highlight cells more recent (greater) than a certain date:
date = datetime.datetime.strptime('2011-01-01', "%Y-%m-%d")
worksheet.conditional_format('A1:F10', {'type': 'date',
'criteria': 'greater than',
'value': date,
'format': format1})
Highlight cells with a date in the last seven days:
worksheet.conditional_format('A1:F10', {'type': 'time_period',
'criteria': 'last 7 days',
'format': format1})
Highlight cells with strings starting with the letter b
:
worksheet.conditional_format('A1:F10', {'type': 'text',
'criteria': 'begins with',
'value': 'b',
'format': format1})
Highlight cells that are 1 standard deviation above the average for the range:
worksheet.conditional_format('A1:F10', {'type': 'average',
'format': format1})
Highlight duplicate cells in a range:
worksheet.conditional_format('A1:F10', {'type': 'duplicate',
'format': format1})
Highlight unique cells in a range:
worksheet.conditional_format('A1:F10', {'type': 'unique',
'format': format1})
Highlight the top 10 cells:
worksheet.conditional_format('A1:F10', {'type': 'top',
'value': 10,
'format': format1})
Highlight blank cells:
worksheet.conditional_format('A1:F10', {'type': 'blanks',
'format': format1})
Set traffic light icons in 3 cells:
worksheet.conditional_format('B3:D3', {'type': 'icon_set',
'icon_style': '3_traffic_lights'})
See also Example: Conditional Formatting.
Tables in Excel are a way of grouping a range of cells into a single entity that has common formatting or that can be referenced from formulas. Tables can have column headers, autofilters, total rows, column formulas and default formatting.
For more information see An Overview of Excel Tables in the Microsoft Office documentation.
Note
Tables aren’t available in XlsxWriter when Workbook()
'constant_memory'
mode is enabled.
Tables are added to a worksheet using the add_table()
method:
worksheet.add_table('B3:F7', {options})
The data range can be specified in ‘A1’ or ‘Row/Column’ notation (see Working with Cell Notation):
worksheet.add_table('B3:F7')
# Same as:
worksheet.add_table(2, 1, 6, 5)
The options parameter should be a dict containing the parameters that describe the table options and data. The available options are:
| data |
| autofilter |
| header_row |
| banded_columns |
| banded_rows |
| first_column |
| last_column |
| style |
| total_row |
| columns |
| name |
These options are explained below. There are no required parameters and the options parameter is itself optional if no options are specified (as shown above).
The data
parameter can be used to specify the data in the cells of the
table:
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
worksheet.add_table('B3:F7', {'data': data})
Table data can also be written separately, as an array or individual cells:
# These statements are the same as the single statement above.
worksheet.add_table('B3:F7')
worksheet.write_row('B4', data[0])
worksheet.write_row('B5', data[1])
worksheet.write_row('B6', data[2])
worksheet.write_row('B7', data[3])
Writing the cell data separately is occasionally required when you need to
control the write_()
methods used to populate the cells or if you wish to
modify individual cell formatting.
The data
structure should be an list of lists holding row data as shown
above.
The header_row
parameter can be used to turn on or off the header row in
the table. It is on by default:
# Turn off the header row.
worksheet.add_table('B4:F7', {'header_row': False})
The header row will contain default captions such as Column 1
,
Column 2
, etc. These captions can be overridden using the columns
parameter below.
The autofilter
parameter can be used to turn on or off the autofilter in
the header row. It is on by default:
# Turn off the default autofilter.
worksheet.add_table('B3:F7', {'autofilter': False})
The autofilter
is only shown if the header_row
is on. Filter conditions
within the table are not supported.
The banded_rows
parameter can be used to create rows of alternating color
in the table. It is on by default:
# Turn off banded rows.
worksheet.add_table('B3:F7', {'banded_rows': False})
The banded_columns
parameter can be used to used to create columns of
alternating color in the table. It is off by default:
# Turn on banded columns.
worksheet.add_table('B3:F7', {'banded_columns': True})
See the above image.
The first_column
parameter can be used to highlight the first column of the
table. The type of highlighting will depend on the style
of the table. It
may be bold text or a different color. It is off by default:
# Turn on highlighting for the first column in the table.
worksheet.add_table('B3:F7', {'first_column': True})
The last_column
parameter can be used to highlight the last column of the
table. The type of highlighting will depend on the style
of the table. It
may be bold text or a different color. It is off by default:
# Turn on highlighting for the last column in the table.
worksheet.add_table('B3:F7', {'last_column': True})
See the above image.
The style
parameter can be used to set the style of the table. Standard
Excel table format names should be used (with matching capitalization):
worksheet.add_table('B3:F7', {'data': data,
'style': 'Table Style Light 11'})
The default table style is ‘Table Style Medium 9’.
You can also turn the table style off by setting it to None:
worksheet.add_table('B3:F7', {'data': data, 'style': None})
By default tables are named Table1
, Table2
, etc. The name
parameter can be used to set the name of the table:
worksheet.add_table('B3:F7', {'name': 'SalesData'})
If you override the table name you must ensure that it doesn’t clash with an existing table name and that it follows Excel’s requirements for table names, see the Microsoft Office documentation.
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
The total_row
parameter can be used to turn on the total row in the last
row of a table. It is distinguished from the other rows by a different
formatting and also with dropdown SUBTOTAL
functions:
worksheet.add_table('B3:F7', {'total_row': True})
The default total row doesn’t have any captions or functions. These must by
specified via the columns
parameter below.
The columns
parameter can be used to set properties for columns within the
table.
The sub-properties that can be set are:
| header |
| header_format |
| formula |
| total_string |
| total_function |
| total_value |
| format |
The column data must be specified as a list of dicts. For example to override the default ‘Column n’ style table headers:
worksheet.add_table('B3:F7', {'data': data,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1'},
{'header': 'Quarter 2'},
{'header': 'Quarter 3'},
{'header': 'Quarter 4'},
]})
See the resulting image above.
If you don’t wish to specify properties for a specific column you pass an empty hash ref and the defaults will be applied:
...
columns, [
{header, 'Product'},
{header, 'Quarter 1'},
{}, # Defaults to 'Column 3'.
{header, 'Quarter 3'},
{header, 'Quarter 4'},
]
...
Column formulas can by applied using the column formula
property:
formula = '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
worksheet.add_table('B3:G7', {'data': data,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1'},
{'header': 'Quarter 2'},
{'header': 'Quarter 3'},
{'header': 'Quarter 4'},
{'header': 'Year',
'formula': formula},
]})
The Excel 2007 style [#This Row]
and Excel 2010 style @
structural
references are supported within the formula. However, other Excel 2010
additions to structural references aren’t supported and formulas should
conform to Excel 2007 style formulas. See the Microsoft documentation on
Using structured references with Excel tables
for details.
As stated above the total_row
table parameter turns on the “Total” row in
the table but it doesn’t populate it with any defaults. Total captions and
functions must be specified via the columns
property and the
total_string
and total_function
sub properties:
options = {'data': data,
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1', 'total_function': 'sum'},
{'header': 'Quarter 2', 'total_function': 'sum'},
{'header': 'Quarter 3', 'total_function': 'sum'},
{'header': 'Quarter 4', 'total_function': 'sum'},
{'header': 'Year',
'formula': '=SUM(Table10[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum'
},
]}
# Add a table to the worksheet.
worksheet.add_table('B3:G8', options)
The supported totals row SUBTOTAL
functions are:
| average |
| count_nums |
| count |
| max |
| min |
| std_dev |
| sum |
| var |
User defined functions or formulas aren’t supported.
It is also possible to set a calculated value for the total_function
using
the total_value
sub property. This is only necessary when creating
workbooks for applications that cannot calculate the value of formulas
automatically. This is similar to setting the value
optional property in
write_formula()
:
options = {'data': data,
'total_row': 1,
'columns': [{'total_string': 'Totals'},
{'total_function': 'sum', 'total_value': 150},
{'total_function': 'sum', 'total_value': 200},
{'total_function': 'sum', 'total_value': 333},
{'total_function': 'sum', 'total_value': 124},
{'formula': '=SUM(Table10[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum',
'total_value': 807}]}
Formatting can also be applied to columns, to the column data using format
and to the header using header_format
:
currency_format = workbook.add_format({'num_format': '$#,##0'})
wrap_format = workbook.add_format({'text_wrap': 1})
worksheet.add_table('B3:D8', {'data': data,
'total_row': 1,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1',
'total_function': 'sum',
'format': currency_format},
{'header': 'Quarter 2',
'header_format': wrap_format,
'total_function': 'sum',
'format': currency_format}]})
Standard XlsxWriter Format object objects are used for this formatting. However, they should be limited to numerical formats for the columns and simple formatting like text wrap for the headers. Overriding other table formatting may produce inconsistent results.
All of the images shown above are taken from Example: Worksheet Tables.
This section explains how to work with some of the options and features of textboxes in XlsxWriter:
import xlsxwriter
workbook = xlsxwriter.Workbook('textbox.xlsx')
worksheet = workbook.add_worksheet()
text = 'Formatted textbox'
options = {
'width': 256,
'height': 100,
'x_offset': 10,
'y_offset': 10,
'font': {'color': 'red',
'size': 14},
'align': {'vertical': 'middle',
'horizontal': 'center'
},
'gradient': {'colors': ['#DDEBCF',
'#9CB86E',
'#156B13']},
}
worksheet.insert_textbox('B2', text, options)
workbook.close()
See also Example: Insert Textboxes into a Worksheet.
This Worksheet insert_textbox()
method is used to insert a textbox
into a worksheet:
worksheet.insert_textbox('B2', 'A simple textbox with some text')
The text can contain newlines to wrap the text:
worksheet.insert_textbox('B2', 'Line 1\nLine 2\n\nMore text')
This insert_textbox()
takes an optional dict
parameter that can be
used to control the size, positioning and format of the textbox:
worksheet.insert_textbox('B2', 'Some text', {'width': 256, 'height': 100})
The available options are:
# Size and position
width
height
x_scale
y_scale
x_offset
y_offset
object_position
# Formatting
line
border
fill
gradient
font
align
text_rotation
# Links
textlink
url
tip
These options are explained in the sections below. They are similar or identical to position and formatting parameters used in charts.
The insert_textbox()
options to control the size and positioning of a
textbox are:
width
height
x_scale
y_scale
x_offset
y_offset
object_position
The width
and height
are in pixels. The default textbox size is 192 x
120 pixels (or equivalent to 3 default columns x 6 default rows).
The size of the textbox can be modified by setting the width
and
height
or by setting the x_scale
and y_scale
:
worksheet.insert_textbox('B2', 'Size adjusted textbox',
{'width': 288, 'height': 30})
# or ...
worksheet.insert_textbox('B2', 'Size adjusted textbox',
{'x_scale': 1.5, 'y_scale': 0.25})
The x_offset
and y_offset
position the top left corner of the textbox in
the cell that it is inserted into.
The object_position
parameter can be used to control the object
positioning of the image:
worksheet.insert_textbox('B2', "Don't move or size with cells",
{'object_position': 3})
Where object_position
has the following allowable values:
See Working with Object Positioning for more detailed information about the positioning and scaling of images within a worksheet.
The following formatting properties can be set for textbox objects:
line
border
fill
gradient
font
align
text_rotation
Textbox formatting properties are set using the options dict:
worksheet.insert_textbox('B2', 'A textbox with a color text',
{'font': {'color': 'green'}})
In some cases the format properties can be nested:
worksheet.insert_textbox('B2', 'Some text in a textbox with formatting',
{'font': {'color': 'white'},
'align': {'vertical': 'middle',
'horizontal': 'center'
},
'gradient': {'colors': ['green', 'white']}})
The line format is used to specify properties of the border in a textbox. The
following properties can be set for line
formats in a textbox:
none
color
width
dash_type
The none
property is uses to turn the line
off (it is always on by
default):
worksheet.insert_textbox('B2', 'A textbox with no border line',
{'line': {'none': True}})
The color
property sets the color of the line
:
worksheet.insert_textbox('B2', 'A textbox with a color border',
{'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:
worksheet.insert_textbox('B2', 'A textbox with a color border',
{'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:
worksheet.insert_textbox('B2', 'A textbox with larger border',
{'line': {'width': 3.25}})
The dash_type
property sets the dash style of the line:
worksheet.insert_textbox('B2', 'A textbox a dash border',
{'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
.
More than one line
property can be specified at a time:
worksheet.insert_textbox('B2', 'A textbox with border formatting',
{'line': {'color': 'red',
'width': 1.25,
'dash_type': 'square_dot'}})
The border
property is a synonym for line
.
Excel uses a common dialog for setting object formatting but depending on context it may refer to a line or a border. For formatting these can be used interchangeably.
The solid fill format is used to specify a fill for a textbox object.
The following properties can be set for fill
formats in a textbox:
none
color
The none
property is used to turn the fill
property off (to make the
textbox transparent):
worksheet.insert_textbox('B2', 'A textbox with no fill',
{'fill': {'none': True}})
The color
property sets the color of the fill
area:
worksheet.insert_textbox('B2', 'A textbox with color fill',
{'fill': {'color': '#FF9900'}})
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:
worksheet.insert_textbox('B2', 'A textbox with color fill',
{'fill': {'color': 'red'}})
The gradient fill format is used to specify a gradient fill for a textbox. The
following properties can be set for gradient
fill formats in a textbox:
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
If gradient fill is used on a textbox object it overrides the solid fill properties of the object.
The colors
property sets a list of colors that define the gradient
:
worksheet.insert_textbox('B2', 'A textbox with gradient fill',
{'gradient': {'colors': ['gray', 'white']}})
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 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:
worksheet.insert_textbox('B2', 'A textbox with gradient fill',
{'gradient': {'colors': ['#DDEBCF',
'#9CB86E',
'#156B13']}})
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:
worksheet.insert_textbox('B2', 'A textbox with gradient fill',
{'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:
worksheet.insert_textbox('B2', 'A textbox with gradient fill',
{'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 (the
default angle is 90 degrees):
worksheet.insert_textbox('B2', 'A textbox with angle gradient',
{'gradient': {'colors': ['#DDEBCF', '#9CB86E', '#156B13'],
'angle': 45}})
The following font properties can be set for the entire textbox:
name
size
bold
italic
underline
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 font properties are:
name
: Set the font name:
{'font': {'name': 'Arial'}}
size
: Set the font size:
{'font': {'name': 'Arial', 'size': 7}}
bold
: Set the font bold property:
{'font': {'bold': True}}
italic
: Set the font italic property:
{'font': {'italic': True}}
underline
: Set the font underline property:
{'font': {'underline': True}}
color
: Set the font color property. Can be a color index, a color name
or HTML style RGB color:
{'font': {'color': 'red' }}
{'font': {'color': '#92D050'}}
Here is an example of Font formatting in a textbox:
worksheet.insert_textbox('B2', 'Some font formatting',
{'font': {'bold': True,
'italic': True,
'underline': True,
'name': 'Arial',
'color': 'red',
'size': 14}})
The align
property is used to set the text alignment for the entire textbox:
worksheet.insert_textbox('B2', 'Alignment: middle - center',
{'align': {'vertical': 'middle',
'horizontal': 'center'}})
The alignment properties that can be set in Excel for a textbox are:
{'align': {'vertical': 'top'}} # Default
{'align': {'vertical': 'middle'}}
{'align': {'vertical': 'bottom'}}
{'align': {'horizontal': 'left'}} # Default
{'align': {'horizontal': 'center'}}
Note, Excel doesn’t support right text alignment for the entire textbox. It does support it for text within the textbox but that currently isn’t supported by XlsxWriter, see the next section.
The default textbox alignment is:
worksheet.insert_textbox('B2', 'Default alignment',
{'align': {'vertical': 'top',
'horizontal': 'left'}})
# Same as this:
worksheet.insert_textbox('B2', 'Default alignment')
The text_rotation
option can be used to set the text rotation for the
entire textbox:
worksheet.insert_textbox('B2', 'Text rotated up',
{'text_rotation': 90})
Textboxes in Excel only support a limited number of rotation options. These are:
90: Rotate text up
-90: Rotate text down
270: Vertical text (stacked)
271: Vertical text (stacked) - for East Asian fonts
The textlink
property is used to link/get the text for a textbox from a
cell in the worksheet. When you use this option the actual text in the textbox
can be left blank or set to None
:
worksheet.insert_textbox('A1', '', {'textlink': '=$A$1'})
The reference can also be to a cell in another worksheet:
worksheet.insert_textbox('A2', None, {'textlink': '=Sheet2!A1'})
The url
parameter can used to add a hyperlink/url to a textbox:
worksheet.insert_textbox('A1', 'This is some text',
{'url': 'https://github.com/jmcnamara'})
The tip
parameter adds an optional mouseover tooltip:
worksheet.insert_textbox('A1', 'This is some text',
{'url': 'https://github.com/jmcnamara',
'tip': 'GitHub'})
See also write_url()
for details on supported URIs.
Sparklines are a feature of Excel 2010+ which allows you to add small charts to worksheet cells. These are useful for showing visual trends in data in a compact format.
Sparklines were invented by Edward Tufte: https://en.wikipedia.org/wiki/Sparklines
The add_sparkline()
worksheet method is used to add sparklines to a cell
or a range of cells:
worksheet.add_sparkline(0, 5, {'range': 'Sheet1!A1:E1'})
Both row-column and A1 style notation are supported. See Working with Cell Notation for more details.
The parameters to add_sparkline()
must be passed in a dictionary. The main
sparkline parameters are:
| range (required) |
| type |
| style |
| markers |
| negative_points |
| axis |
| reverse |
Other, less commonly used parameters are:
| location |
| high_point |
| low_point |
| first_point |
| last_point |
| max |
| min |
| empty_cells |
| show_hidden |
| date_axis |
| weight |
| series_color |
| negative_color |
| markers_color |
| first_color |
| last_color |
| high_color |
| low_color |
These parameters are explained in the sections below.
Note
Sparklines are a feature of Excel 2010+ only. You can write them to an XLSX file that can be read by Excel 2007 but they won’t be displayed.
The range
specifier is the only non-optional parameter.
It specifies the cell data range that the sparkline will plot:
worksheet.add_sparkline('F1', {'range': 'A1:E1'})
The range
should be a 2D array. (For 3D arrays of cells see “Grouped
Sparklines” below).
If range
is not on the same worksheet you can specify its location using
the usual Excel notation:
worksheet.add_sparkline('F1', {'range': 'Sheet2!A1:E1'})
If the worksheet contains spaces or special characters you should quote the worksheet name in the same way that Excel does:
worksheet.add_sparkline('F1', {'range': "'Monthly Data'!A1:E1"})
Specifies the type of sparkline. There are 3 available sparkline types:
line (default)
column
win_loss
For example:
worksheet.add_sparkline('F2', {'range': 'A2:E2',
'type': 'column'})
Excel provides 36 built-in Sparkline styles in 6 groups of 6. The style
parameter can be used to replicate these and should be a corresponding number
from 1 .. 36:
worksheet.add_sparkline('F2', {'range': 'A2:E2',
'type': 'column',
'style': 12})
The style number starts in the top left of the style grid and runs left to
right. The default style is 1. It is possible to override color elements of
the sparklines using the _color
parameters below.
Turn on the markers for line
style sparklines:
worksheet.add_sparkline('A6', {'range': 'Sheet2!A1:J1',
'markers': True})
Markers aren’t shown in Excel for column
and win_loss
sparklines.
Highlight negative values in a sparkline range. This is usually required with
win_loss
sparklines:
worksheet.add_sparkline('A9', {'range': 'Sheet2!A1:J1',
'negative_points': True})
Display a horizontal axis in the sparkline:
worksheet.add_sparkline('A10', {'range': 'Sheet2!A1:J1',
'axis': True})
Plot the data from right-to-left instead of the default left-to-right:
worksheet.add_sparkline('A24', {'range': 'Sheet2!A4:J4',
'type': 'column',
'style': 20,
'reverse': True})
Adjust the default line weight (thickness) for line
style sparklines:
worksheet.add_sparkline('F2', {'range': 'A2:E2',
'weight': 0.25})
The weight value should be one of the following values allowed by Excel:
0.25, 0.5, 0.75, 1, 1.25, 2.25, 3, 4.25, 6
Highlight points in a sparkline range:
worksheet.add_sparkline('A7', {'range': 'Sheet2!A1:J1',
'high_point': True,
'low_point': True,
'first_point': True})
Specify the maximum and minimum vertical axis values:
worksheet.add_sparkline('F1', {'range': 'A1:E1',
'max': 0.5,
'min': -0.5})
As a special case you can set the maximum and minimum to be for a group of sparklines rather than one:
'max': 'group'
See “Grouped Sparklines” below.
Define how empty cells are handled in a sparkline:
worksheet.add_sparkline('F1', {'range': 'A1:E1',
'empty_cells': 'zero'})
The available options are:
gaps
: show empty cells as gaps (the default).zero
: plot empty cells as 0.connect
: Connect points with a line (“line” type sparklines only).Specify an alternative date axis for the sparkline. This is useful if the data being plotted isn’t at fixed width intervals:
worksheet.add_sparkline('F3', {'range': 'A3:E3',
'date_axis': 'A4:E4'})
The number of cells in the date range should correspond to the number of cells in the data range.
It is possible to override the color of a sparkline style using the following parameters:
series_color
negative_color
markers_color
first_color
last_color
high_color
low_color
The color should be specified as a HTML style #rrggbb
hex value:
worksheet.add_sparkline('A18', {'range': 'Sheet2!A2:J2',
'type': 'column',
'series_color': '#E965E0'})
By default the sparkline location is specified by row
and col
in
add_sparkline()
. However, for grouped sparklines it is necessary to
specify more than one cell location. The location
parameter is used to
specify a list of cells. See “Grouped Sparklines” below.
The add_sparkline()
worksheet method can be used multiple times to write as
many sparklines as are required in a worksheet.
However, it is sometimes necessary to group contiguous sparklines so that
changes that are applied to one are applied to all. In Excel this is achieved
by selecting a 3D range of cells for the data range
and a 2D range of
cells for the location
.
In XlsxWriter, you can simulate this by passing an array refs of values to
location
and range
:
worksheet.add_sparkline('A27', {'location': ['A27', 'A28', 'A29'],
'range': ['A5:J5', 'A6:J6', 'A7:J7']})
See Example: Sparklines (Simple) and Example: Sparklines (Advanced).
Cell comments are a way of adding notation to cells in Excel. For example:
worksheet.write('A1', 'Hello')
worksheet.write_comment('A1', 'This is a comment')
The properties of the cell comment can be modified by passing an optional dictionary of key/value pairs to control the format of the comment. For example:
worksheet.write_comment('C3', 'Hello', {'x_scale': 1.2, 'y_scale': 0.8})
The following options are available:
author
visible
x_scale
width
y_scale
height
color
font_name
font_size
start_cell
start_row
start_col
x_offset
y_offset
The options are explained in detail below:
author
: This option is used to indicate who is the author of the cell
comment. Excel displays the author of the comment in the status bar at the
bottom of the worksheet. This is usually of interest in corporate
environments where several people might review and provide comments to a
workbook:
worksheet.write_comment('C3', 'Atonement', {'author': 'Ian McEwan'})
The default author for all cell comments in a worksheet can be set using
the set_comments_author()
method:
worksheet.set_comments_author('John Smith')
visible
: This option is used to make a cell comment visible when the
worksheet is opened. The default behavior in Excel is that comments are
initially hidden. However, it is also possible in Excel to make individual
comments or all comments visible. In XlsxWriter individual comments can be
made visible as follows:
worksheet.write_comment('C3', 'Hello', {'visible': True})
It is possible to make all comments in a worksheet visible using the
show_comments()
worksheet method. Alternatively, if all of the cell
comments have been made visible you can hide individual comments:
worksheet.write_comment('C3', 'Hello', {'visible': False})
x_scale
: This option is used to set the width of the cell comment box
as a factor of the default width:
worksheet.write_comment('C3', 'Hello', {'x_scale': 2 })
worksheet.write_comment('C4', 'Hello', {'x_scale': 4.2})
width
: This option is used to set the width of the cell comment box
explicitly in pixels:
worksheet.write_comment('C3', 'Hello', {'width': 200})
y_scale
: This option is used to set the height of the cell comment box
as a factor of the default height:
worksheet.write_comment('C3', 'Hello', {'y_scale': 2 })
worksheet.write_comment('C4', 'Hello', {'y_scale': 4.2})
height
: This option is used to set the height of the cell comment box
explicitly in pixels:
worksheet.write_comment('C3', 'Hello', {'height': 200})
color
: This option is used to set the background color of cell comment
box. You can use one of the named colors recognized by XlsxWriter or a Html
color. See Working with Colors:
worksheet.write_comment('C3', 'Hello', {'color': 'green' })
worksheet.write_comment('C4', 'Hello', {'color': '#CCFFCC'})
font_name
: This option is used to set the font for the comment:
worksheet.write_comment('C3', 'Hello', {'font_name': 'Courier'})
The default font is ‘Tahoma’.
font_size
: This option is used to set the font size for the comment:
worksheet.write_comment('C3', 'Hello', {'font_size': 10})
The default font size is 8.
start_cell
: This option is used to set the cell in which the comment
will appear. By default Excel displays comments one cell to the right and
one cell above the cell to which the comment relates. However, you can
change this behavior if you wish. In the following example the comment
which would appear by default in cell D2
is moved to E2
:
worksheet.write_comment('C3', 'Hello', {'start_cell': 'E2'})
start_row
: This option is used to set the row in which the comment will
appear. See the start_cell
option above. The row is zero indexed:
worksheet.write_comment('C3', 'Hello', {'start_row': 0})
start_col
: This option is used to set the column in which the comment
will appear. See the start_cell
option above. The column is zero
indexed:
worksheet.write_comment('C3', 'Hello', {'start_col': 4})
x_offset
: This option is used to change the x offset, in pixels, of a
comment within a cell:
worksheet.write_comment('C3', comment, {'x_offset': 30})
y_offset
: This option is used to change the y offset, in pixels, of a
comment within a cell:
worksheet.write_comment('C3', comment, {'y_offset': 30})
You can apply as many of these options as you require. For a working example of these options in use see Example: Adding Cell Comments to Worksheets (Advanced).
Note
Excel only displays offset cell comments when they are displayed as
visible
. Excel does not display hidden cells as displaced
when you mouse over them. Please note this when using options that adjust
the position of the cell comment such as start_cell
, start_row
,
start_col
, x_offset
and y_offset
.
Note
Row height and comments. If you specify the height of a row that
contains a comment then XlsxWriter will adjust the height of the comment
to maintain the default or user specified dimensions. However, the height
of a row can also be adjusted automatically by Excel if the text wrap
property is set or large fonts are used in the cell. This means that
the height of the row is unknown to the module at run time and thus
the comment box is stretched with the row. Use the set_row()
method
to specify the row height explicitly and avoid this problem. See example 8
of Example: Adding Cell Comments to Worksheets (Advanced).
Excel allows you to group rows or columns so that they can be hidden or displayed with a single mouse click. This feature is referred to as outlines and grouping.
Outlines can reduce complex data down to a few salient sub-totals or summaries. For example the following is a worksheet with three outlines.
Rows 2 to 11 are grouped at level 1 and rows 2 to 5 and 7 to 10 are grouped at level 2. The lines at the left hand side are called “outline level” bars and the level is shown by the small numeral above the outline.
Clicking the minus sign on each of the level 2 outlines will collapse and hide the data as shown below.
The minus sign changes to a plus sign to indicate that the data in the outline is hidden. This shows the usefulness of outlines: with 2 mouse clicks we have reduced the amount of visual data down to 2 sub-totals and a master total.
Finally, clicking on the minus sign on the level 1 outline will collapse the remaining rows as follows:
Grouping in XlsxWriter
is achieved by setting the outline level via the
set_row()
and set_column()
worksheet methods:
worksheet.set_row(row, height, cell_format, options)
worksheet.set_column(first_col, last_col, width, cell_format, options)
Adjacent row or columns with the same outline level are grouped together into a single outline.
The 'options'
parameter is a dictionary with the following possible keys:
'hidden'
'level'
'collapsed'
Options can be set as follows:
worksheet.set_row(0, 20, cell_format, {'hidden': True})
# Or use defaults for other properties and set the options only.
worksheet.set_row(0, None, None, {'hidden': True})
The following example sets an outline level of 1 for rows 1 to 4
(zero-indexed) and columns B to G. The parameters height
and
cell_format
are assigned default values:
worksheet.set_row(1, None, None, {'level': 1})
worksheet.set_row(2, None, None, {'level': 1})
worksheet.set_row(3, None, None, {'level': 1})
worksheet.set_row(4, None, None, {'level': 1})
worksheet.set_column('B:G', None, None, {'level': 1})
Rows and columns can be collapsed by setting the hidden
flag for the hidden
rows/columns and setting the collapsed
flag for the row/column that has
the collapsed '+'
symbol:
worksheet.set_row(1, None, None, {'level': 1, 'hidden': True})
worksheet.set_row(2, None, None, {'level': 1, 'hidden': True})
worksheet.set_row(3, None, None, {'level': 1, 'hidden': True})
worksheet.set_row(4, None, None, {'level': 1, 'hidden': True})
worksheet.set_row(5, None, None, {'collapsed': True})
worksheet.set_column('B:G', None, None, {'level': 1, 'hidden': True})
worksheet.set_column('H:H', None, None, {'collapsed': True})
Excel allows up to 7 outline levels. Therefore the level
parameter should
be in the range 0 <= level <= 7
.
For a more complete examples see Example: Outline and Grouping and Example: Collapsed Outline and Grouping.
Some additional outline properties can be set via the outline_settings()
worksheet method.
By default XlsxWriter holds all cell data in memory. This is to allow future features when formatting is applied separately from the data.
The effect of this is that XlsxWriter can consume a lot of memory and it is possible to run out of memory when creating large files.
Fortunately, this memory usage can be reduced almost completely by using the
Workbook()
'constant_memory'
property:
workbook = xlsxwriter.Workbook(filename, {'constant_memory': True})
The optimization works by flushing each row after a subsequent row is written. In this way the largest amount of data held in memory for a worksheet is the amount of data required to hold a single row of data.
Since each new row flushes the previous row, data must be written in sequential
row order when 'constant_memory'
mode is on:
# Ok. With 'constant_memory' you must write data in row by column order.
for row in range(0, row_max):
for col in range(0, col_max):
worksheet.write(row, col, some_data)
# Not ok. With 'constant_memory' this will only write the first column of data.
for col in range(0, col_max):
for row in range(0, row_max):
worksheet.write(row, col, some_data)
Another optimization that is used to reduce memory usage is that cell strings aren’t stored in an Excel structure call “shared strings” and instead are written “in-line”. This is a documented Excel feature that is supported by most spreadsheet applications.
The trade-off when using 'constant_memory'
mode is that you won’t be able
to take advantage of any new features that manipulate cell data after it is
written. Currently the add_table()
method doesn’t work in this mode
and merge_range()
and set_row()
only work for the current row.
The performance figures below show execution time and memory usage for
worksheets of size N
rows x 50 columns with a 50/50 mixture of strings and
numbers. The figures are taken from an arbitrary, mid-range, machine. Specific
figures will vary from machine to machine but the trends should be the same.
XlsxWriter in normal operation mode: the execution time and memory usage increase more of less linearly with the number of rows:
| Rows | Columns | Time (s) | Memory (bytes) |
|---|---|---|---|
| 200 | 50 | 0.43 | 2346728 |
| 400 | 50 | 0.84 | 4670904 |
| 800 | 50 | 1.68 | 8325928 |
| 1600 | 50 | 3.39 | 17855192 |
| 3200 | 50 | 6.82 | 32279672 |
| 6400 | 50 | 13.66 | 64862232 |
| 12800 | 50 | 27.60 | 128851880 |
XlsxWriter in constant_memory
mode: the execution time still increases
linearly with the number of rows but the memory usage remains small and
constant:
| Rows | Columns | Time (s) | Memory (bytes) |
|---|---|---|---|
| 200 | 50 | 0.37 | 62208 |
| 400 | 50 | 0.74 | 62208 |
| 800 | 50 | 1.46 | 62208 |
| 1600 | 50 | 2.93 | 62208 |
| 3200 | 50 | 5.90 | 62208 |
| 6400 | 50 | 11.84 | 62208 |
| 12800 | 50 | 23.63 | 62208 |
In constant_memory
mode the performance should be approximately the same
as normal mode.
These figures were generated using programs in the dev/performance
directory of the XlsxWriter repo.
This section explains how to add a VBA file containing functions or macros to an XlsxWriter file.
An Excel xlsm
file is exactly the same as an xlsx
file except that is
contains an additional vbaProject.bin
file which contains functions and/or
macros. Excel uses a different extension to differentiate between the two file
formats since files containing macros are usually subject to additional
security checks.
The vbaProject.bin
file is a binary OLE COM container. This was the format
used in older xls
versions of Excel prior to Excel 2007. Unlike all of the
other components of an xlsx/xlsm file the data isn’t stored in XML
format. Instead the functions and macros as stored as a pre-parsed binary
format. As such it wouldn’t be feasible to define macros and create a
vbaProject.bin
file from scratch (at least not in the remaining lifespan
and interest levels of the author).
Instead a workaround is used to extract vbaProject.bin
files from existing
xlsm files and then add these to XlsxWriter files.
The vba_extract.py
utility is used to extract the vbaProject.bin
binary
from an Excel 2007+ xlsm file. The utility is included in the XlsxWriter
examples directory and is also installed as a standalone executable file:
$ vba_extract.py macro_file.xlsm
Extracted: vbaProject.bin
Once the vbaProject.bin
file has been extracted it can be added to the
XlsxWriter workbook using the add_vba_project()
method:
workbook.add_vba_project('./vbaProject.bin')
If the VBA file contains functions you can then refer to them in calculations
using write_formula()
:
worksheet.write_formula('A1', '=MyMortgageCalc(200000, 25)')
Excel files that contain functions and macros should use an xlsm
extension
or else Excel will complain and possibly not open the file:
workbook = xlsxwriter.Workbook('macros.xlsm')
It is also possible to assign a macro to a button that is inserted into a
worksheet using the insert_button()
method:
import xlsxwriter
# Note the file extension should be .xlsm.
workbook = xlsxwriter.Workbook('macros.xlsm')
worksheet = workbook.add_worksheet()
worksheet.set_column('A:A', 30)
# Add the VBA project binary.
workbook.add_vba_project('./vbaProject.bin')
# Show text for the end user.
worksheet.write('A3', 'Press the button to say hello.')
# Add a button tied to a macro in the VBA project.
worksheet.insert_button('B3', {'macro': 'say_hello',
'caption': 'Press Me',
'width': 80,
'height': 30})
workbook.close()
It may be necessary to specify a more explicit macro name prefixed by the workbook VBA name as follows:
worksheet.insert_button('B3', {'macro': 'ThisWorkbook.say_hello'})
See Example: Adding a VBA macro to a Workbook from the examples directory for a working example.
Note
Button is the only VBA Control supported by Xlsxwriter. Due to the large effort in implementation (1+ man months) it is unlikely that any other form elements will be added in the future.
VBA macros generally refer to workbook and worksheet objects. If the VBA
codenames aren’t specified then XlsxWriter will use the Excel defaults of
ThisWorkbook
and Sheet1
, Sheet2
etc.
If the macro uses other codenames you can set them using the workbook and
worksheet set_vba_name()
methods as follows:
# Note: set codename for workbook and any worksheets.
workbook.set_vba_name('MyWorkbook')
worksheet1.set_vba_name('MySheet1')
worksheet2.set_vba_name('MySheet2')
You can find the names that are used in the VBA editor or by unzipping the
xlsm
file and grepping the files. The following shows how to do that using
libxml’s xmllint to format the XML for
clarity:
$ unzip myfile.xlsm -d myfile
$ xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName"
<workbookPr codeName="MyWorkbook" defaultThemeVersion="124226"/>
<sheetPr codeName="MySheet"/>
Note
This step is particularly important for macros created with non-English versions of Excel.
The XlsxWriter test suite contains several tests to ensure that this feature works and there is a working example as shown above. However, there is no guarantee that it will work in all cases. Some effort may be required and some knowledge of VBA will certainly help. If things don’t work out here are some things to try:
Start with a simple macro file, ensure that it works and then add complexity.
Check the code names that macros use to refer to the workbook and
worksheets (see the previous section above). In general VBA uses a code
name of ThisWorkbook
to refer to the current workbook and the sheet
name (such as Sheet1
) to refer to the worksheets. These are the
defaults used by XlsxWriter. If the macro uses other names, or the macro
was extracted from an non-English language version of Excel, then you can
specify these using the workbook and worksheet set_vba_name()
methods:
# Note: set codename for workbook and any worksheets.
workbook.set_vba_name('MyWorkbook')
worksheet1.set_vba_name('MySheet1')
worksheet2.set_vba_name('MySheet2')
Try to extract the macros from an Excel 2007 file. The method should work with macros from later versions (it was also tested with Excel 2010 macros). However there may be features in the macro files of more recent version of Excel that aren’t backward compatible.
Python 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.
Pandas writes Excel files using the Xlwt module for xls files and the Openpyxl or XlsxWriter modules for xlsx files.
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:
See the full example at Example: Pandas Excel example.
In order to apply XlsxWriter features such as Charts, Conditional Formatting and Column Formatting to the Pandas output we need to access the underlying workbook and worksheet objects. After that we can treat them as normal XlsxWriter objects.
Continuing on from the above example we do that as follows:
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')
# Get the xlsxwriter objects from the dataframe writer object.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
This is equivalent to the following code when using XlsxWriter on its own:
workbook = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet()
The Workbook and Worksheet objects can then be used to access other XlsxWriter features, see below.
Once we have the Workbook and Worksheet objects, as shown in the previous section, we we can use them to apply other features such as adding a chart:
# Get the xlsxwriter objects from the dataframe writer object.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Create a chart object.
chart = workbook.add_chart({'type': 'column'})
# Configure the series of the chart from the dataframe data.
chart.add_series({'values': '=Sheet1!$B$2:$B$8'})
# Insert the chart into the worksheet.
worksheet.insert_chart('D2', chart)
The output would look like this:
See the full example at Example: Pandas Excel output with a chart.
Note
The above example uses a fixed string =Sheet1!$B$2:$B$8
for the data
range. It is also possible to use a (row, col)
range which can be
varied based on the length of the dataframe. See for example
Example: Pandas Excel output with a line chart and Working with Cell Notation.
Another option is to apply a conditional format like this:
# Apply a conditional format to the cell range.
worksheet.conditional_format('B2:B8', {'type': '3_color_scale'})
Which would give:
See the full example at Example: Pandas Excel output with conditional formatting.
XlsxWriter and Pandas provide very little support for formatting the output data from a dataframe apart from default formatting such as the header and index cells and any cells that contain dates or datetimes. In addition it isn’t possible to format any cells that already have a default format applied.
If you require very controlled formatting of the dataframe output then you would probably be better off using Xlsxwriter directly with raw data taken from Pandas. However, some formatting options are available.
For example it is possible to set the default date and datetime formats via the Pandas interface:
writer = pd.ExcelWriter("pandas_datetime.xlsx",
engine='xlsxwriter',
datetime_format='mmm d yyyy hh:mm:ss',
date_format='mmmm dd yyyy')
Which would give:
See the full example at Example: Pandas Excel output with datetimes.
It is possible to format any other, non date/datetime column data using
set_column()
:
# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})
# Set the column width and format.
worksheet.set_column('B:B', 18, format1)
# Set the format but not the column width.
worksheet.set_column('C:C', None, format2)
Note: This feature requires Pandas >= 0.16.
See the full example at Example: Pandas Excel output with column formatting.
Pandas writes the dataframe header with a default cell format. Since it is a
cell format it cannot be overridden using set_row()
. If you wish to
use your own format for the headings then the best approach is to turn off the
automatic header from Pandas and write your own. For example:
# Turn off the default header and skip one row to allow us to insert a
# user defined header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)
# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Add a header format.
header_format = workbook.add_format({
'bold': True,
'text_wrap': True,
'valign': 'top',
'fg_color': '#D7E4BC',
'border': 1})
# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
worksheet.write(0, col_num + 1, value, header_format)
See the full example at Example: Pandas Excel output with user defined header format.
It is possible to write more than one dataframe to a worksheet or to several worksheets. For example to write multiple dataframes to multiple worksheets:
# Write each dataframe to a different worksheet.
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
df3.to_excel(writer, sheet_name='Sheet3')
See the full example at Example: Pandas Excel with multiple dataframes.
It is also possible to position multiple dataframes within the same worksheet:
# Position the dataframes in the worksheet.
df1.to_excel(writer, sheet_name='Sheet1') # Default position, cell A1.
df2.to_excel(writer, sheet_name='Sheet1', startcol=3)
df3.to_excel(writer, sheet_name='Sheet1', startrow=6)
# Write the dataframe without the header and index.
df4.to_excel(writer, sheet_name='Sheet1',
startrow=7, startcol=4, header=False, index=False)
See the full example at Example: Pandas Excel dataframe positioning.
XlsxWriter supports several Workbook()
constructor options such as
strings_to_urls()
. These can also be applied to the Workbook
object
created by Pandas as follows:
writer = pd.ExcelWriter('pandas_example.xlsx',
engine='xlsxwriter',
options={'strings_to_urls': False})
It is also possible to write the Pandas XlsxWriter DataFrame output to a byte array:
import pandas as pd
import io
# Create a Pandas dataframe from the data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
output = io.BytesIO()
# Use the BytesIO object as the filehandle.
writer = pd.ExcelWriter(output, engine='xlsxwriter')
# Write the data frame to the BytesIO object.
df.to_excel(writer, sheet_name='Sheet1')
writer.save()
xlsx_data = output.getvalue()
# Do something with the data...
Note: This feature requires Pandas >= 0.17.
Here are some additional resources in relation to Pandas, Excel and XlsxWriter.
The following are some of the examples included in the examples directory of the XlsxWriter distribution.
The simplest possible spreadsheet. This is a good place to start to see if the XlsxWriter module is installed correctly.
##############################################################################
#
# A hello world spreadsheet using the XlsxWriter Python module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('hello_world.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()
This program is an example of writing some of the features of the XlsxWriter module.
##############################################################################
#
# A simple example of some of the features of the XlsxWriter Python module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()
# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)
# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})
# Write some simple text.
worksheet.write('A1', 'Hello')
# Text with formatting.
worksheet.write('A2', 'World', bold)
# Write some numbers, with row/column notation.
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)
# Insert an image.
worksheet.insert_image('B5', 'logo.png')
workbook.close()
Notes:
write()
method.A simple program demonstrating a check for exceptions when closing the file.
We try to close()
the file in a loop so that if there is an exception,
such as if the file is open or locked, we can ask the user to close the file,
after which we can try again to overwrite it.
##############################################################################
#
# A simple program demonstrating a check for exceptions when closing the file.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('check_close.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
# Try to close() the file in a loop so that if there is an exception, such as
# if the file is open in Excel, we can ask the user to close the file, and
# try again to overwrite 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
This program is an example of writing some of the features of the XlsxWriter module. See the Working with Dates and Time section for more details on this example.
##############################################################################
#
# A simple program to write some dates and times to an Excel file
# using the XlsxWriter Python module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
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. In the output file compare how changing
# the format codes change the appearance of the date.
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()
This program is an example of writing hyperlinks to a worksheet. See the
write_url()
method for more details.
###############################################################################
#
# Example of how to use the XlsxWriter module to write hyperlinks
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Create a new workbook and add a worksheet
workbook = xlsxwriter.Workbook('hyperlink.xlsx')
worksheet = workbook.add_worksheet('Hyperlinks')
# Format the first column
worksheet.set_column('A:A', 30)
# Add a sample alternative link format.
red_format = workbook.add_format({
'font_color': 'red',
'bold': 1,
'underline': 1,
'font_size': 12,
})
# Write some hyperlinks
worksheet.write_url('A1', 'http://www.python.org/') # Implicit format.
worksheet.write_url('A3', 'http://www.python.org/', string='Python Home')
worksheet.write_url('A5', 'http://www.python.org/', tip='Click here')
worksheet.write_url('A7', 'http://www.python.org/', red_format)
worksheet.write_url('A9'<EMAIL_ADDRESS>string='Mail me')
# Write a URL that isn't a hyperlink
worksheet.write_string('A11', 'http://www.python.org/')
workbook.close()
This program is an example of writing array formulas with one or more return
values. See the write_array_formula()
method for more details.
#######################################################################
#
# Example of how to use Python and the XlsxWriter module to write
# simple array formulas.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Create a new workbook and add a worksheet
workbook = xlsxwriter.Workbook('array_formula.xlsx')
worksheet = workbook.add_worksheet()
# Write some test data.
worksheet.write('B1', 500)
worksheet.write('B2', 10)
worksheet.write('B5', 1)
worksheet.write('B6', 2)
worksheet.write('B7', 3)
worksheet.write('C1', 300)
worksheet.write('C2', 15)
worksheet.write('C5', 20234)
worksheet.write('C6', 21003)
worksheet.write('C7', 10000)
# Write an array formula that returns a single value
worksheet.write_formula('A1', '{=SUM(B1:C1*B2:C2)}')
# Same as above but more verbose.
worksheet.write_array_formula('A2:A2', '{=SUM(B1:C1*B2:C2)}')
# Write an array formula that returns a range of values
worksheet.write_array_formula('A5:A7', '{=TREND(C5:C7,B5:B7)}')
workbook.close()
This program is an example of using autofilters in a worksheet. See Working with Autofilters for more details.
###############################################################################
#
# An example of how to create autofilters with XlsxWriter.
#
# An autofilter is a way of adding drop down lists to the headers of a 2D
# range of worksheet data. This allows users to filter the data based on
# simple criteria so that some data is shown and some is hidden.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('autofilter.xlsx')
# Add a worksheet for each autofilter example.
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet4 = workbook.add_worksheet()
worksheet5 = workbook.add_worksheet()
worksheet6 = workbook.add_worksheet()
# Add a bold format for the headers.
bold = workbook.add_format({'bold': 1})
# Open a text file with autofilter example data.
textfile = open('autofilter_data.txt')
# Read the headers from the first line of the input file.
headers = textfile.readline().strip("\n").split()
# Read the text file and store the field data.
data = []
for line in textfile:
# Split the input data based on whitespace.
row_data = line.strip("\n").split()
# Convert the number data from the text file.
for i, item in enumerate(row_data):
try:
row_data[i] = float(item)
except ValueError:
pass
data.append(row_data)
# Set up several sheets with the same data.
for worksheet in (workbook.worksheets()):
# Make the columns wider.
worksheet.set_column('A:D', 12)
# Make the header row larger.
worksheet.set_row(0, 20, bold)
# Make the headers bold.
worksheet.write_row('A1', headers)
###############################################################################
#
# Example 1. Autofilter without conditions.
#
# Set the autofilter.
worksheet1.autofilter('A1:D51')
row = 1
for row_data in (data):
worksheet1.write_row(row, 0, row_data)
# Move on to the next worksheet row.
row += 1
###############################################################################
#
#
# Example 2. Autofilter with a filter condition in the first column.
#
# Autofilter range using Row-Column notation.
worksheet2.autofilter(0, 0, 50, 3)
# Add filter criteria. The placeholder "Region" in the filter is
# ignored and can be any string that adds clarity to the expression.
worksheet2.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, no further action required.
pass
else:
# We need to hide rows that don't match the filter.
worksheet2.set_row(row, options={'hidden': True})
worksheet2.write_row(row, 0, row_data)
# Move on to the next worksheet row.
row += 1
###############################################################################
#
#
# Example 3. Autofilter with a dual filter condition in one of the columns.
#
# Set the autofilter.
worksheet3.autofilter('A1:D51')
# Add filter criteria.
worksheet3.filter_column('A', 'x == East or x == South')
# 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' or region == 'South':
# Row matches the filter, no further action required.
pass
else:
# We need to hide rows that don't match the filter.
worksheet3.set_row(row, options={'hidden': True})
worksheet3.write_row(row, 0, row_data)
# Move on to the next worksheet row.
row += 1
###############################################################################
#
#
# Example 4. Autofilter with filter conditions in two columns.
#
# Set the autofilter.
worksheet4.autofilter('A1:D51')
# Add filter criteria.
worksheet4.filter_column('A', 'x == East')
worksheet4.filter_column('C', 'x > 3000 and x < 8000')
# Hide the rows that don't match the filter criteria.
row = 1
for row_data in (data):
region = row_data[0]
volume = int(row_data[2])
# Check for rows that match the filter.
if region == 'East' and volume > 3000 and volume < 8000:
# Row matches the filter, no further action required.
pass
else:
# We need to hide rows that don't match the filter.
worksheet4.set_row(row, options={'hidden': True})
worksheet4.write_row(row, 0, row_data)
# Move on to the next worksheet row.
row += 1
###############################################################################
#
#
# Example 5. Autofilter with filter for blanks.
#
# Create a blank cell in our test data.
# Set the autofilter.
worksheet5.autofilter('A1:D51')
# Add filter criteria.
worksheet5.filter_column('A', 'x == Blanks')
# Simulate a blank cell in the data.
data[5][0] = ''
# 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 == '':
# Row matches the filter, no further action required.
pass
else:
# We need to hide rows that don't match the filter.
worksheet5.set_row(row, options={'hidden': True})
worksheet5.write_row(row, 0, row_data)
# Move on to the next worksheet row.
row += 1
###############################################################################
#
#
# Example 6. Autofilter with filter for non-blanks.
#
# Set the autofilter.
worksheet6.autofilter('A1:D51')
# Add filter criteria.
worksheet6.filter_column('A', 'x == NonBlanks')
# 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 != '':
# Row matches the filter, no further action required.
pass
else:
# We need to hide rows that don't match the filter.
worksheet6.set_row(row, options={'hidden': True})
worksheet6.write_row(row, 0, row_data)
# Move on to the next worksheet row.
row += 1
workbook.close()
Example of how to add data validation and drop down lists to an XlsxWriter file. Data validation is a way of limiting user input to certain ranges or to allow a selection from a drop down list.
###############################################################################
#
# Example of how to add data validation and dropdown lists to an
# XlsxWriter file.
#
# Data validation is a feature of Excel which allows you to restrict
# the data that a user enters in a cell and to display help and
# warning messages. It also allows you to restrict input to values in
# a drop down list.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
from datetime import date, time
import xlsxwriter
workbook = xlsxwriter.Workbook('data_validate.xlsx')
worksheet = workbook.add_worksheet()
# Add a format for the header cells.
header_format = workbook.add_format({
'border': 1,
'bg_color': '#C6EFCE',
'bold': True,
'text_wrap': True,
'valign': 'vcenter',
'indent': 1,
})
# Set up layout of the worksheet.
worksheet.set_column('A:A', 68)
worksheet.set_column('B:B', 15)
worksheet.set_column('D:D', 15)
worksheet.set_row(0, 36)
# Write the header cells and some data that will be used in the examples.
heading1 = 'Some examples of data validation in XlsxWriter'
heading2 = 'Enter values in this column'
heading3 = 'Sample Data'
worksheet.write('A1', heading1, header_format)
worksheet.write('B1', heading2, header_format)
worksheet.write('D1', heading3, header_format)
worksheet.write_row('D3', ['Integers', 1, 10])
worksheet.write_row('D4', ['List data', 'open', 'high', 'close'])
worksheet.write_row('D5', ['Formula', '=AND(F5=50,G5=60)', 50, 60])
# Example 1. Limiting input to an integer in a fixed range.
#
txt = 'Enter an integer between 1 and 10'
worksheet.write('A3', txt)
worksheet.data_validation('B3', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 10})
# Example 2. Limiting input to an integer outside a fixed range.
#
txt = 'Enter an integer that is not between 1 and 10 (using cell references)'
worksheet.write('A5', txt)
worksheet.data_validation('B5', {'validate': 'integer',
'criteria': 'not between',
'minimum': '=E3',
'maximum': '=F3'})
# Example 3. Limiting input to an integer greater than a fixed value.
#
txt = 'Enter an integer greater than 0'
worksheet.write('A7', txt)
worksheet.data_validation('B7', {'validate': 'integer',
'criteria': '>',
'value': 0})
# Example 4. Limiting input to an integer less than a fixed value.
#
txt = 'Enter an integer less than 10'
worksheet.write('A9', txt)
worksheet.data_validation('B9', {'validate': 'integer',
'criteria': '<',
'value': 10})
# Example 5. Limiting input to a decimal in a fixed range.
#
txt = 'Enter a decimal between 0.1 and 0.5'
worksheet.write('A11', txt)
worksheet.data_validation('B11', {'validate': 'decimal',
'criteria': 'between',
'minimum': 0.1,
'maximum': 0.5})
# Example 6. Limiting input to a value in a dropdown list.
#
txt = 'Select a value from a drop down list'
worksheet.write('A13', txt)
worksheet.data_validation('B13', {'validate': 'list',
'source': ['open', 'high', 'close']})
# Example 7. Limiting input to a value in a dropdown list.
#
txt = 'Select a value from a drop down list (using a cell range)'
worksheet.write('A15', txt)
worksheet.data_validation('B15', {'validate': 'list',
'source': '=$E$4:$G$4'})
# Example 8. Limiting input to a date in a fixed range.
#
txt = 'Enter a date between 1/1/2013 and 12/12/2013'
worksheet.write('A17', txt)
worksheet.data_validation('B17', {'validate': 'date',
'criteria': 'between',
'minimum': date(2013, 1, 1),
'maximum': date(2013, 12, 12)})
# Example 9. Limiting input to a time in a fixed range.
#
txt = 'Enter a time between 6:00 and 12:00'
worksheet.write('A19', txt)
worksheet.data_validation('B19', {'validate': 'time',
'criteria': 'between',
'minimum': time(6, 0),
'maximum': time(12, 0)})
# Example 10. Limiting input to a string greater than a fixed length.
#
txt = 'Enter a string longer than 3 characters'
worksheet.write('A21', txt)
worksheet.data_validation('B21', {'validate': 'length',
'criteria': '>',
'value': 3})
# Example 11. Limiting input based on a formula.
#
txt = 'Enter a value if the following is true "=AND(F5=50,G5=60)"'
worksheet.write('A23', txt)
worksheet.data_validation('B23', {'validate': 'custom',
'value': '=AND(F5=50,G5=60)'})
# Example 12. Displaying and modifying data validation messages.
#
txt = 'Displays a message when you select the cell'
worksheet.write('A25', txt)
worksheet.data_validation('B25', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 100,
'input_title': 'Enter an integer:',
'input_message': 'between 1 and 100'})
# Example 13. Displaying and modifying data validation messages.
#
txt = "Display a custom error message when integer isn't between 1 and 100"
worksheet.write('A27', txt)
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 is not valid!',
'error_message':
'It should be an integer between 1 and 100'})
# Example 14. Displaying and modifying data validation messages.
#
txt = "Display a custom info message when integer isn't between 1 and 100"
worksheet.write('A29', txt)
worksheet.data_validation('B29', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 100,
'input_title': 'Enter an integer:',
'input_message': 'between 1 and 100',
'error_title': 'Input value is not valid!',
'error_message':
'It should be an integer between 1 and 100',
'error_type': 'information'})
workbook.close()
Example of how to add conditional formatting to an XlsxWriter file. Conditional formatting allows you to apply a format to a cell or a range of cells based on certain criteria.
###############################################################################
#
# Example of how to add conditional formatting to an XlsxWriter file.
#
# Conditional formatting allows you to apply a format to a cell or a
# range of cells based on certain criteria.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('conditional_format.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet4 = workbook.add_worksheet()
worksheet5 = workbook.add_worksheet()
worksheet6 = workbook.add_worksheet()
worksheet7 = workbook.add_worksheet()
worksheet8 = workbook.add_worksheet()
worksheet9 = workbook.add_worksheet()
# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
'font_color': '#9C0006'})
# Add a format. Green fill with dark green text.
format2 = workbook.add_format({'bg_color': '#C6EFCE',
'font_color': '#006100'})
# Some sample data to run the conditional formatting against.
data = [
[34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
[6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
[28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
[27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
[88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
[24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
[6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
[52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
[60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
[70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
]
###############################################################################
#
# Example 1.
#
caption = ('Cells with values >= 50 are in light red. '
'Values < 50 are in light green.')
# Write the data.
worksheet1.write('A1', caption)
for row, row_data in enumerate(data):
worksheet1.write_row(row + 2, 1, row_data)
# Write a conditional format over a range.
worksheet1.conditional_format('B3:K12', {'type': 'cell',
'criteria': '>=',
'value': 50,
'format': format1})
# Write another conditional format over the same range.
worksheet1.conditional_format('B3:K12', {'type': 'cell',
'criteria': '<',
'value': 50,
'format': format2})
###############################################################################
#
# Example 2.
#
caption = ('Values between 30 and 70 are in light red. '
'Values outside that range are in light green.')
worksheet2.write('A1', caption)
for row, row_data in enumerate(data):
worksheet2.write_row(row + 2, 1, row_data)
worksheet2.conditional_format('B3:K12', {'type': 'cell',
'criteria': 'between',
'minimum': 30,
'maximum': 70,
'format': format1})
worksheet2.conditional_format('B3:K12', {'type': 'cell',
'criteria': 'not between',
'minimum': 30,
'maximum': 70,
'format': format2})
###############################################################################
#
# Example 3.
#
caption = ('Duplicate values are in light red. '
'Unique values are in light green.')
worksheet3.write('A1', caption)
for row, row_data in enumerate(data):
worksheet3.write_row(row + 2, 1, row_data)
worksheet3.conditional_format('B3:K12', {'type': 'duplicate',
'format': format1})
worksheet3.conditional_format('B3:K12', {'type': 'unique',
'format': format2})
###############################################################################
#
# Example 4.
#
caption = ('Above average values are in light red. '
'Below average values are in light green.')
worksheet4.write('A1', caption)
for row, row_data in enumerate(data):
worksheet4.write_row(row + 2, 1, row_data)
worksheet4.conditional_format('B3:K12', {'type': 'average',
'criteria': 'above',
'format': format1})
worksheet4.conditional_format('B3:K12', {'type': 'average',
'criteria': 'below',
'format': format2})
###############################################################################
#
# Example 5.
#
caption = ('Top 10 values are in light red. '
'Bottom 10 values are in light green.')
worksheet5.write('A1', caption)
for row, row_data in enumerate(data):
worksheet5.write_row(row + 2, 1, row_data)
worksheet5.conditional_format('B3:K12', {'type': 'top',
'value': '10',
'format': format1})
worksheet5.conditional_format('B3:K12', {'type': 'bottom',
'value': '10',
'format': format2})
###############################################################################
#
# Example 6.
#
caption = ('Cells with values >= 50 are in light red. '
'Values < 50 are in light green. Non-contiguous ranges.')
# Write the data.
worksheet6.write('A1', caption)
for row, row_data in enumerate(data):
worksheet6.write_row(row + 2, 1, row_data)
# Write a conditional format over a range.
worksheet6.conditional_format('B3:K6', {'type': 'cell',
'criteria': '>=',
'value': 50,
'format': format1,
'multi_range': 'B3:K6 B9:K12'})
# Write another conditional format over the same range.
worksheet6.conditional_format('B3:K6', {'type': 'cell',
'criteria': '<',
'value': 50,
'format': format2,
'multi_range': 'B3:K6 B9:K12'})
###############################################################################
#
# Example 7.
#
caption = 'Examples of color scales with default and user colors.'
data = range(1, 13)
worksheet7.write('A1', caption)
worksheet7.write('B2', "2 Color Scale")
worksheet7.write('D2', "2 Color Scale + user colors")
worksheet7.write('G2', "3 Color Scale")
worksheet7.write('I2', "3 Color Scale + user colors")
for row, row_data in enumerate(data):
worksheet7.write(row + 2, 1, row_data)
worksheet7.write(row + 2, 3, row_data)
worksheet7.write(row + 2, 6, row_data)
worksheet7.write(row + 2, 8, row_data)
worksheet7.conditional_format('B3:B14', {'type': '2_color_scale'})
worksheet7.conditional_format('D3:D14', {'type': '2_color_scale',
'min_color': "#FF0000",
'max_color': "#00FF00"})
worksheet7.conditional_format('G3:G14', {'type': '3_color_scale'})
worksheet7.conditional_format('I3:I14', {'type': '3_color_scale',
'min_color': "#C5D9F1",
'mid_color': "#8DB4E3",
'max_color': "#538ED5"})
###############################################################################
#
# Example 8.
#
caption = 'Examples of data bars.'
worksheet8.write('A1', caption)
worksheet8.write('B2', "Default data bars")
worksheet8.write('D2', "Bars only")
worksheet8.write('F2', "With user color")
worksheet8.write('H2', "Solid bars")
worksheet8.write('J2', "Right to left")
worksheet8.write('L2', "Excel 2010 style")
worksheet8.write('N2', "Negative same as positive")
data = range(1, 13)
for row, row_data in enumerate(data):
worksheet8.write(row + 2, 1, row_data)
worksheet8.write(row + 2, 3, row_data)
worksheet8.write(row + 2, 5, row_data)
worksheet8.write(row + 2, 7, row_data)
worksheet8.write(row + 2, 9, row_data)
data = [-1, -2, -3, -2, -1, 0, 1, 2, 3, 2, 1, 0]
for row, row_data in enumerate(data):
worksheet8.write(row + 2, 11, row_data)
worksheet8.write(row + 2, 13, row_data)
worksheet8.conditional_format('B3:B14', {'type': 'data_bar'})
worksheet8.conditional_format('D3:D14', {'type': 'data_bar',
'bar_only': True})
worksheet8.conditional_format('F3:F14', {'type': 'data_bar',
'bar_color': '#63C384'})
worksheet8.conditional_format('H3:H14', {'type': 'data_bar',
'bar_solid': True})
worksheet8.conditional_format('J3:J14', {'type': 'data_bar',
'bar_direction': 'right'})
worksheet8.conditional_format('L3:L14', {'type': 'data_bar',
'data_bar_2010': True})
worksheet8.conditional_format('M3:N14', {'type': 'data_bar',
'bar_negative_color_same': True,
'bar_negative_border_color_same': True})
###############################################################################
#
# Example 9.
#
caption = 'Examples of conditional formats with icon sets.'
data = [
[1, 2, 3],
[1, 2, 3],
[1, 2, 3],
[1, 2, 3],
[1, 2, 3, 4],
[1, 2, 3, 4, 5],
[1, 2, 3, 4, 5],
]
worksheet9.write('A1', caption)
for row, row_data in enumerate(data):
worksheet9.write_row(row + 2, 1, row_data)
worksheet9.conditional_format('B3:D3', {'type': 'icon_set',
'icon_style': '3_traffic_lights'})
worksheet9.conditional_format('B4:D4', {'type': 'icon_set',
'icon_style': '3_traffic_lights',
'reverse_icons': True})
worksheet9.conditional_format('B5:D5', {'type': 'icon_set',
'icon_style': '3_traffic_lights',
'icons_only': True})
worksheet9.conditional_format('B6:D6', {'type': 'icon_set',
'icon_style': '3_arrows'})
worksheet9.conditional_format('B7:E8', {'type': 'icon_set',
'icon_style': '4_arrows'})
worksheet9.conditional_format('B8:F8', {'type': 'icon_set',
'icon_style': '5_arrows'})
worksheet9.conditional_format('B9:F9', {'type': 'icon_set',
'icon_style': '5_ratings'})
workbook.close()
Example of how to create defined names (named ranges) with XlsxWriter.
Defined names are used to define descriptive names to represent a value, a
single cell or a range of cells in a workbook or worksheet. See
define_name()
.
##############################################################################
#
# Example of how to create defined names with the XlsxWriter Python module.
#
# This method is used to define a user friendly name to represent a value,
# a single cell or a range of cells in a workbook.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('defined_name.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
# Define some global/workbook names.
workbook.define_name('Exchange_rate', '=0.96')
workbook.define_name('Sales', '=Sheet1!$G$1:$H$10')
# Define a local/worksheet name. Over-rides the "Sales" name above.
workbook.define_name('Sheet2!Sales', '=Sheet2!$G$1:$G$10')
# Write some text in the file and one of the defined names in a formula.
for worksheet in workbook.worksheets():
worksheet.set_column('A:A', 45)
worksheet.write('A1', 'This worksheet contains some defined names.')
worksheet.write('A2', 'See Formulas -> Name Manager above.')
worksheet.write('A3', 'Example formula in cell B3 ->')
worksheet.write('B3', '=Exchange_rate')
workbook.close()
This program is an example of merging cells in a worksheet. See the
merge_range()
method for more details.
##############################################################################
#
# A simple example of merging cells with the XlsxWriter Python module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('merge1.xlsx')
worksheet = workbook.add_worksheet()
# Increase the cell size of the merged cells to highlight the formatting.
worksheet.set_column('B:D', 12)
worksheet.set_row(3, 30)
worksheet.set_row(6, 30)
worksheet.set_row(7, 30)
# Create a format to use in the merged range.
merge_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'yellow'})
# Merge 3 cells.
worksheet.merge_range('B4:D4', 'Merged Range', merge_format)
# Merge 3 cells over two rows.
worksheet.merge_range('B7:D8', 'Merged Range', merge_format)
workbook.close()
This program is an example of writing rich stings with multiple format to a
cell in a worksheet. See the write_rich_string()
method for more details.
#######################################################################
#
# An example of using Python and XlsxWriter to write some "rich strings",
# i.e., strings with multiple formats.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('rich_strings.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_column('A:A', 30)
# Set up some formats to use.
bold = workbook.add_format({'bold': True})
italic = workbook.add_format({'italic': True})
red = workbook.add_format({'color': 'red'})
blue = workbook.add_format({'color': 'blue'})
center = workbook.add_format({'align': 'center'})
superscript = workbook.add_format({'font_script': 1})
# Write some strings with multiple formats.
worksheet.write_rich_string('A1',
'This is ',
bold, 'bold',
' and this is ',
italic, 'italic')
worksheet.write_rich_string('A3',
'This is ',
red, 'red',
' and this is ',
blue, 'blue')
worksheet.write_rich_string('A5',
'Some ',
bold, 'bold text',
' centered',
center)
worksheet.write_rich_string('A7',
italic,
'j = k',
superscript, '(n-1)',
center)
# If you have formats and segments in a list you can add them like this:
segments = ['This is ', bold, 'bold', ' and this is ', blue, 'blue']
worksheet.write_rich_string('A9', *segments)
workbook.close()
This program is an example of merging cells that contain a rich string.
Using the standard XlsxWriter API we can only write simple types to merged ranges so we first write a blank string to the merged range. We then overwrite the first merged cell with a rich string.
Note that we must also pass the cell format used in the merged cells format at the end
See the merge_range()
and write_rich_string()
methods for more
details.
##############################################################################
#
# An example of merging cells which contain a rich string using the
# XlsxWriter Python module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('merge_rich_string.xlsx')
worksheet = workbook.add_worksheet()
# Set up some formats to use.
red = workbook.add_format({'color': 'red'})
blue = workbook.add_format({'color': 'blue'})
cell_format = workbook.add_format({'align': 'center',
'valign': 'vcenter',
'border': 1})
# We can only write simple types to merged ranges so we write a blank string.
worksheet.merge_range('B2:E5', "", cell_format)
# We then overwrite the first merged cell with a rich string. Note that we
# must also pass the cell format used in the merged cells format at the end.
worksheet.write_rich_string('B2',
'This is ',
red, 'red',
' and this is ',
blue, 'blue',
cell_format)
workbook.close()
This program is an example of inserting images into a worksheet. See the
insert_image()
method for more details.
##############################################################################
#
# An example of inserting images into a worksheet using the XlsxWriter
# Python module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('images.xlsx')
worksheet = workbook.add_worksheet()
# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 30)
# Insert an image.
worksheet.write('A2', 'Insert an image in a cell:')
worksheet.insert_image('B2', 'python.png')
# Insert an image offset in the cell.
worksheet.write('A12', 'Insert an image with an offset:')
worksheet.insert_image('B12', 'python.png', {'x_offset': 15, 'y_offset': 10})
# Insert an image with scaling.
worksheet.write('A23', 'Insert a scaled image:')
worksheet.insert_image('B23', 'python.png', {'x_scale': 0.5, 'y_scale': 0.5})
workbook.close()
This program is an example of inserting images from a Python
io.BytesIO
byte stream into a worksheet.
The example byte streams are populated from a URL and from a local file.
See the insert_image()
method for more details.
##############################################################################
#
# An example of inserting images from a Python BytesIO byte stream into a
# worksheet using the XlsxWriter module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
# Import the byte stream handler.
from io import BytesIO
# Import urlopen() for either Python 2 or 3.
try:
from urllib.request import urlopen
except ImportError:
from urllib2 import urlopen
import xlsxwriter
# Create the workbook and add a worksheet.
workbook = xlsxwriter.Workbook('images_bytesio.xlsx')
worksheet = workbook.add_worksheet()
# Read an image from a remote url.
url = 'https://raw.githubusercontent.com/jmcnamara/XlsxWriter/' + \
'master/examples/logo.png'
image_data = BytesIO(urlopen(url).read())
# Write the byte stream image to a cell. Note, the filename must be
# specified. In this case it will be read from url string.
worksheet.insert_image('B2', url, {'image_data': image_data})
# Read a local image file into a a byte stream. Note, the insert_image()
# method can do this directly. This is for illustration purposes only.
filename = 'python.png'
image_file = open(filename, 'rb')
image_data = BytesIO(image_file.read())
image_file.close()
# Write the byte stream image to a cell. The filename must be specified.
worksheet.insert_image('B8', filename, {'image_data': image_data})
workbook.close()
Example of how to use Python and the XlsxWriter module to change the default worksheet and cell text direction from left-to-right to right-to-left as required by some middle eastern versions of Excel.
Note, this a Python2 unicode version. Remove the u''
to make this work
with Python3. See also the unicode_python3.py.
#######################################################################
# _*_ coding: utf-8
#
# Example of how to use Python and the XlsxWriter module to change the default
# worksheet and cell text direction from left-to-right to right-to-left as
# required by some middle eastern versions of Excel.
#
# Note, this a Python2 unicode version. Remove the u'' to make this work with
# Python3. See also the unicode_python3.py example.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('right_to_left.xlsx')
# Add two worksheets.
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
# Add the cell formats.
format_left_to_right = workbook.add_format({'reading_order': 1})
format_right_to_left = workbook.add_format({'reading_order': 2})
# Make the columns wider for clarity.
worksheet1.set_column('A:A', 25)
worksheet2.set_column('A:A', 25)
# Change the direction for worksheet2.
worksheet2.right_to_left()
# Write some data to show the difference.
# Standard direction: | A1 | B1 | C1 | ...
worksheet1.write('A1', u'نص عربي / English text') # Default direction.
worksheet1.write('A2', u'نص عربي / English text', format_left_to_right)
worksheet1.write('A3', u'نص عربي / English text', format_right_to_left)
# Right to left direction: ... | C1 | B1 | A1 |
worksheet2.write('A1', u'نص عربي / English text') # Default direction.
worksheet2.write('A2', u'نص عربي / English text', format_left_to_right)
worksheet2.write('A3', u'نص عربي / English text', format_right_to_left)
workbook.close()
A simple Django View class to write an Excel file using the XlsxWriter module.
##############################################################################
#
# A simple Django view class to write an Excel file using the XlsxWriter
# module.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import io
from django.http import HttpResponse
from django.views.generic import View
import xlsxwriter
def get_simple_table_data():
# Simulate a more complex table read.
return [[1, 2, 3],
[4, 5, 6],
[7, 8, 9]]
class MyView(View):
def get(self, request):
# Create an in-memory output file for the new workbook.
output = io.BytesIO()
# Even though the final file will be in memory the module uses temp
# files during assembly for efficiency. To avoid this on servers that
# don't allow temp files, for example the Google APP Engine, set the
# 'in_memory' Workbook() constructor option as shown in the docs.
workbook = xlsxwriter.Workbook(output)
worksheet = workbook.add_worksheet()
# Get some data to write to the spreadsheet.
data = get_simple_table_data()
# Write some test data.
for row_num, columns in enumerate(data):
for col_num, cell_data in enumerate(columns):
worksheet.write(row_num, col_num, cell_data)
# Close the workbook before sending the data.
workbook.close()
# Rewind the buffer.
output.seek(0)
# Set up the Http response.
filename = 'django_simple.xlsx'
response = HttpResponse(
output,
content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response['Content-Disposition'] = 'attachment; filename=%s' % filename
return response
Example of using Python and XlsxWriter to create an Excel XLSX file in an in memory string suitable for serving via SimpleHTTPServer or Django or with the Google App Engine.
Even though the final file will be in memory, via the BytesIO object, the
module uses temp files during assembly for efficiency. To avoid this on
servers that don’t allow temp files, for example the Google APP Engine, set
the in_memory
constructor option to True
.
For a Python 3 example see Example: Simple HTTP Server (Python 3).
##############################################################################
#
# Example of using Python and XlsxWriter to create an Excel XLSX file in an in
# memory string suitable for serving via SimpleHTTPServer or Django or with
# the Google App Engine.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
# Note: This is a Python 2 example. For Python 3 see http_server_py3.py.
import SimpleHTTPServer
import SocketServer
import io
import xlsxwriter
class Handler(SimpleHTTPServer.SimpleHTTPRequestHandler):
def do_GET(self):
# Create an in-memory output file for the new workbook.
output = io.BytesIO()
# Even though the final file will be in memory the module uses temp
# files during assembly for efficiency. To avoid this on servers that
# don't allow temp files, for example the Google APP Engine, set the
# 'in_memory' constructor option to True:
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet()
# Write some test data.
worksheet.write(0, 0, 'Hello, world!')
# Close the workbook before streaming the data.
workbook.close()
# Rewind the buffer.
output.seek(0)
# Construct a server response.
self.send_response(200)
self.send_header('Content-Disposition', 'attachment; filename=test.xlsx')
self.send_header('Content-type',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
self.end_headers()
self.wfile.write(output.read())
return
print('Server listening on port 8000...')
httpd = SocketServer.TCPServer(('', 8000), Handler)
httpd.serve_forever()
Example of using Python and XlsxWriter to create an Excel XLSX file in an in memory string suitable for serving via SimpleHTTPRequestHandler or Django or with the Google App Engine.
Even though the final file will be in memory, via the BytesIO object, the
XlsxWriter module uses temp files during assembly for efficiency. To avoid
this on servers that don’t allow temp files set the in_memory
constructor
option to True
.
The Python 3 Runtime Environment in Google App Engine now supports a
filesystem with read/write access to /tmp
which means that the in_memory
option isn’t required. The /tmp
dir
isn’t supported in the Python 2 Runtime Environment so that option is still
required there.
For a Python 2 example see Example: Simple HTTP Server (Python 2).
##############################################################################
#
# Example of using Python and XlsxWriter to create an Excel XLSX file in an in
# memory string suitable for serving via SimpleHTTPRequestHandler or Django or
# with the Google App Engine.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
# Note: This is a Python 3 example. For Python 2 see http_server_py2.py.
import http.server
import socketserver
import io
import xlsxwriter
class Handler(http.server.SimpleHTTPRequestHandler):
def do_GET(self):
# Create an in-memory output file for the new workbook.
output = io.BytesIO()
# Even though the final file will be in memory the module uses temp
# files during assembly for efficiency. To avoid this on servers that
# don't allow temp files set the 'in_memory' constructor option to True.
#
# The Python 3 Runtime Environment in Google App Engine now supports a
# filesystem with read/write access to /tmp which means that the
# 'in_memory' option isn't required. The /tmp dir isn't supported in
# the Python 2 Runtime Environment so that option is still required
# there. See:
#
# https://cloud.google.com/appengine/docs/standard/python3/runtime#filesystem
#
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet()
# Write some test data.
worksheet.write(0, 0, 'Hello, world!')
# Close the workbook before streaming the data.
workbook.close()
# Rewind the buffer.
output.seek(0)
# Construct a server response.
self.send_response(200)
self.send_header('Content-Disposition', 'attachment; filename=test.xlsx')
self.send_header('Content-type',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
self.end_headers()
self.wfile.write(output.read())
return
print('Server listening on port 8000...')
httpd = socketserver.TCPServer(('', 8000), Handler)
httpd.serve_forever()
An example of how to create panes in a worksheet, both “freeze” panes and
“split” panes. See the freeze_panes()
and split_panes()
methods
for more details.
#######################################################################
#
# Example of using Python and the XlsxWriter module to create
# worksheet panes.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('panes.xlsx')
worksheet1 = workbook.add_worksheet('Panes 1')
worksheet2 = workbook.add_worksheet('Panes 2')
worksheet3 = workbook.add_worksheet('Panes 3')
worksheet4 = workbook.add_worksheet('Panes 4')
#######################################################################
#
# Set up some formatting and text to highlight the panes.
#
header_format = workbook.add_format({'bold': True,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
'border': 1})
center_format = workbook.add_format({'align': 'center'})
#######################################################################
#
# Example 1. Freeze pane on the top row.
#
worksheet1.freeze_panes(1, 0)
# Other sheet formatting.
worksheet1.set_column('A:I', 16)
worksheet1.set_row(0, 20)
worksheet1.set_selection('C3')
# Some text to demonstrate scrolling.
for col in range(0, 9):
worksheet1.write(0, col, 'Scroll down', header_format)
for row in range(1, 100):
for col in range(0, 9):
worksheet1.write(row, col, row + 1, center_format)
#######################################################################
#
# Example 2. Freeze pane on the left column.
#
worksheet2.freeze_panes(0, 1)
# Other sheet formatting.
worksheet2.set_column('A:A', 16)
worksheet2.set_selection('C3')
# Some text to demonstrate scrolling.
for row in range(0, 50):
worksheet2.write(row, 0, 'Scroll right', header_format)
for col in range(1, 26):
worksheet2.write(row, col, col, center_format)
#######################################################################
#
# Example 3. Freeze pane on the top row and left column.
#
worksheet3.freeze_panes(1, 1)
# Other sheet formatting.
worksheet3.set_column('A:Z', 16)
worksheet3.set_row(0, 20)
worksheet3.set_selection('C3')
worksheet3.write(0, 0, '', header_format)
# Some text to demonstrate scrolling.
for col in range(1, 26):
worksheet3.write(0, col, 'Scroll down', header_format)
for row in range(1, 50):
worksheet3.write(row, 0, 'Scroll right', header_format)
for col in range(1, 26):
worksheet3.write(row, col, col, center_format)
#######################################################################
#
# Example 4. Split pane on the top row and left column.
#
# The divisions must be specified in terms of row and column dimensions.
# The default row height is 15 and the default column width is 8.43
#
worksheet4.split_panes(15, 8.43)
# Other sheet formatting.
worksheet4.set_selection('C3')
# Some text to demonstrate scrolling.
for col in range(1, 26):
worksheet4.write(0, col, 'Scroll', center_format)
for row in range(1, 50):
worksheet4.write(row, 0, 'Scroll', center_format)
for col in range(1, 26):
worksheet4.write(row, col, col, center_format)
workbook.close()
Example of how to add tables to an XlsxWriter worksheet.
Tables in Excel are used to group rows and columns of data into a single structure that can be referenced in a formula or formatted collectively.
See also Working with Worksheet Tables.
###############################################################################
#
# Example of how to add tables to an XlsxWriter worksheet.
#
# Tables in Excel are used to group rows and columns of data into a single
# structure that can be referenced in a formula or formatted collectively.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
workbook = xlsxwriter.Workbook('tables.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet4 = workbook.add_worksheet()
worksheet5 = workbook.add_worksheet()
worksheet6 = workbook.add_worksheet()
worksheet7 = workbook.add_worksheet()
worksheet8 = workbook.add_worksheet()
worksheet9 = workbook.add_worksheet()
worksheet10 = workbook.add_worksheet()
worksheet11 = workbook.add_worksheet()
worksheet12 = workbook.add_worksheet()
worksheet13 = workbook.add_worksheet()
currency_format = workbook.add_format({'num_format': '$#,##0'})
# Some sample data for the table.
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
###############################################################################
#
# Example 1.
#
caption = 'Default table with no data.'
# Set the columns widths.
worksheet1.set_column('B:G', 12)
# Write the caption.
worksheet1.write('B1', caption)
# Add a table to the worksheet.
worksheet1.add_table('B3:F7')
###############################################################################
#
# Example 2.
#
caption = 'Default table with data.'
# Set the columns widths.
worksheet2.set_column('B:G', 12)
# Write the caption.
worksheet2.write('B1', caption)
# Add a table to the worksheet.
worksheet2.add_table('B3:F7', {'data': data})
###############################################################################
#
# Example 3.
#
caption = 'Table without default autofilter.'
# Set the columns widths.
worksheet3.set_column('B:G', 12)
# Write the caption.
worksheet3.write('B1', caption)
# Add a table to the worksheet.
worksheet3.add_table('B3:F7', {'autofilter': 0})
# Table data can also be written separately, as an array or individual cells.
worksheet3.write_row('B4', data[0])
worksheet3.write_row('B5', data[1])
worksheet3.write_row('B6', data[2])
worksheet3.write_row('B7', data[3])
###############################################################################
#
# Example 4.
#
caption = 'Table without default header row.'
# Set the columns widths.
worksheet4.set_column('B:G', 12)
# Write the caption.
worksheet4.write('B1', caption)
# Add a table to the worksheet.
worksheet4.add_table('B4:F7', {'header_row': 0})
# Table data can also be written separately, as an array or individual cells.
worksheet4.write_row('B4', data[0])
worksheet4.write_row('B5', data[1])
worksheet4.write_row('B6', data[2])
worksheet4.write_row('B7', data[3])
###############################################################################
#
# Example 5.
#
caption = 'Default table with "First Column" and "Last Column" options.'
# Set the columns widths.
worksheet5.set_column('B:G', 12)
# Write the caption.
worksheet5.write('B1', caption)
# Add a table to the worksheet.
worksheet5.add_table('B3:F7', {'first_column': 1, 'last_column': 1})
# Table data can also be written separately, as an array or individual cells.
worksheet5.write_row('B4', data[0])
worksheet5.write_row('B5', data[1])
worksheet5.write_row('B6', data[2])
worksheet5.write_row('B7', data[3])
###############################################################################
#
# Example 6.
#
caption = 'Table with banded columns but without default banded rows.'
# Set the columns widths.
worksheet6.set_column('B:G', 12)
# Write the caption.
worksheet6.write('B1', caption)
# Add a table to the worksheet.
worksheet6.add_table('B3:F7', {'banded_rows': 0, 'banded_columns': 1})
# Table data can also be written separately, as an array or individual cells.
worksheet6.write_row('B4', data[0])
worksheet6.write_row('B5', data[1])
worksheet6.write_row('B6', data[2])
worksheet6.write_row('B7', data[3])
###############################################################################
#
# Example 7.
#
caption = 'Table with user defined column headers'
# Set the columns widths.
worksheet7.set_column('B:G', 12)
# Write the caption.
worksheet7.write('B1', caption)
# Add a table to the worksheet.
worksheet7.add_table('B3:F7', {'data': data,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1'},
{'header': 'Quarter 2'},
{'header': 'Quarter 3'},
{'header': 'Quarter 4'},
]})
###############################################################################
#
# Example 8.
#
caption = 'Table with user defined column headers'
# Set the columns widths.
worksheet8.set_column('B:G', 12)
# Write the caption.
worksheet8.write('B1', caption)
# Formula to use in the table.
formula = '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
# Add a table to the worksheet.
worksheet8.add_table('B3:G7', {'data': data,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1'},
{'header': 'Quarter 2'},
{'header': 'Quarter 3'},
{'header': 'Quarter 4'},
{'header': 'Year',
'formula': formula},
]})
###############################################################################
#
# Example 9.
#
caption = 'Table with totals row (but no caption or totals).'
# Set the columns widths.
worksheet9.set_column('B:G', 12)
# Write the caption.
worksheet9.write('B1', caption)
# Formula to use in the table.
formula = '=SUM(Table9[@[Quarter 1]:[Quarter 4]])'
# Add a table to the worksheet.
worksheet9.add_table('B3:G8', {'data': data,
'total_row': 1,
'columns': [{'header': 'Product'},
{'header': 'Quarter 1'},
{'header': 'Quarter 2'},
{'header': 'Quarter 3'},
{'header': 'Quarter 4'},
{'header': 'Year',
'formula': formula
},
]})
###############################################################################
#
# Example 10.
#
caption = 'Table with totals row with user captions and functions.'
# Set the columns widths.
worksheet10.set_column('B:G', 12)
# Write the caption.
worksheet10.write('B1', caption)
# Options to use in the table.
options = {'data': data,
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1', 'total_function': 'sum'},
{'header': 'Quarter 2', 'total_function': 'sum'},
{'header': 'Quarter 3', 'total_function': 'sum'},
{'header': 'Quarter 4', 'total_function': 'sum'},
{'header': 'Year',
'formula': '=SUM(Table10[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum'
},
]}
# Add a table to the worksheet.
worksheet10.add_table('B3:G8', options)
###############################################################################
#
# Example 11.
#
caption = 'Table with alternative Excel style.'
# Set the columns widths.
worksheet11.set_column('B:G', 12)
# Write the caption.
worksheet11.write('B1', caption)
# Options to use in the table.
options = {'data': data,
'style': 'Table Style Light 11',
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1', 'total_function': 'sum'},
{'header': 'Quarter 2', 'total_function': 'sum'},
{'header': 'Quarter 3', 'total_function': 'sum'},
{'header': 'Quarter 4', 'total_function': 'sum'},
{'header': 'Year',
'formula': '=SUM(Table11[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum'
},
]}
# Add a table to the worksheet.
worksheet11.add_table('B3:G8', options)
###############################################################################
#
# Example 12.
#
caption = 'Table with Excel style removed.'
# Set the columns widths.
worksheet12.set_column('B:G', 12)
# Write the caption.
worksheet12.write('B1', caption)
# Options to use in the table.
options = {'data': data,
'style': None,
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1', 'total_function': 'sum'},
{'header': 'Quarter 2', 'total_function': 'sum'},
{'header': 'Quarter 3', 'total_function': 'sum'},
{'header': 'Quarter 4', 'total_function': 'sum'},
{'header': 'Year',
'formula': '=SUM(Table12[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum'
},
]}
# Add a table to the worksheet.
worksheet12.add_table('B3:G8', options)
###############################################################################
#
# Example 13.
#
caption = 'Table with column formats.'
# Set the columns widths.
worksheet13.set_column('B:G', 12)
# Write the caption.
worksheet13.write('B1', caption)
# Options to use in the table.
options = {'data': data,
'total_row': 1,
'columns': [{'header': 'Product', 'total_string': 'Totals'},
{'header': 'Quarter 1',
'total_function': 'sum',
'format': currency_format,
},
{'header': 'Quarter 2',
'total_function': 'sum',
'format': currency_format,
},
{'header': 'Quarter 3',
'total_function': 'sum',
'format': currency_format,
},
{'header': 'Quarter 4',
'total_function': 'sum',
'format': currency_format,
},
{'header': 'Year',
'formula': '=SUM(Table13[@[Quarter 1]:[Quarter 4]])',
'total_function': 'sum',
'format': currency_format,
},
]}
# Add a table to the worksheet.
worksheet13.add_table('B3:G8', options)
workbook.close()
An example of adding support for user defined types to the XlsxWriter
write()
method using the add_write_handler()
method.
This example takes UUID data and writes it as a string by adding a callback
handler to the write()
method. A UUID data type would normally raise a
TypeError
in XlsxWriter since it isn’t a type that is supported by Excel.
See the Writing user defined types section for more details on how this functionality works.
##############################################################################
#
# An example of adding support for user defined types to the XlsxWriter write()
# method.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
import uuid
# Create a function that will behave like a worksheet write() method.
#
# This function takes a UUID and writes it as as string. It should take the
# parameters shown below and return the return value from the called worksheet
# write_*() method. In this case it changes the UUID to a string and calls
# write_string() to write it.
#
def write_uuid(worksheet, row, col, token, format=None):
return worksheet.write_string(row, col, str(token), format)
# Set up the workbook as usual.
workbook = xlsxwriter.Workbook('user_types1.xlsx')
worksheet = workbook.add_worksheet()
# Make the first column wider for clarity.
worksheet.set_column('A:A', 40)
# Add the write() handler/callback to the worksheet.
worksheet.add_write_handler(uuid.UUID, write_uuid)
# Create a UUID.
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)
workbook.close()
An example of adding support for user defined types to the XlsxWriter
write()
method using the add_write_handler()
method.
This example removes NaN
(Not a Number) values from numeric data and
writes a blank cell instead. Note, another way to handle this is with the
nan_inf_to_errors
option in the Workbook()
constructor.
See the Writing user defined types section for more details on how this functionality works.
##############################################################################
#
# An example of adding support for user defined types to the XlsxWriter write()
# method.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
import math
# Create a function that will behave like a worksheet write() method.
#
# This function takes a float and if it is NaN then it writes a blank cell
# instead. It should take the parameters shown below and return the return
# value from the called worksheet write_*() method.
#
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 for any other number.
return None
# Set up the workbook as usual.
workbook = xlsxwriter.Workbook('user_types2.xlsx')
worksheet = workbook.add_worksheet()
# Add the write() handler/callback to the worksheet.
worksheet.add_write_handler(float, ignore_nan)
# Create some data to write.
my_data = [1, 2, float('nan'), 4, 5]
# Write the data. Note that write_row() calls write() so this will work as
# expected. Writing NaN values would raise a TypeError without the handler.
worksheet.write_row('A1', my_data)
workbook.close()
An example of adding support for user defined types to the XlsxWriter
write()
method using the add_write_handler()
method.
This, somewhat artificial, example shows how to use the row
and col
parameters to control the logic of the callback function. It changes the
worksheet write()
method so that it hides/replaces user passwords when
writing string values based on their position in the worksheet.
See the Writing user defined types section for more details on how this functionality works.
##############################################################################
#
# An example of adding support for user defined types to the XlsxWriter write()
# method.
#
# Copyright 2013-2020, John McNamara<EMAIL_ADDRESS>#
import xlsxwriter
import math
# Create a function that changes the worksheet write() method so that it
# hides/replaces user passwords when writing string data. The password data,
# based on the sample data structure, will be data in the second column, apart
# from the header row.
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)
# Set up the workbook as usual.
workbook = xlsxwriter.Workbook('user_types3.xlsx')
worksheet = workbook.add_worksheet()
# Make the headings in the first row bold.
bold = workbook.add_format({'bold': True})
worksheet.set_row(0, None, bold)
# Add the write() handler/callback to the worksheet. In Python 2 the type
# would be better as 'basestring' if we also wanted to match the 'unicode'
# type.
worksheet.add_write_handler(str, hide_password)