Formatter - Simple Config - Three

Preface

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

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

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

06: Step Six

Steps Overview

These are the topics for this section:

• Helper: Get head range, set head rectangle, apply head border
• Formatting Procedure: Format head border
• Formatting Procedure: Format head colors

Macro Source

The macro source can be obtained in this repository:

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

In Macro Dialog, this would looks like.

Class Diagram

You can see a highlighted private method named format_head_colors(). This method doesn’t need to be altered whatsoever, so I set visibility as private.

You can also see other highlighted method. An abstract method named ~format_head_borders(), with supporting method named apply_head_borders. This apply_head_borders required two helpers: set_head_rectangle() and get_head_range().

We’re almost done.

Import Statements

We need to import TableBorder2 from com.sun.star.

from abc import ABC, abstractmethod

ffrom 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, TableBorder2

Configuration Metadata

We can set the background color in metadata dictionary.

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

       'Rated'    : { 'col': 'I', 'width': 2,   'bg': tealScale[2],
                      'align': 'center' },
       'Runtime'  : { 'col': 'J', 'width': 2.5, 'bg': tealScale[1],
                      'align': 'center' },
       'Metascore': { 'col': 'K', 'width': 2,   'bg': tealScale[2],
                      'align': 'center' }
    }

You can see the relevancy of the background color, shown in below result:

It is a matter of taste. I like to have different color scale in column. Let’s see different color scale in other block.

You can set any color to match different flavor. Or maybe your boss taste.

Basic Flow

Now we can extend the basic flow, with format_head_borders() and format_head_colors().

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

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

And then having the implementation below.

Formatting: Head Colors

How do the coloring works? Let’s have a look at the private method format_head_colors() method in the descendant class.

  def format_head_colors(self) -> None:  
    for field, data in self.fields.items():
      if bg_color := data.get('bg'):
        letter = data['col']      
        row_index = 2
        col_index = self.column_letter_to_index(letter)

        cell = self.sheet.getCellByPosition(
          col_index , row_index)
        cell.CellBackColor = bg_color 

Iterating each metadata fields, First it checks if the background color configuration exist using walrus operator, then change color in that cell address.

Abstract Method

To be flexible, the method format_head_borders() needed to be implemented in descendant class. But called in basic flow in base class. This is why we define the abstract with protected visibility in base class

from abc import ABC, abstractmethod

class FormatterBase(ABC):
  ...

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

Formatting: Head Borders

Now the implementation in descendant class. This is basically just another hardcoded configuration. We will transform this from hardcoded configuration to dictionary later.

  def format_head_borders(self) -> None:  
    # Base Movie Data
    self.apply_head_border(
      'B', 'G', self.lfBlack, self.lfBlack)

    # Additional Data
    self.apply_head_border(
      'I', 'K', self.lfBlack, self.lfBlack)

As you can see, this use apply_head_border(). For real life sheet will very long columns, apply_head_border() is utilized heavily.

Helper: Apply Head Border

This apply_head_border() helper is meant to be used only within the format_head_borders(). The logic so common for any case, so it is better to be implemented in base class. But since it is called in descendant class, this is actually has protected visibility.

Now let’s see what it goes in apply_head_border(). It has two kind of borders:

• Border Outside: Edges and Corner
• Border Inside: Vertical only

First the outside border.

  def apply_head_border(self,
        letter_start, letter_end,
        outer_line, vert_line) -> None:

    self.set_head_rectangle(
      letter_start, letter_end, outer_line)

    ...

Then the inside border.

  def apply_head_border(...)
    ...

    border = TableBorder2()
    border.IsVerticalLineValid = True
    border.VerticalLine = vert_line

    cell_range = self.get_head_range(
      letter_start, letter_end)
    cell_range.TableBorder2 = border

This require two private methods:

• set_head_rectangle()
• get_head_range()

Supporting Helper: Head Rectangle

Border Outside: Edges and Corner

This supporting helper meant to be used only within the apply_head_border(). All it does is defining the cell range for rows and columns: top, bottom (max row), left, and right. All in context of column blocks.

  def set_head_rectangle(self,
        letter_start, letter_end, line_format) -> None:

    a_t = 2
    a_b = 2
    a_l = self.column_letter_to_index(letter_start)
    a_r = self.column_letter_to_index(letter_end)

    self.format_cell_rectangle(a_t, a_b, a_l, a_r, line_format)

This use a hardcoded row number two.

Supporting Helper: Head Range

This supporting helper meant to be used only within the apply_head_border(). All it does is just getting the range.

  def get_head_range(self, letter_start, letter_end):
    # Define the cell range for rows and columns
    head_row = 2
    col_start = self.column_letter_to_index(letter_start)
    col_end   = self.column_letter_to_index(letter_end)

    # Define the cell range for the outer border and vertical lines
    return self.sheet.getCellRangeByPosition(
      col_start, head_row, col_end, head_row)

This use a hardcoded row number two.

Sheet Result

The result output would be as below:

07: Step Seven

Steps Overview

These are the topics for this section:

• Helper: Get data range, set data rectangle, apply data border
• Formatting Procedure: Format data border

Macro Source

The macro source can be obtained in this repository:

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

Class Diagram

You can see highlighted abstract method named ~format_data_borders() with protected visibility specifier.

The structure for data border is very similar with header border. With supporting method named apply_data_borders. This apply_data_borders required two helpers: set_data_rectangle() and get_data_range().

Basic Flow

Now we can extend the basic flow, with format_head_borders() and format_head_colors().

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

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

    # Apply borders to the specified range
    print(' * Formatting Border')
    self.format_data_borders()

Abstract Method

For the same reason with format_head_borders(), the format_data_borders() method in base class is defined as abstract with protected visibility specifier.

from abc import ABC, abstractmethod

class FormatterBase(ABC):
  ...

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

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

Formatting: Data Borders

The implementation in descendant class is, basically just another hardcoded configuration.

  def format_data_borders(self) -> None:  
    # Base Movie Data
    self.apply_data_border(
      'B', 'C', self.lfBlack, self.lfBlack, self.lfGray)
    self.apply_data_border(
      'D', 'G', self.lfBlack, self.lfGray, self.lfGray)

    # Additional Data
    self.apply_data_border(
      'I', 'K', self.lfBlack, self.lfGray, self.lfGray)

As you can see, this use apply_data_border(). Also for real life sheet will very long columns, apply_data_border() is utilized multiple times. This is a pattern that needeed to be moved to unified configuration later.

Helper: Apply Data Border

Flr the same reason with the header counterparts, This apply_data_border() has protected visibility specifier.

The inside of apply_data_border() has two kind of borders:

• Border Outside: Edges and Corner
• Border Inside: Vertical and Horizontal

First the outside border.

  def apply_data_border(self,
        letter_start, letter_end,
        outer_line, vert_line, horz_line) -> None:

    # Border Outside: Edges and Corner
    self.set_data_rectangle(
      letter_start, letter_end, outer_line)

    ...

Then the inside border.

  def apply_data_border(...)
    ...

    border = TableBorder2()
    border.IsVerticalLineValid   = True
    border.IsHorizontalLineValid = True
    border.VerticalLine   = vert_line
    border.HorizontalLine = horz_line

    cell_range = self.get_data_range(
      letter_start, letter_end)
    cell_range.TableBorder2 = border

This also require two private methods:

• set_data_rectangle()
• get_data_range()

Supporting Helper: Data Rectangle

Border Outside: Edges and Corner

This supporting helper meant to be used only within the apply_data_border(). All it does is defining the cell range for rows and columns: top, bottom (max row), left, and right. All in context of column blocks.

  def set_data_rectangle(self,
        letter_start, letter_end, line_format) -> None:

    a_t = 3  
    a_b = self.max_row
    a_l = self.column_letter_to_index(letter_start)
    a_r = self.column_letter_to_index(letter_end)

    self.format_cell_rectangle(a_t, a_b, a_l, a_r, line_format)

Supporting Helper: Data Range

This supporting helper meant to be used only within the apply_data_border(). All it does is just getting the range.

  def get_data_range(self, letter_start, letter_end):
    # Define the cell range for rows and columns
    start_row = 3
    end_row = self.max_row
    col_start = self.column_letter_to_index(letter_start)
    col_end   = self.column_letter_to_index(letter_end)

    # Define the cell range for the outer border and vertical lines
    return self.sheet.getCellRangeByPosition(
      col_start, start_row, col_end, end_row)

Sheet Result

The result output would be as below:

We are done with the core. But I would like to give additional material. You can have any hook for the basic flow. This way we can make a modification for special case, for example coloring row based on specific value.

What is Next 🤔?

We are not finished yet. There is still additional coloring. I need time to complete the material for the rest of the article.

Meanwhile we can continue to merge all the previous messy code into a unified configuration. Let’s start over with this unified-config class, also begin with with rearangging the column. We will also apply design pattern using abstract methods and hook.

Consider continue reading [ Formatter - Unified Config - One ].