Where to Discuss?

Local Group

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.

Capability
The macro shall be able to give basic example on spreadsheet formatting in LibreOffice.

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:

Python Macro: Decorated Cells

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 = nfd

This 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.

Python Macro: Run Using APSO


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

Python Macro: 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 CENTER

Here, we import bold and center.

Class Initialization

This is a common part of loading a new blank worksheet.

Python Macro: Class Initialization

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.CharLocale

Because 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.

Python Macro: Execute

  # 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.

Python Macro: Number 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

This 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.

Python Macro: Column Handling

  def column_example(self):
    column = self.sheet. \
      getColumns().getByName('B')
    column.CharHeight   = 10
    column.CharFontName = "Arial"
    column.Width        = 5000

Serial Format

Consider getting into action.

Python Macro: Serial Format

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 2

Then 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.000

This 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.

Python Macro: Ordinal Date Value

The decoration is simple.

  def date_value_example(self):
    # cell decoration
    cell = self.sheet["B3"]
    cell.CellBackColor = 0xBBDEFB
    cell.HoriJustify   = CENTER # or just 2

But 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() - offset

Date Format

There are also two ways to get the date format. I also found the solution from Pytoniak Book.

Python Macro: Date Format

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 = dateFormat

Or 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.

Python Macro: Main Program

def test():
  sample = NumberFormatTest()
  sample.run()

Result

The output is simple, just two cells as shown in below figure:

Python Macro: Decorated Cells

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 ].