To generate spreadsheet files in Python today, pick the tool by format. For CSV, use the standard-library csv module (csv.writer / csv.DictWriter) - nothing to install. For modern Excel .xlsx files, use openpyxl (read and write) or XlsxWriter (write-only, fast, great formatting and charts). If your data is already in a pandas DataFrame, df.to_csv() and df.to_excel(engine="openpyxl") are the shortest path. Do not start new projects with the old xlwt library - it only writes the legacy .xls format and is effectively unmaintained.
This guide gives you working, copy-paste code for each tool, an Excel-friendly UTF-8 recipe for CSV, a side-by-side comparison table, and download views for Django and Flask. It is written for Python 3.13 (2026) and the current library APIs. We have built data-export and reporting pipelines across Python development projects for 12+ years and 50+ projects, so the patterns below are the ones we ship in production, not just the happy path.
Key takeaways
- CSV: use the built-in
csvmodule -csv.writerfor lists,csv.DictWriterfor dicts. Always open the file withnewline=""to avoid blank rows on Windows. - Excel-friendly CSV: write with
encoding="utf-8-sig"so Excel reads accented and non-Latin characters correctly (the BOM tells Excel it is UTF-8). - Modern
.xlsx: use openpyxl when you also need to read or edit existing files, or XlsxWriter when you are creating new files and want the best formatting, charts, and speed. - pandas: if you already have a DataFrame,
df.to_csv()anddf.to_excel(engine="openpyxl")(orengine="xlsxwriter") are the fastest route. - Do not use
xlwtfor new code. It only writes the legacy.xls(BIFF) format and is unmaintained. Likewise,xlrddropped.xlsxsupport in version 2.0 (2020) and now reads only old.xls. .xlsis the old binary format;.xlsxis the current Open XML format. New projects should produce.xlsx.
CSV vs Excel: which format should I generate?
CSV (comma-separated values) is plain text: one row per line, fields separated by commas. It is universal, tiny, streams easily, and opens in every spreadsheet and database tool - but it carries no types, formulas, formatting, colours, multiple sheets, or charts. Everything is a string.
Excel (.xlsx) is a zipped XML workbook that preserves data types, number/date formats, styling, formulas, multiple worksheets, images, and native charts. It is the right choice when a human will open the file and you want it to look polished, or when you need more than one tab.
Rule of thumb: exporting raw data for another program to ingest, or feeding a data pipeline -> CSV. Producing a report a person will read, with formatting or multiple sheets -> Excel .xlsx.
How do I generate a CSV file in Python?
Use the standard-library csv module - it ships with Python, so there is nothing to install. The two workhorses are csv.writer (you pass each row as a list/tuple) and csv.DictWriter (you pass each row as a dict and declare the column order with fieldnames).
The one detail that trips everyone up: open the file with newline="". The csv module handles line endings itself, and without newline="" you get an extra blank line between every row on Windows.
import csv
rows = [
[1, "Clean Code", "Robert C. Martin"],
[2, "The Pragmatic Programmer", "Hunt & Thomas"],
[3, "Fluent Python", "Luciano Ramalho"],
]
# newline="" is required so csv controls line endings (no blank rows on Windows).
with open("books.csv", "w", newline="", encoding="utf-8") as f:
writer = csv.writer(f)
writer.writerow(["ID", "Title", "Author"]) # header row
writer.writerows(rows) # all data rows at oncewriter.writerow() writes a single row; writer.writerows() writes an iterable of rows in one call. The writer converts each value to a string for you, quoting any field that contains a comma, quote, or newline.
Writing dictionaries with csv.DictWriter
When your data is a list of dicts (very common when it comes from a database or an API), csv.DictWriter is cleaner. You declare fieldnames to fix the column order, call writeheader() once, then writerow/writerows.
import csv
books = [
{"id": 1, "title": "Clean Code", "author": "Robert C. Martin"},
{"id": 2, "title": "Fluent Python", "author": "Luciano Ramalho"},
]
with open("books.csv", "w", newline="", encoding="utf-8") as f:
fieldnames = ["id", "title", "author"]
writer = csv.DictWriter(f, fieldnames=fieldnames)
writer.writeheader() # writes the column names as the first row
writer.writerows(books)
# extrasaction="ignore" silently drops keys not in fieldnames;
# restval="" fills in missing keys. Both are optional safety nets.For the reverse direction - reading dict rows back, including a handy trick for matching headers regardless of case - see our guide on a case-insensitive csv.DictReader in Python.
Make your CSV open correctly in Excel (UTF-8 + BOM)
If your data contains accented or non-Latin characters (cafe with an accent, names in Arabic, emoji, currency symbols), Excel on Windows can mangle them when it guesses the encoding. The fix is to write with encoding="utf-8-sig", which prepends a UTF-8 byte-order mark (BOM). The BOM is the signal Excel looks for to read the file as UTF-8.
import csv
rows = [["Name", "City"], ["Jose", "Sao Paulo"], ["Zoe", "Munchen"]]
# utf-8-sig writes a BOM so Excel detects UTF-8 and shows accents correctly.
with open("people.csv", "w", newline="", encoding="utf-8-sig") as f:
csv.writer(f).writerows(rows)
# Other dialect options you may need:
# csv.writer(f, delimiter=";") # semicolon CSV (common in EU locales)
# csv.writer(f, quoting=csv.QUOTE_ALL) # quote every fieldHow do I generate an Excel (.xlsx) file in Python?
For genuine Excel files there are two modern libraries to know.
- openpyxl - reads and writes
.xlsx. Supports styles, formulas, and charts. Reach for it when you need to open or edit an existing workbook as well as create new ones. - XlsxWriter - write-only
.xlsx. It cannot open existing files, but it is fast and has the richest formatting, conditional-formatting, image, and chart support for fresh exports.
Both produce the current .xlsx (Open XML) format that every modern Excel, Google Sheets, and LibreOffice opens.
Generate an Excel file with openpyxl
Install it with pip install openpyxl. Create a Workbook, grab the active worksheet, append rows, and save. ws.append(list) is the simplest way to add a row left-to-right.
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
ws.title = "Books"
# Header row, made bold.
ws.append(["ID", "Title", "Author"])
for cell in ws[1]:
cell.font = Font(bold=True)
# Data rows.
ws.append([1, "Clean Code", "Robert C. Martin"])
ws.append([2, "Fluent Python", "Luciano Ramalho"])
# Widen a column and write a single cell by reference.
ws.column_dimensions["B"].width = 28
ws["D1"] = "Generated by openpyxl"
wb.save("books.xlsx")openpyxl can also read a workbook (load_workbook("books.xlsx")), modify cells, and save it back - the capability XlsxWriter lacks. That round-trip ability is the main reason to choose openpyxl over XlsxWriter.
Generate an Excel file with XlsxWriter
Install with pip install XlsxWriter (import name is lowercase xlsxwriter). Open a Workbook, add a worksheet, write cells - optionally with a reusable format object - and close. Using the with block guarantees the file is finalised, since the workbook is only written on close.
import xlsxwriter
with xlsxwriter.Workbook("books.xlsx") as workbook:
worksheet = workbook.add_worksheet("Books")
# A reusable format object for the header row.
header = workbook.add_format({"bold": True, "bg_color": "#305496", "font_color": "white"})
worksheet.write_row("A1", ["ID", "Title", "Author"], header)
data = [
[1, "Clean Code", "Robert C. Martin"],
[2, "Fluent Python", "Luciano Ramalho"],
]
for row_num, row in enumerate(data, start=1):
worksheet.write_row(row_num, 0, row)
worksheet.set_column("B:B", 28) # widen the Title column
# File is written automatically when the 'with' block exits.XlsxWriter really shines when you add charts, images, and conditional formatting. For a deeper walkthrough, see creating Excel files with XlsxWriter, including images and bar charts.
Generate CSV and Excel from a pandas DataFrame
If your data already lives in a pandas DataFrame, you do not need the lower-level libraries directly - to_csv() and to_excel() wrap them. For .xlsx, pandas uses an engine under the hood: openpyxl or xlsxwriter (install whichever you pass).
import pandas as pd
df = pd.DataFrame({
"ID": [1, 2, 3],
"Title": ["Clean Code", "Fluent Python", "The Pragmatic Programmer"],
"Author": ["Robert C. Martin", "Luciano Ramalho", "Hunt & Thomas"],
})
# CSV: index=False drops the row numbers; utf-8-sig keeps Excel happy.
df.to_csv("books.csv", index=False, encoding="utf-8-sig")
# Excel via openpyxl (read/write) ...
df.to_excel("books.xlsx", sheet_name="Books", index=False, engine="openpyxl")
# ... or via XlsxWriter (write-only, richer formatting/charts).
df.to_excel("books_xw.xlsx", sheet_name="Books", index=False, engine="xlsxwriter")Why you should not use xlwt (and xlrd) for new code
Many older tutorials - including the 2018 version of this article - reach for xlwt to write Excel files. Avoid it for anything new:
xlwtonly writes the legacy.xls(BIFF) format, the binary format Excel used before 2007. It cannot produce.xlsx. The project is effectively unmaintained.xlrddropped.xlsxsupport in version 2.0 (2020) for security reasons. It now reads only old.xlsfiles. Code that didxlrd.open_workbook("file.xlsx")breaks on modern xlrd.
The net effect: the classic xlwt + xlrd pairing is a dead end for modern spreadsheets. Use openpyxl or XlsxWriter for .xlsx, and the stdlib csv module for CSV. The only reason to touch xlwt/xlrd today is maintaining a legacy system that is genuinely stuck on .xls.
csv vs xlwt vs openpyxl vs XlsxWriter vs pandas
Here is how the options compare at a glance:
| Library | Formats | Read | Write | Charts / images | Maintained? | Best for |
|---|---|---|---|---|---|---|
| csv (stdlib) | .csv |
Yes | Yes | No | Yes (built in) | Plain CSV export/import, streaming, pipelines |
| xlwt | .xls only |
No | Yes | No (limited) | No (legacy) | Nothing new - legacy .xls only |
| openpyxl | .xlsx |
Yes | Yes | Yes (basic) | Yes | Reading and editing/creating .xlsx |
| XlsxWriter | .xlsx |
No | Yes | Yes (rich) | Yes | Creating polished new .xlsx, fast, charts |
| pandas | .csv, .xlsx (via engine) |
Yes | Yes | Via engine | Yes | You already have a DataFrame |
How to choose: CSV -> stdlib csv. New .xlsx you only write -> XlsxWriter. .xlsx you also need to read/edit -> openpyxl. Data already in a DataFrame -> pandas (to_csv / to_excel). Legacy .xls -> openpyxl can't help; that's the one corner where you may still meet xlwt/xlrd, but do not build new systems on it.
How do I return a CSV or Excel file as a download from Django?
In a web app you usually stream the file straight to the browser rather than writing it to disk. Set the right content_type and a Content-Disposition: attachment header so the browser saves it with a filename. For CSV, write the csv module directly to the HttpResponse (it is file-like). For .xlsx, build the workbook in a BytesIO buffer and hand the bytes back.
import csv
from io import BytesIO
import xlsxwriter
from django.http import HttpResponse
XLSX_MIME = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
ROWS = [(1, "Clean Code"), (2, "Fluent Python")]
def export_csv(request):
response = HttpResponse(content_type="text/csv")
response["Content-Disposition"] = 'attachment; filename="books.csv"'
writer = csv.writer(response) # HttpResponse is file-like
writer.writerow(["ID", "Title"])
writer.writerows(ROWS)
return response
def export_xlsx(request):
buffer = BytesIO()
with xlsxwriter.Workbook(buffer, {"in_memory": True}) as workbook:
worksheet = workbook.add_worksheet("Books")
worksheet.write_row("A1", ["ID", "Title"])
for i, row in enumerate(ROWS, start=1):
worksheet.write_row(i, 0, row)
buffer.seek(0)
response = HttpResponse(buffer.getvalue(), content_type=XLSX_MIME)
response["Content-Disposition"] = 'attachment; filename="books.xlsx"'
return responseThe same idea works in Flask and FastAPI: generate the bytes (CSV via io.StringIO, Excel via io.BytesIO), then return them with the matching media type and a Content-Disposition header - send_file(BytesIO(data), ...) in Flask, or Response(content=data, media_type=XLSX_MIME) in FastAPI. If you also produce printable documents, our guide on creating PDF files in Python follows the same stream-to-response pattern.
Frequently Asked Questions
Should I still use xlwt to write Excel files?
No. xlwt only writes the legacy .xls (BIFF) format - it cannot create modern .xlsx files - and the project is effectively unmaintained. For new code, use openpyxl or XlsxWriter to write .xlsx. The only situation where xlwt is justified is maintaining an old system that specifically requires the binary .xls format.
What is the difference between .xls and .xlsx?
.xls is the old binary (BIFF) format Excel used through Excel 2003; it has row/column limits and no clean XML structure. .xlsx is the current Open XML format introduced in Excel 2007 - a zipped XML package that supports far more rows, richer features, and is what every modern spreadsheet tool expects. Generate .xlsx for anything new; treat .xls as legacy-only.
When should I generate CSV instead of Excel?
Generate CSV when another program will read the data, when you need a tiny, streamable, universal format, or when types and formatting do not matter. Generate Excel .xlsx when a person will open the file and you need number/date types preserved, formatting, formulas, multiple sheets, images, or charts. CSV is data interchange; Excel is a presentation-ready document.
Why does my CSV have blank rows between each line on Windows?
Because the file was opened without newline="". The csv module writes its own line endings, and if the file object also translates \n to \r\n (the Windows default), you get a doubled line break. Always open the file as open("out.csv", "w", newline="", encoding="utf-8") and the extra blank rows disappear.
How do I make Excel open my CSV with the correct accented characters?
Write the file with encoding="utf-8-sig" instead of plain "utf-8". The -sig variant prepends a UTF-8 byte-order mark (BOM), which is the signal Excel on Windows uses to detect UTF-8. Without it, Excel may guess the wrong encoding and turn accented or non-Latin characters into garbled text.
openpyxl vs XlsxWriter - which should I choose?
Use openpyxl when you need to read or edit existing .xlsx files as well as create them - it round-trips workbooks. Use XlsxWriter when you are only creating new files and want the richest formatting, conditional formatting, images, and native charts, plus faster writes and a constant-memory mode for huge files. XlsxWriter cannot open existing workbooks; openpyxl can. Many teams use openpyxl to read inputs and XlsxWriter to generate polished outputs. If your data is already in a pandas DataFrame, df.to_excel(engine="openpyxl") or engine="xlsxwriter" wraps either one for you.
Wrapping up
Generating spreadsheets in Python in 2026 comes down to matching the tool to the format: the stdlib csv module for CSV (remember newline="" and utf-8-sig), openpyxl or XlsxWriter for modern .xlsx, and pandas when your data is already a DataFrame. Leave xlwt and xlrd to legacy .xls maintenance - they are not the right foundation for new work.
If you are building reporting, data-export, or document-generation pipelines and want a team that has shipped them across 50+ projects over 12+ years, our Python development services cover everything from CSV/Excel exports to full data platforms. For related export workflows, see creating Excel files with XlsxWriter (images and charts) and creating PDF files in Python.