XlsxWriter is a Python module for creating Excel XLSX files.
(Sample code to create the above spreadsheet.)
XlsxWriter
XlsxWriter is a Python module that can be used to write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file. It supports features such as formatting and many more, including:
It supports Python 2.7, 3.4+ and PyPy and uses standard libraries only.
This document explains how to use the XlsxWriter module. See the following sections for more information, or jump straight to the Introduction.
XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format.
It can be used to write text, numbers, and formulas to multiple worksheets and it supports features such as formatting, images, charts, page setup, autofilters, conditional formatting and many others.
XlsxWriter has some advantages and disadvantages over the alternative Python modules for writing Excel files.
Advantages:
- It supports more Excel features than any of the alternative modules.
- It has a high degree of fidelity with files produced by Excel. In most cases the files produced are 100% equivalent to files produced by Excel.
- It has extensive documentation, example files and tests.
- It is fast and can be configured to use very little memory even for very large output files.
Disadvantages:
- It cannot read or modify existing Excel XLSX files.
XlsxWriter is licensed under a BSD License and the source code is available on GitHub.
To try out the module see the next section on Getting Started with XlsxWriter.
Here are some easy instructions to get you up and running with the XlsxWriter module.
The first step is to install the XlsxWriter module. There are several ways to do this.
The pip installer is the preferred method for installing Python modules from PyPI, the Python Package Index:
$ pip install XlsxWriter
# Or to a non system dir:
$ pip install --user XlsxWriter
If you download a tarball of the latest version of XlsxWriter you can install it as follows (change the version number to suit):
$ tar -zxvf XlsxWriter-1.2.3.tar.gz
$ cd XlsxWriter-1.2.3
$ python setup.py install
A tarball of the latest code can be downloaded from GitHub as follows:
$ curl -O -L http://github.com/jmcnamara/XlsxWriter/archive/master.tar.gz
$ tar zxvf master.tar.gz
$ cd XlsxWriter-master/
$ python setup.py install
The XlsxWriter source code and bug tracker is in the XlsxWriter repository on GitHub. You can clone the repository and install from it as follows:
$ git clone https://github.com/jmcnamara/XlsxWriter.git
$ cd XlsxWriter
$ python setup.py install
If the installation went correctly you can create a small sample program like the following to verify that the module works correctly:
import xlsxwriter
workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()
Save this to a file called hello.py
and run it as follows:
$ python hello.py
This will output a file called hello.xlsx
which should look something like
the following:
If you downloaded a tarball or cloned the repo, as shown above, you should also have a directory called examples with some sample applications that demonstrate different features of XlsxWriter.
The latest version of this document is hosted on Read The Docs. It is also available as a PDF.
Once you are happy that the module is installed and operational you can have a look at the rest of the XlsxWriter documentation. Tutorial 1: Create a simple XLSX file is a good place to start.
Let’s start by creating a simple spreadsheet using Python and the XlsxWriter module.
Say that we have some data on monthly outgoings that we want to convert into an Excel XLSX file:
expenses = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
To do that we can start with a small program like the following:
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()
# Some data we want to write to the worksheet.
expenses = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0
# Iterate over the data and write it out row by row.
for item, cost in (expenses):
worksheet.write(row, col, item)
worksheet.write(row, col + 1, cost)
row += 1
# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')
workbook.close()
If we run this program we should get a spreadsheet that looks like this:
This is a simple example but the steps involved are representative of all programs that use XlsxWriter, so let’s break it down into separate parts.
The first step is to import the module:
import xlsxwriter
The next step is to create a new workbook object using the Workbook()
constructor.
Workbook()
takes one, non-optional, argument which is the filename that
we want to create:
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
Note
XlsxWriter can only create new files. It cannot read or modify existing files.
The workbook object is then used to add a new worksheet via the
add_worksheet()
method:
worksheet = workbook.add_worksheet()
By default worksheet names in the spreadsheet will be Sheet1, Sheet2 etc., but we can also specify a name:
worksheet1 = workbook.add_worksheet() # Defaults to Sheet1.
worksheet2 = workbook.add_worksheet('Data') # Data.
worksheet3 = workbook.add_worksheet() # Defaults to Sheet3.
We can then use the worksheet object to write data via the write()
method:
worksheet.write(row, col, some_data)
Note
Throughout XlsxWriter, rows and columns are zero indexed. The
first cell in a worksheet, A1
, is (0, 0)
.
So in our example we iterate over our data and write it out as follows:
# Iterate over the data and write it out row by row.
for item, cost in (expenses):
worksheet.write(row, col, item)
worksheet.write(row, col + 1, cost)
row += 1
We then add a formula to calculate the total of the items in the second column:
worksheet.write(row, 1, '=SUM(B1:B4)')
Finally, we close the Excel file via the close()
method:
workbook.close()
And that’s it. We now have a file that can be read by Excel and other spreadsheet applications.
In the next sections we will see how we can use the XlsxWriter module to add formatting and other Excel features.
In the previous section we created a simple spreadsheet using Python and the XlsxWriter module.
This converted the required data into an Excel file but it looked a little bare. In order to make the information clearer we would like to add some simple formatting, like this:
The differences here are that we have added Item and Cost column headers in a bold font, we have formatted the currency in the second column and we have made the Total string bold.
To do this we can extend our program as follows:
(The significant changes are shown with a red line.)
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses02.xlsx')
worksheet = workbook.add_worksheet()
# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})
# Add a number format for cells with money.
money = workbook.add_format({'num_format': '$#,##0'})
# Write some data headers.
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Cost', bold)
# Some data we want to write to the worksheet.
expenses = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
# Start from the first cell below the headers.
row = 1
col = 0
# Iterate over the data and write it out row by row.
for item, cost in (expenses):
worksheet.write(row, col, item)
worksheet.write(row, col + 1, cost, money)
row += 1
# Write a total using a formula.
worksheet.write(row, 0, 'Total', bold)
worksheet.write(row, 1, '=SUM(B2:B5)', money)
workbook.close()
The main difference between this and the previous program is that we have added two Format objects that we can use to format cells in the spreadsheet.
Format objects represent all of the formatting properties that can be applied to a cell in Excel such as fonts, number formatting, colors and borders. This is explained in more detail in The Format Class section.
For now we will avoid getting into the details and just use a limited amount of the format functionality to add some simple formatting:
# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})
# Add a number format for cells with money.
money = workbook.add_format({'num_format': '$#,##0'})
We can then pass these formats as an optional third parameter to the
worksheet.write()
method to format the data in the
cell:
write(row, column, token, [format])
Like this:
worksheet.write(row, 0, 'Total', bold)
Which leads us to another new feature in this program. To add the headers in
the first row of the worksheet we used write()
like this:
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Cost', bold)
So, instead of (row, col)
we used the Excel 'A1'
style notation. See
Working with Cell Notation for more details but don’t be too concerned about it for
now. It is just a little syntactic sugar to help with laying out worksheets.
In the next section we will look at handling more data types.
In the previous section we created a simple spreadsheet with formatting using Python and the XlsxWriter module.
This time let’s extend the data we want to write to include some dates:
expenses = (
['Rent', '2013-01-13', 1000],
['Gas', '2013-01-14', 100],
['Food', '2013-01-16', 300],
['Gym', '2013-01-20', 50],
)
The corresponding spreadsheet will look like this:
The differences here are that we have added a Date column with formatting and made that column a little wider to accommodate the dates.
To do this we can extend our program as follows:
(The significant changes are shown with a red line.)
from datetime import datetime
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses03.xlsx')
worksheet = workbook.add_worksheet()
# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': 1})
# Add a number format for cells with money.
money_format = workbook.add_format({'num_format': '$#,##0'})
# Add an Excel date format.
date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
# Adjust the column width.
worksheet.set_column(1, 1, 15)
# Write some data headers.
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Date', bold)
worksheet.write('C1', 'Cost', bold)
# Some data we want to write to the worksheet.
expenses = (
['Rent', '2013-01-13', 1000],
['Gas', '2013-01-14', 100],
['Food', '2013-01-16', 300],
['Gym', '2013-01-20', 50],
)
# Start from the first cell below the headers.
row = 1
col = 0
for item, date_str, cost in (expenses):
# Convert the date string into a datetime object.
date = datetime.strptime(date_str, "%Y-%m-%d")
worksheet.write_string (row, col, item )
worksheet.write_datetime(row, col + 1, date, date_format )
worksheet.write_number (row, col + 2, cost, money_format)
row += 1
# Write a total using a formula.
worksheet.write(row, 0, 'Total', bold)
worksheet.write(row, 2, '=SUM(C2:C5)', money_format)
workbook.close()
The main difference between this and the previous program is that we have added a new Format object for dates and we have additional handling for data types.
Excel treats different types of input data, such as strings and numbers,
differently although it generally does it transparently to the user.
XlsxWriter tries to emulate this in the
worksheet.write()
method by mapping Python data
types to types that Excel supports.
The write()
method acts as a general alias for several more specific
methods:
write_string()
write_number()
write_blank()
write_formula()
write_datetime()
write_boolean()
write_url()
In this version of our program we have used some of these explicit write_
methods for different types of data:
worksheet.write_string (row, col, item )
worksheet.write_datetime(row, col + 1, date, date_format )
worksheet.write_number (row, col + 2, cost, money_format)
This is mainly to show that if you need more control over the type of data you
write to a worksheet you can use the appropriate method. In this simplified
example the write()
method would actually have worked just as well.
The handling of dates is also new to our program.
Dates and times in Excel are floating point numbers that have a number format
applied to display them in the correct format. If the date and time are Python
datetime
objects XlsxWriter makes the required number conversion
automatically. However, we also need to add the number format to ensure that
Excel displays it as as date:
from datetime import datetime
...
date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
...
for item, date_str, cost in (expenses):
# Convert the date string into a datetime object.
date = datetime.strptime(date_str, "%Y-%m-%d")
...
worksheet.write_datetime(row, col + 1, date, date_format )
...
Date handling is explained in more detail in Working with Dates and Time.
The last addition to our program is the set_column()
method to adjust the
width of column ‘B’ so that the dates are more clearly visible:
# Adjust the column width.
worksheet.set_column('B:B', 15)
That completes the tutorial section.
In the next sections we will look at the API in more detail starting with The Workbook Class.
The Workbook class is the main class exposed by the XlsxWriter module and it is the only class that you will need to instantiate directly.
The Workbook class represents the entire spreadsheet as you see it in Excel and internally it represents the Excel file as it is written on disk.
Workbook
(filename[, options])Create a new XlsxWriter Workbook object.
| Parameters: |
|
|---|---|
| Return type: | A Workbook object. |
The Workbook()
constructor is used to create a new Excel workbook with a
given filename:
import xlsxwriter
workbook = xlsxwriter.Workbook('filename.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, 'Hello Excel')
workbook.close()
The constructor options are:
constant_memory: Reduces the amount of data stored in memory so that large files can be written efficiently:
workbook = xlsxwriter.Workbook(filename, {'constant_memory': True})
Note, in this mode a row of data is written and then discarded when a cell
in a new row is added via one of the worksheet write_()
methods.
Therefore, once this mode is active, data should be written in sequential
row order. For this reason the add_table()
and merge_range()
Worksheet methods don’t work in this mode.
See Working with Memory and Performance for more details.
tmpdir: XlsxWriter
stores workbook data in temporary files prior
to assembling the final XLSX file. The temporary files are created in the
system’s temp directory. If the default temporary directory isn’t accessible
to your application, or doesn’t contain enough space, you can specify an
alternative location using the tmpdir
option:
workbook = xlsxwriter.Workbook(filename, {'tmpdir': '/home/user/tmp'})
The temporary directory must exist and will not be created.
in_memory: To avoid the use of temporary files in the assembly of the
final XLSX file, for example on servers that don’t allow temp files, set the
in_memory
constructor option to True
:
workbook = xlsxwriter.Workbook(filename, {'in_memory': True})
This option overrides the constant_memory
option.
Note
This option used to be the recommended way of deploying XlsxWriter on
Google APP Engine since it didn’t support a /tmp
directory. However,
the Python 3 Runtime Environment in Google App Engine now supports a
filesystem with read/write access to /tmp
which means this option isn’t required. The /tmp
dir isn’t supported
in the Python 2 Runtime Environment so this option is still required
there.
strings_to_numbers: Enable the
worksheet.write()
method to convert strings to
numbers, where possible, using float()
in order to avoid an Excel
warning about “Numbers Stored as Text”. The default is False
. To enable
this option use:
workbook = xlsxwriter.Workbook(filename, {'strings_to_numbers': True})
strings_to_formulas: Enable the
worksheet.write()
method to convert strings to
formulas. The default is True
. To disable this option use:
workbook = xlsxwriter.Workbook(filename, {'strings_to_formulas': False})
strings_to_urls: Enable the worksheet.write()
method to convert strings to urls. The default is True
. To disable this
option use:
workbook = xlsxwriter.Workbook(filename, {'strings_to_urls': False})
max_url_length: Set the maximum length for hyperlinks in worksheets. The
default is 2079 and the minimum is 255. Versions of Excel prior to Excel
2015 limited hyperlink links and anchor/locations to 255 characters
each. Versions after that support urls up to 2079 characters. XlsxWriter
versions >= 1.2.3 support the new longer limit by default. However, a lower
or user defined limit can be set via the max_url_length
option:
workbook = xlsxwriter.Workbook(filename, {'max_url_length': 255})
nan_inf_to_errors: Enable the
worksheet.write()
and write_number()
methods to convert nan
, inf
and -inf
to Excel errors. Excel
doesn’t handle NAN/INF as numbers so as a workaround they are mapped to
formulas that yield the error codes #NUM!
and #DIV/0!
. The default
is False
. To enable this option use:
workbook = xlsxwriter.Workbook(filename, {'nan_inf_to_errors': True})
default_date_format: This option is used to specify a default date
format string for use with the
worksheet.write_datetime()
method when an
explicit format isn’t given. See Working with Dates and Time for more
details:
xlsxwriter.Workbook(filename, {'default_date_format': 'dd/mm/yy'})
remove_timezone: Excel doesn’t support timezones in datetimes/times so
there isn’t any fail-safe way that XlsxWriter can map a Python timezone aware
datetime into an Excel datetime in functions such as
write_datetime()
. As such the user should convert and remove the
timezones in some way that makes sense according to their
requirements. Alternatively the remove_timezone
option can be used to
strip the timezone from datetime values. The default is False
. To enable
this option use:
workbook = xlsxwriter.Workbook(filename, {'remove_timezone': True})
See also Timezone Handling in XlsxWriter.
date_1904: Excel for Windows uses a default epoch of 1900 and Excel for
Mac uses an epoch of 1904. However, Excel on either platform will convert
automatically between one system and the other. XlsxWriter stores dates in
the 1900 format by default. If you wish to change this you can use the
date_1904
workbook option. This option is mainly for enhanced
compatibility with Excel and in general isn’t required very often:
workbook = xlsxwriter.Workbook(filename, {'date_1904': True})
When specifying a filename it is recommended that you use an .xlsx
extension or Excel will generate a warning when opening the file.
The Workbook()
method also works using the with
context manager. In
which case it doesn’t need an explicit close() statement:
with xlsxwriter.Workbook('hello_world.xlsx') as workbook:
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
It is possible to write files to in-memory strings using BytesIO as follows:
from io import BytesIO
output = BytesIO()
workbook = xlsxwriter.Workbook(output)
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello')
workbook.close()
xlsx_data = output.getvalue()
To avoid the use of any temporary files and keep the entire file in-memory use
the in_memory
constructor option shown above.
See also Example: Simple HTTP Server (Python 2) and Example: Simple HTTP Server (Python 3).
add_worksheet
([name])Add a new worksheet to a workbook.
| Parameters: | name (string) – Optional worksheet name, defaults to Sheet1, etc. |
|---|---|
| Return type: | A worksheet object. |
| Raises: |
|
The add_worksheet()
method adds a new worksheet to a workbook.
At least one worksheet should be added to a new workbook. The Worksheet object is used to write data and configure a worksheet in the workbook.
The name
parameter is optional. If it is not specified, or blank, the
default Excel convention will be followed, i.e. Sheet1, Sheet2, etc.:
worksheet1 = workbook.add_worksheet() # Sheet1
worksheet2 = workbook.add_worksheet('Foglio2') # Foglio2
worksheet3 = workbook.add_worksheet('Data') # Data
worksheet4 = workbook.add_worksheet() # Sheet4
The worksheet name must be a valid Excel worksheet name:
InvalidWorksheetName
exception.[ ] : * ? / \
. This error will
raise a InvalidWorksheetName
exception.InvalidWorksheetName
exception.name
for more than one
worksheet. This error will raise a DuplicateWorksheetName
exception.The rules for worksheet names in Excel are explained in the Microsoft Office documentation on how to Rename a worksheet.
add_format
([properties])Create a new Format object to formats cells in worksheets.
| Parameters: | properties (dictionary) – An optional dictionary of format properties. |
|---|---|
| Return type: | A format object. |
The add_format()
method can be used to create new Format
objects which are used to apply formatting to a cell. You can either define
the properties at creation time via a dictionary of property values or later
via method calls:
format1 = workbook.add_format(props) # Set properties at creation.
format2 = workbook.add_format() # Set properties later.
See the The Format Class section for more details about Format properties and how to set them.
add_chart
(options)Create a chart object that can be added to a worksheet.
| Parameters: | options (dictionary) – An dictionary of chart type options. |
|---|---|
| Return type: | A Chart object. |
This method is use to create a new chart object that can be inserted into a
worksheet via the insert_chart()
Worksheet method:
chart = workbook.add_chart({'type': 'column'})
The properties that can be set are:
type (required)
subtype (optional)
type
This is a required parameter. It defines the type of chart that will be created:
chart = workbook.add_chart({'type': 'line'})
The available types are:
area
bar
column
doughnut
line
pie
radar
scatter
stock
subtype
Used to define a chart subtype where available:
workbook.add_chart({'type': 'bar', 'subtype': 'stacked'})
See the The Chart Class for a list of available chart subtypes.
Note
A chart can only be inserted into a worksheet once. If several similar
charts are required then each one must be created separately with
add_chart()
.
See also Working with Charts and Chart Examples.
add_chartsheet
([sheetname])Add a new add_chartsheet to a workbook.
| Parameters: | sheetname (string) – Optional chartsheet name, defaults to Chart1, etc. |
|---|---|
| Return type: | A chartsheet object. |
The add_chartsheet()
method adds a new chartsheet to a workbook.
See The Chartsheet Class for details.
The sheetname
parameter is optional. If it is not specified the default
Excel convention will be followed, i.e. Chart1, Chart2, etc.
The chartsheet name must be a valid Excel worksheet name. See
add_worksheet()
for the limitation on Excel worksheet names.
close
()Close the Workbook object and write the XLSX file.
| Raises: |
|
|---|
The workbook close()
method writes all data to the xlsx file and closes
it:
workbook.close()
This is a required method call to close and write the xlsxwriter file, unless
you are using the with
context manager, see below.
The Workbook
object also works using the with
context manager. In
which case it doesn’t need an explicit close()
statement:
With xlsxwriter.Workbook('hello_world.xlsx') as workbook:
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
The workbook will close automatically when exiting the scope of the with
statement.
The most common exception during close()
is FileCreateError
which is
generally caused by a write permission error. On Windows this usually occurs
if the file being created is already open in Excel. This exception can be
caught in a try
block where you can instruct the user to close the open
file before overwriting it:
while True:
try:
workbook.close()
except xlsxwriter.exceptions.FileCreateError as e:
# For Python 3 use input() instead of raw_input().
decision = raw_input("Exception caught in workbook.close(): %s\n"
"Please close the file if it is open in Excel.\n"
"Try to write file again? [Y/n]: " % e)
if decision != 'n':
continue
break
See also Example: Catch exception on closing.
set_size
(width, height)Set the size of a workbook window.
| Parameters: |
|---|
The set_size()
method can be used to set the size of a workbook window:
workbook.set_size(1200, 800)
The Excel window size was used in Excel 2007 to define the width and height of a workbook window within the Multiple Document Interface (MDI). In later versions of Excel for Windows this interface was dropped. This method is currently only useful when setting the window size in Excel for Mac 2011. The units are pixels and the default size is 1073 x 644.
Note, this doesn’t equate exactly to the Excel for Mac pixel size since it is based on the original Excel 2007 for Windows sizing. Some trial and error may be required to get an exact size.
set_tab_ratio
(tab_ratio)Set the ratio between the worksheet tabs and the horizontal slider.
| Parameters: | tab_ratio (float) – The tab ratio between 0 and 100. |
|---|
The set_tab_ratio()
method can be used to set the ratio between worksheet
tabs and the horizontal slider at the bottom of a workbook. This can be
increased to give more room to the tabs or reduced to increase the size of the
horizontal slider:
The default value in Excel is 60. It can be changed as follows:
workbook.set_tab_ratio(75)
set_properties
(properties)Set the document properties such as Title, Author etc.
| Parameters: | properties (dict) – Dictionary of document properties. |
|---|
The set_properties()
method can be used to set the document properties of the
Excel file created by XlsxWriter
. These properties are visible when you
use the Office Button -> Prepare -> Properties
option in Excel and are
also available to external applications that read or index windows files.
The properties that can be set are:
title
subject
author
manager
company
category
keywords
comments
status
hyperlink_base
create
- the file creation date as a datetime.date
object.The properties are all optional and should be passed in dictionary format as follows:
workbook.set_properties({
'title': 'This is an example spreadsheet',
'subject': 'With document properties',
'author': 'John McNamara',
'manager': 'Dr. Heinz Doofenshmirtz',
'company': 'of Wolves',
'category': 'Example spreadsheets',
'keywords': 'Sample, Example, Properties',
'created': datetime.date(2018, 1, 1),
'comments': 'Created with Python and XlsxWriter'})
See also Example: Setting Document Properties.
set_custom_property
(name, value[, property_type])Set a custom document property.
| Parameters: |
|
|---|
The set_custom_property()
method can be used to set one or more custom
document properties not covered by the standard properties in the
set_properties()
method above.
For example:
date = datetime.strptime('2016-12-12', '%Y-%m-%d')
workbook.set_custom_property('Checked by', 'Eve')
workbook.set_custom_property('Date completed', date)
workbook.set_custom_property('Document number', 12345)
workbook.set_custom_property('Reference number', 1.2345)
workbook.set_custom_property('Has review', True)
workbook.set_custom_property('Signed off', False)
Date parameters should be datetime.datetime
objects.
The optional property_type
parameter can be used to set an explicit type
for the custom property, just like in Excel. The available types are:
text
date
number
bool
However, in almost all cases the type will be inferred correctly from the Python type, like in the example above.
Note: the name
and value
parameters are limited to 255 characters by
Excel.
define_name
()Create a defined name in the workbook to use as a variable.
| Parameters: |
|
|---|
This method is used to defined a name that can be used to represent a value, a single cell or a range of cells in a workbook. These are sometimes referred to as a “Named Range”.
Defined names are generally used to simplify or clarify formulas by using descriptive variable names:
workbook.define_name('Exchange_rate', '=0.96')
worksheet.write('B3', '=B2*Exchange_rate')
As in Excel a name defined like this is “global” to the workbook and can be referred to from any worksheet:
# Global workbook name.
workbook.define_name('Sales', '=Sheet1!$G$1:$H$10')
It is also possible to define a local/worksheet name by prefixing it with the
sheet name using the syntax 'sheetname!definedname'
:
# Local worksheet name.
workbook.define_name('Sheet2!Sales', '=Sheet2!$G$1:$G$10')
If the sheet name contains spaces or special characters you must follow the Excel convention and enclose it in single quotes:
workbook.define_name("'New Data'!Sales", '=Sheet2!$G$1:$G$10')
The rules for names in Excel are explained in the Microsoft Office documentation on how to Define and use names in formulas.
See also Example: Defined names/Named ranges.
add_vba_project
(vba_project[, is_stream])Add a vbaProject binary to the Excel workbook.
| Parameters: |
|
|---|
The add_vba_project()
method can be used to add macros or functions to a
workbook using a binary VBA project file that has been extracted from an
existing Excel xlsm file:
workbook.add_vba_project('./vbaProject.bin')
Only one vbaProject.bin
file can be added per workbook.
The is_stream
parameter is used to indicate that vba_project
refers to
a BytesIO byte stream rather than a physical file. This can be used when
working with the workbook in_memory
mode.
See Working with VBA Macros for more details.
set_vba_name
(name)Set the VBA name for the workbook.
| Parameters: | name (string) – The VBA name for the workbook. |
|---|
The set_vba_name()
method can be used to set the VBA codename for the
workbook. This is sometimes required when a vbaProject macro included via
add_vba_project()
refers to the workbook. The default Excel VBA name of
ThisWorkbook
is used if a user defined name isn’t specified.
See Working with VBA Macros for more details.
worksheets
()Return a list of the worksheet objects in the workbook.
| Return type: | A list of worksheet objects. |
|---|
The worksheets()
method returns a list of the worksheets in a workbook.
This is useful if you want to repeat an operation on each worksheet in a
workbook:
for worksheet in workbook.worksheets():
worksheet.write('A1', 'Hello')
get_worksheet_by_name
(name)Return a worksheet object in the workbook using the sheetname.
| Parameters: | name (string) – Name of worksheet that you wish to retrieve. |
|---|---|
| Return type: | A worksheet object. |
The get_worksheet_by_name()
method returns the worksheet or chartsheet
object with the the given name
or None
if it isn’t found:
worksheet = workbook.get_worksheet_by_name('Sheet1')
get_default_url_format
()Return a format object.
| Return type: | A format object. |
|---|
The get_default_url_format()
method gets a copy of the default url format
used when a user defined format isn’t specified with write_url()
. The
format is the hyperlink style defined by Excel for the default theme:
url_format = workbook.get_default_url_format()
set_calc_mode
(mode)Set the Excel calculation mode for the workbook.
| Parameters: | mode (string) – The calculation mode string |
|---|
Set the calculation mode for formulas in the workbook. This is mainly of use for workbooks with slow formulas where you want to allow the user to calculate them manually.
The mode
parameter can be:
auto
: The default. Excel will re-calculate formulas when a formula or a
value affecting the formula changes.manual
: Only re-calculate formulas when the user requires it. Generally
by pressing F9.auto_except_tables
: Excel will automatically re-calculate formulas
except for tables.use_zip64
()Allow ZIP64 extensions when writing the xlsx file zip container.
Use ZIP64 extensions when writing the xlsx file zip container to allow files greater than 4 GB.
Note
When using the use_zip64()
option the zip file created by the Python
standard library zipfile.py
may cause Excel to issue a warning about
repairing the file. This warning is annoying but harmless. The “repaired”
file will contain all of the data written by XlsxWriter, only the zip
container will be changed.
The worksheet class represents an Excel worksheet. It handles operations such as writing data to cells or formatting worksheet layout.
A worksheet object isn’t instantiated directly. Instead a new worksheet is
created by calling the add_worksheet()
method from a Workbook()
object:
workbook = xlsxwriter.Workbook('filename.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet1.write('A1', 123)
workbook.close()
XlsxWriter supports Excels worksheet limits of 1,048,576 rows by 16,384 columns.
write
(row, col, *args)Write generic data to a worksheet cell.
| Parameters: |
|
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | Other values from the called write methods. |
Excel makes a distinction between data types such as strings, numbers, blanks,
formulas and hyperlinks. To simplify the process of writing data to an
XlsxWriter file the write()
method acts as a general alias for several
more specific methods:
write_string()
write_number()
write_blank()
write_formula()
write_datetime()
write_boolean()
write_url()
The rules for handling data in write()
are as follows:
float
, int
, long
, decimal.Decimal
and
fractions.Fraction
are written using write_number()
.datetime.datetime
, datetime.date
datetime.time
or datetime.timedelta
are written using
write_datetime()
.None
and empty strings ""
are written using write_blank()
.bool
is written using write_boolean()
.Strings are then handled as follows:
"="
are take to match a formula and are written
using write_formula()
. This can be overridden, see below.write_url()
. This can be overridden, see below.Workbook()
constructor strings_to_numbers
option is
True
strings that convert to numbers using float()
are written
using write_number()
in order to avoid Excel warnings about “Numbers
Stored as Text”. See the note below.write_string()
.If none of the above types are matched the value is evaluated with float()
to see if it corresponds to a user defined float type. If it does then it is
written using write_number()
.
Finally, if none of these rules are matched then a TypeError
exception is
raised. However, it is also possible to handle additional, user defined, data
types using the add_write_handler()
method explained below and in
Writing user defined types.
Here are some examples:
worksheet.write(0, 0, 'Hello') # write_string()
worksheet.write(1, 0, 'World') # write_string()
worksheet.write(2, 0, 2) # write_number()
worksheet.write(3, 0, 3.00001) # write_number()
worksheet.write(4, 0, '=SIN(PI()/4)') # write_formula()
worksheet.write(5, 0, '') # write_blank()
worksheet.write(6, 0, None) # write_blank()
This creates a worksheet like the following:
Note
The Workbook()
constructor option takes three optional arguments
that can be used to override string handling in the write()
function.
These options are shown below with their default values:
xlsxwriter.Workbook(filename, {'strings_to_numbers': False,
'strings_to_formulas': True,
'strings_to_urls': True})
The write()
method supports two forms of notation to designate the position
of cells: Row-column notation and A1 notation:
# These are equivalent.
worksheet.write(0, 0, 'Hello')
worksheet.write('A1', 'Hello')
See Working with Cell Notation for more details.
The cell_format
parameter in the sub write
methods is used to apply
formatting to the cell. This parameter is optional but when present it should
be a valid Format object:
cell_format = workbook.add_format({'bold': True, 'italic': True})
worksheet.write(0, 0, 'Hello', cell_format) # Cell is bold and italic.
add_write_handler
(user_type, user_function)Add a callback function to the write()
method to handle user define
types.
| Parameters: |
|
|---|
As explained above, the write()
method maps basic Python types to
corresponding Excel types. If you want to write an unsupported type then you
can either avoid write()
and map the user type in your code to one of the
more specific write methods or you can extend it using the
add_write_handler()
method.
For example, say you wanted to automatically write uuid
values as
strings using write()
you would start by creating a function that takes the
uuid, converts it to a string and then writes it using write_string()
:
def write_uuid(worksheet, row, col, uuid, format=None):
string_uuid = str(uuid)
return worksheet.write_string(row, col, string_uuid, format)
You could then add a handler that matches the uuid
type and calls your
user defined function:
# match, action()
worksheet.add_write_handler(uuid.UUID, write_uuid)
Then you can use write()
without further modification:
my_uuid = uuid.uuid3(uuid.NAMESPACE_DNS, 'python.org')
# Write the UUID. This would raise a TypeError without the handler.
worksheet.write('A1', my_uuid)
Multiple callback functions can be added using add_write_handler()
but
only one callback action is allowed per type. However, it is valid to use the
same callback function for different types:
worksheet.add_write_handler(int, test_number_range)
worksheet.add_write_handler(float, test_number_range)
See Writing user defined types for more details on how this feature works and how to write callback functions, and also the following examples:
write_string
(row, col, string[, cell_format])Write a string to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | -2: String truncated to 32k characters. |
The write_string()
method writes a string to the cell specified by row
and column
:
worksheet.write_string(0, 0, 'Your text here')
worksheet.write_string('A2', 'or here')
Both row-column and A1 style notation are supported, as shown above. See Working with Cell Notation for more details.
The cell_format
parameter is used to apply formatting to the cell. This
parameter is optional but when present is should be a valid
Format object.
Unicode strings are supported in UTF-8 encoding. This generally requires that your source file in also UTF-8 encoded:
# _*_ coding: utf-8
# Python 2 example.
worksheet.write('A1', u'Some UTF-8 text')
See Example: Simple Unicode with Python 2 and Example: Simple Unicode with Python 3 for more complete examples.
Alternatively, you can read data from an encoded file, convert it to UTF-8 during reading and then write the data to an Excel file. See Example: Unicode - Polish in UTF-8 and Example: Unicode - Shift JIS.
The maximum string size supported by Excel is 32,767 characters. Strings longer
than this will be truncated by write_string()
.
Note
Even though Excel allows strings of 32,767 characters it can only display 1000 in a cell. However, all 32,767 characters are displayed in the formula bar.
write_number
(row, col, number[, cell_format])Write a number to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
The write_number()
method writes numeric types to the cell specified by
row
and column
:
worksheet.write_number(0, 0, 123456)
worksheet.write_number('A2', 2.3451)
Both row-column and A1 style notation are supported, as shown above. See Working with Cell Notation for more details.
The numeric types supported are float
, int
, long
,
decimal.Decimal
and fractions.Fraction
or anything that can
be converted via float()
.
When written to an Excel file numbers are converted to IEEE-754 64-bit double-precision floating point. This means that, in most cases, the maximum number of digits that can be stored in Excel without losing precision is 15.
Note
NAN and INF are not supported and will raise a TypeError exception.
The cell_format
parameter is used to apply formatting to the cell. This
parameter is optional but when present is should be a valid
Format object.
write_formula
(row, col, formula[, cell_format[, value]])Write a formula to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
The write_formula()
method writes a formula or function to the cell
specified by row
and column
:
worksheet.write_formula(0, 0, '=B3 + B4')
worksheet.write_formula(1, 0, '=SIN(PI()/4)')
worksheet.write_formula(2, 0, '=SUM(B1:B5)')
worksheet.write_formula('A4', '=IF(A3>1,"Yes", "No")')
worksheet.write_formula('A5', '=AVERAGE(1, 2, 3, 4)')
worksheet.write_formula('A6', '=DATEVALUE("1-Jan-2013")')
Both row-column and A1 style notation are supported, as shown above. See Working with Cell Notation for more details.
Array formulas are also supported:
worksheet.write_formula('A7', '{=SUM(A1:B1*A2:B2)}')
See also the write_array_formula()
method below.
The cell_format
parameter is used to apply formatting to the cell. This
parameter is optional but when present is should be a valid
Format object.
If required, it is also possible to specify the calculated result of the
formula using the optional value
parameter. This is occasionally
necessary when working with non-Excel applications that don’t calculate the
result of the formula:
worksheet.write('A1', '=2+2', num_format, 4)
See Formula Results for more details.
Excel stores formulas in US style formatting regardless of the Locale or Language of the Excel version:
worksheet.write_formula('A1', '=SUM(1, 2, 3)') # OK
worksheet.write_formula('A2', '=SOMME(1, 2, 3)') # French. Error on load.
See Non US Excel functions and syntax for a full explanation.
Excel 2010 and 2013 added functions which weren’t defined in the original file
specification. These functions are referred to as future functions. Examples
of these functions are ACOT
, CHISQ.DIST.RT
, CONFIDENCE.NORM
,
STDEV.P
, STDEV.S
and WORKDAY.INTL
. In XlsxWriter these require a
prefix:
worksheet.write_formula('A1', '=_xlfn.STDEV.S(B1:B10)')
See Formulas added in Excel 2010 and later for a detailed explanation and full list of functions that are affected.
write_array_formula
(first_row, first_col, last_row, last_col, formula[, cell_format[, value]])Write an array formula to a worksheet cell.
| Parameters: |
|
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
The write_array_formula()
method writes an array formula to a cell range. In
Excel an array formula is a formula that performs a calculation on a set of
values. It can return a single value or a range of values.
An array formula is indicated by a pair of braces around the formula:
{=SUM(A1:B1*A2:B2)}
.
For array formulas that return a range of values you must specify the range that the return values will be written to:
worksheet.write_array_formula(0, 0, 2, 0, '{=TREND(C1:C3,B1:B3)}')
worksheet.write_array_formula('A1:A3', '{=TREND(C1:C3,B1:B3)}')
Both row-column and A1 style notation are supported, as shown above. See Working with Cell Notation for more details.
If the array formula returns a single value then the first_
and last_
parameters should be the same:
worksheet.write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}')
It this case however it is easier to just use the write_formula()
or
write()
methods:
# Same as above but more concise.
worksheet.write('A1', '{=SUM(B1:C1*B2:C2)}')
worksheet.write_formula('A1', '{=SUM(B1:C1*B2:C2)}')
The cell_format
parameter is used to apply formatting to the cell. This
parameter is optional but when present is should be a valid
Format object.
If required, it is also possible to specify the calculated result of the
formula (see discussion of formulas and the value
parameter for the
write_formula()
method above). However, using this parameter only writes a
single value to the upper left cell in the result array. See
Formula Results for more details.
See also Example: Array formulas.
write_blank
(row, col, blank[, cell_format])Write a blank worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
Write a blank cell specified by row
and column
:
worksheet.write_blank(0, 0, None, cell_format)
worksheet.write_blank('A2', None, cell_format)
Both row-column and A1 style notation are supported, as shown above. See Working with Cell Notation for more details.
This method is used to add formatting to a cell which doesn’t contain a string or number value.
Excel differentiates between an “Empty” cell and a “Blank” cell. An “Empty” cell is a cell which doesn’t contain data or formatting whilst a “Blank” cell doesn’t contain data but does contain formatting. Excel stores “Blank” cells but ignores “Empty” cells.
As such, if you write an empty cell without formatting it is ignored:
worksheet.write('A1', None, cell_format) # write_blank()
worksheet.write('A2', None) # Ignored
This seemingly uninteresting fact means that you can write arrays of data
without special treatment for None
or empty string values.
write_boolean
(row, col, boolean[, cell_format])Write a boolean value to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
The write_boolean()
method writes a boolean value to the cell specified by
row
and column
:
worksheet.write_boolean(0, 0, True)
worksheet.write_boolean('A2', False)
Both row-column and A1 style notation are supported, as shown above. See Working with Cell Notation for more details.
The cell_format
parameter is used to apply formatting to the cell. This
parameter is optional but when present is should be a valid
Format object.
write_datetime
(row, col, datetime[, cell_format])Write a date or time to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
The write_datetime()
method can be used to write a date or time to the cell
specified by row
and column
:
worksheet.write_datetime(0, 0, datetime, date_format)
worksheet.write_datetime('A2', datetime, date_format)
Both row-column and A1 style notation are supported, as shown above. See Working with Cell Notation for more details.
The datetime should be a datetime.datetime
, datetime.date
datetime.time
or datetime.timedelta
object. The
datetime
class is part of the standard Python libraries.
There are many ways to create datetime objects, for example the
datetime.datetime.strptime()
method:
date_time = datetime.datetime.strptime('2013-01-23', '%Y-%m-%d')
See the datetime
documentation for other date/time creation methods.
A date/time should have a cell_format
of type Format,
otherwise it will appear as a number:
date_format = workbook.add_format({'num_format': 'd mmmm yyyy'})
worksheet.write_datetime('A1', date_time, date_format)
If required, a default date format string can be set using the Workbook()
constructor default_date_format
option.
See Working with Dates and Time for more details and also Timezone Handling in XlsxWriter.
write_url
(row, col, url[, cell_format[, string[, tip]]])Write a hyperlink to a worksheet cell.
| Parameters: |
|
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | -2: String longer than 32k characters. |
| Returns: | -3: Url longer than Excel limit of 2079 characters. |
| Returns: | -4: Exceeds Excel limit of 65,530 urls per worksheet. |
The write_url()
method is used to write a hyperlink in a worksheet cell.
The url is comprised of two elements: the displayed string and the
non-displayed link. The displayed string is the same as the link unless an
alternative string is specified:
worksheet.write_url(0, 0, 'https://www.python.org/')
worksheet.write_url('A2', 'https://www.python.org/')
Both row-column and A1 style notation are supported, as shown above. See Working with Cell Notation for more details.
The cell_format
parameter is used to apply formatting to the cell. This
parameter is optional and the default Excel hyperlink style will be used if it
isn’t specified. If required you can access the default url format using the
Workbook get_default_url_format()
method:
url_format = workbook.get_default_url_format()
Four web style URI’s are supported: http://
, https://
, ftp://
and
mailto:
:
worksheet.write_url('A1', 'ftp://www.python.org/')
worksheet.write_url('A2', 'https://www.python.org/')
worksheet.write_url('A3'<EMAIL_ADDRESS>All of the these URI types are recognized by the write()
method, so the
following are equivalent:
worksheet.write_url('A2', 'https://www.python.org/')
worksheet.write ('A2', 'https://www.python.org/') # Same.
You can display an alternative string using the string
parameter:
worksheet.write_url('A1', 'https://www.python.org', string='Python home')
Note
If you wish to have some other cell data such as a number or a formula you
can overwrite the cell using another call to write_*()
:
worksheet.write_url('A1', 'https://www.python.org/')
# Overwrite the URL string with a formula. The cell will still be a link.
# Note the use of the default url format for consistency with other links.
url_format = workbook.get_default_url_format()
worksheet.write_formula('A1', '=1+1', url_format)
There are two local URIs supported: internal:
and external:
. These are
used for hyperlinks to internal worksheet references or external workbook and
worksheet references:
# Link to a cell on the current worksheet.
worksheet.write_url('A1', 'internal:Sheet2!A1')
# Link to a cell on another worksheet.
worksheet.write_url('A2', 'internal:Sheet2!A1:B2')
# Worksheet names with spaces should be single quoted like in Excel.
worksheet.write_url('A3', "internal:'Sales Data'!A1")
# Link to another Excel workbook.
worksheet.write_url('A4', r'external:c:\temp\foo.xlsx')
# Link to a worksheet cell in another workbook.
worksheet.write_url('A5', r'external:c:\foo.xlsx#Sheet2!A1')
# Link to a worksheet in another workbook with a relative link.
worksheet.write_url('A7', r'external:..\foo.xlsx#Sheet2!A1')
# Link to a worksheet in another workbook with a network link.
worksheet.write_url('A8', r'external:\\NET\share\foo.xlsx')
Worksheet references are typically of the form Sheet1!A1
. You can also link
to a worksheet range using the standard Excel notation: Sheet1!A1:B2
.
In external links the workbook and worksheet name must be separated by the
#
character: external:Workbook.xlsx#Sheet1!A1'
.
You can also link to a named range in the target worksheet. For example say you
have a named range called my_name
in the workbook c:\temp\foo.xlsx
you
could link to it as follows:
worksheet.write_url('A14', r'external:c:\temp\foo.xlsx#my_name')
Excel requires that worksheet names containing spaces or non alphanumeric
characters are single quoted as follows 'Sales Data'!A1
.
Links to network files are also supported. Network files normally begin with
two back slashes as follows \\NETWORK\etc
. In order to generate this in a
single or double quoted string you will have to escape the backslashes,
'\\\\NETWORK\\etc'
or use a raw string r'\\NETWORK\etc'
.
Alternatively, you can avoid most of these quoting problems by using forward slashes. These are translated internally to backslashes:
worksheet.write_url('A14', "external:c:/temp/foo.xlsx")
worksheet.write_url('A15', 'external://NETWORK/share/foo.xlsx')
See also Example: Adding hyperlinks.
Note
XlsxWriter will escape the following characters in URLs as required
by Excel: \s " < > \ [ ] ` ^ { }
unless the URL already contains %xx
style escapes. In which case it is assumed that the URL was escaped
correctly by the user and will by passed directly to Excel.
Note
Versions of Excel prior to Excel 2015 limited hyperlink links and
anchor/locations to 255 characters each. Versions after that support urls
up to 2079 characters. XlsxWriter versions >= 1.2.3 support this longer
limit by default. However, a lower or user defined limit can be set via
the max_url_length
property in the Workbook()
constructor.
write_rich_string
(row, col, *string_parts[, cell_format])Write a “rich” string with multiple formats to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | -2: String longer than 32k characters. |
| Returns: | -3: 2 consecutive formats used. |
| Returns: | -4: Empty string used. |
| Returns: | -5: Insufficient parameters. |
The write_rich_string()
method is used to write strings with multiple
formats. For example to write the string “This is bold and this is
italic” you would use the following:
bold = workbook.add_format({'bold': True})
italic = workbook.add_format({'italic': True})
worksheet.write_rich_string('A1',
'This is ',
bold, 'bold',
' and this is ',
italic, 'italic')
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.write_rich_string(0, 0, 'This is ', bold, 'bold')
worksheet.write_rich_string('A1', 'This is ', bold, 'bold')
See Working with Cell Notation for more details.
The basic rule is to break the string into fragments and put a
Format
object before the fragment that you want to format.
For example:
# Unformatted string.
'This is an example string'
# Break it into fragments.
'This is an ', 'example', ' string'
# Add formatting before the fragments you want formatted.
'This is an ', format, 'example', ' string'
# In XlsxWriter.
worksheet.write_rich_string('A1',
'This is an ', format, 'example', ' string')
String fragments that don’t have a format are given a default format. So for example when writing the string “Some bold text” you would use the first example below but it would be equivalent to the second:
# Some bold format and a default format.
bold = workbook.add_format({'bold': True})
default = workbook.add_format()
# With default formatting:
worksheet.write_rich_string('A1',
'Some ',
bold, 'bold',
' text')
# Or more explicitly:
worksheet.write_rich_string('A1',
default, 'Some ',
bold, 'bold',
default, ' text')
If you have formats and segments in a list you can add them like this, using the standard Python list unpacking syntax:
segments = ['This is ', bold, 'bold', ' and this is ', blue, 'blue']
worksheet.write_rich_string('A9', *segments)
In Excel only the font properties of the format such as font name, style, size, underline, color and effects are applied to the string fragments in a rich string. Other features such as border, background, text wrap and alignment must be applied to the cell.
The write_rich_string()
method allows you to do this by using the last
argument as a cell format (if it is a format object). The following example
centers a rich string in the cell:
bold = workbook.add_format({'bold': True})
center = workbook.add_format({'align': 'center'})
worksheet.write_rich_string('A5',
'Some ',
bold, 'bold text',
' centered',
center)
Note
Excel doesn’t allow the use of two consecutive formats in a rich string or
an empty string fragment. For either of these conditions a warning is
raised and the input to write_rich_string()
is ignored.
See also Example: Writing “Rich” strings with multiple formats and Example: Merging Cells with a Rich String.
write_row
(row, col, data[, cell_format])Write a row of data starting from (row, col).
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | Other: Error return value of the |
The write_row()
method can be used to write a list of data in one go. This
is useful for converting the results of a database query into an Excel
worksheet. The write()
method is called for each element of the data.
For example:
# Some sample data.
data = ('Foo', 'Bar', 'Baz')
# Write the data to a sequence of cells.
worksheet.write_row('A1', data)
# The above example is equivalent to:
worksheet.write('A1', data[0])
worksheet.write('B1', data[1])
worksheet.write('C1', data[2])
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.write_row(0, 0, data)
worksheet.write_row('A1', data)
See Working with Cell Notation for more details.
write_column
(row, col, data[, cell_format])Write a column of data starting from (row, col).
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | Other: Error return value of the |
The write_column()
method can be used to write a list of data in one go.
This is useful for converting the results of a database query into an Excel
worksheet. The write()
method is called for each element of the data.
For example:
# Some sample data.
data = ('Foo', 'Bar', 'Baz')
# Write the data to a sequence of cells.
worksheet.write_column('A1', data)
# The above example is equivalent to:
worksheet.write('A1', data[0])
worksheet.write('A2', data[1])
worksheet.write('A3', data[2])
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.write_column(0, 0, data)
worksheet.write_column('A1', data)
See Working with Cell Notation for more details.
set_row
(row, height, cell_format, options)Set properties for a row of cells.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row is out of worksheet bounds. |
The set_row()
method is used to change the default properties of a row. The
most common use for this method is to change the height of a row:
worksheet.set_row(0, 20) # Set the height of Row 1 to 20.
The other common use for set_row()
is to set the Format for
all cells in the row:
cell_format = workbook.add_format({'bold': True})
worksheet.set_row(0, 20, cell_format)
If you wish to set the format of a row without changing the height you can pass
None
as the height parameter or use the default row height of 15:
worksheet.set_row(1, None, cell_format)
worksheet.set_row(1, 15, cell_format) # Same as above.
The cell_format
parameter will be applied to any cells in the row that
don’t have a format. As with Excel it is overridden by an explicit cell
format. For example:
worksheet.set_row(0, None, format1) # Row 1 has format1.
worksheet.write('A1', 'Hello') # Cell A1 defaults to format1.
worksheet.write('B1', 'Hello', format2) # Cell B1 keeps format2.
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 'hidden'
option is used to hide a row. This can be used, for example,
to hide intermediary steps in a complicated calculation:
worksheet.set_row(0, 20, cell_format, {'hidden': True})
The 'level'
parameter is used to set the outline level of the row. Outlines
are described in Working with Outlines and Grouping. Adjacent rows with the same outline level
are grouped together into a single outline.
The following example sets an outline level of 1 for some rows:
worksheet.set_row(0, None, None, {'level': 1})
worksheet.set_row(1, None, None, {'level': 1})
worksheet.set_row(2, None, None, {'level': 1})
Excel allows up to 7 outline levels. The 'level'
parameter should be in the
range 0 <= level <= 7
.
The 'hidden'
parameter can also be used to hide collapsed outlined rows
when used in conjunction with the 'level'
parameter:
worksheet.set_row(1, None, None, {'hidden': 1, 'level': 1})
worksheet.set_row(2, None, None, {'hidden': 1, 'level': 1})
The 'collapsed'
parameter is used in collapsed outlines to indicate which
row has the collapsed '+'
symbol:
worksheet.set_row(3, None, None, {'collapsed': 1})
set_column
(first_col, last_col, width, cell_format, options)Set properties for one or more columns of cells.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Column is out of worksheet bounds. |
The set_column()
method can be used to change the default properties of a
single column or a range of columns:
worksheet.set_column(1, 3, 30) # Width of columns B:D set to 30.
If set_column()
is applied to a single column the value of first_col
and last_col
should be the same:
worksheet.set_column(1, 1, 30) # Width of column B set to 30.
It is also possible, and generally clearer, to specify a column range using the form of A1 notation used for columns. See Working with Cell Notation for more details.
Examples:
worksheet.set_column(0, 0, 20) # Column A width set to 20.
worksheet.set_column(1, 3, 30) # Columns B-D width set to 30.
worksheet.set_column('E:E', 20) # Column E width set to 20.
worksheet.set_column('F:H', 30) # Columns F-H width set to 30.
Ranges cannot overlap. Each unique contiguous range should be specified separately:
# This won't work.
worksheet.set_column('A:D', 50)
worksheet.set_column('C:C', 10)
# It needs to be split into non-overlapping regions.
worksheet.set_column('A:B', 50)
worksheet.set_column('C:C', 10)
worksheet.set_column('D:E', 50)
The width
parameter sets the column width in the same units used by Excel
which is: the number of characters in the default font. The default width is
8.43 in the default font of Calibri 11. The actual relationship between a
string width and a column width in Excel is complex. See the following
explanation of column widths
from the Microsoft support documentation for more details.
There is no way to specify “AutoFit” for a column in the Excel file format. This feature is only available at runtime from within Excel. It is possible to simulate “AutoFit” in your application by tracking the maximum width of the data in the column as your write it and then adjusting the column width at the end.
As usual the cell_format
Format parameter is optional. If
you wish to set the format without changing the width you can pass None
as
the width parameter:
cell_format = workbook.add_format({'bold': True})
worksheet.set_column(0, 0, None, cell_format)
The cell_format
parameter will be applied to any cells in the column that
don’t have a format. For example:
worksheet.set_column('A:A', None, format1) # Col 1 has format1.
worksheet.write('A1', 'Hello') # Cell A1 defaults to format1.
worksheet.write('A2', 'Hello', format2) # Cell A2 keeps format2.
A row format takes precedence over a default column format:
worksheet.set_row(0, None, format1) # Set format for row 1.
worksheet.set_column('A:A', None, format2) # Set format for col 1.
worksheet.write('A1', 'Hello') # Defaults to format1
worksheet.write('A2', 'Hello') # Defaults to format2
The options
parameter is a dictionary with the following possible keys:
'hidden'
'level'
'collapsed'
Options can be set as follows:
worksheet.set_column('D:D', 20, cell_format, {'hidden': 1})
# Or use defaults for other properties and set the options only.
worksheet.set_column('E:E', None, None, {'hidden': 1})
The 'hidden'
option is used to hide a column. This can be used, for
example, to hide intermediary steps in a complicated calculation:
worksheet.set_column('D:D', 20, cell_format, {'hidden': 1})
The 'level'
parameter is used to set the outline level of the column.
Outlines are described in Working with Outlines and Grouping. Adjacent columns with the same
outline level are grouped together into a single outline.
The following example sets an outline level of 1 for columns B to G:
worksheet.set_column('B:G', None, None, {'level': 1})
Excel allows up to 7 outline levels. The 'level'
parameter should be in the
range 0 <= level <= 7
.
The 'hidden'
parameter can also be used to hide collapsed outlined columns
when used in conjunction with the 'level'
parameter:
worksheet.set_column('B:G', None, None, {'hidden': 1, 'level': 1})
The 'collapsed'
parameter is used in collapsed outlines to indicate which
column has the collapsed '+'
symbol:
worksheet.set_column('H:H', None, None, {'collapsed': 1})
insert_image
(row, col, image[, options])Insert an image in a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
This method can be used to insert a image into a worksheet. The image can be in PNG, JPEG, BMP, WMF or EMF format (see the notes about BMP and EMF below):
worksheet.insert_image('B2', 'python.png')
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.insert_image(1, 1, 'python.png')
worksheet.insert_image('B2', 'python.png')
See Working with Cell Notation for more details.
A file path can be specified with the image name:
worksheet1.insert_image('B10', '../images/python.png')
worksheet2.insert_image('B20', r'c:\images\python.png')
The insert_image()
method takes optional parameters in a dictionary to
position and scale the image. The available parameters with their default
values are:
{
'x_offset': 0,
'y_offset': 0,
'x_scale': 1,
'y_scale': 1,
'object_position': 2,
'image_data': None,
'url': None,
'tip': None,
}
The offset values are in pixels:
worksheet1.insert_image('B2', 'python.png', {'x_offset': 15, 'y_offset': 10})
The offsets can be greater than the width or height of the underlying cell. This can be occasionally useful if you wish to align two or more images relative to the same cell.
The x_scale
and y_scale
parameters can be used to scale the image
horizontally and vertically:
worksheet.insert_image('B3', 'python.png', {'x_scale': 0.5, 'y_scale': 0.5})
The url
parameter can used to add a hyperlink/url to the image. The tip
parameter gives an optional mouseover tooltip for images with hyperlinks:
worksheet.insert_image('B4', 'python.png', {'url': 'https://python.org'})
See also write_url()
for details on supported URIs.
The image_data
parameter is used to add an in-memory byte stream in
io.BytesIO
format:
worksheet.insert_image('B5', 'python.png', {'image_data': image_data})
This is generally used for inserting images from URLs:
url = 'https://python.org/logo.png'
image_data = io.BytesIO(urllib2.urlopen(url).read())
worksheet.insert_image('B5', url, {'image_data': image_data})
When using the image_data
parameter a filename must still be passed to
insert_image()
since it is required by Excel. In the previous example
the filename is extracted from the URL string. See also
Example: Inserting images from a URL or byte stream into a worksheet.
The object_position
parameter can be used to control the object
positioning of the image:
worksheet.insert_image('B3', 'python.png', {'object_position': 1})
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.
Note
insert_chart
(row, col, chart[, options])Write a string to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
This method can be used to insert a chart into a worksheet. A chart object is
created via the Workbook add_chart()
method where the chart type is
specified:
chart = workbook.add_chart({type, 'column'})
It is then inserted into a worksheet as an embedded chart:
worksheet.insert_chart('B5', chart)
Note
A chart can only be inserted into a worksheet once. If several similar
charts are required then each one must be created separately with
add_chart()
.
See The Chart Class, Working with Charts and Chart Examples.
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.insert_chart(4, 1, chart)
worksheet.insert_chart('B5', chart)
See Working with Cell Notation for more details.
The insert_chart()
method takes optional parameters in a dictionary to
position and scale the chart. The available parameters with their default
values are:
{
'x_offset': 0,
'y_offset': 0,
'x_scale': 1,
'y_scale': 1,
'object_position': 1,
}
The offset values are in pixels:
worksheet.insert_chart('B5', chart, {'x_offset': 25, 'y_offset': 10})
The x_scale
and y_scale
parameters can be used to scale the chart
horizontally and vertically:
worksheet.insert_chart('B5', chart, {'x_scale': 0.5, 'y_scale': 0.5})
These properties can also be set via the Chart set_size()
method.
The object_position
parameter can be used to control the object
positioning of the image:
worksheet.insert_chart('B5', chart, {'object_position': 2})
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.
insert_textbox
(row, col, textbox[, options])Write a string to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
This method can be used to insert a textbox into a worksheet:
worksheet.insert_textbox('B2', 'A simple textbox with some text')
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.insert_textbox(1, 1, 'Some text')
worksheet.insert_textbox('B2', 'Some text')
See Working with Cell Notation for more details.
The size and formatting of the textbox can be controlled via the options
dict:
# 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 more detail in the Working with Textboxes section.
See also Example: Insert Textboxes into a Worksheet.
See Working with Object Positioning for more detailed information about the positioning and scaling of images within a worksheet.
Insert a VBA button control on a worksheet.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
The insert_button(
) method can be used to insert an Excel form button into a worksheet.
This method is generally only useful when used in conjunction with the
Workbook add_vba_project()
method to tie the button to a macro from an
embedded VBA project:
# Add the VBA project binary.
workbook.add_vba_project('./vbaProject.bin')
# Add a button tied to a macro in the VBA project.
worksheet.insert_button('B3', {'macro': 'say_hello',
'caption': 'Press Me'})
See Working with VBA Macros and Example: Adding a VBA macro to a Workbook for more details.
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.insert_button(2, 1, {'macro': 'say_hello',
'caption': 'Press Me'})
worksheet.insert_button('B3', {'macro': 'say_hello',
'caption': 'Press Me'})
See Working with Cell Notation for more details.
The insert_button()
method takes optional parameters in a dictionary to
position and scale the chart. The available parameters with their default
values are:
{
'macro': None,
'caption': 'Button 1',
'width': 64,
'height': 20.
'x_offset': 0,
'y_offset': 0,
'x_scale': 1,
'y_scale': 1,
}
The macro
option is used to set the macro that the button will invoke when
the user clicks on it. The macro should be included using the Workbook
add_vba_project()
method shown above.
The caption
is used to set the caption on the button. The default is
Button n
where n
is the button number.
The default button width
is 64 pixels which is the width of a default cell
and the default button height
is 20 pixels which is the height of a
default cell.
The offset and scale options are the same as for insert_chart()
, see above.
data_validation
(first_row, first_col, last_row, last_col, options)Write a conditional format to range of cells.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | -2: Incorrect parameter or option. |
The data_validation()
method is used to construct an Excel data validation
or to limit the user input to a dropdown list of values:
worksheet.data_validation('B3', {'validate': 'integer',
'criteria': 'between',
'minimum': 1,
'maximum': 10})
worksheet.data_validation('B13', {'validate': 'list',
'source': ['open', 'high', 'close']})
The data validation can be applied to a single cell or a range of cells. As usual you can use A1 or Row/Column notation, see Working with Cell Notation:
worksheet.data_validation(1, 1, {'validate': 'list',
'source': ['open', 'high', 'close']})
worksheet.data_validation('B2', {'validate': 'list',
'source': ['open', 'high', 'close']})
With Row/Column notation you must specify all four cells in the range:
(first_row, first_col, last_row, last_col)
. If you need to refer to a
single cell set the last_ values equal to the first_ values. With A1
notation you can refer to a single cell or a range of cells:
worksheet.data_validation(0, 0, 4, 1, {...})
worksheet.data_validation('B1', {...})
worksheet.data_validation('C1:E5', {...})
The options parameter in data_validation()
must be a dictionary containing
the parameters that describe the type and style of the data validation. There
are a lot of available options which are described in detail in a separate
section: Working with Data Validation. See also Example: Data Validation and Drop Down Lists.
conditional_format
(first_row, first_col, last_row, last_col, options)Write a conditional format to range of cells.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | -2: Incorrect parameter or option. |
The conditional_format()
method is used to add formatting to a cell or
range of cells based on user defined criteria:
worksheet.conditional_format('B3:K12', {'type': 'cell',
'criteria': '>=',
'value': 50,
'format': format1})
The conditional format can be applied to a single cell or a range of cells. As usual you can use A1 or Row/Column notation, see Working with Cell Notation:
worksheet.conditional_format(0, 0, 2, 1, {'type': 'cell',
'criteria': '>=',
'value': 50,
'format': format1})
# This is equivalent to the following:
worksheet.conditional_format('A1:B3', {'type': 'cell',
'criteria': '>=',
'value': 50,
'format': format1})
With Row/Column notation you must specify all four cells in the range:
(first_row, first_col, last_row, last_col)
. If you need to refer to a
single cell set the last_ values equal to the first_ values. With A1
notation you can refer to a single cell or a range of cells:
worksheet.conditional_format(0, 0, 4, 1, {...})
worksheet.conditional_format('B1', {...})
worksheet.conditional_format('C1:E5', {...})
The options parameter in conditional_format()
must be a dictionary
containing the parameters that describe the type and style of the conditional
format. There are a lot of available options which are described in detail in
a separate section: Working with Conditional Formatting. See also
Example: Conditional Formatting.
add_table
(first_row, first_col, last_row, last_col, options)Add an Excel table to a worksheet.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | -2: Incorrect parameter or option. |
The add_table()
method is used to group a range of cells into an Excel
Table:
worksheet.add_table('B3:F7', { ... })
This method contains a lot of parameters and is described in Working with Worksheet Tables.
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.add_table(2, 1, 6, 5, { ... })
worksheet.add_table('B3:F7', { ... })
See Working with Cell Notation for more details.
See also the examples in Example: Worksheet Tables.
Note
Tables aren’t available in XlsxWriter when Workbook()
'constant_memory'
mode is enabled.
add_sparkline
(row, col, options)Add sparklines to a worksheet.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | -2: Incorrect parameter or option. |
Sparklines are small charts that fit in a single cell and are used to show trends in data.
The add_sparkline()
worksheet method is used to add sparklines to a cell or
a range of cells:
worksheet.add_sparkline('F1', {'range': 'A1:E1'})
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.add_sparkline(0, 5, {'range': 'A1:E1'})
worksheet.add_sparkline('F1', {'range': 'A1:E1'})
See Working with Cell Notation for more details.
This method contains a lot of parameters and is described in detail in Working with Sparklines.
See also Example: Sparklines (Simple) and Example: Sparklines (Advanced).
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.
write_comment
(row, col, comment[, options])Write a comment to a worksheet cell.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | -2: String longer than 32k characters. |
The write_comment()
method is used to add a comment to a cell. A comment is
indicated in Excel by a small red triangle in the upper right-hand corner of
the cell. Moving the cursor over the red triangle will reveal the comment.
The following example shows how to add a comment to a cell:
worksheet.write('A1', 'Hello')
worksheet.write_comment('A1', 'This is a comment')
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.write_comment(0, 0, 'This is a comment')
worksheet.write_comment('A1', 'This is a comment')
See Working with Cell Notation for more details.
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})
Most of these options are quite specific and in general the default comment behavior will be all that you need. However, should you need greater control over the format of the cell comment 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
For more details see Working with Cell Comments and Example: Adding Cell Comments to Worksheets (Advanced) .
show_comments
()Make any comments in the worksheet visible.
This method is used to make all cell comments visible when a worksheet is opened:
worksheet.show_comments()
Individual comments can be made visible using the visible
parameter of the
write_comment
method (see above):
worksheet.write_comment('C3', 'Hello', {'visible': True})
If all of the cell comments have been made visible you can hide individual comments as follows:
worksheet.show_comments()
worksheet.write_comment('C3', 'Hello', {'visible': False})
For more details see Working with Cell Comments and Example: Adding Cell Comments to Worksheets (Advanced) .
Set the default author of the cell comments.
| Parameters: | author (string) – Comment author. |
|---|
This method is used to set the default author of all cell comments:
worksheet.set_comments_author('John Smith')
Individual comment authors can be set using the author
parameter of the
write_comment
method (see above).
If no author is specified the default comment author name is an empty string.
For more details see Working with Cell Comments and Example: Adding Cell Comments to Worksheets (Advanced) .
get_name
()Retrieve the worksheet name.
The get_name()
method is used to retrieve the name of a worksheet. This is
something useful for debugging or logging:
for worksheet in workbook.worksheets():
print worksheet.get_name()
There is no set_name()
method. The only safe way to set the worksheet name
is via the add_worksheet()
method.
activate
()Make a worksheet the active, i.e., visible worksheet.
The activate()
method is used to specify which worksheet is initially
visible in a multi-sheet workbook:
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet3.activate()
More than one worksheet can be selected via the select()
method, see below,
however only one worksheet can be active.
The default active worksheet is the first worksheet.
select
()Set a worksheet tab as selected.
The select()
method is used to indicate that a worksheet is selected in a
multi-sheet workbook:
worksheet1.activate()
worksheet2.select()
worksheet3.select()
A selected worksheet has its tab highlighted. Selecting worksheets is a way of
grouping them together so that, for example, several worksheets could be
printed in one go. A worksheet that has been activated via the activate()
method will also appear as selected.
hide
()Hide the current worksheet.
The hide()
method is used to hide a worksheet:
worksheet2.hide()
You may wish to hide a worksheet in order to avoid confusing a user with intermediate data or calculations.
A hidden worksheet can not be activated or selected so this method is mutually
exclusive with the activate()
and select()
methods. In
addition, since the first worksheet will default to being the active
worksheet, you cannot hide the first worksheet without activating another
sheet:
worksheet2.activate()
worksheet1.hide()
See Example: Hiding Worksheets for more details.
set_first_sheet
()Set current worksheet as the first visible sheet tab.
The activate()
method determines which worksheet is initially selected.
However, if there are a large number of worksheets the selected worksheet may
not appear on the screen. To avoid this you can select which is the leftmost
visible worksheet tab using set_first_sheet()
:
for in range(1, 21):
workbook.add_worksheet
worksheet19.set_first_sheet() # First visible worksheet tab.
worksheet20.activate() # First visible worksheet.
This method is not required very often. The default value is the first worksheet.
merge_range
(first_row, first_col, last_row, last_col, data[, cell_format])Merge a range of cells.
| Parameters: | |
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
| Returns: | Other: Error return value of the called |
The merge_range()
method allows cells to be merged together so that they
act as a single area.
Excel generally merges and centers cells at same time. To get similar behavior with XlsxWriter you need to apply a Format:
merge_format = workbook.add_format({'align': 'center'})
worksheet.merge_range('B3:D4', 'Merged Cells', merge_format)
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.merge_range(2, 1, 3, 3, 'Merged Cells', merge_format)
worksheet.merge_range('B3:D4', 'Merged Cells', merge_format)
See Working with Cell Notation for more details.
It is possible to apply other formatting to the merged cells as well:
merge_format = workbook.add_format({
'bold': True,
'border': 6,
'align': 'center',
'valign': 'vcenter',
'fg_color': '#D7E4BC',
})
worksheet.merge_range('B3:D4', 'Merged Cells', merge_format)
See Example: Merging Cells for more details.
The merge_range()
method writes its data
argument using
write()
. Therefore it will handle numbers, strings and formulas as
usual. If this doesn’t handle your data correctly then you can overwrite the
first cell with a call to one of the other
write_*()
methods using the same Format as in the merged cells. See Example: Merging Cells with a Rich String.
Note
Merged ranges generally don’t work in XlsxWriter when Workbook()
'constant_memory'
mode is enabled.
autofilter
(first_row, first_col, last_row, last_col)Set the autofilter area in the worksheet.
| Parameters: |
|---|
The autofilter()
method allows an autofilter to be added to a worksheet. 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.
To add an autofilter to a worksheet:
worksheet.autofilter('A1:D11')
Both row-column and A1 style notation are supported. The following are equivalent:
worksheet.autofilter(0, 0, 10, 3)
worksheet.autofilter('A1:D11')
See Working with Cell Notation for more details.
Filter conditions can be applied using the filter_column()
or
filter_column_list()
methods.
See Working with Autofilters for more details.
filter_column
(col, criteria)Set the column filter criteria.
| Parameters: |
|
|---|
The filter_column
method can be used to filter columns in a autofilter
range based on simple conditions.
The conditions for the filter are specified using simple expressions:
worksheet.filter_column('A', 'x > 2000')
worksheet.filter_column('B', 'x > 2000 and x < 5000')
The col
parameter can either be a zero indexed column number or a string
column name:
worksheet.filter_column(2, 'x > 2000')
worksheet.filter_column('C', 'x > 2000')
See Working with Cell Notation for more details.
It isn’t sufficient to just specify the filter condition. You must also hide any rows that don’t match the filter condition. See Working with Autofilters for more details.
filter_column_list
(col, filters)Set the column filter criteria in Excel 2007 list style.
| Parameters: |
|---|
The filter_column_list()
method can be used to represent filters with
multiple selected criteria:
worksheet.filter_column_list('A', ['March', 'April', 'May'])
The col
parameter can either be a zero indexed column number or a string
column name:
worksheet.filter_column_list(2, ['March', 'April', 'May'])
worksheet.filter_column_list('C', ['March', 'April', 'May'])
See Working with Cell Notation for more details.
One or more criteria can be selected:
worksheet.filter_column_list('A', ['March'])
worksheet.filter_column_list('C', [100, 110, 120, 130])
To filter blanks as part of the list use Blanks as a list item:
worksheet.filter_column_list('A', ['March', 'April', 'May', 'Blanks'])
It isn’t sufficient to just specify filters. You must also hide any rows that don’t match the filter condition. See Working with Autofilters for more details.
set_selection
(first_row, first_col, last_row, last_col)Set the selected cell or cells in a worksheet.
| Parameters: |
|---|
The set_selection()
method can be used to specify which cell or range of
cells is selected in a worksheet. The most common requirement is to select a
single cell, in which case the first_
and last_
parameters should be
the same.
The active cell within a selected range is determined by the order in which
first_
and last_
are specified.
Examples:
worksheet1.set_selection(3, 3, 3, 3) # 1. Cell D4.
worksheet2.set_selection(3, 3, 6, 6) # 2. Cells D4 to G7.
worksheet3.set_selection(6, 6, 3, 3) # 3. Cells G7 to D4.
worksheet4.set_selection('D4') # Same as 1.
worksheet5.set_selection('D4:G7') # Same as 2.
worksheet6.set_selection('G7:D4') # Same as 3.
As shown above, both row-column and A1 style notation are supported. See
Working with Cell Notation for more details. The default cell selection is
(0, 0)
, 'A1'
.
freeze_panes
(row, col[, top_row, left_col])Create worksheet panes and mark them as frozen.
| Parameters: |
|---|
This freeze_panes
method can be used to divide a worksheet into horizontal
or vertical regions known as panes and to “freeze” these panes so that the
splitter bars are not visible.
The parameters row
and col
are used to specify the location of the
split. It should be noted that the split is specified at the top or left of a
cell and that the method uses zero based indexing. Therefore to freeze the
first row of a worksheet it is necessary to specify the split at row 2 (which
is 1 as the zero-based index).
You can set one of the row
and col
parameters as zero if you do not
want either a vertical or horizontal split.
Examples:
worksheet.freeze_panes(1, 0) # Freeze the first row.
worksheet.freeze_panes('A2') # Same using A1 notation.
worksheet.freeze_panes(0, 1) # Freeze the first column.
worksheet.freeze_panes('B1') # Same using A1 notation.
worksheet.freeze_panes(1, 2) # Freeze first row and first 2 columns.
worksheet.freeze_panes('C2') # Same using A1 notation.
As shown above, both row-column and A1 style notation are supported. See Working with Cell Notation for more details.
The parameters top_row
and left_col
are optional. They are used to
specify the top-most or left-most visible row or column in the scrolling
region of the panes. For example to freeze the first row and to have the
scrolling region begin at row twenty:
worksheet.freeze_panes(1, 0, 20, 0)
You cannot use A1 notation for the top_row
and left_col
parameters.
See Example: Freeze Panes and Split Panes for more details.
split_panes
(x, y[, top_row, left_col])Create worksheet panes and mark them as split.
| Parameters: |
|---|
The split_panes
method can be used to divide a worksheet into horizontal
or vertical regions known as panes. This method is different from the
freeze_panes()
method in that the splits between the panes will be visible
to the user and each pane will have its own scroll bars.
The parameters y
and x
are used to specify the vertical and horizontal
position of the split. The units for y
and x
are the same as those
used by Excel to specify row height and column width. However, the vertical
and horizontal units are different from each other. Therefore you must specify
the y
and x
parameters in terms of the row heights and column widths
that you have set or the default values which are 15
for a row and
8.43
for a column.
You can set one of the y
and x
parameters as zero if you do not want
either a vertical or horizontal split. The parameters top_row
and
left_col
are optional. They are used to specify the top-most or left-most
visible row or column in the bottom-right pane.
Example:
worksheet.split_panes(15, 0) # First row.
worksheet.split_panes(0, 8.43) # First column.
worksheet.split_panes(15, 8.43) # First row and column.
You cannot use A1 notation with this method.
See Example: Freeze Panes and Split Panes for more details.
set_zoom
(zoom)Set the worksheet zoom factor.
| Parameters: | zoom (int) – Worksheet zoom factor. |
|---|
Set the worksheet zoom factor in the range 10 <= zoom <= 400
:
worksheet1.set_zoom(50)
worksheet2.set_zoom(75)
worksheet3.set_zoom(300)
worksheet4.set_zoom(400)
The default zoom factor is 100. It isn’t possible to set the zoom to “Selection” because it is calculated by Excel at run-time.
Note, set_zoom()
does not affect the scale of the printed page. For that
you should use set_print_scale()
.
right_to_left
()Display the worksheet cells from right to left for some versions of Excel.
The right_to_left()
method is used to change the default direction of the
worksheet from left-to-right, with the A1 cell in the top left, to
right-to-left, with the A1 cell in the top right:
worksheet.right_to_left()
This is useful when creating Arabic, Hebrew or other near or far eastern worksheets that use right-to-left as the default direction.
See also the Format set_reading_order()
property to set the direction of the
text withing cells and the Example: Left to Right worksheets and text example program.
hide_zero
()Hide zero values in worksheet cells.
The hide_zero()
method is used to hide any zero values that appear in
cells:
worksheet.hide_zero()
set_tab_color
()Set the color of the worksheet tab.
| Parameters: | color (string) – The tab color. |
|---|
The set_tab_color()
method is used to change the color of the worksheet
tab:
worksheet1.set_tab_color('red')
worksheet2.set_tab_color('#FF9900') # Orange
The color can be a Html style #RRGGBB
string or a limited number named
colors, see Working with Colors.
See Example: Setting Worksheet Tab Colors for more details.
protect
()Protect elements of a worksheet from modification.
| Parameters: |
|
|---|
The protect()
method is used to protect a worksheet from modification:
worksheet.protect()
The protect()
method also has the effect of enabling a cell’s locked
and hidden
properties if they have been set. A locked cell cannot be
edited and this property is on by default for all cells. A hidden cell will
display the results of a formula but not the formula itself. These properties
can be set using the set_locked()
and set_hidden()
format methods.
You can optionally add a password to the worksheet protection:
worksheet.protect('abc123')
Passing the empty string ''
is the same as turning on protection without a
password.
You can specify which worksheet elements you wish to protect by passing a
dictionary in the options
argument with any or all of the following keys:
# Default values shown.
options = {
'objects': False,
'scenarios': False,
'format_cells': False,
'format_columns': False,
'format_rows': False,
'insert_columns': False,
'insert_rows': False,
'insert_hyperlinks': False,
'delete_columns': False,
'delete_rows': False,
'select_locked_cells': True,
'sort': False,
'autofilter': False,
'pivot_tables': False,
'select_unlocked_cells': True,
}
The default boolean values are shown above. Individual elements can be protected as follows:
worksheet.protect('abc123', {'insert_rows': True})
For chartsheets the allowable options and default values are:
options = {
'objects': True,
'content': True,
}
See also the set_locked()
and set_hidden()
format methods and
Example: Enabling Cell protection in Worksheets.
Note
Worksheet level passwords in Excel offer very weak protection. They do not
encrypt your data and are very easy to deactivate. Full workbook encryption
is not supported by XlsxWriter
. However, it is possible to encrypt an
XlsxWriter file using a third party open source tool called msoffice-crypt. This works for macOS, Linux and
Windows:
msoffice-crypt.exe -e -p password clear.xlsx encrypted.xlsx
set_default_row
(height, hide_unused_rows)Set the default row properties.
| Parameters: |
|---|
The set_default_row()
method is used to set the limited number of default
row properties allowed by Excel which are the default height and the option to
hide unused rows. These parameters are an optimization used by Excel to set
row properties without generating a very large file with an entry for each row.
To set the default row height:
worksheet.set_default_row(24)
To hide unused rows:
worksheet.set_default_row(hide_unused_rows=True)
See Example: Hiding Rows and Columns for more details.
outline_settings
(visible, symbols_below, symbols_right, auto_style)Control outline settings.
| Parameters: |
|
|---|
The outline_settings()
method is used to control the appearance of outlines
in Excel. Outlines are described in Working with Outlines and Grouping:
worksheet1.outline_settings(False, False, False, True)
The 'visible'
parameter is used to control whether or not outlines are
visible. Setting this parameter to False
will cause all outlines on the
worksheet to be hidden. They can be un-hidden in Excel by means of the “Show
Outline Symbols” command button. The default setting is True
for visible
outlines.
The 'symbols_below'
parameter is used to control whether the row outline
symbol will appear above or below the outline level bar. The default setting
is True
for symbols to appear below the outline level bar.
The 'symbols_right'
parameter is used to control whether the column outline
symbol will appear to the left or the right of the outline level bar. The
default setting is True
for symbols to appear to the right of the outline
level bar.
The 'auto_style'
parameter is used to control whether the automatic outline
generator in Excel uses automatic styles when creating an outline. This has no
effect on a file generated by XlsxWriter
but it does have an effect on how
the worksheet behaves after it is created. The default setting is False
for “Automatic Styles” to be turned off.
The default settings for all of these parameters correspond to Excel’s default parameters.
The worksheet parameters controlled by outline_settings()
are rarely used.
set_vba_name
(name)Set the VBA name for the worksheet.
| Parameters: | name (string) – The VBA name for the worksheet. |
|---|
The set_vba_name()
method can be used to set the VBA codename for the
worksheet (there is a similar method for the workbook VBA name). This is
sometimes required when a vbaProject macro included via add_vba_project()
refers to the worksheet. The default Excel VBA name of Sheet1
, etc., is
used if a user defined name isn’t specified.
See Working with VBA Macros for more details.
Page set-up methods affect the way that a worksheet looks to the user or when it is printed. They control features such as paper size, orientation, page headers and margins and gridlines.
These methods are really just standard worksheet methods. They are documented separately for the sake of clarity.
set_landscape
()Set the page orientation as landscape.
This method is used to set the orientation of a worksheet’s printed page to landscape:
worksheet.set_landscape()
set_portrait
()Set the page orientation as portrait.
This method is used to set the orientation of a worksheet’s printed page to portrait. The default worksheet orientation is portrait, so you won’t generally need to call this method:
worksheet.set_portrait()
set_page_view
()Set the page view mode.
This method is used to display the worksheet in “Page View/Layout” mode:
worksheet.set_page_view()
set_paper
(index)Set the paper type.
| Parameters: | index (int) – The Excel paper format index. |
|---|
This method is used to set the paper format for the printed output of a worksheet. The following paper styles are available:
| Index | Paper format | Paper size |
|---|---|---|
| 0 | Printer default | Printer default |
| 1 | Letter | 8 1/2 x 11 in |
| 2 | Letter Small | 8 1/2 x 11 in |
| 3 | Tabloid | 11 x 17 in |
| 4 | Ledger | 17 x 11 in |
| 5 | Legal | 8 1/2 x 14 in |
| 6 | Statement | 5 1/2 x 8 1/2 in |
| 7 | Executive | 7 1/4 x 10 1/2 in |
| 8 | A3 | 297 x 420 mm |
| 9 | A4 | 210 x 297 mm |
| 10 | A4 Small | 210 x 297 mm |
| 11 | A5 | 148 x 210 mm |
| 12 | B4 | 250 x 354 mm |
| 13 | B5 | 182 x 257 mm |
| 14 | Folio | 8 1/2 x 13 in |
| 15 | Quarto | 215 x 275 mm |
| 16 | — | 10x14 in |
| 17 | — | 11x17 in |
| 18 | Note | 8 1/2 x 11 in |
| 19 | Envelope 9 | 3 7/8 x 8 7/8 |
| 20 | Envelope 10 | 4 1/8 x 9 1/2 |
| 21 | Envelope 11 | 4 1/2 x 10 3/8 |
| 22 | Envelope 12 | 4 3/4 x 11 |
| 23 | Envelope 14 | 5 x 11 1/2 |
| 24 | C size sheet | — |
| 25 | D size sheet | — |
| 26 | E size sheet | — |
| 27 | Envelope DL | 110 x 220 mm |
| 28 | Envelope C3 | 324 x 458 mm |
| 29 | Envelope C4 | 229 x 324 mm |
| 30 | Envelope C5 | 162 x 229 mm |
| 31 | Envelope C6 | 114 x 162 mm |
| 32 | Envelope C65 | 114 x 229 mm |
| 33 | Envelope B4 | 250 x 353 mm |
| 34 | Envelope B5 | 176 x 250 mm |
| 35 | Envelope B6 | 176 x 125 mm |
| 36 | Envelope | 110 x 230 mm |
| 37 | Monarch | 3.875 x 7.5 in |
| 38 | Envelope | 3 5/8 x 6 1/2 in |
| 39 | Fanfold | 14 7/8 x 11 in |
| 40 | German Std Fanfold | 8 1/2 x 12 in |
| 41 | German Legal Fanfold | 8 1/2 x 13 in |
Note, it is likely that not all of these paper types will be available to the end user since it will depend on the paper formats that the user’s printer supports. Therefore, it is best to stick to standard paper types:
worksheet.set_paper(1) # US Letter
worksheet.set_paper(9) # A4
If you do not specify a paper type the worksheet will print using the printer’s default paper style.
center_horizontally
()Center the printed page horizontally.
Center the worksheet data horizontally between the margins on the printed page:
worksheet.center_horizontally()
center_vertically
()Center the printed page vertically.
Center the worksheet data vertically between the margins on the printed page:
worksheet.center_vertically()
set_margins
([left=0.7,] right=0.7,] top=0.75,] bottom=0.75]]])Set the worksheet margins for the printed page.
| Parameters: |
|---|
The set_margins()
method is used to set the margins of the worksheet when
it is printed. The units are in inches. All parameters are optional and have
default values corresponding to the default Excel values.
set_header
([header='',] options]])Set the printed page header caption and options.
| Parameters: |
|
|---|
Headers and footers are generated using a string which is a combination of plain text and control characters.
The available control character are:
| Control | Category | Description |
|---|---|---|
| &L | Justification | Left |
| &C | Center | |
| &R | Right | |
| &P | Information | Page number |
| &N | Total number of pages | |
| &D | Date | |
| &T | Time | |
| &F | File name | |
| &A | Worksheet name | |
| &Z | Workbook path | |
| &fontsize | Font | Font size |
| &”font,style” | Font name and style | |
| &U | Single underline | |
| &E | Double underline | |
| &S | Strikethrough | |
| &X | Superscript | |
| &Y | Subscript | |
| &[Picture] | Images | Image placeholder |
| &G | Same as &[Picture] |
Text in headers and footers can be justified (aligned) to the left, center and
right by prefixing the text with the control characters &L
, &C
and
&R
.
For example:
worksheet.set_header('&LHello')
---------------------------------------------------------------
| |
| Hello |
| |
$worksheet->set_header('&CHello');
---------------------------------------------------------------
| |
| Hello |
| |
$worksheet->set_header('&RHello');
---------------------------------------------------------------
| |
| Hello |
| |
For simple text, if you do not specify any justification the text will be
centered. However, you must prefix the text with &C
if you specify a font
name or any other formatting:
worksheet.set_header('Hello')
---------------------------------------------------------------
| |
| Hello |
| |
You can have text in each of the justification regions:
worksheet.set_header('&LCiao&CBello&RCielo')
---------------------------------------------------------------
| |
| Ciao Bello Cielo |
| |
The information control characters act as variables that Excel will update as the workbook or worksheet changes. Times and dates are in the users default format:
worksheet.set_header('&CPage &P of &N')
---------------------------------------------------------------
| |
| Page 1 of 6 |
| |
worksheet.set_header('&CUpdated at &T')
---------------------------------------------------------------
| |
| Updated at 12:30 PM |
| |
Images can be inserted using the options
shown below. Each image must
have a placeholder in header string using the &[Picture]
or &G
control characters:
worksheet.set_header('&L&G', {'image_left': 'logo.jpg'})
You can specify the font size of a section of the text by prefixing it with the
control character &n
where n
is the font size:
worksheet1.set_header('&C&30Hello Big')
worksheet2.set_header('&C&10Hello Small')
You can specify the font of a section of the text by prefixing it with the
control sequence &"font,style"
where fontname
is a font name such as
“Courier New” or “Times New Roman” and style
is one of the standard
Windows font descriptions: “Regular”, “Italic”, “Bold” or “Bold Italic”:
worksheet1.set_header('&C&"Courier New,Italic"Hello')
worksheet2.set_header('&C&"Courier New,Bold Italic"Hello')
worksheet3.set_header('&C&"Times New Roman,Regular"Hello')
It is possible to combine all of these features together to create
sophisticated headers and footers. As an aid to setting up complicated headers
and footers you can record a page set-up as a macro in Excel and look at the
format strings that VBA produces. Remember however that VBA uses two double
quotes ""
to indicate a single double quote. For the last example above
the equivalent VBA code looks like this:
.LeftHeader = ""
.CenterHeader = "&""Times New Roman,Regular""Hello"
.RightHeader = ""
Alternatively you can inspect the header and footer strings in an Excel file by unzipping it and grepping the XML sub-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` | egrep "Header|Footer"
<headerFooter scaleWithDoc="0">
<oddHeader>&L&P</oddHeader>
</headerFooter>
Note that in this case you need to unescape the Html. In the above example the header string would be:
'&L&P'
To include a single literal ampersand &
in a header or footer you should
use a double ampersand &&
:
worksheet1.set_header('&CCuriouser && Curiouser - Attorneys at Law')
The available options are:
margin
: (float) Header margin in inches. Defaults to 0.3 inch.image_left
: (string) The path to the image. Needs &G
placeholder.image_center
: (string) Same as above.image_right
: (string) Same as above.image_data_left
: (BytesIO) A byte stream of the image data.image_data_center
: (BytesIO) Same as above.image_data_right
: (BytesIO) Same as above.scale_with_doc
: (boolean) Scale header with document. Defaults to True.align_with_margins
: (boolean) Align header to margins. Defaults to True.As with the other margins the margin
value should be in inches. The
default header and footer margin is 0.3 inch. It can be changed as follows:
worksheet.set_header('&CHello', {'margin': 0.75})
The header and footer margins are independent of, and should not be confused with, the top and bottom worksheet margins.
The image options must have an accompanying &[Picture]
or &G
control
character in the header string:
worksheet.set_header('&L&[Picture]&C&[Picture]&R&[Picture]',
{'image_left': 'red.jpg',
'image_center': 'blue.jpg',
'image_right': 'yellow.jpg'})
The image_data_
parameters are used to add an in-memory byte stream in
io.BytesIO
format:
image_file = open('logo.jpg', 'rb')
image_data = BytesIO(image_file.read())
worksheet.set_header('&L&G',
{'image_left': 'logo.jpg',
'image_data_left': image_data})
When using the image_data_
parameters a filename must still be passed to
to the equivalent image_
parameter since it is required by Excel. See also
insert_image()
for details on handling images from byte streams.
Note, Excel does not allow header or footer strings longer than 255 characters, including control characters. Strings longer than this will not be written and an exception will be thrown.
repeat_rows
(first_row[, last_row])Set the number of rows to repeat at the top of each printed page.
| Parameters: |
|---|
For large Excel documents it is often desirable to have the first row or rows of the worksheet print out at the top of each page.
This can be achieved by using the repeat_rows()
method. The parameters
first_row
and last_row
are zero based. The last_row
parameter is
optional if you only wish to specify one row:
worksheet1.repeat_rows(0) # Repeat the first row.
worksheet2.repeat_rows(0, 1) # Repeat the first two rows.
repeat_columns
(first_col[, last_col])Set the columns to repeat at the left hand side of each printed page.
| Parameters: |
|---|
For large Excel documents it is often desirable to have the first column or columns of the worksheet print out at the left hand side of each page.
This can be achieved by using the repeat_columns()
method. The parameters
first_column
and last_column
are zero based. The last_column
parameter is optional if you only wish to specify one column. You can also
specify the columns using A1 column notation, see Working with Cell Notation for
more details.:
worksheet1.repeat_columns(0) # Repeat the first column.
worksheet2.repeat_columns(0, 1) # Repeat the first two columns.
worksheet3.repeat_columns('A:A') # Repeat the first column.
worksheet4.repeat_columns('A:B') # Repeat the first two columns.
hide_gridlines
([option=1])Set the option to hide gridlines on the screen and the printed page.
| Parameters: | option (int) – Hide gridline options. See below. |
|---|
This method is used to hide the gridlines on the screen and printed page. Gridlines are the lines that divide the cells on a worksheet. Screen and printed gridlines are turned on by default in an Excel worksheet.
If you have defined your own cell borders you may wish to hide the default gridlines:
worksheet.hide_gridlines()
The following values of option
are valid:
If you don’t supply an argument the default option is 1, i.e. only the printed gridlines are hidden.
print_row_col_headers
()Set the option to print the row and column headers on the printed page.
When you print a worksheet from Excel you get the data selected in the print area. By default the Excel row and column headers (the row numbers on the left and the column letters at the top) aren’t printed.
The print_row_col_headers()
method sets the printer option to print these
headers:
worksheet.print_row_col_headers()
hide_row_col_headers
()Set the option to hide the row and column headers in a worksheet.
This method is similar to the print_row_col_headers()
except that it hides
the row and column headers on the worksheet:
worksheet.hide_row_col_headers()
print_area
(first_row, first_col, last_row, last_col)Set the print area in the current worksheet.
| Parameters: |
|
|---|---|
| Returns: | 0: Success. |
| Returns: | -1: Row or column is out of worksheet bounds. |
This method is used to specify the area of the worksheet that will be printed.
All four parameters must be specified. You can also use A1 notation, see Working with Cell Notation:
worksheet1.print_area('A1:H20') # Cells A1 to H20.
worksheet2.print_area(0, 0, 19, 7) # The same as above.
In order to set a row or column range you must specify the entire range:
worksheet3.print_area('A1:H1048576') # Same as A:H.
print_across
()Set the order in which pages are printed.
The print_across
method is used to change the default print direction. This
is referred to by Excel as the sheet “page order”:
worksheet.print_across()
The default page order is shown below for a worksheet that extends over 4 pages. The order is called “down then across”:
[1] [3]
[2] [4]
However, by using the print_across
method the print order will be changed
to “across then down”:
[1] [2]
[3] [4]
fit_to_pages
(width, height)Fit the printed area to a specific number of pages both vertically and horizontally.
| Parameters: |
|---|
The fit_to_pages()
method is used to fit the printed area to a specific
number of pages both vertically and horizontally. If the printed area exceeds
the specified number of pages it will be scaled down to fit. This ensures that
the printed area will always appear on the specified number of pages even if
the page size or margins change:
worksheet1.fit_to_pages(1, 1) # Fit to 1x1 pages.
worksheet2.fit_to_pages(2, 1) # Fit to 2x1 pages.
worksheet3.fit_to_pages(1, 2) # Fit to 1x2 pages.
The print area can be defined using the print_area()
method as described
above.
A common requirement is to fit the printed output to n
pages wide but have
the height be as long as necessary. To achieve this set the height
to
zero:
worksheet1.fit_to_pages(1, 0) # 1 page wide and as long as necessary.
Note
Although it is valid to use both fit_to_pages()
and
set_print_scale()
on the same worksheet in Excel only allows one of
these options to be active at a time. The last method call made will set
the active option.
Note
The fit_to_pages()
will override any manual page breaks that are
defined in the worksheet.
Note
When using fit_to_pages()
it may also be required to set the
printer paper size using set_paper()
or else Excel will default
to “US Letter”.
set_start_page
()Set the start page number when printing.
| Parameters: | start_page (int) – Starting page number. |
|---|
The set_start_page()
method is used to set the number of the starting page
when the worksheet is printed out:
# Start print from page 2.
worksheet.set_start_page(2)
set_print_scale
()Set the scale factor for the printed page.
| Parameters: | scale (int) – Print scale of worksheet to be printed. |
|---|
Set the scale factor of the printed page. Scale factors in the range
10 <= $scale <= 400
are valid:
worksheet1.set_print_scale(50)
worksheet2.set_print_scale(75)
worksheet3.set_print_scale(300)
worksheet4.set_print_scale(400)
The default scale factor is 100. Note, set_print_scale()
does not affect
the scale of the visible page in Excel. For that you should use
set_zoom()
.
Note also that although it is valid to use both fit_to_pages()
and
set_print_scale()
on the same worksheet Excel only allows one of these
options to be active at a time. The last method call made will set the active
option.
set_h_pagebreaks
(breaks)Set the horizontal page breaks on a worksheet.
| Parameters: | breaks (list) – List of page break rows. |
|---|
The set_h_pagebreaks()
method adds horizontal page breaks to a worksheet. A
page break causes all the data that follows it to be printed on the next page.
Horizontal page breaks act between rows.
The set_h_pagebreaks()
method takes a list of one or more page breaks:
worksheet1.set_v_pagebreaks([20])
worksheet2.set_v_pagebreaks([20, 40, 60, 80, 100])
To create a page break between rows 20 and 21 you must specify the break at row 21. However in zero index notation this is actually row 20. So you can pretend for a small while that you are using 1 index notation:
worksheet.set_h_pagebreaks([20]) # Break between row 20 and 21.
Note
Note: If you specify the “fit to page” option via the fit_to_pages()
method it will override all manual page breaks.
There is a silent limitation of 1023 horizontal page breaks per worksheet in line with an Excel internal limitation.
set_v_pagebreaks
(breaks)Set the vertical page breaks on a worksheet.
| Parameters: | breaks (list) – List of page break columns. |
|---|
The set_v_pagebreaks()
method is the same as the above
set_h_pagebreaks()
method except it adds page breaks between columns.
This section describes the methods and properties that are available for formatting cells in Excel.
The properties of a cell that can be formatted include: fonts, colors, patterns, borders, alignment and number formatting.
Cell formatting is defined through a Format object. Format objects are created
by calling the workbook add_format()
method as follows:
cell_format1 = workbook.add_format() # Set properties later.
cell_format2 = workbook.add_format(props) # Set properties at creation.
There are two ways of setting Format properties: by using the object interface or by setting the property as a dictionary of key/value pairs in the constructor. For example, a typical use of the object interface would be as follows:
cell_format = workbook.add_format()
cell_format.set_bold()
cell_format.set_font_color('red')
By comparison the properties can be set by passing a dictionary of properties
to the add_format()
constructor:
cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})
In general the key/value interface is more flexible and clearer than the object method and is the recommended method for setting format properties. However, both methods produce the same result.
Once a Format object has been constructed and its properties have been set it
can be passed as an argument to the worksheet write()
methods as
follows:
worksheet.write (0, 0, 'Foo', cell_format)
worksheet.write_string(1, 0, 'Bar', cell_format)
worksheet.write_number(2, 0, 3, cell_format)
worksheet.write_blank (3, 0, '', cell_format)
Formats can also be passed to the worksheet set_row()
and
set_column()
methods to define the default formatting properties for a
row or column:
worksheet.set_row(0, 18, cell_format)
worksheet.set_column('A:D', 20, cell_format)
The default Excel 2007+ cell format is Calibri 11 with all other properties off.
In general a format method call without an argument will turn a property on, for example:
cell_format = workbook.add_format()
cell_format.set_bold() # Turns bold on.
cell_format.set_bold(True) # Also turns bold on.
Since most properties are already off by default it isn’t generally required to turn them off. However, it is possible if required:
cell_format.set_bold(False) # Turns bold off.
Each unique cell format in an XlsxWriter spreadsheet must have a corresponding
Format object. It isn’t possible to use a Format with a write()
method
and then redefine it for use at a later stage. This is because a Format is
applied to a cell not in its current state but in its final state. Consider
the following example:
cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})
worksheet.write('A1', 'Cell A1', cell_format)
# Later...
cell_format.set_font_color('green')
worksheet.write('B1', 'Cell B1', cell_format)
Cell A1 is assigned a format which initially has the font set to the color red. However, the color is subsequently set to green. When Excel displays Cell A1 it will display the final state of the Format which in this case will be the color green.
The set_num_format()
method, shown below, is used to set the number
format for numbers:
import xlsxwriter
workbook = xlsxwriter.Workbook('currency_format.xlsx')
worksheet = workbook.add_worksheet()
currency_format = workbook.add_format({'num_format': '$#,##0.00'})
worksheet.write('A1', 1234.56, currency_format)
workbook.close()
If the number format you use is the same as one of Excel’s built in number formats then it will have a number category such as General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special or Custom. In the case of the example above the formatted output shows up as a Number category:
If we wanted it to have a different category, such as Currency, then we would have to match the number format string with the number format used by Excel. The easiest way to do this is to open the Number Formatting dialog in Excel and set the format that you want:
Then, while still in the dialog, change to Custom. The format displayed is the format used by Excel.
If we put the format that we found ('[$$-409]#,##0.00'
) into our previous
example and rerun it we will get a number format in the Currency category:
import xlsxwriter
workbook = xlsxwriter.Workbook('currency_format.xlsx')
worksheet = workbook.add_worksheet()
currency_format = workbook.add_format({'num_format': '[$$-409]#,##0.00'})
worksheet.write('A1', 1234.56, currency_format)
workbook.close()
Here is the output:
The same process can be used to find format strings for Date or Accountancy formats. However, you also need to be aware of the OS settings Excel uses for number separators such as the “grouping/thousands” separator and the “decimal” point. See the next section for details.
As shown in the previous section the set_num_format()
method is used to
set the number format for Xlsxwriter formats. A common use case is to set a
number format with a “grouping/thousands” separator and a “decimal” point:
import xlsxwriter
workbook = xlsxwriter.Workbook('number_format.xlsx')
worksheet = workbook.add_worksheet()
number_format = workbook.add_format({'num_format': '#,##0.00'})
worksheet.write('A1', 1234.56, number_format)
workbook.close()
In the US locale (and some others) where the number “grouping/thousands” separator is “,” and the “decimal” point is “.” this would be shown in Excel as:
In other locales these values may be reversed or different. They are generally
set in the “Region” settings of Windows or Mac OS. Excel handles this by
storing the number format in the file format in the US locale, in this case
#,##0.00
, but renders it according to the regional settings of the host
OS. For example, here is the same, unmodified, output file shown above in a
German locale:
And here is the same file in a Russian locale. Note the use of a space as the “grouping/thousands” separator:
In order to replicate Excel’s behavior all XlsxWriter programs should use US locale formatting which will then be rendered in the settings of your host OS.
The following table shows the Excel format categories, the formatting properties that can be applied and the equivalent object method:
| Category | Description | Property | Method Name |
|---|---|---|---|
| Font | Font type | 'font_name' |
set_font_name() |
| Font size | 'font_size' |
set_font_size() |
|
| Font color | 'font_color' |
set_font_color() |
|
| Bold | 'bold' |
set_bold() |
|
| Italic | 'italic' |
set_italic() |
|
| Underline | 'underline' |
set_underline() |
|
| Strikeout | 'font_strikeout' |
set_font_strikeout() |
|
| Super/Subscript | 'font_script' |
set_font_script() |
|
| Number | Numeric format | 'num_format' |
set_num_format() |
| Protection | Lock cells | 'locked' |
set_locked() |
| Hide formulas | 'hidden' |
set_hidden() |
|
| Alignment | Horizontal align | 'align' |
set_align() |
| Vertical align | 'valign' |
set_align() |
|
| Rotation | 'rotation' |
set_rotation() |
|
| Text wrap | 'text_wrap' |
set_text_wrap() |
|
| Reading order | 'reading_order' |
set_reading_order() |
|
| Justify last | 'text_justlast' |
set_text_justlast() |
|
| Center across | 'center_across' |
set_center_across() |
|
| Indentation | 'indent' |
set_indent() |
|
| Shrink to fit | 'shrink' |
set_shrink() |
|
| Pattern | Cell pattern | 'pattern' |
set_pattern() |
| Background color | 'bg_color' |
set_bg_color() |
|
| Foreground color | 'fg_color' |
set_fg_color() |
|
| Border | Cell border | 'border' |
set_border() |
| Bottom border | 'bottom' |
set_bottom() |
|
| Top border | 'top' |
set_top() |
|
| Left border | 'left' |
set_left() |
|
| Right border | 'right' |
set_right() |
|
| Border color | 'border_color' |
set_border_color() |
|
| Bottom color | 'bottom_color' |
set_bottom_color() |
|
| Top color | 'top_color' |
set_top_color() |
|
| Left color | 'left_color' |
set_left_color() |
|
| Right color | 'right_color' |
set_right_color() |
The format properties and methods are explained in the following sections.
set_font_name
(fontname)Set the font used in the cell.
| Parameters: | fontname (string) – Cell font. |
|---|
Specify the font used used in the cell format:
cell_format.set_font_name('Times New Roman')
Excel can only display fonts that are installed on the system that it is running on. Therefore it is best to use the fonts that come as standard such as ‘Calibri’, ‘Times New Roman’ and ‘Courier New’.
The default font for an unformatted cell in Excel 2007+ is ‘Calibri’.
set_font_size
(size)Set the size of the font used in the cell.
| Parameters: | size (int) – The cell font size. |
|---|
Set the font size of the cell format:
cell_format = workbook.add_format()
cell_format.set_font_size(30)
Excel adjusts the height of a row to accommodate the largest font size in the
row. You can also explicitly specify the height of a row using the
set_row()
worksheet method.
set_font_color
(color)Set the color of the font used in the cell.
| Parameters: | color (string) – The cell font color. |
|---|
Set the font color:
cell_format = workbook.add_format()
cell_format.set_font_color('red')
worksheet.write(0, 0, 'Wheelbarrow', cell_format)
The color can be a Html style #RRGGBB
string or a limited number of named
colors, see Working with Colors.
Note: The set_font_color()
method is used to set the color of the font
in a cell. To set the color of a cell use the set_bg_color()
and
set_pattern()
methods.
set_bold
()Turn on bold for the format font.
Set the bold property of the font:
cell_format.set_bold()
set_italic
()Turn on italic for the format font.
Set the italic property of the font:
cell_format.set_italic()
set_underline
()Turn on underline for the format.
| Parameters: | style (int) – Underline style. |
|---|
Set the underline property of the format:
cell_format.set_underline()
The available underline styles are:
set_font_strikeout
()Set the strikeout property of the font.
set_font_script
()Set the superscript/subscript property of the font.
The available options are:
This property is generally only useful when used in conjunction with
write_rich_string()
.
set_num_format
(format_string)Set the number format for a cell.
| Parameters: | format_string (string) – The cell number format. |
|---|
This method is used to define the numerical format of a number in Excel. It controls whether a number is displayed as an integer, a floating point number, a date, a currency value or some other user defined format.
The numerical format of a cell can be specified by using a format string or an index to one of Excel’s built-in formats:
cell_format1 = workbook.add_format()
cell_format2 = workbook.add_format()
cell_format1.set_num_format('d mmm yyyy') # Format string.
cell_format2.set_num_format(0x0F) # Format index.
Format strings can control any aspect of number formatting allowed by Excel:
cell_format01.set_num_format('0.000')
worksheet.write(1, 0, 3.1415926, cell_format01) # -> 3.142
cell_format02.set_num_format('#,##0')
worksheet.write(2, 0, 1234.56, cell_format02) # -> 1,235
cell_format03.set_num_format('#,##0.00')
worksheet.write(3, 0, 1234.56, cell_format03) # -> 1,234.56
cell_format04.set_num_format('0.00')
worksheet.write(4, 0, 49.99, cell_format04) # -> 49.99
cell_format05.set_num_format('mm/dd/yy')
worksheet.write(5, 0, 36892.521, cell_format05) # -> 01/01/01
cell_format06.set_num_format('mmm d yyyy')
worksheet.write(6, 0, 36892.521, cell_format06) # -> Jan 1 2001
cell_format07.set_num_format('d mmmm yyyy')
worksheet.write(7, 0, 36892.521, cell_format07) # -> 1 January 2001
cell_format08.set_num_format('dd/mm/yyyy hh:mm AM/PM')
worksheet.write(8, 0, 36892.521, cell_format08) # -> 01/01/2001 12:30 AM
cell_format09.set_num_format('0 "dollar and" .00 "cents"')
worksheet.write(9, 0, 1.87, cell_format09) # -> 1 dollar and .87 cents
# Conditional numerical formatting.
cell_format10.set_num_format('[Green]General;[Red]-General;General')
worksheet.write(10, 0, 123, cell_format10) # > 0 Green
worksheet.write(11, 0, -45, cell_format10) # < 0 Red
worksheet.write(12, 0, 0, cell_format10) # = 0 Default color
# Zip code.
cell_format11.set_num_format('00000')
worksheet.write(13, 0, 1209, cell_format11)
The number system used for dates is described in Working with Dates and Time.
The color format should have one of the following values:
[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
For more information refer to the Microsoft documentation on cell formats.
For information on how to get a number format to show up as one of the number format categories such as Currency, Accounting, Date, Time, Percentage, Fraction, Scientific or Text, see Number Format Categories, above.
For backwards compatibility XlsxWriter also supports Excel’s built-in formats which are set via an index number, rather than a string:
cell_format.set_num_format(3) # Same as #,##0
The format indexes and the equivalent strings are shown in the following table:
| Index | Format String |
|---|---|
| 0 | General |
| 1 | 0 |
| 2 | 0.00 |
| 3 | #,##0 |
| 4 | #,##0.00 |
| 5 | ($#,##0_);($#,##0) |
| 6 | ($#,##0_);[Red]($#,##0) |
| 7 | ($#,##0.00_);($#,##0.00) |
| 8 | ($#,##0.00_);[Red]($#,##0.00) |
| 9 | 0% |
| 10 | 0.00% |
| 11 | 0.00E+00 |
| 12 | # ?/? |
| 13 | # ??/?? |
| 14 | m/d/yy |
| 15 | d-mmm-yy |
| 16 | d-mmm |
| 17 | mmm-yy |
| 18 | h:mm AM/PM |
| 19 | h:mm:ss AM/PM |
| 20 | h:mm |
| 21 | h:mm:ss |
| 22 | m/d/yy h:mm |
| … | … |
| 37 | (#,##0_);(#,##0) |
| 38 | (#,##0_);[Red](#,##0) |
| 39 | (#,##0.00_);(#,##0.00) |
| 40 | (#,##0.00_);[Red](#,##0.00) |
| 41 | _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) |
| 42 | _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_) |
| 43 | _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_) |
| 44 | _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) |
| 45 | mm:ss |
| 46 | [h]:mm:ss |
| 47 | mm:ss.0 |
| 48 | ##0.0E+0 |
| 49 | @ |
Numeric formats 23 to 36 are not documented by Microsoft and may differ in international versions. The listed date and currency formats may also vary depending on system settings.
The dollar sign in the above format usually appears as the defined local currency symbol. To get more locale specific formatting see see Number Format Categories, above.
set_locked
(state)Set the cell locked state.
| Parameters: | state (bool) – Turn cell locking on or off. Defaults to True. |
|---|
This property can be used to prevent modification of a cell’s contents.
Following Excel’s convention, cell locking is turned on by default. However,
it only has an effect if the worksheet has been protected using the worksheet
protect()
method:
locked = workbook.add_format()
locked.set_locked(True)
unlocked = workbook.add_format()
unlocked.set_locked(False)
# Enable worksheet protection
worksheet.protect()
# This cell cannot be edited.
worksheet.write('A1', '=1+2', locked)
# This cell can be edited.
worksheet.write('A2', '=1+2', unlocked)
set_align
(alignment)Set the alignment for data in the cell.
| Parameters: | alignment (string) – The vertical and or horizontal alignment direction. |
|---|
This method is used to set the horizontal and vertical text alignment within a cell. The following are the available horizontal alignments:
| Horizontal alignment |
|---|
| left |
| center |
| right |
| fill |
| justify |
| center_across |
| distributed |