Migrate from openpyxl¶

WolfXL 2.0 audit note: the tracked openpyxl-parity surface is green for compatibility and fidelity, including pivot tables, pivot caches, and pivot-chart linkage. The current strict SOTA audit marks the supported-scope gate green for the current measured clean-source OpenPyXL and Rust/Rust-backed benchmark lanes. The broader all-future-surface claim is still not ready, so this guide stays scoped to the tracked openpyxl construction idioms and the caveats below.

TL;DR — minimal import change¶

# before
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image
from openpyxl.comments import Comment
from openpyxl.pivot.table import TableDefinition
from openpyxl.pivot.cache import CacheDefinition

# after
from wolfxl import load_workbook, Workbook
from wolfxl.styles import Font, PatternFill, Alignment, Border
from wolfxl.utils import get_column_letter, column_index_from_string
from wolfxl.chart import BarChart, Reference
from wolfxl.drawing.image import Image
from wolfxl.comments import Comment
from wolfxl.pivot import PivotTable, PivotCache

Almost every openpyxl import has the same name under wolfxl. The exceptions live in Compatibility Matrix.

What usually stays the same¶

wb = load_workbook("data.xlsx")
ws = wb["Sheet"]
ws["A1"].value
ws["A1"].font.bold
ws["A1"] = "hello"
ws["B2"].font = Font(bold=True)
ws["B2"].fill = PatternFill("solid", fgColor="FFFF00")

ws.merge_cells("A1:B2")
ws.append(["a", "b", "c"])
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)
ws.cell(row=3, column=2, value="x")

wb.save("out.xlsx")

Construction-side parity (v2.0 audit target)¶

WolfXL 1.7 was the first release where the non-pivot construction-side idioms all work end-to-end with the same code you'd write against openpyxl 3.1.x. WolfXL 2.0 closes the pivot-table gap in the tracked parity matrix for compatibility and fidelity. Fresh clean-source benchmark reruns are still required before treating package-level performance proof as current supported-scope SOTA evidence, and the broader all-future-surface claim remains gated by the final blocker audit.

Plain English boundary: this is not a universal replacement claim for every team and every workflow today. The current evidence still names these broad-claim boundaries:

Registered release-artifact lanes are proven, but not every future package route or installer.
Openpyxl still has ecosystem maturity and long-tail workflow history.
High-risk render variant space remains open-ended.
Click-level Excel interaction variant space remains open-ended.
Future or unseen real-world Excel surfaces cannot be fully exhausted.

Charts (v1.6 + v1.6.1)¶

Sixteen chart types ship at full openpyxl-3.1.x feature depth:

Family	Classes
Bar	`BarChart`, `BarChart3D`
Line	`LineChart`, `LineChart3D`
Pie	`PieChart`, `PieChart3D` (alias `Pie3D`), `DoughnutChart`, `ProjectedPieChart`
Area	`AreaChart`, `AreaChart3D`
Scatter	`ScatterChart`
Bubble	`BubbleChart`
Radar	`RadarChart`
Surface	`SurfaceChart`, `SurfaceChart3D`
Stock	`StockChart` (Open-High-Low-Close)

from wolfxl import Workbook
from wolfxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Region", "Q1", "Q2", "Q3", "Q4"])
ws.append(["NA", 100, 110, 120, 140])
ws.append(["EU", 80,  95,  110, 85])
ws.append(["APAC", 60, 70, 85, 100])

chart = BarChart()
chart.title = "Quarterly Revenue"
chart.style = 10
chart.x_axis.title = "Region"
chart.y_axis.title = "Revenue (USD)"

data = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

ws.add_chart(chart, "G2")
wb.save("out.xlsx")

Sprint Ξ (v1.7) adds two more chart-management methods:

ws.remove_chart(chart)            # mirrors openpyxl ws._charts.remove(chart)
ws.replace_chart(old, new)        # convenience: keeps anchor + list position

Modify-mode add_chart works with any of the 16 families:

wb = load_workbook("template.xlsx", modify=True)
ws = wb.active
chart = BarChart()
# ... configure chart ...
ws.add_chart(chart, "B10")
wb.save("template.xlsx")

Images (v1.5)¶

from wolfxl.drawing.image import Image

img = Image("logo.png")          # PNG / JPEG / GIF / BMP
img.width = 200
img.height = 100
ws.add_image(img, "A1")          # one-cell anchor

# Two-cell anchor:
from wolfxl.drawing import TwoCellAnchor, AnchorMarker
img2 = Image("chart.png")
img2.anchor = TwoCellAnchor(
    _from=AnchorMarker(col=2, row=2, colOff=0, rowOff=0),
    to=AnchorMarker(col=8, row=10, colOff=0, rowOff=0),
)
ws.add_image(img2)

Encrypted reads + writes (v1.3 read; v1.5 write)¶

# read
wb = load_workbook("encrypted.xlsx", password="hunter2")

# write — Agile (AES-256 / SHA-512) on save
wb = Workbook()
# ... build workbook ...
wb.save("secret.xlsx", password="hunter2")

Install with pip install wolfxl[encrypted] (pulls msoffcrypto-tool).

Streaming reads — `read_only=True` (v1.3)¶

wb = load_workbook("huge.xlsx", read_only=True)
for row in wb.active.iter_rows(values_only=True):
    process(row)

Auto-engages for sheets with > 50,000 rows even when the caller didn't opt in. Streaming cells are immutable — assignment raises RuntimeError.

`.xlsb` and `.xls` reads (v1.4)¶

wb_b = load_workbook("data.xlsb")     # Binary OOXML
wb_x = load_workbook("data.xls")      # Legacy BIFF8

# Modify mode + read_only + password are xlsx-only.
# To round-trip a .xlsb to .xlsx, transcribe via a fresh Workbook().

Modify-mode mutations (v1.0 / v1.1)¶

Every T1.5 mutation that openpyxl supports works in WolfXL modify mode (surgical ZIP patching — much faster than a full DOM rewrite):

wb = load_workbook("template.xlsx", modify=True)

# Document properties
wb.properties.title = "Q4 2025 Report"
wb.properties.creator = "Finance Team"

# Defined names
from wolfxl.defined_names import DefinedName
wb.defined_names["RevenueRange"] = DefinedName(
    name="RevenueRange",
    attr_text="Sheet1!$B$2:$B$100",
)

# Comments + hyperlinks + tables + DV + CF
from wolfxl.comments import Comment
ws["A1"].comment = Comment("Reviewed", "auditor")

from wolfxl.cell.hyperlink import Hyperlink
ws["B1"].hyperlink = Hyperlink(target="https://example.com", display="Source")

from wolfxl.worksheet.table import Table, TableStyleInfo
ws.add_table(Table(displayName="Sales", ref="A1:D10"))

from wolfxl.worksheet.datavalidation import DataValidation
dv = DataValidation(type="whole", operator="between", formula1=0, formula2=100)
dv.add("A1:A100")
ws.data_validations.append(dv)

from wolfxl.formatting.rule import CellIsRule
from wolfxl.styles import PatternFill
ws.conditional_formatting.add(
    "B2:B100",
    CellIsRule(operator="greaterThan", formula=["100"],
               fill=PatternFill(fgColor="FFFF00", patternType="solid")),
)

wb.save("template.xlsx")

Structural ops (v1.1)¶

# Insert / delete rows + columns; everything shifts (formulas, hyperlinks,
# CF rules, data validations, defined names, tables, conditional formatting).
ws.insert_rows(idx=2, amount=3)
ws.delete_rows(idx=10, amount=1)
ws.insert_cols(idx=2, amount=2)
ws.delete_cols(idx=5, amount=1)

# Move a 2D range
ws.move_range("B2:D5", rows=3, cols=1)

# Copy a worksheet (deep-clones tables, DV, CF, sheet-scoped defined
# names, charts with cell-range re-pointing)
ws_copy = wb.copy_worksheet(wb["Source"])

# Reorder sheets
wb.move_sheet("Sheet2", offset=-1)

Rich text (v1.3)¶

from wolfxl.cell.rich_text import CellRichText, TextBlock, InlineFont

cell = ws["A1"]
cell.value = CellRichText([
    TextBlock(InlineFont(b=True), "Bold "),
    "and ",
    TextBlock(InlineFont(color="FF0000"), "red"),
])

# Reading:
wb = load_workbook("rich.xlsx", rich_text=True)
print(ws["A1"].value)             # CellRichText(...)
print(ws["A1"].rich_text)         # always returns CellRichText (or None)

Pivot tables (Sprint Ν / v2.0)¶

WolfXL 2.0 closes the last construction-side gap. Pivot caches, pivot tables, and pivot-chart linkage all ship; the v0.5+ _make_stub is replaced with real classes.

import wolfxl
from wolfxl.chart import Reference, BarChart
from wolfxl.pivot import PivotCache, PivotTable

wb = wolfxl.load_workbook("source-data.xlsx", modify=True)
ws = wb.active
ws.append(["region", "quarter", "product", "revenue"])
ws.append(["NA",     "Q1",      "Widget",  100])
ws.append(["NA",     "Q2",      "Widget",  120])
ws.append(["EU",     "Q1",      "Widget",   80])
ws.append(["EU",     "Q2",      "Widget",   95])
# ... fill source data ...

# 1. Build a cache around the source range:
src = Reference(ws, min_col=1, min_row=1, max_col=4, max_row=100)
cache = wb.add_pivot_cache(PivotCache(source=src))

# 2. Build a pivot table referencing the cache:
pt = PivotTable(
    cache=cache,
    location="F2",
    rows=["region"],
    cols=["quarter"],
    data=[("revenue", "sum")],          # or [DataField("revenue", function="sum")]
)
ws.add_pivot_table(pt)

# 3. (Optional) link a chart to the pivot:
chart = BarChart()
chart.title = "Revenue by region × quarter"
chart.pivot_source = pt                  # emits <c:pivotSource>
ws.add_chart(chart, "F18")

wb.save("pivot.xlsx")

Open pivot.xlsx in Excel, LibreOffice, or read it with openpyxl.load_workbook(...) — the pivot's data is already populated; no refresh-on-open is required. In the current project comparison, WolfXL is the only Python OOXML library we have identified that constructs pivot tables with pre-aggregated pivotCacheRecords (openpyxl preserves them on round-trip but doesn't construct them; XlsxWriter doesn't support pivots at all). Keep public "first/only" wording tied to the current trust reports.

Import paths¶

openpyxl path	WolfXL path
`from openpyxl.pivot.table import TableDefinition`	`from wolfxl.pivot import PivotTable`
`from openpyxl.pivot.cache import CacheDefinition`	`from wolfxl.pivot import PivotCache`
`from openpyxl.pivot.fields import PivotField, DataField, RowField, ColumnField, PageField`	`from wolfxl.pivot import PivotField, DataField, RowField, ColumnField, PageField`
`from openpyxl.pivot.table import PivotItem, Location, PivotTableStyleInfo`	`from wolfxl.pivot import PivotItem, Location, PivotTableStyleInfo`
`Reference(ws, min_col=..., min_row=..., max_col=..., max_row=...)`	Same — re-uses `wolfxl.chart.Reference` (mirrors openpyxl 3.1.x's shared reference type)

API differences¶

openpyxl exposes a one-step ws.add_pivot(table) (where the cache is implied). WolfXL splits cache and table into two steps:

# wolfxl — explicit two-step, modify-mode workbook
wb = wolfxl.load_workbook("source-data.xlsx", modify=True)
cache = wb.add_pivot_cache(PivotCache(source=src))
pt    = PivotTable(cache=cache, location="F2", ...)
ws.add_pivot_table(pt)

The split exists because OOXML caches are workbook-scoped (one cache can serve multiple tables) while tables are sheet-scoped. Aliasing a cache to several tables is the same idiom wolfxl uses for shared image media on copy_worksheet.

Aggregator functions¶

DataField(function=...) accepts:

wolfxl name	OOXML name
`"sum"`	Sum
`"count"`	Count
`"average"`	Average
`"max"`	Max
`"min"`	Min
`"product"`	Product
`"count_nums"`	CountNums
`"std_dev"`	StdDev
`"std_dev_p"`	StdDevp
`"var"`	Var
`"var_p"`	Varp

The bare-string axis spec (rows=["region"]) is shorthand for rows=[RowField("region")] and uses the field's column header as the caption. Use the explicit builders (RowField, ColumnField, DataField, PageField) for custom captions, custom subtotals, or custom sort orders.

Limits¶

OLAP / external pivot caches — needs the PowerPivot data-model (xl/model/). Out of scope permanently.
Pivot-table styling beyond the current PivotArea / pivot-CF support — broader themes and banded-format polish remain limited.
Pivot cache record regeneration after layout edits — existing pivot source ranges, row/column/page field placement, page-field selection, and data-field aggregation can be edited. Layout edits stamp refreshOnLoad="1" and let Excel regenerate derived cache records on open rather than recalculating pivotCacheRecords inside WolfXL. This is not an openpyxl advantage; openpyxl preserves existing pivot parts but does not provide a public cache-record regeneration engine.

Caveat — empty cache¶

If the chart's pivot_source points at a PivotTable whose cache walked an empty source range, the chart will render blank in Excel. This matches openpyxl's behaviour and is documented in RFC-049 §10.4. Validate the source range has at least one data row before constructing.

What to validate during migration¶

Style fidelity in your critical sheets — open the saved workbook in Excel and diff visually. WolfXL's tests/parity/openpyxl_surface.py ratchet tracks every flaky serialisation.
Formula behavior in your downstream consumers — formulas are preserved verbatim; cached results are recomputed when Excel opens.
Pivot tables — fully constructible from Python (v2.0+) with pre-aggregated records, so the saved workbook opens in any OOXML reader (Excel / LibreOffice / openpyxl) without refresh-on-open. See "Pivot tables (Sprint Ν / v2.0)" above.
Rare openpyxl APIs — see the Compatibility Matrix for anything that's Partial or Not Yet.

Migration playbook¶

Swap imports in one workflow.
Run your existing test suite — for the tracked supported openpyxl API rows outside the caveats below, wolfxl preserves the same read/write behavior, so most tests should pass unchanged.
Compare a representative output workbook in Excel side-by-side with the openpyxl-produced version.
Measure runtime/memory — see Performance.
Roll out gradually to other pipelines.

Edge cases worth knowing¶

Worksheet.max_row / max_column — public properties (not methods).
merged_cells — backed by _MergedCellsProxy in read mode.
Cell.coordinate — always uppercase (e.g. "A1").
Cell.number_format — accepts the same Excel format strings openpyxl does.
copy_worksheet — diverges from openpyxl in five documented ways (preserves tables, DV, CF, sheet-scoped defined names, image media). See tests/parity/KNOWN_GAPS.md "RFC-035 — copy_worksheet divergences from openpyxl" for the full record. In those five documented cases, WolfXL preserves more workbook state than openpyxl.

When to keep openpyxl alongside¶

You depend on an openpyxl API that is not listed as supported in the Compatibility Matrix.
You have a business-critical template whose Excel visual output has not yet been checked against your own acceptance files.
You have a pivot-table workflow that needs OLAP / external caches or visual styling beyond the current PivotArea / pivot-CF support; keep Excel or your template-authoring workflow in the loop for that case.
You need an adjacent spreadsheet surface that neither openpyxl nor WolfXL treats as tracked openpyxl parity, such as OpenDocument (.ods) authoring.
Your team values openpyxl's older ecosystem, staff familiarity, existing integrations, or dependency approval history more than WolfXL's measured advantages for this workflow.

For the tracked supported openpyxl API surface outside those caveats, v2.0 is intended as a drop-in replacement. Validate business-critical templates with your own files before removing openpyxl from a production pipeline.