Where to Discuss?

Local Group

Preface

Goal: Build a custom multi column pivot table using LibreOffice Macro.

We are going to write to worksheet using our previous Dog Class, with a few additional method.


3: Pivot in Sheet Preparation

The difference is just the output. The output is a sheet instead of CLI.

Code Skeleton

The big picture of the Dog Class.

# coding: utf-8
from __future__ import unicode_literals

class DogSample:
  #...

  def __init__(self, sheet_name, rows):
    #...

  def reset(self):
    self.sample = #...

  def get_total(self):
    return #...

  def display_to_console(self):
    #...

  def update(self, pred, prob):
    #...

  def walk_each_row(self):
    for row in self.rows:
      #...

  def prepare_sheet(self):
    #...

  def write_to_sheet(self):
    #...

  def pivot(self, #... ):
    #...

def main():
  #...

Dog Class Property

We can add more probability group, to serve more category.

class DogSample:
  str_ranges  = {  0: '0.0-0.2',
                  20: '0.2-0.4', 40: '0.4-0.6',
                  60: '0.6-0.8', 80: '0.8-1.0' }

In this case, we have more probability for male dogs, so we have to add the ranges.

Dog Class Initialization

Nothing change.

Pivot Method

After this three variables

  • title,
  • name_pred,
  • name_prob

This also initialize two other variables

  • name_key_dest,
  • name_val_dest
  def pivot(
    self, title,
    name_pred, name_prob,
    name_key_dest, name_val_dest
  ):
    self.title     = title
    self.name_pred = name_pred
    self.name_prob = name_prob
    self.name_key_dest = name_key_dest
    self.name_val_dest = name_val_dest

    self.reset()
    self.walk_each_row()
    self.display_to_console()
    self.write_to_sheet()

Now it call four functions.

Defining Property

We should reset the counter for each calls.

  def reset(self):
    self.sample = {
      'puppy'    : 0,
      'juvenile' : 0,
      'junior'   : { 40: 0, 60: 0, 80: 0 },
      'female'   : { 40: 0, 60: 0, 80: 0 },
      'male'     : { 0:  0, 20: 0, 40: 0, 60: 0, 80:0 }
    }

Walk The Loop

Nothing change.

This require update method.

Updating Sample Count

Nothing change.

Counting Total

Nothing change.

Printing The Output

Nothing change.


4: Fill the Worksheet

This is still the same Class as before, but we focus on the the worksheet.

Main function

Consider see the bigger picture first. This is how we call the pivot picture.

def main():
  r_start =  3
  r_stop  = 43
  rows = range(r_start, r_stop)

  sample = DogSample("Combined", rows)
  sample.prepare_sheet()
  sample.pivot('First Pool',  'B', 'C', 'C', 'D')
  sample.pivot('Second Pool', 'D', 'E', 'E', 'F')
  sample.pivot('Third Pool',  'F', 'G', 'G', 'H')

I will explain the sample.pivot(...) later.

Dog Class Initialization

Instead of the setting destination sheet (sheet_dst), in initialization part, we put it in prepare function. New sheet pivot sheet only be called, if the data gathering valid.

Preparing Sheet

This is the code for creating new pivot sheet.

  def prepare_sheet(self):
    document   = XSCRIPTCONTEXT.getDocument()
    sheets_dst = document.Sheets
    if not sheets_dst.hasByName('Pivot'):
      sheets_dst.insertNewByName('Pivot', 1)
    self.sheet_dst = sheets_dst['Pivot']

This is the part where can activate the newly created pivot sheet.

    desktop    = XSCRIPTCONTEXT.getDesktop()
    model      = desktop.getCurrentComponent()
    controller = model.getCurrentController()
    controller.setActiveSheet(self.sheet_dst)

Now we can write directly the row header into the worksheet.

    self.sheet_dst["B7"].String = 'Puppy'
    self.sheet_dst["B8"].String = 'Junior'
    self.sheet_dst["B11"].String = 'Female'
    self.sheet_dst["B14"].String = 'Male'
    self.sheet_dst["B19"].String = 'Total Result'

Worksheet: Preparation

We need to add column header.

  def write_to_sheet(self):
    # shorter form of property
    s  = self.sample
    sr = self.str_ranges

    row_p = self.name_key_dest # row name: probability
    row_c = self.name_val_dest # row name: count value
    self.sheet_dst[row_p + '6'].String = 'Prob'
    self.sheet_dst[row_c + '6'].String = 'Count'

Worksheet: Puppy

Then we can continue with Puppy.

    self.sheet_dst[row_p + '7'].String = '1.0'
    self.sheet_dst[row_c + '7'].Value  = s['puppy']

Worksheet: Junior or Juvenile

Since we have two mode for different cameras, the displayed result is different for both:

    if s['juvenile'] > 0:
      self.sheet_dst[row_p + '10'].String = 'Juvenile'
      self.sheet_dst[row_c + '10'].Value  = s['juvenile']

    if sum(s['junior'].values()) > 0:
      for key in [40, 60, 80]:
        col = str(8+int(key/20)-2)
        self.sheet_dst[row_p + col].String = sr[key]
        self.sheet_dst[row_c + col].Value  = s['junior'][key]

I remind that the second camera use juvenile term, without probability.

Worksheet: Male or Female

And the rest is the same

    for key in [40, 60, 80]:
      if s['female'][key]:
        col = str(11+int(key/20)-2)
        self.sheet_dst[row_p + col].String = sr[key]
        self.sheet_dst[row_c + col].Value  = s['female'][key]

    for key in [0, 20, 40, 60, 80]:
      if s['male'][key]:
        col = str(14+int(key/20))
        self.sheet_dst[row_p + col].String = sr[key]
        self.sheet_dst[row_c + col].Value  = s['male'][key]

Total Count

Do not forget to also write the count.

    self.sheet_dst[row_c + '19'].Value = self.get_total()

We are done.

The Result in Sheet

The plain result without formatting comes as below figure:

Python Macro Output: Plain Pivot

Formatting

I’m still using manual formatting, using paste special. Because I still change the design a lot.

Python Macro: Output: Paste Special

The output could be as below:

Python Macro: Output: Colored Pivot

Like I said, in my real life busy office hours, Sometimes I have to build many multi column pivot table urgently.

This ready to used script really save my precious time.


Conclusion

This is just an example about modelling data with python, and writing back to worksheet. We can explore more for cool stuff, such as statistical analysis and such. After all, the limit is our imagination.

That is all. Thank you for reading.

What do you think?


What is Next 🤔?

Consider continue reading [ LibreOffice - Diff ].