Preface
Goal: Example Macro to Decorate LibreOffice Calc Format
Once I jump into LibreOffice Macro with python, I see a place where no tutorial is exist. Except, of course from the great book of Pytoniak. He is the Bible of OpenOffice Macro, and after one decade his work is still useful.
The main issue is I still have to write it to python. It tooks two days to find out, even some simple issue. So I put my work here for you, at my blog.
Why Decoration?
Your spreadsheets should looks good.
For daily basis automation, good data is not enough.
I have made an accounting application, about fifteen years ago (2007) with openoffice, and the output looks nice. Now I want to do it with simple tools, such as python macro for daily basis.
How you handle view result is important. This include, font, style, alignment, and color. Also range handling, such as column width and visibility. And do not forget the number format, such as your favorite currency, and serial number formatting, and date representation.
The rest you can find out yourself, based on this short example.
Example
Store value in cell programatically,
And decorate cell programmatically using UNO python macro.
I once have an issue. I have documented the solution. I hope it is useful.
1: Quick And Dirty
Result
The output is simple, just two cells as shown in below figure:
Nothing fancy here, we are going to have more data, in the next article.
Source
The quick and dirty version is here.
The complete code is here.
# coding: utf-8
from __future__ import unicode_literals
from datetime import datetime
from com.sun.star.awt.FontWeight import BOLD
from com.sun.star.table.CellHoriJustify import CENTER
def test():
desktop = XSCRIPTCONTEXT.getDesktop()
model = desktop.loadComponentFromURL(
"private:factory/scalc", "_blank", 0, ())
numberfmt = model.NumberFormats
locale = model.CharLocale
sheet = model.Sheets[0]
column = sheet.getColumns().getByName('B')
column.CharHeight = 10
column.CharFontName = "Arial"
column.Width = 5000
# serial format example
cell = sheet["B2"]
cell.CellBackColor = 0x64B5F6
cell.CharWeight = BOLD
cell.HoriJustify = CENTER # or just 2
cell.Value = 81443518011000.0
serial_format = '00\.000\.000\.0-000\.000'
nfs = numberfmt.queryKey(serial_format, locale, True)
if nfs == -1:
nfs = numberfmt.addNew(serial_format, locale)
cell.NumberFormat = nfs
# date format example
cell = sheet["B3"]
cell.CellBackColor = 0xBBDEFB
cell.HoriJustify = CENTER # or just 2
# Offset of the date value for the date of 1900-01-00
offset = 693594
date_string = '05/07/2022'
date_value = datetime.strptime(date_string, "%d/%m/%Y")
cell.Value = date_value.toordinal()-offset
# nFormat = numberfmt.getStandardFormat( 2, locale )
# cell.NumberFormat = nFormat
date_format = 'DD-MMM-YY;@'
nfd = numberfmt.queryKey(date_format, locale, True)
if nfd == -1:
nfd = numberfmt.addNew(date_format, locale)
cell.NumberFormat = nfdThis code above might be short, but code tends to get longer and longer. You are going to run into trouble soon, if you do not organize it well.
I will migrate this barbaric code, into more readable proper OOP approach. So I can explain one by one, clearly.
Running Macro
As usual, you need APSO extension.
2: Example Class
It is a good idea to refactor stuff. Consider the tidier code using OOP below:
Source
The quick and dirty version is here.
We can examine step by step.
Skeleton
Consider begin with the big picture.
class NumberFormatTest:
def __init__(self):
def run(self):
def get_number_format(self, format_string):
def column_example(self):
def serial_example(self):
def date_value_example(self):
def date_format_standard(self):
def date_format_custom(self):
def test():UNO Import
Apart from common import, UNO constant can be accessed using import
# coding: utf-8
from __future__ import unicode_literals
from datetime import datetime
from com.sun.star.\
awt.FontWeight import BOLD
from com.sun.star.\
table.CellHoriJustify import CENTERHere, we import bold and center.
Class Initialization
This is a common part of loading a new blank worksheet.
class NumberFormatTest:
def __init__(self):
desktop = XSCRIPTCONTEXT.getDesktop()
model = desktop.loadComponentFromURL(
"private:factory/scalc", "_blank", 0, ())
self.numberfmt = model.NumberFormats
self.locale = model.CharLocale
self.sheet = model.Sheets[0]The tricky part is getting the NumberFormats and CharLocale.
Loading using below code obviously won’t work:
doc = XSCRIPTCONTEXT.getDocument()
numbers = doc.NumberFormats
locale = doc.CharLocaleBecause LibreOffice generate, different desktop instance while loading new sheet.
Execute The Process
This is all five method is the examples we are going to run.
# gather all example here
def run(self):
self.column_example()
self.serial_example()
self.date_value_example()
# self.date_format_standard()
self.date_format_custom()There are two different way to get date format. You can pick either of them. I choose to comment the standard format.
Helper: Number Format
This is also tricky part. We cannot just set numberFormat. We have to add the format string to LO index first, then assign the index to the cell.
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 nfThis is considered a helper method. We are probably going to call this method many times.
Column Handling
Range handling can be achieved different way. One most common is column selection.
def column_example(self):
column = self.sheet. \
getColumns().getByName('B')
column.CharHeight = 10
column.CharFontName = "Arial"
column.Width = 5000Serial Format
Consider getting into action.
First, some common decoration, such as color, font, and alignment.
def serial_example(self):
# cell decoration
cell = self.sheet["B2"]
cell.CellBackColor = 0x64B5F6
cell.CharWeight = BOLD
cell.HoriJustify = CENTER # or just 2Then the value.
# handling value and format
cell.Value = 81443518011000.0
serial_format = '00\.000\.000\.0-000\.000'
cell.NumberFormat = self. \
get_number_format(serial_format)We can format such number so we have this as output:
81.443.58.0-110.000This is the tax serial number in Indonesia.
Commonly referred as NPWP.
Ordinal Date Value
You have to get the ordinal value first, then offset.
This is the trickies part so far. I found the solution after googling somewhere for hours.
The decoration is simple.
def date_value_example(self):
# cell decoration
cell = self.sheet["B3"]
cell.CellBackColor = 0xBBDEFB
cell.HoriJustify = CENTER # or just 2But you need to examine the value handling, more closely.
# Offset of the date value
# for the date of 1900-01-00
offset = 693594
# handling date value
date_string = '05/07/2022'
date_value = datetime. \
strptime(date_string, "%d/%m/%Y")
cell.Value = date_value.toordinal() - offsetDate Format
There are also two ways to get the date format. I also found the solution from Pytoniak Book.
The standard date format. We can get the constant from UNO object.
def date_format_standard(self):
cell = self.sheet["B3"]
dateFormat = self.numberfmt. \
getStandardFormat(2, self.locale)
cell.NumberFormat = dateFormatOr we can make our own format, as usual using our helper.
def date_format_custom(self):
cell = self.sheet["B3"]
date_format = 'DD-MMM-YY;@'
cell.NumberFormat = \
self.get_number_format(date_format)Main Program: Test()
Finally, we can execute the main code.
def test():
sample = NumberFormatTest()
sample.run()Result
The output is simple, just two cells as shown in below figure:
I think that is all.
What is Next 🤔?
We are going to utilize example data. We are going to apply this decoration stuff, to a a master detail table, with a lot of column fields.
Consider continue reading [ LibreOffice - CSV Master Detail ].