Formatter - Unified Config - Two

Preface

Goal: Separating formatting code and configuration. Example class that uses the unified configuration.

After covering the basic macro, we can now step into utilizing the unified configuration, and explore how far the formatting can go. We will transition all formatting procedures, from the simple configuration to the unified one.

Example Sheet

You can download the example ODS source file here:

• github.com/…/movies/data/movies.ods

And also example XLSX result here:

• github.com/…/movies/data/movies.xlsx

This is basically a workbook with 40 worksheets.

04: Additional Helper

A few helpful methods

A lengthy article section can be overwhelming. Let’s move all supporting helper methods here, before we dive into the class itself.

There are some brand-new helper methods, that were not available in the previous example. I’ll list them all here so you can easily spot the differences:

• _column_letter_to_index().
• _get_relative_column_letter().
• __get_number_format().

Helper: Column Letter To Index

This helper method converts Excel-style column letters to a 0-based column index. It will be used in various parts of the code later.

Think of it as the translator between Python’s love for zero, and Excel’s obsession with letters.

  def _column_letter_to_index(self,
        column_letter: str) -> int:

    index = 0
    for i, char in enumerate(reversed(column_letter)):
      index += (ord(char) - ord('A') + 1) * (26 ** i)
    return index - 1

It’s the reverse of the previously mention method.

Helper: Get Relative Column Letter

This helper method retrieves the Excel-style column letter at an offset from the start_letter. It will be used in several parts of the code.

  def _get_relative_column_letter(self,
        start_letter: str, offset: int) -> str:

    start_index    = self._column_letter_to_index(start_letter)
    relative_index = start_index + offset - 1
    return self._column_index_to_letter(relative_index)

Sheet Helper: Get Number Format

This helper method is designed to be used exclusively, within the _set_columns_format() function.

  def __get_number_format(self,
        format_string: str) -> XNumberFormats:

    nf = self._numberfmt.queryKey(  \
              format_string, self._locale, True)
    if nf == -1:
       nf = self._numberfmt.addNew( \
              format_string, self._locale)
    return nf

Optionally, we can use type annotations with XNumberFormats.

Import Statements

We need to include additional import statements from com.sun.star, for the interface type.

from abc import ABC, abstractmethod

from com.sun.star.sheet import XSpreadsheetDocument
from com.sun.star.util  import XNumberFormats

from com.sun.star.\
  table.CellHoriJustify import LEFT, CENTER, RIGHT

Now we can use this helper in the class.

04: Step Four

TIn this section, we introduce configuration metadata, into the formatting process for the first time.

Step Overview

This section covers the following topics for unified configuration:

• Class: BorderFormatManager
• Formatting Procedures:
  • Add merged titles (for multiple column blocks)
  • Set merged title (refactored for individual blocks)
  • Add merged titles post (hook method)

Other topics follow the simple configuration pattern:

• Configuration: Field Metadata Configuration
• Helper Methods:
  • Letter to Index
  • Number Formats
• Formatting Procedure: Format Columns

Macro Source

The macro source for this step can be found in the following repository:

• github.com/…/movies/steps-02/FormatterTabular04.py

Class Diagram

What’s new?

In addition to the three helper methods listed above, this step introduces:

• Two abstract methods, each with an implementation
• A supporting method
• Three instance variables

Abstract Method

This step adds two new abstract methods, one for initializing configuration metadata, and another for the column formatting procedure.

  @abstractmethod
  def _init_metadatas(self) -> None:
    pass

  ...

  @abstractmethod
  def _set_columns_format(self) -> None:
    pass

Basic Flow

The _set_columns_format() method fits naturally, into the flow at this point.

  def __format_one_sheet(self) -> None:
    ...

    # Apply Sheet Wide
    print(' * Formatting Columns')
    self._set_sheetwide_view()
    self._set_columns_format()

Initialization: Init Metadatas

The _init_metadatas() method is introduced here, alongside the initial value for the _metadatas variable.

  def __init__(self) -> None:
    self._sheet = None
    self._controller = self._document.getCurrentController()
    self._gaps = []
    self._metadatas = []

    self.__prepare_sheet()
    self._init_metadatas()
    self._merge_metadatas()

We also have this __prepare_sheet() method.

Initialization: Prepare Sheet

A small but powerful setup.

The __prepare_sheet() method handles the initialization, of key instance variables for number and date formats. Streamlining the process right from the start.

  def __prepare_sheet(self) -> None:
    self._numberfmt = self._document.NumberFormats
    self._locale    = self._document.CharLocale

I often use this trick in other macros as well. It keeps things tidy and consistent.

Configuration: Init Metadatas

Managing Instance Variables

To unify the configuration metadata, we start by defining fields as dictionaries. These dictionaries hold the column letter, width, and optional alignment properties.

The _init_metadatas() method organizes collections, for each block of columns. In this movie example, we work with two blocks:

• _metadata_movies_base, and
• _metadata_movies_additional.

The key difference from the simple configuration is that, column letters are no longer hardcoded individually. Instead, we determine column positions dynamically, by merging all blocks.

This process relies on a helper method called _get_relative_column_letter(), to find starting positions. Nomore manual adjustments every time the source sheet changes.

Here’s the setup:

  def _init_metadatas(self) -> None:
    self._metadata_movies_base = {
      'fields': {
        'Year'     : { 'width': 1.5, 'align': 'center' },
        'Title'    : { 'width': 6 },
        'Genre'    : { 'width': 3 },
        'Plot'     : { 'width': 6 },
        'Actors'   : { 'width': 6 },
        'Director' : { 'width': 5 }
      }
    }

    self._metadata_movies_additional = {
      'fields': {
        'Rated'    : { 'width': 2,   'align': 'center' },
        'Runtime'  : { 'width': 2.5, 'align': 'center' },
        'Metascore': { 'width': 2,   'align': 'center' }
      }
    }

This modular approach makes it easier to handle different sheets, even when the column structures vary.

In practice, I’ve found that manually updating, long column references every time the source sheet changes is exhausting. Automating this with relative positioning is far more efficient.

Configuration: Merge Metadatas

Combining Block Configurations

To bring everything together, we merge all column blocks into a single metadata collection.

class FormatterTabularMovies(FormatterTabularData):

  def _merge_metadatas(self) -> None:
    # Columns:   A, H,  L
    self._gaps = [0, 7, 11]

    self._metadatas = [{
      'col-start'     : 'B',
      **self._metadata_movies_base
    }, {
      'col-start'     : 'I',
      **self._metadata_movies_additional
    }]

The focus here is on keeping track of starting points for each block. In this case, columns B and I mark the beginning of each section.

By specifying just the key positions, we eliminate the need for tedious letter-by-letter adjustments.

Formatting: Set Columns Formats

Implementing Unified Configuration

Let’s revisit the _set_columns_format() method in the descendant class. How do we integrate unified configuration into the process?

We can break it down into two main stages:

• Outer loop: Iterates through unified metadata blocks.
• Inner loop: Applies formatting to each field within those blocks.

Stage One: Outer Loop

The outer loop iterates over each metadata block stored in _metadatas.

class FormatterCommon(FormatterBase):
  ...

  def _set_columns_format(self) -> None:
    columns = self._sheet.Columns

    # Alignment mapping
    alignment_map = {
        'left'  : LEFT,  'center': CENTER, 'right' : RIGHT }

    for metadata in self._metadatas:
      start_letter = metadata['col-start']

      pairs = metadata['fields'].items()
      for pair_index, (field, data) in enumerate(pairs, start=1):

At this stage, the method prepares for the inner loop, by extracting fields and starting positions. The start_letter indicates the beginning of each block.

The outer loop effectively creates a foundation, for iterating through each field within the block.

Stage Two: Inner Loop – Applying Formats

Now let’s focus on the inner loop, where the real formatting happens.

      for pair_index, (field, data) in enumerate(pairs, start=1):
        letter = self._get_relative_column_letter(
          start_letter, pair_index)
        width  = data['width'] * 1000
        align  = data.get('align')

        col_index = self._column_letter_to_index(letter)
        column = columns.getByIndex(col_index)
        column.Width = width

        ...

In this part:

• Column letters are dynamically calculated using _get_relative_column_letter().
• Column widths are set by multiplying the configured width by 1000.
• Alignment settings are fetched, but not yet applied.

The variable col_index points to the correct column, and the width adjustment happens directly on the column object.

Formatting Cell Ranges

Next, we define the cell range, and apply alignment and number formats, if specified.

      for pair_index, (field, data) in enumerate(pairs, start=1):
        ...

        start_row = 3
        end_row = self._max_row
        cell_range = self._sheet.getCellRangeByPosition(
          col_index, start_row, col_index, end_row)

        if align in alignment_map:
           cell_range.HoriJustify = alignment_map[align]

        if cell_format := data.get('format'):
           cell_range.NumberFormat = self._get_number_format(cell_format)

Here’s what happens:

• A cell range is created from row 3 to the maximum row in the sheet.
• Horizontal alignment is applied if specified in the metadata.
• If a number format is provided, the method _get_number_format() is called to fetch the format and apply it to the column.

Required Helper Methods

To make this work, we rely on three essential helper methods:

• _column_letter_to_index(): Converts column letters to indices.
• _get_relative_column_letter(): Finds column letters relative to a starting point.
• _get_number_format(): Retrieves or creates number formats as needed.

We’ve already covered these helpers in previous sections, aking this part of the process straightforward to implement.

By combining these pieces, the entire column formatting process becomes highly adaptable. Nomore hardcoding individual columns.

As a summary, we have set:

• Width
• Alignment
• Number Format

Sheet Result

Here’s a preview of the final result after applying the configuration:

Console Logging.

No additional configuration or surprises for this step. Just smooth execution.

>>> tabular_single_movies()
 * Rearranging Columns
   - Insert Gap: A
   - Insert Gap: H
   - Insert Gap: L
 * Setting Rows Width
 * Formatting Columns
 * Finished

The console logs give a clear, step-by-step breakdown of what’s happening behind the scenes. From inserting column gaps to finalizing the column formatting, the output reflects the unified configuration at work. Efficient and predictable.

No hidden traps or unexpected behaviors. Just the macro doing its job.

05: Additional Helper, Constant and Class

Let’s keep things organized.

Helper Method:

• _format_cell_rectangle().

Color scale constant:

• blueScale, tealScale, amberScale, brownScale.
• clBlack.

Border Type:

• lfNone, lfBlack, lfGray.

Helper: Format Cell Rectangle.

No surprises here. This helper is exactly the same as the one used in the simple configuration.

Constant: Color Scale

Same as before. The color constants are carried over without modification.

Class: Border Format Manager

To keep the configuration concise and readable, let’s avoid verbose references like self.lfNone. Instead, we’ll simplify by defining a BorderFormatManager class that returns border formats directly as variables.

class BorderFormatManager:
  def create_line_format_none(self) -> BorderLine2:
    lineFormatNone = BorderLine2()
    lineFormatNone.LineStyle = BorderLineStyle.NONE
    return lineFormatNone

  def create_line_format_black(self) -> BorderLine2:
    ...

  def create_line_format_gray(self) -> BorderLine2:
    .

Now, instead of typing out the full method call every time, we can initialize the border formats once, and reference them as needed:

bfm = BorderFormatManager()
lfNone  = bfm.create_line_format_none()
lfBlack = bfm.create_line_format_black()
lfGray  = bfm.create_line_format_gray()

This keeps the code clean and reduces repetition. Plus, it makes border configurations feel, more like natural variables than method calls.

Import State

To support the new border formats and helpers, a few extra imports are required from com.sun.star:

from abc import ABC, abstractmethod

from com.sun.star.sheet import XSpreadsheetDocument
from com.sun.star.util  import XNumberFormats

from com.sun.star.\
  awt.FontWeight import BOLD
from com.sun.star.\
  table.CellHoriJustify import LEFT, CENTER, RIGHT
from com.sun.star.\
  table import BorderLine2, BorderLineStyle

Nothing fancy, just the essentials to get everything working smoothly.

05: Step Five

Step Overview

In this section, we focus on expanding the unified configuration with a few key additions:

• New Topics (Unified Configuration):

  • Class: BorderFormatManager
  • Formatting Procedure: Add merged titles (multiple block of columns)
  • Formatting Procedure: Set merge title (refactored for one block)
  • Formatting Procedure: Add merged titles post (Hook)

• Revisited Topics (Simple Configuration):

  • Helper: Format Cell Rectangle
  • Predefined Constant: Color Scale (GMC)
  • Predefined Constant: Line Format
  • Formatting Procedure: Add merged title

Macro Source

The full macro source for this section can be found here:

• github.com/…/movies/steps-02/FormatterTabular05.py

Class Diagram

Focus on the highlights, the new additions stand out.

In the updated diagram, you’ll notice a few key elements:

• New Abstract Method: _add_merged_titles()
• New Protected Helper: format_cell_rectangle() for formatting cells.
• Color Scale Box: This holds the predefined color constants for easy reference.

Unlike the simple configuration class, the core of prepare_sheet() remains unchanged, since border-related configurations are separated for clarity.

Abstract Method

One abstract method named _add_merged_titles() is introduced at this stage:

  @abstractmethod
  def _add_merged_titles(self) -> None:
    pass

This sets the foundation for handling merged title formatting in descendant classes.

Basic Flow

With this new method in place, we extend the existing flow to incorporate, the merged title formatting.with add_merged_title().

  def __format_one_sheet(self) -> None:
    ...

    # Apply Header Settings
    print(' * Formatting Header')
    self._add_merged_titles()

The actual implementation of _add_merged_titles(), will be handled in the descendant class, keeping the base class lean and abstract.

Configuration: Init Metadatas

Instance Variables

In line with the unified configuration approach, we consolidate metadata initialization into a structured format. This starts with the fields dictionary, defining column letters, widths, and optional alignments. On top of that, we introduce titles to manage block-level configurations.

class FormatterTabularData(FormatterTabular):
  ...

  # Unified Configuration
  def _init_metadatas(self) -> None:
    self._metadata_movies_base = {
      'fields': {...},
      'titles': [{...}]
    }

    self._metadata_movies_additional = {
      'fields': {...},
      'titles': [{...}]
    }

Each section represents a discrete block of columns, simplifying layout management.

Configuration: Column Blocks

Now, let’s break down the individual column blocks.

Base Column Block

This block covers the core movie attributes: year, title, genre, plot, and so on.

    self._metadata_movies_base = {
      'fields': {
        'Year'     : { 'width': 1.5, 'align': 'center' },
        'Title'    : { 'width': 6 },
        'Genre'    : { 'width': 3, },
        'Plot'     : { 'width': 6, },
        'Actors'   : { 'width': 6, },
        'Director' : { 'width': 5, }
      },

      'titles': [{ 
        'col-start-id' : 1, 'col-end-id' : 6, 'text' : 'Base Movie Data', 
        'bg' : blueScale[3], 'fg' : clBlack                    
      }]
    }

Additional Column Block

The additional block handles auxiliary data: ratings, runtime, and metascore.

    self._metadata_movies_additional = {
      'fields': {
        'Rated'    : { 'width': 2,   'align': 'center' },
        'Runtime'  : { 'width': 2.5, 'align': 'center' },
        'Metascore': { 'width': 2,   'align': 'center' }
      },
      'titles': [{ 
        'col-start-id' : 1, 'col-end-id' : 3, 'text' : 'Additional Data', 
        'bg' : tealScale[3], 'fg' : clBlack                    
      }]
    }

Configuration: Titles

You might wonder. Why use a list for titles?

      'titles': [{ 
        'col-start-id' : 1, 'col-end-id' : 6, 'text' : 'Base Movie Data', 
        'bg' : blueScale[3], 'fg' : clBlack                    
      }]

The answer lies in flexibility. This structure allows multiple title rows within the same block, like this:

      'titles': [{ 
        'col-start-id' : 1, 'col-end-id' : 3, 'text' : 'Base Movie Data', 
        'bg' : blueScale[3], 'fg' : clBlack                    
      }, { 
        'col-start-id' : 4, 'col-end-id' : 6, 'text' : 'Other Movie Data', 
        'bg' : tealScale[3], 'fg' : clBlack                    
      }]

This modular approach makes it easy, to adjust titles or split sections without rewriting large chunks of code.

Configuration: Merge Metadatas

For now, no changes are needed in the metadata merging process. Everything flows seamlessly until the end of this tutorial.

Formatting: Add Merged Title

Inner Loop

How do we implement unified configuration for merged titles?

The process involves two main loop stages:

• Outer Loop: Iterates over unified metadata blocks.
• Inner Loop: Iterates over:
  • Fields: Handled separately by set_columns_format().
  • Titles: Directly relevant to merged title formatting.

In this method, we only leverage the titles portion of the inner loop, as the fields logic resides in set_columns_format().

Now, let’s break down _add_merged_titles(), which calls the helper method __set_merged_title().

  def _add_merged_titles(self) -> None:
    for metadata in self._metadatas:
      self.__set_merged_title(metadata)

    # Call the hook method (default does nothing)
    self._add_merged_titles_post()

Hook for Extensibility

I’ve added a hook method _add_merged_titles_post() for subclasses to override if necessary.

Most of the time, this hook stays untouched. However, in edge cases where additional formatting, or adjustments are required. This hook proves valuable.

  def _add_merged_titles_post(self) -> None:
    pass

This setup ensures the base class maintains simplicity, while descendant classes can introduce custom behavior when needed.

Without modifying core logic.

Solving Hardcoded

Remember those hardcoded lines from earlier?

    self._sheet['B2:BC3'].HoriJustify = CENTER
    self._sheet['B2:BC2'].CharWeight  = BOLD 

Instead of relying on fixed ranges, we can dynamically calculate them based on the titles configuration. This approach keeps the code flexible and adaptable to changes in metadata.

Helper: Set Merged Title

To avoid cluttering _add_merged_titles() with too much detail, I extracted the logic into a private method named __set_merged_title().

This method is essentially a refactored, and cleaner version of the original logic.

  def __set_merged_title(self, metadata: dict[str, any]) -> None:
    start_letter = metadata['col-start']

    for title in metadata['titles']:
      col_letter_start = self._get_relative_column_letter(
          start_letter, title['col-start-id'])  
      col_letter_end   = self._get_relative_column_letter(
          start_letter, title['col-end-id'])

      cell = self._sheet[f"{col_letter_start}2"]
      cell.String        = title['text']
      cell.CellBackColor = title['bg']
      cell.CharColor     = title['fg']

Default Formatting Behavior

The merged header defaults to:

• Bold text (BOLD)
• Center-aligned (CENTER)
• Black border

Here’s the complete version, including the formatting and merging logic:

  def __set_merged_title(self, metadata: dict[str, any]) -> None:
    start_letter = metadata['col-start']

    for title in metadata['titles']:
      ...

      pos = self._column_letter_to_index(col_letter_start)
      self._format_cell_rectangle(
        1, 1, pos, pos, lfBlack)

      merge_address = f"{col_letter_start}2:{col_letter_end}2"
      self._sheet[merge_address].merge(True)
      self._sheet[merge_address].CharWeight = BOLD

      header_address = f"{col_letter_start}2:{col_letter_end}3"
      self._sheet[header_address].HoriJustify = CENTER

That’s It!

This refactor eliminates brittle hardcoding, ensuring the code adapts seamlessly to any column configuration

Sheet Result

The final output for this step will appear as follows:

Console Logging.

For this step, the console output will look like this:

>>> tabular_single_movies()
 * Rearranging Columns
   - Insert Gap: A
   - Insert Gap: H
   - Insert Gap: L
 * Setting Rows Width
 * Formatting Columns
 * Formatting Header
 * Finished

Sometimes, we may not configure everything correctly. By reviewing the console logs, we can quickly debug what went wrong.

Additionally, for heavy processing tasks, using logs like this is more efficient, than constantly watching htop, and seeing a CPU core hogging 100%.

What is Next 🤔?

With the design pattern in place, we can now proceed to colorize the header. And also add borders for both the header and the data.

Feel free to continue reading [ Formatter - Unified Config - Three ].