Skip to content

Worksheet API

Version scope

This API reference describes WolfXL 2.2.0.

Access

  • wb["Sheet1"]
  • ws["A1"]
  • ws.cell(row=1, column=1, value=...)

Core methods

  • iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
  • iter_cell_records(min_row=None, max_row=None, min_col=None, max_col=None, data_only=None, include_format=True, include_empty=False, include_formula_blanks=True, include_coordinate=True)
  • cell_records(...)
  • calculate_dimension() — returns the actual used range, including offset ranges like C4:C4
  • merge_cells(range_string) (write mode)

Examples

ws["A1"] = "Hello"
cell = ws.cell(2, 1, "World")

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

Bulk Cell Records

Use cell_records() when you need values plus compact formatting metadata without paying for one Python property access per cell.

records = ws.cell_records(include_format=True)

for record in records:
    print(
        record["coordinate"],
        record["value"],
        record.get("number_format"),
        record.get("bold", False),
    )

Each record uses openpyxl-style 1-based coordinates:

  • row, column, coordinate
  • value, data_type
  • formula when the cell contains a formula
  • style_id, number_format, bold, italic, font_size, h_align, indent
  • bottom_border_style, has_bottom_border, is_double_underline

Empty cells are skipped by default. Pass include_empty=True to emit a dense rectangular record stream for the requested range.

Formula cells return formula text by default. Pass data_only=True to return cached formula values instead; uncached formula blanks are skipped unless include_empty=True. For ingestion/dataframe workloads that do not want sparse template formulas to inflate the record stream, pass include_formula_blanks=False. Pass include_coordinate=False when 1-based row / column integers are enough and you want to skip A1 coordinate string allocation. Pass include_style_id=False when you need semantic format fields but not workbook-internal style ids. Pass include_extended_format=False to keep raw font flags and number formats while skipping style-grid fields such as fill, alignment, and border cues.

calculate_dimension() follows openpyxl's used-range shape. A blank sheet returns A1:A1; a sheet with only C4 populated returns C4:C4; a sheet with values from A1 through C7 returns A1:C7. max_row and max_column continue to expose the bottom/right edge of that range.

Find and replace

Search text cell values, formulas, or comments in deterministic row-major order:

matches = ws.find_all("revenue", case_sensitive=False, whole_cell=True)
first = ws.find_one("SUM", scope="formulas")
preview = ws.replace_all("Draft", "Approved", scope="comments", dry_run=True)
report = ws.replace_all("Draft", "Approved", scope="comments")

Use regex=True for regular expressions and scope="values", "formulas", or "comments" to select the edited content. FindMatch describes each matched cell; ReplaceReport records matches, replacements, skips, and dry-run state. Use limit with find_all and count with replace_all to bound results. Value replacement preserves literal formula-looking text. Drawing text and run-level rich-text editing are outside this cell-level API.

Remove duplicate rows

Deduplicate a worksheet rectangle, keeping the first occurrence of each key:

report = ws.remove_duplicates("A1:D20", key_columns=["A", "D"], header=True)
assert report.rows_removed == 3

key_columns accepts absolute 1-based column indexes or letters and defaults to every column in the range. header=True (the default) preserves the first row untouched, and ignore_case=True compares text keys case-insensitively using Unicode casefold while formula text is never folded. Keys compare typed: booleans are distinct from numbers, None is distinct from empty text, 1 equals 1.0, NaN matches NaN, integers above 2**53 keep exact precision, and dates compare by workbook-epoch serial.

Duplicate rows are physically removed with Worksheet.delete_rows, shifting later rows up. To prevent silent data loss, the operation refuses before any mutation when a candidate removed row carries populated values, comments, or hyperlinks outside the range's columns; when the range or a removed row intersects merged cells, tables, array formulas, or data-table hosts; or while structural shifts are still pending on the sheet. Read-only and write-only worksheets are refused. No cross-sheet or external reference is rewritten and no recalculation is performed. The immutable RemovalReport records evaluated, kept, and removed counts plus the original 1-based row identities of kept and removed rows.

Data cleaning helpers

Clean text values and populate blank cells safely across bounded rectangular ranges or the worksheet's populated bounds:

# Trim leading/trailing ASCII spaces and collapse consecutive internal spaces
trim_report = ws.trim_cells("A1:D100")

# Remove ASCII control characters 0..31 (NUL, tabs, newlines, CR, etc.)
clean_report = ws.clean_cells()

# Fill genuine blank cells (excluding formulas returning "" and existing text)
fill_report = ws.fill_blanks("B2:B50", 0)

Trim and Clean Semantics

Worksheet.trim_cells(cell_range=None) implements exact ASCII space trim/collapse: - Leading and trailing ASCII spaces (0x20) are stripped. - Consecutive internal runs of multiple ASCII spaces are collapsed to a single ASCII space. - Non-ASCII whitespace (such as U+00A0 non-breaking space, U+2009 thin space, and U+3000 ideographic space) and ASCII control characters (such as newlines and tabs) are preserved and never stripped or collapsed. - Formula cells (data_type == "f", array formulas, data tables) are never altered. - Formula-like string typing is strictly preserved: strings starting with = (or that start with = after trimming leading spaces) retain explicit string typing (data_type = "s") and are never converted to formulas. - Cells of non-string types (numbers, booleans, dates, errors, blanks) and cells outside the range are completely untouched.

Worksheet.clean_cells(cell_range=None) implements ASCII 0..31 control removal: - ASCII control characters in the 0..31 range (such as TAB \t / 9, LF \n / 10, and CR \r / 13) are stripped from string values. In standard OOXML and Python cell assignment, non-whitespace control codes below 32 are disallowed by XML string validation (IllegalCharacterError), bounding public API coverage to valid XML control characters. - ASCII space (0x20 / 32) and all characters with code point 32 or higher (including DEL \x7f, extended ASCII, and all Unicode characters) are preserved. - Formula cells are never altered; formula-like string typing is preserved. When cell_range is omitted (None), the operation targets the populated used bounds of the worksheet without materializing empty cells.

Fill Blanks Semantics

Worksheet.fill_blanks(cell_range, value) fills genuine blank cells: - Only genuine blank cells (cell.value is None and no formula) are filled with value. - Cells containing existing values (numbers, booleans, dates, text) or empty strings ("") are never overwritten. - Cells containing formulas (even formulas evaluating or returning empty strings or None) are never overwritten. - Replacement value types must be valid scalars (str, int, float, bool, date, datetime, time, timedelta). Values of None, NaN, Infinity, XML-illegal characters, or unsupported types (lists, dicts) are refused before mutation. - Existing cell formatting, styles, comments, and hyperlinks are preserved.

Refusal Boundaries and Reports

All cleaning operations perform preflight validation before any mutation: - Read-only workbooks and worksheets are refused (ReadOnlyWorkbookException). - Write-only worksheets are refused (ValueError). - Pending structural axis shifts or range moves on the sheet are refused (ValueError). - Any intersection with merged cell ranges is refused (ValueError). - Out-of-bounds or inverted ranges are refused (ValueError).

Outcomes are returned as an immutable, content-safe CleaningReport (also accessible as TrimReport, CleanReport, or FillBlanksReport). Reports record operation, cell_range, cells_evaluated, cells_modified, cells_skipped, and the tuple of modified_cells coordinates without exposing sensitive cell values.

Subtotals

Insert subtotal formulas for contiguous groups using one-based column indexes:

from wolfxl import AggregateSpec

report = ws.insert_subtotals([1], [AggregateSpec(2, "sum")], data_range="A1:B20", has_header=True)
wb.save("grouped.xlsx")

Use remove_subtotals() to remove rows recorded by this API. Generated-row identity is checked before removal; arbitrary user-authored SUBTOTAL formulas are not removal targets. Read-only sheets, table/merge intersections, and stale generated-row metadata are refused before mutation. Formula authoring does not constitute recalculation or Excel-equivalence evidence.

When providing data_range for removal, include every generated subtotal row. Partial removal, missing identity metadata, and edits to generated rows are refused before mutation. Existing detail-row and unrelated outline levels are preserved through save, reopen, and removal.

Outline groups

Use ws.row_dimensions or ws.column_dimensions to group, ungroup, collapse, and expand a contiguous range. Row indexes are one-based; column indexes accept letters. Set outline_level from 0 through 7 when grouping, and use summary_below for rows or summary_right for columns to select summary placement. Nested collapsed groups remain hidden when expanding an outer group.

Save and reload outline levels, hidden detail, collapsed summary markers, and summary placement in new XLSX workbooks or loaded workbooks opened with modify=True. Invalid levels and bounds fail before mutation. Hidden state uses the OOXML hidden flag; no separate manual-hidden provenance is recorded. This contract covers structure and round-trip behavior, not native Excel rendering equivalence.

Sort a range

from wolfxl.worksheet.filters import SortCondition

report = ws.sort_range("A1:C20", [SortCondition(ref="B2:B20", descending=True)], header=True)

Sort rows within the explicit rectangle using one or more column conditions. Use case_sensitive=True for case-sensitive text ordering. Cell values, formatting, comments, and hyperlinks move with their rows; relative references inside moved formulas translate by the row displacement unless translate_formulas=False. References elsewhere in the workbook are not rewritten, and formulas are not recalculated. Table, merge, array-formula, and unsupported-criterion intersections refuse before destination mutation.

Copy a range

report = ws.copy_range("A1:C20", "E1", mode="all")
report = ws.copy_range("A1:C20", "A1", target_ws=other_ws, mode="values")

Copy within one workbook using all, values, formulas, or formats. Overlapping ranges are read from the unchanged source before writes. Relative formulas translate by the destination offset by default. In all mode, copy cell content, styles, comments, hyperlinks, supported merges, and contained data validations. In values mode, preserve destination formatting and copy static values or available cached formula results; uncached formulas refuse rather than becoming blank cells. No recalculation is performed.

In formats mode, preserve destination values and formulas; refuse a proposed merge if it would discard an occupied non-anchor destination cell. Copy an array formula only with its complete bounded range. Unsafe partial merges, array ranges, and unsupported table or pivot intersections refuse before destination writes. Cross-workbook dependency copying is outside this API.

Calendar date-group filters

Use FilterColumn.date_group_items or the openpyxl-shaped Filters.dateGroupItem construction path for year, month, day, hour, minute, and second groups. Native modify-mode row filtering uses the workbook's 1900 or 1904 date system. Groups within a column are alternatives; filters across columns are combined.

Color and icon filters

Use ColorFilter to select a differential fill or font color, and IconFilter to select a standard conditional-format icon band. Native save-time filtering resolves cell styles and applicable conditional formatting, then combines visual filters with ordinary column filters. Unsupported visual metadata, custom icon sets, and unsupported color formats fail visibly before output publication. This is bounded row filtering, not an Excel visual-parity claim.

Loaded table metadata in modify mode

Edit and persist table style, banding, comments, and totals metadata on loaded worksheets using modify mode (load_workbook(path, modify=True)):

from wolfxl import load_workbook
from wolfxl.worksheet.table import TableFormula, TableStyleInfo

wb = load_workbook("report.xlsx", modify=True)
ws = wb["Sheet1"]
table = ws.tables["SalesTable"]

# Mutate table style and banding
table.tableStyleInfo = TableStyleInfo(
    name="TableStyleMedium2",
    showRowStripes=True,
    showColumnStripes=False,
    showFirstColumn=True,
    showLastColumn=False,
)

# Mutate table comment and extend range to include totals row
table.comment = "Q4 sales summary"
table.ref = "A1:C11"

# Configure totals row and column aggregates
table.totalsRowCount = 1
table.totalsRowShown = True
table.tableColumns[0].totalsRowLabel = "Total"
table.tableColumns[1].totalsRowFunction = "sum"
table.tableColumns[2].totalsRowFormula = TableFormula(text="SUM([Amount])")

wb.save("report_updated.xlsx")

Save behavior and synchronization

  • Metadata persistence: Bounded edits to tableStyleInfo, comment, ref, totalsRowCount, totalsRowShown, and TableColumn totals metadata (totalsRowLabel, totalsRowFunction, totalsRowFormula) serialize to the sheet's table part (xl/tables/tableN.xml).
  • Totals cell synchronization: When totals metadata is declared or modified, WolfXL synchronizes the declared label, standard subtotal formula (e.g. =SUBTOTAL(109,[Qty])), or custom formula into the totals row worksheet cells before save.
  • Updating prior totals: Changing an existing totals function or label (e.g. sum to average or "Total" to "Grand Total") updates the previously owned totals cell value in the worksheet.
  • Clearing owned totals: Clearing a column's declared label, function, and formula removes its previously owned totals cell value while preserving unrelated cells. Hiding the totals row or changing its count does not delete worksheet rows or clear their contents.
  • Preserving unrelated cells and XML: Non-totals cells on the worksheet and cells in table columns without declared totals metadata are preserved untouched. Unrelated table XML attributes, custom column properties (<xmlColumnPr>), extension elements (<extLst>), and unrelated package parts remain intact.
  • Save workflows: Edits persist across all standard modify-mode save destinations: file paths (wb.save(path)), binary streams (wb.save_to_stream(stream) or file-like wb.save(bio)), raw bytes (wb.save_to_bytes()), and password-protected encrypted packages (wb.save(path, password="...")). Repeated saves on the same workbook instance accumulate edits cleanly.

Refusal boundaries

WolfXL validates table integrity before publication and raises explicit errors:

  • Conflicting cell edits: If a totals row cell was explicitly edited to a value that conflicts with declared totals metadata, wb.save() raises ValueError.
  • Table identity immutability: Mutating table.name or table.displayName on a loaded table raises ValueError.
  • Column structure and identity: Modifying column cardinality (adding or removing TableColumn entries) or mutating column id or column name on a loaded table raises ValueError.
  • Conflicting totals flags: Conflicting totals state (totalsRowCount=0 with totalsRowShown=True, or totalsRowCount=1 with totalsRowShown=False) raises ValueError.
  • Unsupported totals row count: totalsRowCount values other than 0 or 1 raise ValueError.
  • Invalid totals functions: totalsRowFunction must be one of "none", "average", "count", "countNums", "max", "min", "stdDev", "sum", "var", or "custom". Unrecognized functions raise ValueError.
  • Table range boundaries: Inverted ranges, ranges too small for header plus totals rows, or ranges whose column count does not match column definitions raise ValueError.
  • Formula field types: totalsRowFormula must be a str or TableFormula (with text as str and array as bool); invalid types raise TypeError.
  • Canonical table resolution: Worksheets with unreferenced tables, broken relationship IDs, or external table targets are refused before output publication (ValueError or PermissionError).

Scope and boundaries

This capability covers bounded OOXML table metadata persistence and totals cell synchronization in modify mode. It does not evaluate formulas, run calculation, or claim Excel equivalence. Broader tables capability (FS-SHEET-016) remains Partial.

Worksheet background images

Assign a PNG or JPEG image to a worksheet background:

from wolfxl.drawing.image import Image

ws.background_image = Image("background.png")
wb.save("with-background.xlsx")

Read an existing background through ws.background_image. To replace or remove a loaded background, open the workbook with load_workbook(path, modify=True); assign another Image, or assign None to remove it, and save. Same-workbook worksheet copies retain their backgrounds. Shared media still referenced by other sheets and unrelated package parts are preserved.

Pillow is required to validate PNG/JPEG bytes during assignment, loading, and saving. Missing Pillow fails explicitly. Corrupt image data is refused before replacing the current background or publishing a workbook.

External image relationships and invalid or unsupported image formats are refused. Native rendering of worksheet backgrounds and watermark authoring remain unsupported; rendering a sheet with a background fails visibly instead of silently omitting it. This is a package-authoring and preservation contract, not an Excel visual-equivalence claim.

Font-metrics autofit

Measure cell contents using native renderer font metrics and adjust column widths and row heights:

from wolfxl import AutofitRecord, AutofitReport

# Measure and adjust column widths
col_report = ws.autofit_columns(
    cols=["A", "B", "C"],
    min_width=5.0,
    max_width=50.0,
    dpi=96.0,
    merged="anchor",
)

# Measure and adjust row heights with text wrapping
row_report = ws.autofit_rows(
    rows=range(1, 10),
    min_height=15.0,
    max_height=120.0,
    wrap=True,
    merged="anchor",
)

autofit_columns() and autofit_rows() return an immutable AutofitReport containing per-index AutofitRecord items with the measured pixel extent, applied dimension, winning coordinate, sample kind, and line count.

Measurement uses WolfXL's bundled Carlito metrics or caller-configured fonts (font_dirs, font_substitutions, use_system_fonts). Selections exceeding 100,000 candidate cells refuse with ValueError rather than silently truncating. Formula cells without cached results are not measured as calculated output. Merged cells support 'anchor' (attributing excess extent to the anchor dimension after accounting for spanned dimensions) and 'skip' (ignoring multi-cell merges).

This authoring tool computes dimensions mathematically from font extents and OOXML conversion formulas; it does not claim exact visual or rendering equivalence with Excel.