Skip to content

Openpyxl

Data & AnalyticsDataPython
Official docs ↗GitHub ↗

What it is

Openpyxl is a Python library to read, write, and modify Excel 2010 xlsx/xlsm/xltx/xltm files. It allows you to create spreadsheets, read data, and perform operations on Excel files programmatically.

Openpyxl allows creating new Excel workbooks, reading existing files, modifying cells, formatting, adding charts, and saving changes. It supports formulas, styles, merged cells, and more.

Installation

pip install openpyxl

Getting started

The smallest useful thing you can do with it, and what each part means.

Creating a new workbook and adding data
from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Name'
sheet['B1'] = 'Age'
sheet.append(['Alice', 30])
sheet.append(['Bob', 25])
wb.save('example.xlsx')
Creates a new Excel file with headers and two rows of data, then saves it.
Reading an existing workbook
from openpyxl import load_workbook
wb = load_workbook('example.xlsx')
sheet = wb.active
for row in sheet.iter_rows(values_only=True):
    print(row)
Opens an existing Excel file and prints all rows in the active sheet.

Advanced usage

Where the library earns its place over a simpler alternative.

Formatting cells
from openpyxl.styles import Font
sheet['A1'].font = Font(bold=True, color='FF0000')
wb.save('example.xlsx')
Applies bold and red font to a specific cell.
Adding formulas
sheet['C2'] = '=SUM(B2:B3)'
wb.save('example.xlsx')
Adds a formula to sum values in a column.
Merging and unmerging cells
sheet.merge_cells('A4:B4')
sheet['A4'] = 'Merged Cell'
wb.save('example.xlsx')
Merges two cells and assigns a value to the merged area.
Adding charts
from openpyxl.chart import BarChart, Reference
chart = BarChart()
data = Reference(sheet, min_col=2, min_row=1, max_row=3, max_col=2)
chart.add_data(data, titles_from_data=True)
sheet.add_chart(chart, 'E5')
wb.save('example.xlsx')
Adds a bar chart to visualize the data in the worksheet.

Errors and fixes

The failures you are most likely to hit, and what actually resolves them.

FileNotFoundError
Ensure the path to the Excel file exists when using `load_workbook()`.
InvalidFileException
Make sure the file is a valid .xlsx, .xlsm, .xltx, or .xltm file.
KeyError: Worksheet
Check that the worksheet name exists before accessing it with `wb[sheet_name]`.

Best practices

  • Always close or save the workbook after modifications to persist changes.
  • Use `iter_rows(values_only=True)` when reading large files to save memory.
  • Leverage cell styles and formats for readability in reports.
  • Avoid modifying open files from multiple processes simultaneously.
  • Use formulas and charts to enhance automation and reporting capabilities.

Background

Why it exists, and what it was reacting to.

Openpyxl was created by Eric Gazoni and Charlie Clark to provide Python developers with a tool to interact with Excel files without relying on Microsoft Excel itself. It has become the standard library for working with modern Excel files in Python, widely used for data automation, reporting, and spreadsheet manipulation.

Related libraries

Others in Data & Analytics for Python.

← All Python librariesPython guide