Metadata-Version: 2.5
Name: openxlsx
Version: 0.1.2
Summary: Excel toolkit: security hardening, conversion, wrangling, charting, and merging.
Author: Eduardo Amorim Pereira
License: MIT
License-File: LICENSE
Requires-Python: >=3.10
Requires-Dist: openpyxl>=3.1
Provides-Extra: dev
Requires-Dist: mypy>=1.10; extra == 'dev'
Requires-Dist: pyarrow>=15.0; extra == 'dev'
Requires-Dist: pytest-cov>=5.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.5; extra == 'dev'
Requires-Dist: types-openpyxl>=3.1; extra == 'dev'
Provides-Extra: parquet
Requires-Dist: pyarrow>=15.0; extra == 'parquet'
Description-Content-Type: text/markdown

# OpenXLSX

A toolkit for Excel workbooks: security hardening, structured conversion, chart generation, deduplication, formatting normalization, and workbook merging behind a small, consistent API.

## Status

All five verbs are complete: noduplicate, uniformat, convert, graphic, and merger. The security subsystem (five checks) is complete. The package installs and the CLI runs; every documented subcommand is functional.

## Install

    pip install openxlsx

Parquet support is an optional dependency:

    pip install "openxlsx[parquet]"

For development:

    pip install -e ".[dev]"

## The five verbs

Every transformation OpenXLSX offers is a verb that mirrors a task teams actually perform on spreadsheets. The verbs are designed to be self-explanatory, because a verb that needs documentation to be understood is a verb that will be used incorrectly in production.

noduplicate
    Removes duplicate rows from a worksheet under a configurable key policy. Full rows or named columns, first or last retention, case and whitespace folding, all with style preservation for the surviving rows.

uniformat
    Applies uniform Excel number formats to named or positioned columns. Values are never parsed or modified; only the display format of non-empty cells is touched.

convert
    Converts a workbook between .xlsx, .csv, and .parquet. Runs the security policy first, exports computed values for csv and parquet, and neutralizes formula-injection vectors on csv export.

graphic
    Generates native Excel charts (not embedded images) from worksheet ranges. Supports bar, column, line, pie, scatter, and area charts, with configurable series, titles, and placement.

merger
    Merges multiple workbooks into a single workbook. Sheets with the same name merge their cells by default, with cell-value collisions detected as conflicts. Sheet-name collisions can be resolved by skip, overwrite, or rename instead of merging.

## Quick start

    from openxlsx import MergePolicy, convert, graphic, merge_workbooks, noduplicate, uniformat
    from pathlib import Path

    noduplicate(Path("messy.xlsx"), Path("clean.xlsx"), key_columns=["email"])
    uniformat(
        Path("clean.xlsx"),
        Path("final.xlsx"),
        {"date": "yyyy-mm-dd", "amount": "#,##0.00"},
    )
    graphic(
        Path("final.xlsx"),
        Path("final.xlsx"),
        chart_type="line",
        data_range="A1:D10",
        title="Monthly revenue",
        anchor="F2",
    )
    convert(Path("final.xlsx"), Path("final.csv"), to="csv")

    result = merge_workbooks(
        [Path("q1.xlsx"), Path("q2.xlsx")],
        Path("merged.xlsx"),
        MergePolicy(on_cell_conflict="overwrite"),
    )
    print(f"{result.sheets_added} sheets added")

From the command line:

    openxlsx security book.xlsx
    openxlsx noduplicate clean.xlsx messy.xlsx --keys email --keep last
    openxlsx uniformat final.xlsx clean.xlsx --rule date=yyyy-mm-dd --rule amount=#,##0.00
    openxlsx convert final.csv final.xlsx --to csv
    openxlsx graphic chart.xlsx final.xlsx --type line --range A1:D10 --title "Monthly revenue"
    openxlsx merger merged.xlsx q1.xlsx q2.xlsx --on-cell-conflict=overwrite

Exit codes are stable: 0 for success, 1 for generic errors, 2 for usage errors, 3 for security violations, 4 for merge conflicts.

## Security model

Security runs by default. Every check has one of three modes: off, warn, or enforce. In off mode the check is skipped entirely. In warn mode the check reports findings without modifying the workbook, and the CLI exits with 0. In enforce mode the check reports findings with error severity, and the CLI exits with 3.

The default policy runs formula-injection and external-link checks in warn mode, and DDE, zip-bomb, and path-traversal checks in enforce mode. The rationale is that formula-based checks can produce false positives against legitimate spreadsheets, while archive-level checks cannot.

A check can be overridden per invocation:

    openxlsx security book.xlsx --security dde=off --security formula_injection=enforce

Or, from Python:

    policy = SecurityPolicy().with_mode("dde", "off").with_mode("formula_injection", "enforce")

Both `convert` and `graphic` run the security policy before any output is written, so a conversion or chart generation never launders a malicious workbook into a second malicious workbook. The CLI exits with 3 when the policy raises.

## Merge model

The merger copies every meaningful cell — value, formula, and style — plus merged ranges, column widths, row heights, and freeze panes. Sheet order follows source order, then in-workbook order within each source.

When a source sheet shares its name with an existing destination sheet, four actions are available through `on_sheet_conflict`:

merge
    Cells from the source sheet merge into the existing destination sheet. Cell-value collisions follow `on_cell_conflict`. This is the default.

skip
    The entire source sheet is discarded. The destination sheet is left untouched.

overwrite
    The destination sheet is deleted and recreated from the source sheet. Cells are not compared.

rename
    A new destination sheet is created with the source content and a name derived from the original, disambiguated with `rename_suffix` and, on further collisions, a numeric counter.

Cell-value conflicts under `"merge"` are governed by `on_cell_conflict`:

fail
    The conflict is recorded, the cell is skipped, and the merge raises `MergeConflictsError` after all sources are processed. The CLI reports this as exit code 4. The output file is never written. This is the default.

skip
    The destination value is kept. The source value is discarded.

overwrite
    The destination value is replaced with the source value.

Equal values are never a conflict. A formula cell and a text cell that display the same characters are treated as different values, because their data types differ. A boolean and an integer with the same truth value are treated as different values, because `True` and `1` are semantically distinct in a spreadsheet.

## Noduplicate model

The noduplicate verb loads the workbook with `data_only=False`, so formulas are preserved as formulas, and rewrites only the target worksheet. Every other worksheet is copied verbatim into the output.

Rows are compared by a normalized key. When `key_columns` is omitted, the key is the full row. When `key_columns` is provided, the key is the named columns in the order given. Comparison normalizes values so that cosmetic differences do not produce false duplicates:

- Strings are lowercased and trimmed by default. Both behaviors can be disabled independently.
- Integers and floats compare equal when their numeric values match, so `1` and `1.0` collide.
- Booleans never compare equal to numbers, so `True` and `1` are distinct keys.
- `None` has its own type tag and never collides with the empty string.

For each distinct key, either the first or the last occurrence survives depending on `keep`. Surviving rows retain their original relative order. Values, formulas, fonts, fills, borders, alignments, number formats, and protection are preserved cell by cell. Column widths and freeze panes are preserved.

Merged cell ranges on the target worksheet are removed before the rewrite, because a merge that straddles a removed row becomes invalid. Every other worksheet keeps its merges untouched.

## Uniformat model

The uniformat verb applies Excel number-format strings to columns. Only the cell's `number_format` attribute is modified; values, formulas, styles, and every other attribute round-trip untouched.

Column identifiers are interpreted according to `has_header`:

- With `has_header=True` (the default), identifiers are header names from the first row. Duplicate header names raise `OpenXLSXUsageError`, because a rule that targets two columns is ambiguous.
- With `has_header=False`, identifiers are Excel column letters (`A`, `B`, `AA`). Lowercase letters are accepted and normalized, matching Excel's own case-insensitive treatment.

The header row is never formatted. Data rows start at row 2 when `has_header=True` and at row 1 otherwise. Empty cells are skipped: a cell whose value is `None` is not given a number format, since the format would have no visible effect. Non-empty cells, including cells whose value is `0` or a formula string, receive the format.

Number-format strings are passed to openpyxl verbatim, so any format Excel accepts is accepted here, including locale-specific ones such as `[$-409]mmmm d, yyyy`. The verb does not validate formats against Excel's grammar; an invalid format is rejected by Excel when the file is opened, not by uniformat.

## Convert model

The convert verb transforms a `.xlsx` input into one of three outputs. The target format is chosen by the `to` argument or inferred from the output suffix (`.csv`, `.parquet`, `.pq`, `.xlsx`).

Input security
    Every conversion runs the security policy first. When the policy reports an error-severity finding — DDE, zip bomb, or path traversal under the default policy — the conversion raises `OpenXLSXSecurityError` before writing anything, and the CLI exits with 3.

Worksheet selection
    `sheet=None` selects the first worksheet for csv and parquet output and every worksheet for xlsx output. `sheet="all"` selects every worksheet, which produces one multi-sheet xlsx or a directory of one file per worksheet for csv and parquet. `sheet="SheetName"` selects exactly that worksheet.

Value semantics
    xlsx output round-trips the workbook with `data_only=False`, so formulas stay formulas. csv and parquet output read the workbook with `data_only=True`, so formula cells are exported as their cached values. A formula cell without a cached value — a workbook generated programmatically and never opened in Excel — is exported as the empty string; the formula itself is not exported.

CSV sanitization
    Every csv string value that begins with `=`, `+`, `-`, `@`, tab, or carriage return is prefixed with a single quote, because a CSV carrying such a value is a formula-injection vector when re-opened in Excel. The prefix preserves the original characters and Excel displays them literally. Parquet and xlsx output do not sanitize, because the value is stored as data or as a genuine formula.

CSV formatting
    `delimiter` defaults to `,`, `encoding` to `utf-8`, and `quoting` to `minimal`. `None` values are written as empty strings. Header is always the first line.

Parquet requirement
    Parquet output needs `pyarrow`, available through `pip install "openxlsx[parquet]"`. Requesting parquet output without `pyarrow` raises `OpenXLSXUsageError` with installation guidance.

## Graphic model

The graphic verb writes a native Excel chart — not an embedded image — into a worksheet. The chart is interactive in Excel, participates in workbook recalculation, and can be edited by the user after generation.

Chart types
    `bar` (horizontal bars), `column` (vertical columns), `line`, `pie`, `scatter`, and `area`.

Data layout
    `data_range` is A1 notation, such as `A1:D10`. With `series_in="columns"` (the default), the first column provides category labels, the first row provides series titles when `has_header=True`, and each subsequent column is a series. With `series_in="rows"`, the roles are transposed.

Placement
    `anchor` places the chart at a cell reference; the default is `H2`, leaving column A visible alongside the chart.

In-place operation
    `output` may equal `path`. The chart is added to the loaded workbook, and the workbook is saved to `output`. No other worksheet, chart, or cell is touched.

Input security
    The graphic verb runs the security policy before adding the chart. When the policy raises on an error-severity finding, no output is written and the CLI exits with 3.

## Checks

| Check | Default | What it detects |
|---|---|---|
| dde | enforce | Formulas that invoke Dynamic Data Exchange payloads, which can execute external programs. |
| formula_injection | warn | Formulas using WEBSERVICE or unsafe HYPERLINK schemes (javascript, vbscript, data, file). |
| external_links | warn | References to workbooks or URLs outside the current file. |
| zip_bomb | enforce | Archives whose compression ratio or uncompressed size exceeds configured limits. |
| path_traversal | enforce | Archive members whose names escape the extraction root. |

## License

MIT. See [LICENSE](LICENSE) for more info.
