Chapter 22. Styles

This chapter looks at how spreadsheet styles are stored, how they can be examined, and how new styles can be instantiated and used.

22.1 Obtaining Style Information

Calc uses the same style API as Writer, Draw, and Impress documents. Fig. 183 shows its structure.

Calc Style Families and their Property Sets

Fig. 183 :Calc Style Families and their Property Sets

The Calc API only has two style families, CellStyles and PageStyles. A cell style can be applied to a cell, a cell range, or a spreadsheet (which is a very big cell range). A page style can be applied only to a spreadsheet.

Each style family consists of styles, which are collection of property sets. The default cell style is called Default, as is the default page style.

The example prints out the style family names and the style names associated with the input document:

# in
from __future__ import annotations

import uno
from import XSpreadsheetDocument

from import Calc
from ooodev.utils.file_io import FileIO
from import Info
from ooodev.utils.lo import Lo
from ooodev.utils.props import Props
from ooodev.utils.type_var import PathOrStr

class StylesAllInfo:
    def __init__(self, fnm: PathOrStr, rpt_cell_styles: bool) -> None:
        _ = FileIO.is_exist_file(fnm, True)
        self._fnm = FileIO.get_absolute_path(fnm)
        self._rpt_cell_styles = rpt_cell_styles

    def main(self) -> None:
        with Lo.Loader(Lo.ConnectSocket(headless=True)) as loader:

            doc = Calc.open_doc(fnm=self._fnm, loader=loader)

                # get all the style families for this document
                style_families = Info.get_style_family_names(doc)
                print(f"Style Family Names ({len(style_families)})")
                for style_family in style_families:
                    print(f"  {style_family}")

                # list all the style names for each style family
                for i, style_family in enumerate(style_families):
                    print(f'{i + 1}. "{style_family}" Family Styles:')
                    style_names = Info.get_style_names(
                        doc=doc, family_style_name=style_family

                if self._rpt_cell_styles:

            except Exception:

                Lo.close_doc(doc=doc, deliver_ownership=True)

    def _report_cell_styles(self, doc: XSpreadsheetDocument) -> None:
            "CellStyles Default", Info.get_style_props(
                doc=doc, family_style_name="CellStyles", prop_set_nm="Default"

            "PageStyles Default", Info.get_style_props(
                doc=doc, family_style_name="PageStyles", prop_set_nm="Default"

This code uses the Info.get_style_family_names() and Info.get_style_names() functions that is utilized in earlier chapters, so won’t explain their implementation again. The output for a simple spreadsheet is:

Style Family Names (2)

1. "CellStyles" Family Styles:
No. of names: 20
  Accent    | Accent 1  | Accent 2  | Accent 3
  Bad       | Default   | Error     | Footnote
  Good      | Heading   | Heading 1 | Heading 2
  Good      | Heading   | Heading 1 | Heading 2
  Hyperlink | Neutral   | Note      | Result
  Result2   | Status    | Text      | Warning

2. "PageStyles" Family Styles:
No. of names: 3
  Default                 | PageStyle_ACPT (Python) | Report

Finding Style Information

From a programming point of view, the main difficult with styles is finding documentation about their properties, so that a value can be correctly read or changed.

One approach is to use Info.get_style_props() method to list the properties for a given style family and style name. For example, the _report_cell_styles() from above from displays all the properties for the default cell and page styles:

The problem is that the output for _report_cell_styles() is extremely long, and some property names are less descriptive/understandable than others.

It’s probably better to search the online documentation for properties. Cell styles are managed by the TableCellStyle service (see Fig. 184) and page styles by the TablePageStyle service (Fig. 185).

The properties managed by TableCellStyle are inherited from a number of places, as summarized by Fig. 184.

The Table Cell Style Service

Fig. 184 :The TableCellStyle Service

By far the most important source of cell style properties is the CellProperties class in the table module. However, if a property relates to the text in a cell then it’s more likely to originate from the CharacterProperties or ParagraphProperties classes in the style module.

The properties managed by TablePageStyle are also inherited from a few places, as summarized by Fig. 185.

The Table Page Style Service.

Fig. 185 :The TablePageStyle Service.

The main place to look for page properties is the PageProperties class in the style module. The properties relate to things such as page margins, headers, and footers, which become important when printing a sheet.

22.2 Creating and Using New Styles

The steps required in creating and using a new style are illustrated by, in _create_styles() and _apply_styles():

# in
class BuildTable:
    HEADER_STYLE_NAME = "My HeaderStyle"
    DATA_STYLE_NAME = "My DataStyle"

    def main(self) -> None:
        loader = Lo.load_office(Lo.ConnectSocket())

            doc = Calc.create_doc(loader)
            GUI.set_visible(is_visible=True, odoc=doc)
            sheet = Calc.get_sheet(doc=doc, index=0)


            # ...

            if self._add_style:
        # ...

_create_styles() creates two cell styles called My HeaderStyle and My DataStyle, which are applied to the spreadsheet by _apply_styles(). The result is shown in Fig. 186.

Styled Spreadsheet Cells

Fig. 186 :Styled Spreadsheet Cells.

The My HeaderStyle style is applied to the top row and the first column: the cells are colored blue, and the text made white and centered. The My DataStyle is used for the numerical data and formulae cells: the background color is made a light blue, and the text is right-justified. _apply_styles() also changes the border properties of the bottom edges of the cells in the last row to be thick and blue.

If the resulting spreadsheet is saved and this document is examined by the All Styles Info program, it lists the new styles in the CellStyles family:

Style Family Names (2)

1. "CellStyles" Family Styles:
No. of names: 21
  Accent         | Accent 1       | Accent 2       | Accent 3
  Bad            | Default        | Error          | Footnote
  Good           | Heading        | Heading 1      | Heading 2
  Hyperlink      | My DataStyle   | My HeaderStyle | Neutral
  Note           | Result         | Status         | Text

2. "PageStyles" Family Styles:
No. of names: 2
  Default | Report

22.2.1 Creating a New Style calls _create_styles() to create two styles:

# in
def _create_styles(self, doc: XSpreadsheetDocument) -> None:
        # create a style using Calc
        header_style = Calc.create_cell_style(
            doc=doc, style_name=BuildTable.HEADER_STYLE_NAME

        # create formats to apply to header_style
        header_bg_color_style = BgColor(
            color=CommonColor.ROYAL_BLUE, style_name=BuildTable.HEADER_STYLE_NAME
        effects_style = FontEffects(
            color=CommonColor.WHITE, style_name=BuildTable.HEADER_STYLE_NAME
        txt_align_style = TextAlign(
        # Apply formatting to header_style
            header_style, header_bg_color_style, effects_style, txt_align_style

        # create style
        data_style = Calc.create_cell_style(doc=doc, style_name=BuildTable.DATA_STYLE_NAME)

        # create formats to apply to data_style
        footer_bg_color_style = BgColor(
            color=CommonColor.LIGHT_BLUE, style_name=BuildTable.DATA_STYLE_NAME
        bdr_style = modify_borders.Borders(

        # Apply formatting to data_style
            data_style, footer_bg_color_style, bdr_style, txt_align_style

    except Exception as e:

The styles are created by two calls to Calc.create_cell_style(), which stores them in the CellStyles family:

# in Calc class
def create_cell_style(doc: XSpreadsheetDocument, style_name: str) -> XStyle:
    comp_doc = Lo.qi(XComponent, doc, raise_err=True)
    style_families = Info.get_style_container(doc=comp_doc, family_style_name="CellStyles")
    style = Lo.create_instance_msf(XStyle, "", raise_err=True)

        style_families.insertByName(style_name, style)
        return style
    except Exception as e:
        raise Exception(f"Unable to create style: {style_name}") from e

Calc.create_cell_style() calls Info.get_style_container() to return a reference to the CellStyles family as an XNameContainer. A new cell style is created by calling Lo.create_instance_msf(), and referred to using the XStyle interface. This style is added to the family by calling XNameContainer.insertByName() with the name passed to the function.

A new style is automatically derived from the Default style, so the rest of the _create_styles() method involves the changing of properties. Five are adjusted in the My HeaderStyle style, and three in My DataStyle.

The header properties are IsCellBackgroundTransparent, CellBackColor, CharColor, HoriJustify, and VertJustify, which are all defined in the CellProperties class (see Fig. 184).

The data properties are IsCellBackgroundTransparent, CellBackColor, and ParaRightMargin. Although IsCellBackgroundTransparent and CellBackColor are from the CellProperties class, ParaRightMargin is inherited from the ParagraphProperties class in the style module (also in Fig. 184).

22.2.2 Applying a New Style

The new styles, My HeaderStyle and My DataStyle, are applied to the spreadsheet by the method _apply_styles():

# in
def _apply_styles(self, sheet: XSpreadsheet) -> None:

        sheet=sheet, style_name=BuildTable.HEADER_STYLE_NAME, range_name="B1:N1"
        sheet=sheet, style_name=BuildTable.HEADER_STYLE_NAME, range_name="A2:A4"
        sheet=sheet, style_name=BuildTable.DATA_STYLE_NAME, range_name="B2:N4"

    # ... other code

The header style is applied to two cell ranges: B1:N1 is the top row containing the months (see Fig. 186), and A2:A4 is the first column. The data style is applied to B2:N4 which spans the numerical data and formulae.

# in Calc class (overload method, simplified)
def change_style(cls, sheet: XSpreadsheet, style_name: str, range_name: str) -> bool:
    cell_range = cls.get_cell_range(sheet=sheet, range_name=range_name)
    Props.set(cell_range, CellStyle=style_name)

Calc.change_styles() manipulates the styles via the cell range. The cell_range variable refers to a SheetCellRange service which inherits many properties, including those from CellProperties. Its CellStyle property holds the style name used by that cell range.

22.2.3 Adding Borders

The Calc.add_border() method highlights borders for a given range of cells. The two calls in _apply_styles() draw a blue line along the bottom edge of the A4:N4 cell range, and two lines on either side of the SUM column (the N1:N4 range), as shown in Fig. 187.

Borders around the Data spreadsheet screen shot.

Fig. 187 :Borders around the Data.

The border style is applied to the bottom row of the table, and the right column.

Using the module (imported as direct_borders in the code), the border style is created by calling Side class. The side has a width of 2.85 points, and a color of CommonColor.DARK_BLUE.

The side is applied to the bottom of the A4:N4 range by creating a Borders object, and to the left and right of the N1:N4 range by creating a second Borders object.

# in
from import borders as direct_borders
# ... other imports

def _apply_styles(self, sheet: XSpreadsheet) -> None:

    # ... other code

    # create a border side, default width units are points
    side = direct_borders.Side(width=2.85, color=CommonColor.DARK_BLUE)
    # create a border setting bottom side
    bdr = direct_borders.Borders(bottom=side)
    # Apply border to range
    Calc.set_style_range(sheet=sheet, range_name="A4:N4", styles=[bdr])

    # create a border with left and right
    bdr = direct_borders.Borders(left=side, right=side)
    # Apply border to range
    Calc.set_style_range(sheet=sheet, range_name="N1:N4", styles=[bdr])