Python XlsxWriter: Create Excel and Write Pandas DataFrame
How to use XlsxWriter with pandas to write DataFrames to Excel, format columns and cells, create multi-sheet workbooks, and avoid common export pitfalls.

To generate an Excel file from a pandas DataFrame and keep control over the finished workbook, use XlsxWriter as the engine behind pandas' to_excel method. This combination lets you write a DataFrame quickly and then apply formatting, add multiple sheets, or set column widths on the resulting workbook.
The Core Pattern: to_excel with engine='xlsxwriter'
The simplest way to write a DataFrame to an Excel file is to call to_excel directly. By specifying engine='xlsxwriter', you tell pandas to use XlsxWriter as the backend:
import pandas as pd
df = pd.DataFrame({
'Product': ['Widget', 'Gadget', 'Sprocket'],
'Price': [25.0, 35.5, 12.75],
'Quantity': [10, 4, 8]
})
df.to_excel('output.xlsx', engine='xlsxwriter', index=False)This creates a workbook with a single worksheet named Sheet1 containing the DataFrame data. The index=False argument prevents pandas from writing the row index as a separate column. With the default index=True, pandas writes the index into the first column; the header cell is blank, so reading the file back with pandas may label that column Unnamed: 0.
This pattern is sufficient when you only need a plain export. The real value of XlsxWriter appears when you need formatting, multiple sheets, or precise control over how data is written.
Installing the Required Packages
You need both pandas and XlsxWriter installed. Install them with pip:
pip install pandas xlsxwriterXlsxWriter is a separate library and an optional engine for pandas, so you must install it explicitly. If you do not install an engine that can write .xlsx files, pandas raises an ImportError. XlsxWriter is the engine used when you pass engine='xlsxwriter'; if you do not specify an engine, pandas uses its default .xlsx engine, openpyxl, when that library is available.
Gaining Control with pd.ExcelWriter
For most real-world exports, you need to format cells, set column widths, or add a title. That requires access to the underlying workbook and worksheet objects. Use pd.ExcelWriter to create a writer object and then retrieve the XlsxWriter objects:
import pandas as pd
with pd.ExcelWriter('formatted.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sales', index=False)
workbook = writer.book
worksheet = writer.sheets['Sales']
# Add a number format for the price column
price_format = workbook.add_format({'num_format': '$#,##0.00'})
worksheet.set_column('B:B', 12, price_format)
# Set column widths
worksheet.set_column('A:A', 20)
worksheet.set_column('C:C', 10)The with block ensures the file is closed and saved properly. writer.book is the XlsxWriter workbook, and writer.sheets maps sheet names to worksheet objects once the sheets have been written.
You can apply formats to entire columns, as shown, or to specific cells using worksheet.write() with a format. This is the key difference from using to_excel directly: you can now style the output.
Writing Multiple DataFrames to One Workbook
A common requirement is to put several DataFrames into separate sheets of the same workbook. With pd.ExcelWriter, you simply call to_excel multiple times with different sheet_name values:
import pandas as pd
sales = pd.DataFrame({'Region': ['North', 'South'], 'Revenue': [1000, 1500]})
costs = pd.DataFrame({'Region': ['North', 'South'], 'Cost': [600, 800]})
with pd.ExcelWriter('report.xlsx', engine='xlsxwriter') as writer:
sales.to_excel(writer, sheet_name='Sales', index=False)
costs.to_excel(writer, sheet_name='Costs', index=False)
# Adjust each sheet individually
for sheet_name in writer.sheets:
worksheet = writer.sheets[sheet_name]
worksheet.set_column('A:A', 15)
worksheet.set_column('B:B', 12)Each to_excel call creates a new sheet if the name doesn't exist. If you reuse a sheet name, pandas will overwrite it. This is useful for building multi-tab reports in a single pass.
Handling Data Types and Date/Time Values
XlsxWriter writes pandas data types to Excel cells with reasonable defaults, but date and time values require attention. When a DataFrame column contains datetime64 objects, pandas converts them to Excel serial dates. However, the cell format defaults to a number unless you explicitly set a date format.
import pandas as pd
from datetime import datetime
orders = pd.DataFrame({
'OrderDate': [datetime(2024, 1, 5), datetime(2024, 2, 14)],
'Amount': [200, 350]
})
with pd.ExcelWriter('dates.xlsx', engine='xlsxwriter') as writer:
orders.to_excel(writer, sheet_name='Orders', index=False)
workbook = writer.book
worksheet = writer.sheets['Orders']
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})
worksheet.set_column('A:A', 12, date_format)Without the num_format, the date cells display as Excel serial numbers instead of readable dates. Setting the column format to a date pattern makes them readable. This is a common pitfall when exporting DataFrames with datetime columns.
Performance and Memory Considerations for Large DataFrames
XlsxWriter is generally fast, but writing a very large DataFrame can consume significant memory. By default, XlsxWriter buffers worksheet data in memory, and the pandas DataFrame itself is also in memory. Excel's .xlsx format also limits a single worksheet to 1,048,576 rows, so the chunking approach below is useful when you are slicing an existing DataFrame or processing data in batches; it does not allow you to exceed Excel's row limit.
One approach is to write the DataFrame in slices using the startrow and header parameters of to_excel:
import pandas as pd
# Assume df is a large DataFrame with fewer than 1,048,576 rows.
chunk_size = 10000
with pd.ExcelWriter('large.xlsx', engine='xlsxwriter') as writer:
# Write only the column headers once, at row 0.
df.head(0).to_excel(writer, sheet_name='Data', index=False)
for start in range(0, len(df), chunk_size):
chunk = df.iloc[start:start + chunk_size]
chunk.to_excel(
writer,
sheet_name='Data',
startrow=start + 1,
header=False,
index=False
)This writes each chunk starting at the appropriate row after the header. The header is written once, before the loop, so it is not repeated. This can lower peak memory use because only one chunk is passed to XlsxWriter at a time, but it does not remove the memory cost of holding the full DataFrame if you built it before the loop.
XlsxWriter also supports its own constant_memory mode for reducing memory use. Recent pandas versions can pass engine options through engine_kwargs; for example:
with pd.ExcelWriter('large.xlsx', engine='xlsxwriter',
engine_kwargs={'options': {'constant_memory': True}}) as writer:
...If your pandas version does not support engine_kwargs, or if you need the most direct control over memory use, you can bypass pandas and use XlsxWriter's write methods directly, at the cost of losing the convenience of DataFrame conversion.
Common Pitfalls and Compatibility Notes
Several behaviors can surprise developers new to this combination.
NaN values are written as empty cells by default. If you want a specific placeholder, you need to fill the DataFrame before writing:
df = df.fillna('N/A')Formula-looking strings are not a reliable way to add calculated cells. How a string such as '=SUM(D2:D4)' is handled depends on the pandas version and XlsxWriter's strings_to_formulas option: it may be written as a formula or treated as text. If you need an actual Excel formula, use worksheet.write_formula() manually after the DataFrame is written. For example, to add a total row:
worksheet.write_formula('D5', '=SUM(D2:D4)')If you want a value that starts with = to remain text, use worksheet.write_string().
Column names with special characters are written as-is. If a column name contains spaces or starts with a digit, Excel will still display it, but it may cause issues if you later read the file with pandas and use df. attribute access.
Sheet name limits apply: Excel sheet names cannot exceed 31 characters and cannot contain certain characters like : \ / ? * [ ]. pandas will raise an error if you try to use an invalid name.
Compatibility with Excel versions: XlsxWriter produces .xlsx files that are compatible with Excel 2007 and later. It does not support the older .xls format. If you need .xls, you must use a different engine like xlwt.
By understanding these behaviors, you can avoid the most common issues when using XlsxWriter to create Excel files from pandas DataFrames. The combination gives you the convenience of pandas' data manipulation with the fine-grained control of a dedicated Excel writer.
Try matching browser tools
All toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.