Working with styles

Styling in openpyxl-templates is entirely reliant on the NamedStyle provided by openpyxl. Using NamedStyles offers significantly better performance compared to styling each individual cells, as well as the benefits of making the styles avaliable in the produced excel file.

To manage all named styles within a TemplatedWorkbook openpyxl-templates uses the StyleSet which is a dictionary like collection of NamedStyles. Using the StyleSet when creating templates is entierly optional but offers several advantages:

• Using a common colleciton of styles for all TemplatedSheets makes it easier to avoid duplicated styles and name conflicts
• The StyleSet accepts ExtendedStyle s as well as NamedStyles which enables inheritance between styles within the StyleSet
• Styles will only be added to the excel file when they are needed allowing the developer to use a single StyleSet for multiple templates without having to worry about unused styles being included in the excel file.
• Using NamedStyles offers significantly better performance compared to styling each cell individually when writing a large amout of data

Creating a StyleSet

To create a StyleSet simply pass NamedStyles or ExtendedStyles as arguments.

demo_style = StyleSet(
    NamedStyle(
        name="Default",
        font=Font(
            name="Arial",
            size=12
        )
    ),
    NamedStyle(
        name="Header",
        font=Font(
            name="Arial",
            size=12,
            bold=True,
        ),
    )
)

If we want to avoid having to redeclare the font we could refactor the above example using an ExtendedStyle

demo_style = StyleSet(
    NamedStyle(
        name="Default",
        font=Font(
            name="Arial",
            size=12
        )
    ),
    ExtendedStyle(
        base="Default",  # Reference to the style defined above
        name="Header",
        font={
            "bold": True,
        }
    )
)

The ExtendedStyle can be viewed as a NamedStyle factory. It accepts the same arguments as a NamedStyle with the addition of the base which is the name of the intended parent. The StyleSet will make sure that the parent style is found irregardless of declaration order.

The font, border, alignment and fill arguments of the NamedStyle are supplied as objects which (since they have default values) prevent inheritance. To circomvent this limitation you can supply the kwarg dicts to the extended style instead of the object itself, as we have done in the example above. If the openpyxl class is used instead of kwargs, inheritance will be broken

bad_example = StyleSet(
    NamedStyle(
        name="Default",
        font=Font(
            name="Arial",
            size=12
        )
    ),
    ExtendedStyle(
        base="Default",
        name="Header",
        font=Font(
            # Openpyxl will set name="Calibri" by default which will override name="Arial and break inheritance.
            bold=True
        )
    )

if a name is declared multiple times the last declaration will take precedence making it easy to modify an existing StyleSet. One common usage for this is to modify the DefaultStyleSet. Which is demonstrated ModifyDefaultStyleSet.

Accessing styles

When you need to use a specific style just retrive it by name

TODO

DefaultStyleSet

Openpyxl-templates includes a DefaultStyleSet which is used as a fallback for all TemplatedWorkbook. Many of the styles it declares (or their names) are required by the TableSheet. The DefaultStyleSet is defined like this

            raise ValueError("StyleSet can only handle NamedStyles")

        if style.name in self:
            raise ValueError("Style already exists")

        self._styles[style.name] = style
        return style

    @property
    def names(self):
        return tuple(style.name for style in self._styles.values())

    def extend(self, extended_style):
        return self._add(extended_style)

    def style_cell(self, cell, style):
        if type(style) in (NamedStyle, ExtendedStyle):
            if style.name not in self:
                named_style = self._add(style)
            else:
                named_style = self[style.name]
        else:
            named_style = self[style]

        # print("    style cell", named_style.name, type(style), style.name if type(style)!= str else "")
        cell.style = named_style


class DefaultStyleSet(StyleSet):
    def __init__(self, *styles):
        super(DefaultStyleSet, self).__init__(
            NamedStyle(
                name="Default",
                alignment=Alignment(vertical="top")
            ),
            ExtendedStyle(
                base="Default",
                name="Empty",
            ),
            ExtendedStyle(
                base="Empty",
                name="Title",
                font={"size": 20}
            ),
            ExtendedStyle(
                base="Empty",
                name="Description",
                font={"color": "FF777777"},
                alignment={"wrap_text": True}
            ),
            ExtendedStyle(
                base="Default",
                name="Header",
                font={"bold": True, "color": "FFFFFFFF"}, fill=SolidFill(DEFAULT_ACCENT_COLOR)
            ),
            ExtendedStyle(
                base="Header",
                name="Header, center",
                alignment={"horizontal": "center"}
            ),
            ExtendedStyle(
                base="Default",
                name="Row"
            ),
            ExtendedStyle(
                base="Row",
                name="Row, string",
                number_format="@",
            ),
            ExtendedStyle(
                base="Row",

If you which to modify the DefaultStyle you can easily replace any or all of the styles it contains by passing them as arguments to the constructor. Below we utilize the heavy usage of ExtendedStyles in the DefaultStyleSet to change of all styles.

    NamedStyle(  # Replace the existing "Default" font with a new one.
        name="Default",
        font=Font(
            name="Arial",
        )
    )
)