Create an Excel file in Python with XlsxWriter
To create an .xlsx file in Python, install XlsxWriter (pip install XlsxWriter), open a Workbook, add a worksheet, write your cells, and close the workbook. That is all it takes:
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook("report.xlsx")
worksheet = workbook.add_worksheet()
# Write a few cells (row and column are zero-indexed).
worksheet.write(0, 0, "Hello")
worksheet.write("B1", "World")
# Close the workbook so the file is written to disk.
workbook.close()Run it and you get a fully valid Excel 2007+ report.xlsx. From this starting point you can add formatting, formulas, conditional formatting, images, and native charts/bar graphs, write directly from a pandas DataFrame, and stream the file straight to a browser in memory with no temp files.
One thing to know up front: XlsxWriter is write-only. It creates brand-new .xlsx files extremely fast and supports the full Excel feature set, but it cannot open, read, or edit an existing workbook. If you need to read or modify a spreadsheet that already exists, that is openpyxl's job. We compare the two in detail below.
This guide is written for Python 3.12+ and the current XlsxWriter API. At MicroPyramid we have built data pipelines, financial reports, and exportable dashboards for clients for 12+ years across 50+ projects, and generating Excel deliverables comes up constantly, so the patterns here are the ones we reach for in production.
Install XlsxWriter
XlsxWriter is a pure-Python module with no external dependencies, so installation is a single command:
pip install XlsxWriter
# or, with uv / poetry
uv add XlsxWriter
poetry add XlsxWriterImport it as import xlsxwriter (the PyPI package name is XlsxWriter, the import name is lowercase xlsxwriter). Check your version with python -c "import xlsxwriter; print(xlsxwriter.__version__)".
Create a workbook and worksheets
The Workbook object represents the whole .xlsx file; each add_worksheet() call adds a tab. If you do not pass a name, sheets are auto-named Sheet1, Sheet2, and so on. You can also give them meaningful names (max 31 characters, no [ ] : * ? / \\).
import xlsxwriter
workbook = xlsxwriter.Workbook("report.xlsx")
sheet1 = workbook.add_worksheet() # -> "Sheet1"
work_log = workbook.add_worksheet("WorkLog") # -> "WorkLog"
workbook.close()Prefer the context-manager form
Forgetting workbook.close() is the single most common reason a file comes out empty or corrupt. Using with guarantees the workbook is finalised even if an exception is raised:
import xlsxwriter
with xlsxwriter.Workbook("report.xlsx") as workbook:
worksheet = workbook.add_worksheet("Sales")
worksheet.write("A1", "Region")
worksheet.write("B1", "Revenue")
# No explicit close() needed - the 'with' block handles it.Write data to the worksheet
Cells are addressed two ways, and both work everywhere XlsxWriter accepts a position:
- Row/column (zero-indexed):
worksheet.write(0, 0, value)writes to A1. - A1 notation:
worksheet.write("A1", value).
The generic write() method inspects the Python type and routes to the right writer (string, number, boolean, formula, URL, datetime, None). You can also call the type-specific methods directly when you want explicit control.
import xlsxwriter
with xlsxwriter.Workbook("data.xlsx") as workbook:
worksheet = workbook.add_worksheet()
# Generic write() picks the type automatically.
worksheet.write("A1", "Text") # write_string
worksheet.write("A2", 123) # write_number
worksheet.write("A3", 123.456) # write_number (float)
worksheet.write("A4", True) # write_boolean
worksheet.write("A5", "=2*3") # write_formula
worksheet.write("A6", None) # write_blank
# Type-specific writes are explicit and slightly faster.
worksheet.write_string(6, 0, "Explicit string") # A7
worksheet.write_number(7, 0, 42) # A8
# Write a whole row or column in one call.
worksheet.write_row("A10", ["Q1", "Q2", "Q3", "Q4"])
worksheet.write_column("A11", [100, 200, 300, 400])write_row writes left-to-right from the start cell; write_column writes top-to-bottom. For a 2D block of data, loop over rows and call write_row for each.
Format cells with add_format
Formatting in XlsxWriter is done with format objects created once via workbook.add_format(...) and then passed as the last argument to any write call. Reuse a format across many cells rather than creating a new one each time. The common properties:
| Property | Example | Effect |
|---|---|---|
bold / italic |
{"bold": True} |
Font weight / style |
font_color / bg_color |
{"bg_color": "#FFC000"} |
Text and fill colour |
num_format |
{"num_format": "$#,##0.00"} |
Number / currency / date format |
align / valign |
{"align": "center"} |
Horizontal / vertical alignment |
border |
{"border": 1} |
Cell border |
text_wrap |
{"text_wrap": True} |
Wrap long text |
import xlsxwriter
with xlsxwriter.Workbook("formatted.xlsx") as workbook:
worksheet = workbook.add_worksheet("Sales")
# Reusable format objects.
header = workbook.add_format({
"bold": True,
"font_color": "white",
"bg_color": "#305496",
"align": "center",
"border": 1,
})
money = workbook.add_format({"num_format": "$#,##0.00"})
date_fmt = workbook.add_format({"num_format": "yyyy-mm-dd"})
# Header row.
worksheet.write_row("A1", ["Date", "Region", "Revenue"], header)
# Data rows.
import datetime
rows = [
(datetime.date(2026, 1, 31), "EMEA", 12450.50),
(datetime.date(2026, 2, 28), "APAC", 9870.00),
(datetime.date(2026, 3, 31), "AMER", 15320.75),
]
for i, (d, region, rev) in enumerate(rows, start=1):
worksheet.write_datetime(i, 0, d, date_fmt)
worksheet.write_string(i, 1, region)
worksheet.write_number(i, 2, rev, money)Size columns and rows
Set column widths with set_column(first_col, last_col, width) and row heights with set_row(row, height). Widths are in Excel character units; heights are in points. You can also attach a default format to a whole column or row.
# Make columns A-C wide enough for the data.
worksheet.set_column("A:A", 14) # Date column
worksheet.set_column("B:B", 12) # Region column
worksheet.set_column(2, 2, 16, money) # Column C (index 2) + a default format
# Set the header row height and freeze it so it stays visible when scrolling.
worksheet.set_row(0, 22)
worksheet.freeze_panes(1, 0) # freeze the first rowWrite formulas
Pass a string beginning with = to write() (or use write_formula). XlsxWriter writes the formula; Excel calculates it on open. Use write_dynamic_array_formula for spill formulas like =SUM(...) over dynamic ranges.
worksheet.write_row("A1", ["Item", "Cost"])
worksheet.write_column("A2", ["Rent", "Gas", "Food"])
worksheet.write_column("B2", [1000, 100, 300])
# A standard formula.
worksheet.write("A5", "Total")
worksheet.write_formula("B5", "=SUM(B2:B4)")
# A modern dynamic-array formula (Excel 365).
worksheet.write_dynamic_array_formula("D2:D4", "=B2:B4*1.2")Conditional formatting
conditional_format(range, options) mirrors Excel's Conditional Formatting menu - colour scales, data bars, cell rules, top/bottom, and more.
# Highlight revenue cells above 12000 in green.
green = workbook.add_format({"bg_color": "#C6EFCE", "font_color": "#006100"})
worksheet.conditional_format("C2:C4", {
"type": "cell",
"criteria": ">",
"value": 12000,
"format": green,
})
# Add a 3-colour scale across the same range.
worksheet.conditional_format("C2:C4", {"type": "3_color_scale"})Insert an image into an Excel file
Use insert_image(cell, filename) to drop a PNG, JPEG, GIF, BMP, or WebP image onto the worksheet, anchored at the top-left of the given cell. The options dict gives you scaling, positioning, a hyperlink, and alt text.
with xlsxwriter.Workbook("with_image.xlsx") as workbook:
worksheet = workbook.add_worksheet()
# Simplest case: anchor the image at cell B5.
worksheet.insert_image("B5", "logo.png")
# Row/column form with options.
worksheet.insert_image(2, 4, "logo.png", {
"x_scale": 0.5, # scale width to 50%
"y_scale": 0.5, # scale height to 50%
"x_offset": 8, # nudge right (pixels)
"y_offset": 4, # nudge down (pixels)
"object_position": 1, # move and size with cells
"url": "https://micropyramid.com", # make the image a hyperlink
"description": "Company logo", # alt text for accessibility
})Insert an image from memory (no file on disk)
If your image is already in memory - downloaded from a URL, pulled from S3, or generated by matplotlib - wrap the bytes in a BytesIO and pass them via the image_data option. The filename argument is still required (it is used only to infer the type and for the internal name):
from io import BytesIO
from urllib.request import urlopen
import xlsxwriter
url = "https://raw.githubusercontent.com/jmcnamara/XlsxWriter/master/examples/logo.png"
image_data = BytesIO(urlopen(url).read())
with xlsxwriter.Workbook("image_from_memory.xlsx") as workbook:
worksheet = workbook.add_worksheet()
worksheet.insert_image("B2", url, {"image_data": image_data})Tip: On Excel 365 (2023+) you can use
worksheet.embed_image(row, col, "logo.png")to place an image inside a cell so it scales with the cell - the equivalent of Excel's "Place in Cell". Older Excel versions show a#VALUE!error for embedded images, so useinsert_imageif you need broad compatibility.
Draw charts and bar graphs
XlsxWriter creates native Excel charts (not images), so they stay interactive and editable in Excel. A vertical bar graph is a column chart; a horizontal bar graph is a bar chart. The workflow is always:
chart = workbook.add_chart({"type": "column"})- Add one or more data series with
chart.add_series({...}), pointingcategoriesandvaluesat worksheet ranges. - Set the title and axes.
worksheet.insert_chart(cell, chart).
import xlsxwriter
with xlsxwriter.Workbook("chart.xlsx") as workbook:
worksheet = workbook.add_worksheet()
bold = workbook.add_format({"bold": True})
# Some data: months and revenue.
months = ["Jan", "Feb", "Mar", "Apr", "May"]
revenue = [12450, 9870, 15320, 13110, 17890]
worksheet.write_row("A1", ["Month", "Revenue"], bold)
worksheet.write_column("A2", months)
worksheet.write_column("B2", revenue)
# Create a column (vertical bar) chart.
chart = workbook.add_chart({"type": "column"})
# Add a data series. categories = x-axis labels, values = bar heights.
chart.add_series({
"name": "Revenue 2026",
"categories": "=Sheet1!$A$2:$A$6",
"values": "=Sheet1!$B$2:$B$6",
"data_labels": {"value": True},
})
# Titles, axes, size.
chart.set_title({"name": "Monthly Revenue"})
chart.set_x_axis({"name": "Month"})
chart.set_y_axis({"name": "Revenue (USD)"})
chart.set_size({"width": 640, "height": 400})
# Insert the chart below the data.
worksheet.insert_chart("D2", chart)The range strings (=Sheet1!$A$2:$A$6) are convenient, but you can also pass a list [sheet, first_row, first_col, last_row, last_col] which avoids string-building when ranges are computed dynamically - handy with pandas (see below). Change "type" to "bar" for horizontal bars, "line", "pie", "area", "scatter", "doughnut", or "radar" for other chart types, and use the subtype key (e.g. {"type": "column", "subtype": "stacked"}) for stacked variants.
Write an Excel file from a pandas DataFrame
If your data is already in a DataFrame, use df.to_excel() with engine="xlsxwriter". Crucially, you can reach through the writer to the underlying XlsxWriter workbook and worksheet objects to add formatting, conditional formats, images, and charts that plain to_excel cannot produce.
import pandas as pd
df = pd.DataFrame({
"Month": ["Jan", "Feb", "Mar", "Apr", "May"],
"Revenue": [12450, 9870, 15320, 13110, 17890],
})
# Use XlsxWriter as the pandas Excel engine.
with pd.ExcelWriter("pandas_report.xlsx", engine="xlsxwriter") as writer:
df.to_excel(writer, sheet_name="Sales", index=False)
# Reach the underlying XlsxWriter objects.
workbook = writer.book
worksheet = writer.sheets["Sales"]
# Format the Revenue column as currency.
money = workbook.add_format({"num_format": "$#,##0"})
worksheet.set_column("B:B", 14, money)
# Add a column chart driven by the DataFrame data.
max_row = len(df) # number of data rows
chart = workbook.add_chart({"type": "column"})
chart.add_series({
"name": "Revenue",
"categories": ["Sales", 1, 0, max_row, 0], # column A labels
"values": ["Sales", 1, 1, max_row, 1], # column B values
})
chart.set_title({"name": "Monthly Revenue"})
worksheet.insert_chart("D2", chart)Note the list-form ranges ["Sales", 1, 1, max_row, 1] - they read directly from df.shape/len(df) so the chart always covers exactly the rows pandas wrote, no off-by-one string math required.
Return an Excel file from a web app (in memory, no temp file)
For a download endpoint you usually do not want to touch the filesystem. Pass a BytesIO object to Workbook() instead of a filename, write as usual, then hand the bytes to your response. This works with Django, Flask, FastAPI, or any WSGI/ASGI framework.
from io import BytesIO
import xlsxwriter
def build_report_xlsx(rows):
"""Build an .xlsx entirely in memory and return the raw bytes."""
output = BytesIO()
# 'in_memory': True avoids temp files on filesystems that disallow them.
workbook = xlsxwriter.Workbook(output, {"in_memory": True})
worksheet = workbook.add_worksheet("Report")
bold = workbook.add_format({"bold": True})
worksheet.write_row("A1", ["Name", "Score"], bold)
for i, (name, score) in enumerate(rows, start=1):
worksheet.write(i, 0, name)
worksheet.write(i, 1, score)
workbook.close() # finalise before reading the buffer
output.seek(0)
return output.getvalue()Django download view
Plug the bytes into an HttpResponse with the correct content type and a Content-Disposition header so the browser downloads it as a file:
from django.http import HttpResponse
XLSX_MIME = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
def export_report(request):
rows = [("Alice", 95), ("Bob", 88), ("Carol", 91)]
data = build_report_xlsx(rows)
response = HttpResponse(data, content_type=XLSX_MIME)
response["Content-Disposition"] = 'attachment; filename="report.xlsx"'
return responseThe same build_report_xlsx bytes work in Flask (send_file(BytesIO(data), ...)) or FastAPI (StreamingResponse/Response(content=data, media_type=XLSX_MIME)). If you need to export comma-separated data instead, see our guide on generating CSV and Excel files using Python.
XlsxWriter vs openpyxl vs pandas.to_excel
Picking the right tool saves a lot of pain. Here is how the three common options compare:
| Capability | XlsxWriter | openpyxl | pandas .to_excel() |
|---|---|---|---|
Create new .xlsx |
Yes | Yes | Yes |
Read existing .xlsx |
No (write-only) | Yes | Yes (read via read_excel) |
| Edit/append to existing file | No | Yes | No |
| Native charts | Yes (rich) | Yes (basic) | Only via an engine |
| Insert images | Yes (incl. in-memory) | Yes | No |
| Formatting & conditional formats | Extensive | Good | Limited (needs engine) |
| Write speed / large files | Fastest (+ constant-memory mode) | Slower | Depends on engine |
| Best for | Generating polished reports, dashboards, downloads | Reading and modifying existing workbooks | Quickly dumping a DataFrame |
Rule of thumb: generating a new, well-formatted report or chart-heavy export from scratch -> XlsxWriter. Reading or editing a file that already exists -> openpyxl. Quick DataFrame dump with little styling -> pandas to_excel (and set engine="xlsxwriter" when you want to add charts/formatting on top).
Best practices
- Always close the workbook (or use the
withcontext manager). The file is only written on close. - Create format objects once and reuse them. A new
add_formatper cell bloats the file and slows writing. - Use
{"constant_memory": True}in theWorkbookoptions for very large files - it writes row by row and keeps memory flat (with the caveat that data must be written in row order). - Prefer list-form chart/range references (
[sheet, r1, c1, r2, c2]) when ranges are computed from data sizes. - Set
num_format, not pre-formatted strings. Write the real number/date and let Excel format it, so the values stay sortable and usable in formulas. - For downloads, stream from
BytesIOrather than writing a temp file, and set the right MIME type andContent-Disposition. - Remember the write-only boundary: if a requirement says "update this existing spreadsheet", reach for openpyxl instead.
Frequently Asked Questions
XlsxWriter vs openpyxl - which should I use?
Use XlsxWriter when you are creating a new Excel file from scratch and want rich formatting, native charts, images, or the fastest write performance. Use openpyxl when you need to read or modify an existing .xlsx file, because XlsxWriter is write-only and cannot open existing workbooks. Many teams use both: openpyxl to read inputs and XlsxWriter to generate polished outputs.
Can XlsxWriter edit an existing Excel file?
No. XlsxWriter only creates new .xlsx files - it cannot open, read, append to, or edit an existing workbook. To modify a file that already exists (add a sheet, change cells, read values), use openpyxl, or read with pandas.read_excel and re-generate the output. This write-only design is exactly what makes XlsxWriter so fast and feature-complete for fresh exports.
How do I add a chart (bar graph) to an Excel file in Python?
Call chart = workbook.add_chart({"type": "column"}) for a vertical bar graph (use "bar" for horizontal), add data with chart.add_series({"categories": ..., "values": ...}) pointing at worksheet ranges, set the title/axes, then place it with worksheet.insert_chart("D2", chart). The chart is a real, editable Excel chart, not a picture.
How do I insert an image into an Excel file with Python?
Use worksheet.insert_image("B5", "logo.png"). To scale or position it, pass an options dict, e.g. {"x_scale": 0.5, "y_scale": 0.5}. To insert an image that is only in memory (downloaded or generated, not on disk), pass it through {"image_data": BytesIO(image_bytes)}. Supported formats include PNG, JPEG, GIF, BMP, and WebP.
How do I return an Excel file from a web app without saving to disk?
Create the workbook against a BytesIO buffer instead of a filename: workbook = xlsxwriter.Workbook(output, {"in_memory": True}). Write your data, call workbook.close(), then output.getvalue() to get the bytes. Return those bytes with content type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet and a Content-Disposition: attachment header - works in Django, Flask, and FastAPI.
How do I write a pandas DataFrame to Excel with charts and formatting?
Use pd.ExcelWriter("out.xlsx", engine="xlsxwriter"), call df.to_excel(writer, sheet_name="Sheet1"), then access writer.book and writer.sheets["Sheet1"] to add formats, conditional formatting, images, and charts with the full XlsxWriter API. Plain df.to_excel() writes the data but cannot add charts on its own - the engine hook is what unlocks them.
Wrapping up
XlsxWriter turns Python into a serious Excel-generation engine: a few lines to write data, a format object for styling, insert_image for branding, add_chart for native bar graphs, and a BytesIO workbook for zero-disk web downloads. Pair it with pandas for data-heavy reports and remember the one boundary - it writes, it does not read.
If you are building reporting, data exports, or automated document pipelines and want a team that has shipped these for 12+ years across 50+ projects, our Python development services cover everything from data pipelines to production reporting. For related export workflows, see generating PDF files in Python and extracting data from PDF and Office files in Python.