openpyxl-toolkit

openpyxl formatting without the boilerplate.

Purpose

It is our opinion that formatting and styling worksheets with openpyxl is clunky and non-intuitive. We wanted to build something to make the process less painful and the end result more readable.

For example, openpyxl stores a cell's style as an immutable object. This means changing one attribute results in building an entirely new Font object and assigning it, which drops the size, the color, and anything else that was set:

>>> from copy import copy
>>> from openpyxl import Workbook
>>> from openpyxl.styles import Font

>>> wb = Workbook()
>>> ws = wb.active
>>> ws["A1"] = "My cell text"

>>> ws["A1"].font = Font(size=14, color="FF1D3557") # openpyxl wants ARGB
>>> ws["A1"].font = Font(bold=True) # the size and the color are now gone

If you try ws["A1"].font.bold = True, you get Style objects are immutable and cannot be changed. Reassign the style with a copy. So if you wanted to modify just one part of a font, you would have to make a copy, modify it, and assign it:

>>> font = copy(ws["A1"].font)
>>> font.bold = True
>>> ws["A1"].font = font

This toolkit allows you to change attributes one by one without having to copy previously set attributes.

>>> from openpyxl_toolkit import WorksheetToolkit

>>> toolkit = WorksheetToolkit(ws)
>>> toolkit.set_font(cells="A1", size=14, color="#1d3557")
>>> toolkit.set_font(cells="A1", bold=True) # size and color are not discarded

Install

pip install openpyxl-toolkit

Python 3.10 or newer, and openpyxl 3.1 or newer.

Choosing cells

On every method that formats cells, there are 2 methods of selecting which cells the action should be applied to:

1. cells
2. rows and columns

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

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 normalized, 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.

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

Giving both cells and rows/columns raises.

Column letters and numbers

rows and columns take numbers, so there is a pair for converting either way.

>>> from openpyxl_toolkit import column_letter, column_index

>>> column_letter(3)    # 'C'
>>> column_index("C")   # 3
>>> toolkit.set_column_width(width=18, columns=[column_index("D")])

openpyxl has its own pair of methods in openpyxl.utils, but they run off the end of the grid. get_column_letter(16385) returns "XFE", and a workbook using that column will not open. These stop at XFD, column 16,384.

Methods

Each method returns the toolkit, so calls chain. Most parameters are keyword-only.

Anything not named is left as is. None is not the same as leaving out an argument: set_font(color=None) clears the color, 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(padding=1.5, min_width=9)

Built-in metrics cover Aptos, Arial, Calibri, Cambria, Courier New, Futura, Garamond, Georgia, Inter, Open Sans, Palatino, Roboto, Segoe UI, Tahoma, Times New Roman and Verdana. Helvetica, Book Antiqua, Selawik, Arimo, Carlito, Tinos, Cousine, Caladea and the Liberation faces are recognized too, each sharing the table of the face it matches advance for advance.

Any other face is measured with Verdana's character widths. Verdana is the widest of them, so a face with no metrics errs wide: a column that is too narrow hides what it holds, while one that is too wide only looks untidy. If that is not what you want for a particular column, set its width directly with set_column_width, or open an issue to have the face added to the table.

Four things to note:

• A wrapped cell is skipped. Wrapping exists so text conforms to the column, so sizing the column to the unwrapped line would guarantee it never wraps. Pass ignore_wrapped=False to override this behavior.
• 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. Use min_width or set the column width manually in these cases.
• Formula cells are skipped by default. Set those column widths directly, or pass ignore_formulas=False to override this behavior.
• A cell merged across columns is skipped. ignore_merged=False measures it, widening the one column that holds the value to fit text the reader sees spread across the merge. A merge running down a single column is measured either way.

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".

License

MIT.