Openpyxl 3 1 3

openpyxl 3.1.3

Overview

openpyxl is a Python library for reading and writing Excel 2010+ files in the Office Open XML format (.xlsx, .xlsm, .xltx, .xltm). It provides full control over workbook structure, cell data, styles, charts, conditional formatting, data validation, tables, filters, images, and more.

Key classes:

• Workbook — top-level container for worksheets
• Worksheet — individual sheet within a workbook
• Cell — single cell with value, style, and formula support

openpyxl follows the OOXML specification closely. It does not evaluate formulas, filter data, or sort rows — those operations happen in Excel itself. openpyxl writes the instructions; Excel executes them.

When to Use

• Creating Excel files from Python data (reports, exports, templates)
• Reading and extracting data from existing .xlsx files
• Applying styles, charts, conditional formatting, or data validation to spreadsheets
• Generating large datasets with optimized read-only or write-only modes
• Converting between Pandas DataFrames and Excel worksheets
• Adding images, comments, hyperlinks, or page setup to workbooks

Installation / Setup

pip install openpyxl

Optional dependencies:

• pillow — required for inserting images (pip install pillow)
• lxml — improves performance when creating large files
• defusedxml — guards against XML-based attacks when reading untrusted files

Usage Examples

Create and save a workbook

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales"

# Write data row by row
ws.append(["Product", "Q1", "Q2", "Q3"])
ws.append(["Widget A", 100, 150, 200])
ws.append(["Widget B", 80, 120, 95])

wb.save("sales.xlsx")

Read an existing workbook

from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)

Quick chart

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = "Quarterly Sales"
chart.y_axis.title = "Units"
data = Reference(ws, min_col=2, min_row=1, max_row=3, max_col=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=3)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "E2")
wb.save("sales_with_chart.xlsx")

Advanced Topics

Workbooks: Workbook creation, loading flags, sheet management, saving modes, document properties → Workbooks

Cells and Data: Cell access patterns, range iteration, data appending, row/column operations, coordinate utilities → Cells and Data

Styles and Formatting: Fonts, fills, borders, alignment, named styles, number formats, page setup → Styles and Formatting

Charts: All chart types (bar, line, pie, scatter, bubble, area, doughnut, radar, stock, surface), axes, layout, patterns → Charts

Conditional Formatting: ColorScale, IconSet, DataBar, CellIsRule, FormulaRule, expression rules → Conditional Formatting

Worksheet Features: Merging, data validation, auto-filters, tables, comments, hyperlinks, protection, print settings, pivot tables, formulas, datetime handling → Worksheet Features

Images and Drawings: Image insertion, drawing anchors, rich text in cells → Images and Drawings

Performance and Modes: Read-only mode, write-only mode, memory considerations, parallelization → Performance and Modes

Pandas Integration: DataFrame to worksheet, worksheet to DataFrame, NumPy type support → Pandas Integration