Table of Contents
Overview
Obtaining Style Information: the TableCellStyle and TablePageStyle Services; Creating and Using a New Style; Adding Borders
Examples: All Styles Info_ and Build Table_.
This chapter looks at how spreadsheet styles are stored, how they can be examined, and how new styles can be instantiated and used.
Calc uses the same style API as Writer, Draw, and Impress documents. ch22fig_calc_style_familes_props shows its structure.
diagram invert
: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 styles_all_info.py_ example prints out the style family names and the style names associated with the input document:
python
# in styles_all_info.py from __future__ import annotations
import uno from com.sun.star.sheet import XSpreadsheetDocument
from ooodev.office.calc import Calc from ooodev.utils.file_io import FileIO from ooodev.utils.info import Info from ooodev.loader.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) try:
# 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}") print()
# 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 ) Lo.print_names(style_names)
- if self._rpt_cell_styles:
print() self._report_cell_styles(doc)
- except Exception:
raise
- finally:
Lo.close_doc(doc=doc, deliver_ownership=True)
- def _report_cell_styles(self, doc: XSpreadsheetDocument) -> None:
- Props.show_props(
- "CellStyles Default", Info.get_style_props(
doc=doc, family_style_name="CellStyles", prop_set_nm="Default"
)
)
- Props.show_props(
- "PageStyles Default", Info.get_style_props(
doc=doc, family_style_name="PageStyles", prop_set_nm="Default"
)
)
html
tab-none
None
This code uses the :py.Info.get_style_family_names and :py.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:
rst-collapse
Output:
Style Family Names (2)
CellStyles
PageStyles
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) | ReportFrom 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 :py.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 ch22fig_table_cell_style_srv) and page styles by the TablePageStyle service (ch22fig_table_page_style_srv).
The properties managed by TableCellStyle are inherited from a number of places, as summarized by ch22fig_table_cell_style_srv.
diagram invert
: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 ch22fig_table_page_style_srv.
diagram invert
: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.
The steps required in creating and using a new style are illustrated by build_table.py_, in _create_styles() and _apply_styles():
python
# in build_table.py class BuildTable: HEADER_STYLE_NAME = "My HeaderStyle" DATA_STYLE_NAME = "My DataStyle"
- def main(self) -> None:
loader = Lo.load_office(Lo.ConnectSocket())
- try:
doc = CalcDoc(Calc.create_doc(loader)) doc.set_visible() sheet = doc.get_sheet(0) self._convert_addresses(sheet)
self._build_array(sheet)
# ...
- if self._add_style:
self._create_styles(doc) self._apply_styles(sheet)
# ...
html
tab-none
None
_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 ch22fig_styles_sheet_cells.
screen_shot
: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:
rst-collapse
Output:
Style Family Names (2)
CellStyles
PageStyles
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
Warning
2. "PageStyles" Family Styles:
No. of names: 2
--------|---------
Default | Reportbuild_table.py_ calls _create_styles() to create two styles:
python
# in build_table.py def _create_styles(self, doc: CalcDoc) -> None: try: # create a style using Calc header_style = doc.create_cell_style( 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( hori_align=HoriAlignKind.CENTER, vert_align=VertAlignKind.MIDDLE, style_name=BuildTable.HEADER_STYLE_NAME, ) # Apply formatting to header_style Styler.apply( header_style, header_bg_color_style, effects_style, txt_align_style )
# create style data_style = doc.create_cell_style(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( padding=modify_borders.Padding(left=UnitMM(5)) )
# Apply formatting to data_style Styler.apply(data_style, footer_bg_color_style, bdr_style, txt_align_style)
- except Exception as e:
print(e)
html
tab-none
None
The styles are created by two calls to :py.Calc.create_cell_style, which stores them in the CellStyles family:
python
# in Calc class @staticmethod 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, "com.sun.star.style.CellStyle", raise_err=True)
- try:
style_families.insertByName(style_name, style) return style
- except Exception as e:
raise Exception(f"Unable to create style: {style_name}") from e
html
tab-none
None
:py.Calc.create_cell_style calls :py.Info.get_style_container to return a reference to the CellStyles family as an XNameContainer. A new cell style is created by calling :py.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 ch22fig_table_cell_style_srv).
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 ch22fig_table_cell_style_srv).
The new styles, My HeaderStyle and My DataStyle, are applied to the spreadsheet by the build_table.py_ method _apply_styles():
python
# in build_table.py def _apply_styles(self, sheet: CalcSheet) -> None: sheet.change_style(style_name=BuildTable.HEADER_STYLE_NAME, range_name="B1:N1")
sheet.change_style(style_name=BuildTable.HEADER_STYLE_NAME, range_name="A2:A4") rng = sheet.get_range(range_name="B2:N4") rng.change_style(style_name=BuildTable.DATA_STYLE_NAME)
# 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
sheet.set_style_range(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 rng = sheet.get_range(range_name="N1:N4") rng.set_style(styles=[bdr])
html
tab-none
None
The header style is applied to two cell ranges: B1:N1 is the top row containing the months (see ch22fig_styles_sheet_cells), and A2:A4 is the first column. The data style is applied to B2:N4 which spans the numerical data and formulae.
python
# in Calc class (overload method, simplified) @classmethod 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)
html
tab-none
None
src-link
:odev_src_calc_meth:change_style
:py.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.
The :py.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 ch22fig_borders_and_data.
screen_shot
:Borders around the Data.
The border style is applied to the bottom row of the table, and the right column.
Using the :pyooodev.format.calc.direct.cell.borders module (imported as direct_borders in the code), the border style is created by calling :py~ooodev.format.inner.direct.structs.side.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 :py~ooodev.format.calc.direct.cell.borders.Borders object, and to the left and right of the N1:N4 range by creating a second :py~ooodev.format.calc.direct.cell.borders.Borders object.
python
# in build_table.py from ooodev.format.calc.direct.cell import borders as direct_borders # ... other imports
def _apply_styles(self, sheet: CalcSheet) -> 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 sheet.set_style_range(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 rng = sheet.get_range(range_name="N1:N4") rng.set_style(styles=[bdr])
html
tab-none
None