openpyxl-toolkit

Chainable formatting helpers for openpyxl worksheets that merge styles into what a cell already carries, rather than replacing the whole thing.

openpyxl stores a cell's style as one immutable object. Setting a font means building a whole Font and assigning it, which silently drops the size, the colour and everything else that was there. This toolkit allows you to change attributes one by one without having to copy previously set attributes.

>>> from openpyxl import Workbook
>>> from openpyxl_toolkit import WorksheetToolkit

>>> workbook = Workbook()
>>> sheet = workbook.active
>>> sheet["A1"] = "Region"

>>> toolkit = WorksheetToolkit(sheet)
>>> toolkit.set_font(cells="A1", size=14, color="#1d3557")
<WorksheetToolkit 'Sheet'>
>>> toolkit.set_font(cells="A1", bold=True)
<WorksheetToolkit 'Sheet'>

>>> sheet["A1"].font.sz, sheet["A1"].font.bold
(14.0, True)

The second call keeps the size and the colour. Written against openpyxl directly, sheet["A1"].font = Font(bold=True) would have thrown both away.

Install

pip install openpyxl-toolkit

Python 3.10 or newer, and openpyxl 3.1 or newer.

Choosing cells

Three ways, on every method that formats cells.

>>> toolkit.set_fill(cells="A1:C3", fill_type="solid", start_color="#f4f6f8")
<WorksheetToolkit 'Sheet'>
>>> toolkit.set_fill(cells="A:C", fill_type="solid", start_color="#f4f6f8")
<WorksheetToolkit 'Sheet'>
>>> toolkit.set_fill(rows=[1, 2], columns=[1, 2], intersections_only=True,
...                  fill_type="solid", start_color="#ffff00")
<WorksheetToolkit 'Sheet'>

cells takes a block ("A1:C3"), whole columns ("B:D"), whole rows ("2:5") or one cell ("C3"). Case does not matter, a reversed range such as "C3:A1" is normalised, and an unbounded side is filled in from the used range, so "B:B" means column B as far as the sheet goes rather than all 1,048,576 rows.

rows and columns take lists, for a selection that is computed rather than written out. A list, not a number:

>>> toolkit.set_font(rows=3, bold=True)
Traceback (most recent call last):
    ...
TypeError: rows takes a list of numbers, not a single one. Write rows=[3] rather than rows=3.

Tuples, ranges, sets and generators all work.

Given both rows and columns, intersections_only decides what they mean:

Giving both cells and rows/columns raises rather than quietly picking one.

Methods

Each returns the toolkit, so calls chain.

Every parameter is keyword-only, apart from freeze_panes, which takes its cell either way. Anything not named is left as it was, and None is not the same as leaving it out: set_font(color=None) clears the colour, while omitting color keeps whatever was there.

Fitting columns

set_column_best_fit measures every character at its own width in the font being used, so a column of i's does not come out as wide as a column of W's. Excel's own formula turns the total into a width:

width = (pixels of text + 5 padding pixels) / max digit width

>>> toolkit.set_column_best_fit(ignore_rows=[1], padding=1.5, min_width=9)
<WorksheetToolkit 'Sheet'>

Built-in metrics cover Aptos, Calibri, Arial, Helvetica, Times New Roman, Courier New, Cambria, Verdana, Georgia, Tahoma and Futura. Any other face is measured with Calibri's character widths unless measure is given.

Three items to note:

• Text is assumed to be on one line. Wrapped text is not accounted for.
• Only dates and times are rendered as Excel displays them. Other number formats, including the ones set_number_format writes, are measured as the value is stored, so a currency column can come out narrower than it needs to be. min_width is the answer.
• Formula cells are skipped by default, since the formula text is not what the reader sees. Set that column's width directly, or pass ignore_formulas=False.

A merged title in row 1 will size column A to the whole title, because a merged range stores its value in the top-left cell. ignore_rows is what to reach for.

Type hints

The package ships py.typed. Alignment names, border styles, fill patterns and underline styles are Literal types, so an editor completes them and a checker catches a typo before the code runs:

error: Argument "horizontal" to "set_alignment" has incompatible type "Literal['centre']"

openpyxl rejects the same typo, but only once the call runs; the Literal types move it into the editor. Elsewhere the annotations really are stricter than openpyxl is at runtime, which matters more than it sounds: openpyxl accepts bold="no" and stores True, and name=42 and stores "42".

Licence

MIT.