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
Working with Cell Notation
Working with Formulas
Working with Dates and Time
Working with Colors
Working with Charts
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 Python Pandas and XlsxWriter
Examples
Chart Examples
Pandas with XlsxWriter Examples
Alternative modules for handling Excel files
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_properties()
workbook.set_custom_property()
workbook.define_name()
workbook.add_vba_project()
workbook.set_vba_name()
workbook.worksheets()
workbook.get_worksheet_by_name()
workbook.set_calc_mode()
workbook.use_zip64()
The Worksheet Class
worksheet.write()
worksheet.write_string()
worksheet.write_number()
worksheet.write_formula()
worksheet.write_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_column()
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.freeze_panes()
worksheet.split_panes()
worksheet.set_zoom()
worksheet.right_to_left()
worksheet.hide_zero()
worksheet.set_tab_color()
worksheet.protect()
worksheet.set_default_row()
worksheet.outline_settings()
worksheet.set_vba_name()
The Worksheet Class (Page Setup)
worksheet.set_landscape()
worksheet.set_portrait()
worksheet.set_page_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.print_area()
worksheet.print_across()
worksheet.fit_to_pages()
worksheet.set_start_page()
worksheet.set_print_scale()
worksheet.set_h_pagebreaks()
worksheet.set_v_pagebreaks()
The Format Class
Creating and using a Format object
Format Defaults
Modifying Formats
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_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()
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
Working with Cell Notation
Relative and Absolute cell references
Defined Names and Named Ranges
Cell Utility Functions
Working with Formulas
Non US Excel functions and syntax
Formulas added in Excel 2010 and later
Using Tables in Formulas
Dealing with formula errors
Formula Results
Working with Dates and Time
Default Date Formatting
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: 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 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 Fonts
Textbox Align
Other Textbox Features
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
Benchmark of Python Excel Writers
Working with VBA Macros
The Excel XLSM file format
How VBA macros are included in XlsxWriter
The vba_extract utility
Adding the VBA macros to a XlsxWriter file
Setting the VBA codenames
What to do if it doesn’t work
Working with Python 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
Handling multiple Pandas Dataframes
Passing XlsxWriter constructor options to Pandas
Saving the Dataframe output to a string
Additional Pandas and Excel Information
Examples
Example: Hello World
Example: Simple Feature Demonstration
Example: Dates and Times in Excel
Example: Adding hyperlinks
Example: Array formulas
Example: Applying Autofilters
Example: Data Validation and Drop Down Lists
Example: Conditional Formatting
Example: Defined names/Named ranges
Example: Merging Cells
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: Simple HTTP Server (Python 2)
Example: Simple HTTP Server (Python 3)
Example: Adding Headers and Footers to Worksheets
Example: Freeze Panes and Split Panes
Example: Worksheet Tables
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 2
Example: Simple Unicode with Python 3
Example: Unicode - Polish in UTF-8
Example: Unicode - Shift JIS
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: Adding a VBA macro to a Workbook
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: Clustered Chart
Example: Date Axis Chart
Example: Charts with Data Tables
Example: Charts with Data Tools
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 datetimes
Example: Pandas Excel output with column formatting
Example: Pandas Excel output with a line chart
Example: Pandas Excel output with a stock chart
Example: Pandas Excel output with a column chart
Alternative modules for handling Excel files
XLWT
XLRD
OpenPyXL
Xlwings
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
Strings aren’t displayed in Apple Numbers in ‘constant_memory’ mode
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
Changes in XlsxWriter
Author
Asking questions
If you found XlsxWriter useful
License