Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

python xlsxwriter vs openpyxl: Choosing the Right Excel Library

python xlsxwriter vs openpyxl: Compare xlsxwriter and openpyxl for Python Excel workflows: API design, performance, formatting, and when to use each.

~6 min readPython
#xlsxwriter#openpyxl#Excel#Python
A split illustration comparing the Python libraries xlsxwriter and openpyxl for Excel file generation, showing a stream of data on one side and an editable workbook on the other.

Choosing between xlsxwriter and openpyxl is mostly about whether you need to read and modify existing workbook files. xlsxwriter is write-only and is a strong fit for creating new exports and polished reports. openpyxl can also load, edit, and save existing .xlsx files, which makes it necessary for template workflows. Both libraries generate .xlsx files, but they take different approaches to writing, reading, and formatting data. Understanding those differences matters because the choice affects memory usage, formatting options, and how much code you need to maintain.

Core Differences in API Design

The most visible difference is that xlsxwriter is write-only. It creates new workbooks and writes data into them, but it cannot read or modify an existing file. openpyxl, on the other hand, can load an existing workbook, change cells, and save it back. This single distinction drives most other design choices.

With xlsxwriter, you create a workbook and add worksheets, then write cell by cell or row by row:

python
import xlsxwriter workbook = xlsxwriter.Workbook('report.xlsx') worksheet = workbook.add_worksheet() worksheet.write('A1', 'Quarter') worksheet.write('B1', 'Revenue') worksheet.write(1, 0, 'Q1') worksheet.write(1, 1, 12000) workbook.close()

With openpyxl, you can do the same, but you also have the option to load an existing file:

python
from openpyxl import Workbook wb = Workbook() ws = wb.active ws['A1'] = 'Quarter' ws['B1'] = 'Revenue' ws.append(['Q1', 12000]) wb.save('report.xlsx')

Both APIs are straightforward, but openpyxl uses a cell model in which you assign values to cells by coordinate, while xlsxwriter uses explicit write() methods. This difference becomes more pronounced when you handle formulas, formatting, and merged cells.

Writing Data: Performance and Memory Behavior

The libraries differ significantly in how they build the final file. xlsxwriter is designed for writing new workbooks and, when used with constant_memory=True, keeps memory usage from growing with the size of the data. This makes it a strong choice for generating very large files where memory is a concern.

openpyxl keeps the entire workbook in memory by default. When you modify a cell, it updates an in-memory representation of the worksheet. When you call save(), it serializes the whole workbook to disk. This approach is useful for reading and modifying existing files, but memory usage grows with the number of cells and the amount of formatting. openpyxl also provides a write-only mode (write_only=True) that is designed for creating large files from scratch, but it cannot read or modify existing workbooks and has some limitations compared with the normal mode.

For a one-time script that writes a million rows, xlsxwriter with constant_memory=True can keep memory use much lower than openpyxl's default mode. If you never need to read an existing file, xlsxwriter is often the better fit for large data dumps. If you need round-trip workflows, openpyxl is the choice.

Formatting and Styling Capabilities

Both libraries support cell formatting, but their feature sets overlap only partially. xlsxwriter is known for its rich formatting options: conditional formatting, data bars, color scales, sparklines, and a wide range of number formats. It also supports charts and images and is generally more complete for generating polished reports from scratch.

openpyxl also supports formatting, charts, and images, but its conditional formatting and some advanced features are less extensive. However, because openpyxl can read existing formatting, it is the only choice when you need to preserve or update styles in a workbook that was created elsewhere.

For example, to apply a currency format in xlsxwriter:

python
currency_format = workbook.add_format({'num_format': '$#,##0'}) worksheet.write('B2', 12000, currency_format)

In openpyxl, number formatting is a cell property:

python
ws['B2'] = 12000 ws['B2'].number_format = '$#,##0'

The APIs differ, but both give you control over the final appearance. The deciding factor is whether you need to read existing formatting or whether you are generating a fresh file.

Reading and Modifying Existing Workbooks

openpyxl is the clear winner when you need to read an .xlsx file, change a few cells, and save it back. Its load_workbook() function parses the file and gives you access to worksheets, cells, and styles. xlsxwriter cannot do this at all.

python
from openpyxl import load_workbook wb = load_workbook('existing.xlsx') ws = wb.active ws['A1'] = 'Updated value' wb.save('existing_modified.xlsx')

This capability is essential for automated reporting that must fill in a pre-designed template. If your workflow starts with a template that contains formulas and formatting, openpyxl is the only viable option among these two.

Handling Large Datasets

When generating very large files, xlsxwriter with constant_memory=True avoids accumulating the whole workbook in memory. You can write rows in a loop without keeping the entire dataset in memory. openpyxl also has a write-only mode (write_only=True) for creating new files with lower memory usage, but it cannot read existing workbooks and has limitations compared with normal mode.

Consider a scenario where you need to export a database table with hundreds of thousands of rows. With xlsxwriter, you can iterate over the query result and write each row directly:

python
import xlsxwriter workbook = xlsxwriter.Workbook('export.xlsx', {'constant_memory': True}) worksheet = workbook.add_worksheet() for row_idx, row in enumerate(query_result): worksheet.write_row(row_idx, 0, row) workbook.close()

Here query_result is an iterable of row tuples or lists. For cases where you need to read or edit existing files, use openpyxl in its normal mode.

Choosing Based on Your Use Case

The decision between xlsxwriter and openpyxl should be driven by whether you need to read existing workbooks and by the size of the data you generate.

Use xlsxwriter when you are creating new files from scratch, especially if you are generating large datasets or need advanced formatting like conditional formatting and sparklines. For large exports, enable constant_memory=True; the formatting API is comprehensive.

Use openpyxl when you must read or modify an existing .xlsx file. This includes filling in templates, updating specific cells, or adjusting styles in workbooks created elsewhere. It is also a reasonable choice for small to medium files where memory is not a concern.

For a project that requires both reading and writing, you might end up using both libraries: openpyxl to load and modify a template, and xlsxwriter to generate a large data sheet that another openpyxl step can read in later. This is a common pattern in reporting pipelines.

Compatibility and File Format Details

Both libraries generate .xlsx files that are compatible with Excel 2007 and later. They also support .xlsm for macro-enabled workbooks. xlsxwriter cannot open and edit an existing macro-enabled file, but it can embed a pre-built VBA project with add_vba_project() when you provide a vbaProject.bin extracted from another workbook. openpyxl can load and save .xlsm files, making it the better choice when you need to manipulate macro-enabled workbooks, though it is worth checking that all objects survive a save cycle.

Another subtle difference is how the libraries handle formulas. xlsxwriter writes formulas as strings and does not calculate their results; it leaves that to Excel when the file is opened. openpyxl can also write formulas, and it can read cached formula results from an existing file when you load with data_only=True, provided the file was saved with such values. If your workflow relies on reading calculated values, openpyxl gives you that access.

These compatibility details matter when you are integrating with systems that consume the files programmatically, such as data pipelines that parse values without opening Excel.

Final Code Example: A Practical Comparison

To see the two libraries side by side, consider a simple task: create a workbook with a header row, a few data rows, and a sum formula. Here is the xlsxwriter version:

python
import xlsxwriter workbook = xlsxwriter.Workbook('sales.xlsx') ws = workbook.add_worksheet('Sales') headers = ['Product', 'Units', 'Price'] ws.write_row(0, 0, headers) data = [['Widget', 10, 5.5], ['Gadget', 7, 9.0]] for r, row in enumerate(data, start=1): ws.write_row(r, 0, row) ws.write(4, 0, 'Total') ws.write(4, 2, '=SUM(C2:C3)') workbook.close()

And the openpyxl equivalent:

python
from openpyxl import Workbook wb = Workbook() ws = wb.active ws.title = 'Sales' headers = ['Product', 'Units', 'Price'] ws.append(headers) data = [['Widget', 10, 5.5], ['Gadget', 7, 9.0]] for row in data: ws.append(row) ws['A5'] = 'Total' ws['C5'] = '=SUM(C2:C3)' wb.save('sales.xlsx')

Both produce a valid .xlsx file. The xlsxwriter version uses explicit row indices, while openpyxl uses append(), which is more convenient when you are adding rows sequentially. The choice is not about which syntax is better, but about the broader capabilities you need beyond this simple case.

When you are building a new reporting system, evaluate your data volume and whether you need to read existing files. Those two factors will point you to the right library more reliably than any feature list.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.