Formatter - Simple Config - Two

Preface

Goal: Separating formatting code and configuration. Example class using simple configuration.

After basic macro, we can start with simple configuration. And see how far can the formatting go.

Example Sheet

You can download example ODS source here:

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

And also example XLSX result here:

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

04: Step Four

This will be the first time that we see the configuration metadata.

Steps Overview

These are the topics for this section:

• Initialization: Prepare Sheet
• Configuration: Field Metadata Configuration
• Helper: Column Letter to Index
• Helper: Number Formats
• Formatting Procedure: Format Columns

Macro Source

The macro source can be obtained in this repository:

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

Class Diagram

See The highlight

You can see an abstract method named ~init_field_metadata(), a protected helper named column_letter_to_index().

We can also see a new private formatting procedure set_columns_format(), and a helper named get_number_format(). This get_number_format() helper is a supporting part of set_columns_format().

At the descendant class below, we have a new method named prepare_sheet(), as a supporting part of initialization.

All abstract method here are protected and denoted by # sharp symbol. No need to be public as there is no need to be accessed feom outside the class. And cannot be set as private, as this required to be overriden in descendant class.

Import Statements

We have additional import statement. com.sun.star is the root module of the LibreOffice UNO API. It provides access to the underlying components of LibreOffice, like documents, tables, spreadsheets, and formatting capabilities.

from abc import ABC, abstractmethod

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

Abstract Method

Also more abstract method. All abstract are protected This abstract method denoted by # sharp symbol.

from abc import ABC, abstractmethod

class FormatterBase(ABC):
  ...

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

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

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

Configuration Metadata

The configuration metadata is very simple. This is dictionary that holds the column letter, the column width, and additionaly the alignment.

  def init_field_metadata(self) -> None:
    self.fields = {
       'Year'     : { 'col': 'B', 'width': 1.5, 'align': 'center' },
       'Title'    : { 'col': 'C', 'width': 6 },
       'Genre'    : { 'col': 'D', 'width': 3 },
       'Plot'     : { 'col': 'E', 'width': 6 },
       'Actors'   : { 'col': 'F', 'width': 6 },
       'Director' : { 'col': 'G', 'width': 5 },

       'Rated'    : { 'col': 'I', 'width': 2,   'align': 'center' },
       'Runtime'  : { 'col': 'J', 'width': 2.5, 'align': 'center' },
       'Metascore': { 'col': 'K', 'width': 2,   'align': 'center' }
    }

You can see the relevancy of the letter shown in below result:

This init_field_metadata() is called in base class initialization.

class FormatterBase(ABC):
  def __init__(self, document) -> None:
    self.document   = document
    self.controller = self.document.getCurrentController()

    self.init_field_metadata()
    self.prepare_sheet()

So yes, this is the very core part. But so flexible that it is implemented in descedndant class.

Basic Flow

Now we can extend the basic flow with set_columns_format().

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

    # Apply Sheet Wide
    print(' * Formatting Columns')
    self.set_sheetwide_view()
    self.set_columns_format()

And then having the implementation written in descendant class.

Formatting: Columns

Now let’s have a look at set_columns_format() method in the descendant class.

class FormatterTabularMovies(FormatterBase):
  ...

  def set_columns_format(self) -> None:
    columns = self.sheet.Columns

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

    for field, data in self.fields.items():
      ...

The loop will iterate all metadatas, and then for each metadata setting these formatting properties

• Width
• Alignment
• Number Format

    for field, data in self.fields.items():
      letter = data['col']
      width  = data['width'] * 1000
      align  = data.get('align')

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

      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)

We require two helpers here:

• column_letter_to_index()
• get_number_format()

Helper: Column Letter to Index

This column_letter_to_index() has multiple usages later. And would be accessed from desecendant class.

  def column_letter_to_index(self, column_letter) -> None:
    index = 0
    for i, char in enumerate(reversed(column_letter)):
      index += (ord(char) - ord('A') + 1) * (26 ** i)
    return index - 1  # Convert to 0-based index

Helper: Get Number Format

This get_number_format() sheet helper, is meant to be used only within the set_columns_format().

  def get_number_format(self, format_string):
    nf = self.numberfmt.queryKey(  \
              format_string, self.locale, True)
    if nf == -1:
       nf = self.numberfmt.addNew( \
              format_string, self.locale)
    return nf

we can see here we have two property:

• self.numberfmt
• self.locale

Initialization: Prepare Sheet

A very useful trick.

This get_number_format() sheet helper, required properties initialization.

class FormatterBase(ABC):
  def __init__(self, document) -> None:
    self.document   = document
    self.controller = self.document.getCurrentController()

    self.init_field_metadata()
    self.prepare_sheet()

The detail is in prepare_sheet().

  def prepare_sheet(self):
    # number and date format
    self.numberfmt = self.document.NumberFormats
    self.locale    = self.document.CharLocale

I usually use this trick for my other macro.

Sheet Result

The result output would be as below:

05: Step Five

After basic formatting that looks so plain, now we can get into color and border.

Steps Overview

These are the topics for this section:

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

Macro Source

The macro source can be obtained in this repository:

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

Class Diagram

The highlight and the new box

You can see an abstract method named ~add_merged_title(), a protected helper named format_cell_rectangle() as supporting method.

Also we have new box, containing color scale.

Why do the prepare_sheet() is also highlighted? Because it has so many additional lines. Almost like a new method.

Import Statements

We need additional import statement from com.sun.star.

from abc import ABC, abstractmethod

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

Constant: Color Scale

We are going to add some color right? So let us define the color first. I woukd like to use color scale, and my choice is Google Material Scale.

For example this blue:

blueScale = {
  0: 0xE3F2FD, 1: 0xBBDEFB, 2: 0x90CAF9,
  3: 0x64B5F6, 4: 0x42A5F5, 5: 0x2196F3,
  6: 0x1E88E5, 7: 0x1976D2, 8: 0x1565C0,
  9: 0x0D47A1
}

Let’s continue the rest. You may add your own favorite color scale.

blueScale  = {...}
tealScale  = {...}
amberScale = {...}
brownScale = {...}
redScale   = {...}

And finally the default black color.

clBlack = 0x000000

Initialization: Prepare Sheet

We also need to define border color, for example.

    lineFormatGray = BorderLine2()
    lineFormatGray.LineStyle = BorderLineStyle.SOLID
    lineFormatGray.LineWidth = 20
    lineFormatGray.Color = 0xE0E0E0 #gray300
    self.lfGray = lineFormatGray

This predefined border property can be included, as a part of prepare_sheet().

  def prepare_sheet(self):
    # number and date format
    self.numberfmt = self.document.NumberFormats
    self.locale    = self.document.CharLocale

    ...
    self.lfNone= lineFormatNone

    ...
    self.lfBlack = lineFormatBlack

    ...
    self.lfGray = lineFormatGray

Basic Flow

Now we can extend the basic flow with set_columns_format().

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

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

And then having the implementation written in desccendant class.

Formatting: Merged Title

Now let’s have a look at add_merged_title() method in the descendant class.

Let’s start with property for the whole length column.

  def add_merged_title(self) -> None:
    self.sheet['B2:K3'].HoriJustify = CENTER
    self.sheet['B2:K2'].CharWeight = BOLD 

This ranges are hardcoded in this method. We will deal with this later in unified configuration, by calculating the range automatically.

Now let’s move on to changing property for the first block column one by one. We will also deal with hardcoded setting later, move the hardcoced part into unified configuration.

    cell = self.sheet['B2']
    cell.String = 'Base Movie Data'
    cell.CellBackColor = blueScale[3]
    cell.CharColor = clBlack
    self.format_cell_rectangle(
      1, 1, 1, 1, self.lfBlack)
    self.sheet['B2:G2'].merge(True)

And also for the second block column one by one. We also notice this format_cell_rectangle().

    cell = self.sheet['I2']
    cell.String = 'Additional'
    cell.CellBackColor = tealScale[3]
    cell.CharColor = clBlack
    self.format_cell_rectangle(
      1, 1, 8, 8, self.lfBlack)
    self.sheet['I2:K2'].merge(True)

That’s all for main title of the column block.

Helper: Format Cell Rectangle.

So what is inside this format_cell_rectangle() anyway? It is just automation of long border formatting:

1. Four Edges: top, botom, left, right.
2. Four Corners: top_left, top_right, bottom_left, bottom_right.

We need to define the cell range for that eight border format setting. These eight has different cell range. To avoid long unreadable code, we need to make a shortcut of the method.

 def format_cell_rectangle(self,
        a_t, a_b, a_l, a_r, line_format) -> None:

    func_gcrb = self.sheet.getCellRangeByPosition

Now we can directly format the border for rectangle edges: top, botom, left, right.

    cr_top = func_gcrb(a_l, a_t, a_r, a_t)
    cr_top.TopBorder       = line_format

    cr_bottom = func_gcrb(a_l, a_b, a_r, a_b)
    cr_bottom.BottomBorder = line_format
    
    cr_left = func_gcrb(a_l, a_t, a_l, a_b)
    cr_left.LeftBorder     = line_format

    cr_right = func_gcrb(a_r, a_t, a_r, a_b)
    cr_right.RightBorder   = line_format

And also rectangle corner: top_left, top_right, bottom_left, bottom_right.

    cr_top_left = func_gcrb(a_l, a_t, a_l, a_t)
    cr_top_left.TopBorder        = line_format
    cr_top_left.LeftBorder       = line_format

    cr_top_right = func_gcrb(a_r, a_t, a_r, a_t)
    cr_top_right.TopBorder       = line_format
    cr_top_right.RightBorder     = line_format

    cr_bottom_left = func_gcrb(a_l, a_b, a_l, a_b)
    cr_bottom_left.BottomBorder  = line_format
    cr_bottom_left.LeftBorder    = line_format

    cr_bottom_right = func_gcrb(a_r, a_b, a_r, a_b)
    cr_bottom_right.BottomBorder = line_format
    cr_bottom_right.RightBorder  = line_format

We are done with column block title formatting.

Sheet Result

The result output would be as below:

What is Next 🤔?

After basic formatting, we can colorized the header. And also give border for both header and data.

Consider continue reading [ Formatter - Simple Config - Three ].