XlsxWriter
Contents
Contents
Introduction
Getting Started with XlsxWriter
Tutorial 1: Create a simple XLSX file
Tutorial 2: Adding formatting to the XLSX File
Tutorial 3: Writing different types of data to the XLSX File
The Workbook Class
The Worksheet Class
The Worksheet Class (Page Setup)
The Format Class
The Chart Class
The Chartsheet Class
The Exceptions Class
Working with Cell Notation
Working with and Writing Data
Working with Formulas
Working with Dates and Time
Working with Colors
Working with Charts
Working with Object Positioning
Working with Autofilters
Working with Data Validation
Working with Conditional Formatting
Working with Worksheet Tables
Working with Textboxes
Working with Sparklines
Working with Cell Comments
Working with Outlines and Grouping
Working with Memory and Performance
Working with VBA Macros
Working with Pandas and XlsxWriter
Working with Polars and XlsxWriter
Examples
Chart Examples
Pandas with XlsxWriter Examples
Polars with XlsxWriter Examples
Alternative modules for handling Excel files
Libraries that use or enhance XlsxWriter
Known Issues and Bugs
Reporting Bugs
Frequently Asked Questions
Changes in XlsxWriter
Author
License
Page
Contents
« Creating Exce...
Introduction »
Contents
Introduction
Getting Started with XlsxWriter
Installing XlsxWriter
Running a sample program
Documentation
Tutorial 1: Create a simple XLSX file
Tutorial 2: Adding formatting to the XLSX File
Tutorial 3: Writing different types of data to the XLSX File
The Workbook Class
Constructor
workbook.add_worksheet()
workbook.add_format()
workbook.add_chart()
workbook.add_chartsheet()
workbook.close()
workbook.set_size()
workbook.tab_ratio()
workbook.set_properties()
workbook.set_custom_property()
workbook.define_name()
workbook.add_vba_project()
workbook.set_vba_name()
workbook.worksheets()
workbook.get_worksheet_by_name()
workbook.get_default_url_format()
workbook.set_calc_mode()
workbook.use_zip64()
workbook.read_only_recommended()
The Worksheet Class
worksheet.write()
worksheet.add_write_handler()
worksheet.write_string()
worksheet.write_number()
worksheet.write_formula()
worksheet.write_array_formula()
worksheet.write_dynamic_array_formula()
worksheet.write_blank()
worksheet.write_boolean()
worksheet.write_datetime()
worksheet.write_url()
worksheet.write_rich_string()
worksheet.write_row()
worksheet.write_column()
worksheet.set_row()
worksheet.set_row_pixels()
worksheet.set_column()
worksheet.set_column_pixels()
worksheet.autofit()
worksheet.insert_image()
worksheet.insert_chart()
worksheet.insert_textbox()
worksheet.insert_button()
worksheet.data_validation()
worksheet.conditional_format()
worksheet.add_table()
worksheet.add_sparkline()
worksheet.write_comment()
worksheet.show_comments()
worksheet.set_comments_author()
worksheet.get_name()
worksheet.activate()
worksheet.select()
worksheet.hide()
worksheet.set_first_sheet()
worksheet.merge_range()
worksheet.autofilter()
worksheet.filter_column()
worksheet.filter_column_list()
worksheet.set_selection()
worksheet.set_top_left_cell()
worksheet.freeze_panes()
worksheet.split_panes()
worksheet.set_zoom()
worksheet.right_to_left()
worksheet.hide_zero()
worksheet.set_background()
worksheet.set_tab_color()
worksheet.protect()
worksheet.unprotect_range()
worksheet.set_default_row()
worksheet.outline_settings()
worksheet.set_vba_name()
worksheet.ignore_errors()
The Worksheet Class (Page Setup)
worksheet.set_landscape()
worksheet.set_portrait()
worksheet.set_page_view()
worksheet.set_pagebreak_view()
worksheet.set_paper()
worksheet.center_horizontally()
worksheet.center_vertically()
worksheet.set_margins()
worksheet.set_header()
worksheet.set_footer()
worksheet.repeat_rows()
worksheet.repeat_columns()
worksheet.hide_gridlines()
worksheet.print_row_col_headers()
worksheet.hide_row_col_headers()
worksheet.print_area()
worksheet.print_across()
worksheet.fit_to_pages()
worksheet.set_start_page()
worksheet.set_print_scale()
worksheet.print_black_and_white()
worksheet.set_h_pagebreaks()
worksheet.set_v_pagebreaks()
The Format Class
Creating and using a Format object
Format Defaults
Modifying Formats
Number Format Categories
Number Formats in different locales
Format methods and Format properties
format.set_font_name()
format.set_font_size()
format.set_font_color()
format.set_bold()
format.set_italic()
format.set_underline()
format.set_font_strikeout()
format.set_font_script()
format.set_num_format()
format.set_locked()
format.set_hidden()
format.set_align()
format.set_center_across()
format.set_text_wrap()
format.set_rotation()
format.set_reading_order()
format.set_indent()
format.set_shrink()
format.set_text_justlast()
format.set_pattern()
format.set_bg_color()
format.set_fg_color()
format.set_border()
format.set_bottom()
format.set_top()
format.set_left()
format.set_right()
format.set_border_color()
format.set_bottom_color()
format.set_top_color()
format.set_left_color()
format.set_right_color()
format.set_diag_border()
format.set_diag_type()
format.set_diag_color()
format.set_quote_prefix()
The Chart Class
chart.add_series()
chart.set_x_axis()
chart.set_y_axis()
chart.set_x2_axis()
chart.set_y2_axis()
chart.combine()
chart.set_size()
chart.set_title()
chart.set_legend()
chart.set_chartarea()
chart.set_plotarea()
chart.set_style()
chart.set_table()
chart.set_up_down_bars()
chart.set_drop_lines()
chart.set_high_low_lines()
chart.show_blanks_as()
chart.show_hidden_data()
chart.set_rotation()
chart.set_hole_size()
The Chartsheet Class
chartsheet.set_chart()
Worksheet methods
Chartsheet Example
The Exceptions Class
Exception: XlsxWriterException
Exception: XlsxFileError
Exception: XlsxInputError
Exception: FileCreateError
Exception: UndefinedImageSize
Exception: UnsupportedImageFormat
Exception: FileSizeError
Exception: EmptyChartSeries
Exception: DuplicateTableName
Exception: InvalidWorksheetName
Exception: DuplicateWorksheetName
Exception: OverlappingRange
Working with Cell Notation
Row and Column Ranges
Relative and Absolute cell references
Defined Names and Named Ranges
Cell Utility Functions
Working with and Writing Data
Writing data to a worksheet cell
Writing unicode data
Writing lists of data
Writing dicts of data
Writing dataframes
Writing user defined types
Working with Formulas
Non US Excel functions and syntax
Formula Results
Dynamic Array support
Dynamic Arrays - The Implicit Intersection Operator “@”
Dynamic Arrays - The Spilled Range Operator “#”
The Excel 365 LAMBDA() function
Formulas added in Excel 2010 and later
Using Tables in Formulas
Dealing with formula errors
Working with Dates and Time
Default Date Formatting
Timezone Handling
Working with Colors
Working with Charts
Chart Value and Category Axes
Chart Series Options
Chart series option: Marker
Chart series option: Trendline
Chart series option: Error Bars
Chart series option: Data Labels
Chart series option: Custom Data Labels
Chart series option: Points
Chart series option: Smooth
Chart Formatting
Chart formatting: Line
Chart formatting: Border
Chart formatting: Solid Fill
Chart formatting: Pattern Fill
Chart formatting: Gradient Fill
Chart Fonts
Chart Layout
Date Category Axes
Chart Secondary Axes
Combined Charts
Chartsheets
Charts from Worksheet Tables
Chart Limitations
Chart Examples
Working with Object Positioning
Object scaling due to automatic row height adjustment
Object Positioning with Cell Moving and Sizing
Image sizing and DPI
Reporting issues with image insertion
Working with Autofilters
Applying an autofilter
Filter data in an autofilter
Setting a filter criteria for a column
Setting a column list filter
Example
Working with Data Validation
data_validation()
Data Validation Examples
Working with Conditional Formatting
The conditional_format() method
Conditional Format Options
Conditional Formatting Examples
Working with Worksheet Tables
add_table()
data
header_row
autofilter
banded_rows
banded_columns
first_column
last_column
style
name
total_row
columns
Example
Working with Textboxes
Textbox options
Textbox size and positioning
Textbox Formatting
Textbox formatting: Line
Textbox formatting: Border
Textbox formatting: Solid Fill
Textbox formatting: Gradient Fill
Textbox formatting: Fonts
Textbox formatting: Align
Textbox formatting: Text Rotation
Textbox Textlink
Textbox Hyperlink
Textbox Description
Textbox Decorative
Working with Sparklines
The add_sparkline() method
range
type
style
markers
negative_points
axis
reverse
weight
high_point, low_point, first_point, last_point
max, min
empty_cells
show_hidden
date_axis
series_color
location
Grouped Sparklines
Sparkline examples
Working with Cell Comments
Setting Comment Properties
Working with Outlines and Grouping
Outlines and Grouping in XlsxWriter
Working with Memory and Performance
Performance Figures
Working with VBA Macros
The Excel XLSM file format
How VBA macros are included in XlsxWriter
The vba_extract.py utility
Adding the VBA macros to a XlsxWriter file
Setting the VBA codenames
What to do if it doesn’t work
Working with Pandas and XlsxWriter
Using XlsxWriter with Pandas
Accessing XlsxWriter from Pandas
Adding Charts to Dataframe output
Adding Conditional Formatting to Dataframe output
Formatting of the Dataframe output
Formatting of the Dataframe headers
Adding a Dataframe to a Worksheet Table
Adding an autofilter to a Dataframe output
Handling multiple Pandas Dataframes
Passing XlsxWriter constructor options to Pandas
Saving the Dataframe output to a string
Additional Pandas and Excel Information
Working with Polars and XlsxWriter
Sharing XlsxWriter workbooks with Polars
Adding Charts to Dataframe output
Adding Conditional Formatting to Dataframe output
Handling multiple Polars Dataframes
Formatting the dataframe output
Adding Sparklines to the output dataframe
Examples
Example: Hello World
Example: Simple Feature Demonstration
Example: Catch exception on closing
Example: Dates and Times in Excel
Example: Adding hyperlinks
Example: Array formulas
Example: Dynamic array formulas
Example: Applying Autofilters
Example: Data Validation and Drop Down Lists
Example: Conditional Formatting
Example: Defined names/Named ranges
Example: Merging Cells
Example: Autofitting columns
Example: Writing “Rich” strings with multiple formats
Example: Merging Cells with a Rich String
Example: Inserting images into a worksheet
Example: Inserting images from a URL or byte stream into a worksheet
Example: Left to Right worksheets and text
Example: Simple Django class
Example: Simple HTTP Server
Example: Adding Headers and Footers to Worksheets
Example: Freeze Panes and Split Panes
Example: Worksheet Tables
Example: Writing User Defined Types (1)
Example: Writing User Defined Types (2)
Example: Writing User Defined types (3)
Example: Ignoring Worksheet errors and warnings
Example: Sparklines (Simple)
Example: Sparklines (Advanced)
Example: Adding Cell Comments to Worksheets (Simple)
Example: Adding Cell Comments to Worksheets (Advanced)
Example: Insert Textboxes into a Worksheet
Example: Outline and Grouping
Example: Collapsed Outline and Grouping
Example: Setting Document Properties
Example: Simple Unicode with Python 3
Example: Unicode - Polish in UTF-8
Example: Unicode - Shift JIS
Example: Setting a Worksheet Watermark
Example: Setting the Worksheet Background
Example: Setting Worksheet Tab Colors
Example: Diagonal borders in cells
Example: Enabling Cell protection in Worksheets
Example: Hiding Worksheets
Example: Hiding Rows and Columns
Example: Example of subclassing the Workbook and Worksheet classes
Example: Advanced example of subclassing
Example: Adding a VBA macro to a Workbook
Example: Excel 365 LAMBDA() function
Chart Examples
Example: Chart (Simple)
Example: Area Chart
Example: Bar Chart
Example: Column Chart
Example: Line Chart
Example: Pie Chart
Example: Doughnut Chart
Example: Scatter Chart
Example: Radar Chart
Example: Stock Chart
Example: Styles Chart
Example: Chart with Pattern Fills
Example: Chart with Gradient Fills
Example: Secondary Axis Chart
Example: Combined Chart
Example: Pareto Chart
Example: Gauge Chart
Example: Clustered Chart
Example: Date Axis Chart
Example: Charts with Data Tables
Example: Charts with Data Tools
Example: Charts with Data Labels
Example: Chartsheet
Pandas with XlsxWriter Examples
Example: Pandas Excel example
Example: Pandas Excel with multiple dataframes
Example: Pandas Excel dataframe positioning
Example: Pandas Excel output with a chart
Example: Pandas Excel output with conditional formatting
Example: Pandas Excel output with an autofilter
Example: Pandas Excel output with a worksheet table
Example: Pandas Excel output with datetimes
Example: Pandas Excel output with column formatting
Example: Pandas Excel output with user defined header format
Example: Pandas Excel output with percentage formatting
Example: Pandas Excel output with a line chart
Example: Pandas Excel output with a column chart
Polars with XlsxWriter Examples
Example: Polars Excel getting started example
Example: Polars integration with XlsxWriter
Example: Polars Excel dataframe positioning
Example: Polars Excel with multiple dataframes
Example: Polars Excel output with a chart
Example: Polars Excel output with conditional formatting
Example: Polars default format example
Example: Polars custom format example
Example: Polars Excel output with sparklines
Alternative modules for handling Excel files
OpenPyXL
Xlwings
XLWT
XLRD
Libraries that use or enhance XlsxWriter
Pandas
XlsxPandasFormatter
Known Issues and Bugs
“Content is Unreadable. Open and Repair”
“Exception caught in workbook destructor. Explicit close() may be required”
Formulas displayed as
#NAME?
until edited
Formula results displaying as zero in non-Excel applications
Images not displayed correctly in Excel 2001 for Mac and non-Excel applications
Charts series created from Worksheet Tables cannot have user defined names
Reporting Bugs
Upgrade to the latest version of the module
Read the documentation
Look at the example programs
Use the official XlsxWriter Issue tracker on GitHub
Pointers for submitting a bug report
Frequently Asked Questions
Q. Can XlsxWriter use an existing Excel file as a template?
Q. Why do my formulas show a zero result in some, non-Excel applications?
Q. Why do my formulas have a @ in them?
Q. Can I apply a format to a range of cells in one go?
Q. Is feature X supported or will it be supported?
Q. Can I password protect an XlsxWriter xlsx file
Q. Do people actually ask these questions frequently, or at all?
Changes in XlsxWriter
Author
Asking questions
Sponsorship and Donations
License