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.
Create a new XlsxWriter Workbook object.
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:
workbook = xlsxwriter.Workbook(filename, 'constant_memory': True>)
workbook = xlsxwriter.Workbook(filename, 'tmpdir': '/home/user/tmp'>)
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 supports a filesystem with read/write access to /tmp which means this option isn’t required.
workbook = xlsxwriter.Workbook(filename, 'strings_to_numbers': True>)
workbook = xlsxwriter.Workbook(filename, 'strings_to_formulas': False>)
workbook = xlsxwriter.Workbook(filename, 'strings_to_urls': False>)
workbook = xlsxwriter.Workbook(filename, 'use_future_functions': True>)
workbook = xlsxwriter.Workbook(filename, 'max_url_length': 255>)
workbook = xlsxwriter.Workbook(filename, 'nan_inf_to_errors': True>)
xlsxwriter.Workbook(filename, 'default_date_format': 'dd/mm/yy'>)
workbook = xlsxwriter.Workbook(filename, 'remove_timezone': True>)
workbook = xlsxwriter.Workbook(filename, 'use_zip64': True>) # Same as: workbook = xlsxwriter.Workbook(filename) workbook.use_zip64()
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.
Add a new worksheet to a workbook.
name (string) – Optional worksheet name, defaults to Sheet1, etc.
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:
The rules for worksheet names in Excel are explained in the Microsoft Office documentation on how to Rename a worksheet.
Create a new Format object to formats cells in worksheets.
properties (dictionary) – An optional dictionary of format properties.
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.
Create a chart object that can be added to a worksheet.
options (dictionary) – An dictionary of chart type options.
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) name (optional)
chart = workbook.add_chart('type': 'line'>)The available types are:
area bar column doughnut line pie radar scatter stock
workbook.add_chart('type': 'bar', 'subtype': 'stacked'>)
chart = workbook.add_chart('type': 'column', 'name': 'MyChart'>)
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() .
Add a new add_chartsheet to a workbook.
sheetname (string) – Optional chartsheet name, defaults to Chart1, etc.
The add_chartsheet() method adds a new chartsheet to a workbook.
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 the Workbook object and write the XLSX file.
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: decision = 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
The close() method can only write a file once. It doesn’t behave like a save method and it cannot be called multiple times to write a file at different stages. If it is called more than once it will raise a UserWarning in order to help avoid issues where a file is closed within a loop or at the wrong scope level.
Set the size of a workbook window.
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 the ratio between the worksheet tabs and the horizontal slider.
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 the document properties such as Title, Author etc.
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:
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'>)
Set a custom document property.
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.
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.
Custom properties can also be used to set Sensitivity Labels. Sensitivity Labels are a property that can be added to an Office 365 document to indicate that it is compliant with a company’s information protection policies. Sensitivity Labels have designations like “Confidential”, “Internal use only”, or “Public” depending on the policies implemented by the company. They are generally only enabled for enterprise versions of Office.
See the following Microsoft documentation on how to Apply sensitivity labels to your files and email.
Sensitivity Labels are generally stored as custom document properties so they can be enabled using set_custom_property() . However, since the metadata differs from company to company you will need to extract some of the required metadata from sample files.
The first step is to create a new file in Excel and set a non-encrypted sensitivity label. Then unzip the file by changing the extension from .xlsx to .zip or by using a command line utility like this:
$ unzip myfile.xlsx -d myfile Archive: myfile.xlsx inflating: myfile/[Content_Types].xml inflating: myfile/docProps/app.xml inflating: myfile/docProps/custom.xml inflating: myfile/docProps/core.xml inflating: myfile/_rels/.rels inflating: myfile/xl/workbook.xml inflating: myfile/xl/worksheets/sheet1.xml inflating: myfile/xl/styles.xml inflating: myfile/xl/theme/theme1.xml inflating: myfile/xl/_rels/workbook.xml.rels
Then examine the docProps/custom.xml file from the unzipped xlsx file. The file doesn’t contain newlines so it is best to view it in an editor that can handle XML or use a commandline utility like libxml’s xmllint to format the XML for clarity:
$ xmllint --format myfile/docProps/custom.xml xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"> fmtid="" pid="2" name="MSIP_Label_2096f6a2-d2f7-48be-b329-b73aaa526e5d_Enabled"> true fmtid="" pid="3" name="MSIP_Label_2096f6a2-d2f7-48be-b329-b73aaa526e5d_SetDate"> 2024-01-01T12:00:00Z fmtid="" pid="4" name="MSIP_Label_2096f6a2-d2f7-48be-b329-b73aaa526e5d_Method"> Privileged fmtid="" pid="5" name="MSIP_Label_2096f6a2-d2f7-48be-b329-b73aaa526e5d_Name"> Confidential fmtid="" pid="6" name="MSIP_Label_2096f6a2-d2f7-48be-b329-b73aaa526e5d_SiteId"> cb46c030-1825-4e81-a295-151c039dbf02 fmtid="" pid="7" name="MSIP_Label_2096f6a2-d2f7-48be-b329-b73aaa526e5d_ActionId"> 88124cf5-1340-457d-90e1-0000a9427c99 fmtid="" pid="8" name="MSIP_Label_2096f6a2-d2f7-48be-b329-b73aaa526e5d_ContentBits"> 2
The MSIP (Microsoft Information Protection) labels in the name attributes contain a GUID that is unique to each company. The “SiteId” field will also be unique to your company/location. The meaning of each of these fields is explained in the the following Microsoft document on Microsoft Information Protection SDK - Metadata.
Once you have identified the necessary metadata you can add it to a new document as shown below:
import xlsxwriter workbook = xlsxwriter.Workbook("sensitivity_label.xlsx") worksheet = workbook.add_worksheet() # Metadata extracted from a company specific file. company_guid = "2096f6a2-d2f7-48be-b329-b73aaa526e5d" site_id = "cb46c030-1825-4e81-a295-151c039dbf02" action_id = "88124cf5-1340-457d-90e1-0000a9427c99" # Add the document properties. Note that these should all be in text format. workbook.set_custom_property(f"MSIP_Label_company_guid>_Enabled", "true", "text") workbook.set_custom_property(f"MSIP_Label_company_guid>_SetDate", "2024-01-01T12:00:00Z", "text") workbook.set_custom_property(f"MSIP_Label_company_guid>_Method", "Privileged", "text") workbook.set_custom_property(f"MSIP_Label_company_guid>_Name", "Confidential", "text") workbook.set_custom_property(f"MSIP_Label_company_guid>_SiteId", site_id, "text") workbook.set_custom_property(f"MSIP_Label_company_guid>_ActionId", action_id, "text") workbook.set_custom_property(f"MSIP_Label_company_guid>_ContentBits", "2", "text") workbook.close()
Note, some sensitivity labels require that the document is encrypted. In order to extract the required metadata you will need to unencrypt the file which may remove the sensitivity label. In that case you may need to use a third party tool such as msoffice-crypt.
Create a defined name in the workbook to use as a variable.
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.
Add a vbaProject binary to the Excel workbook.
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 name doesn’t have to be vbaProject.bin . Any suitable path/name for an existing VBA bin file will do.
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.
Add a vbaProject binary and a vbaProjectSignature binary to the Excel workbook.
The add_signed_vba_project() method can be used to add digitally signed macros or functions to a workbook. The method adds a binary VBA project file and a binary VBA project signature file that have been extracted from an existing Excel xlsm file with digitally signed macros:
Only one vbaProject.bin file can be added per workbook. The name doesn’t have to be vbaProject.bin . Any suitable path/name for an existing VBA bin file will do. The same applies for vbaProjectSignature.bin .
The project_is_stream ( signature_is_stream , resp.) parameter is used to indicate that vba_project ( signature , resp.) refers to a BytesIO byte stream rather than a physical file. This can be used when working with the workbook in_memory mode.
Set the VBA name for the workbook.
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.
Return a list of the worksheet objects in the workbook.
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')
Return a worksheet object in the workbook using the sheetname.
name (string) – Name of worksheet that you wish to retrieve.
The get_worksheet_by_name() method returns the worksheet or chartsheet object with the given name or None if it isn’t found:
worksheet = workbook.get_worksheet_by_name('Sheet1')
Return 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 the Excel calculation mode for the workbook.
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:
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.
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.
Add a recommendation to open the file in “read-only” mode.
This method can be used to set the Excel “Read-only Recommended” option that is available when saving a file. This presents the user of the file with an option to open it in “read-only” mode. This means that any changes to the file can’t be saved back to the same file and must be saved to a new file. It can be set as follows:
import xlsxwriter workbook = xlsxwriter.Workbook('file.xlsx') worksheet = workbook.add_worksheet() workbook.read_only_recommended() workbook.close()
Which will raise a dialog like the following when opening the file:
Tutorial 3: Writing different types of data to the XLSX File
