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:
Formatting
I’m still using manual formatting, using paste special. Because I still change the design a lot.
The output could be as below:
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 ].