LibreOffice

Python - Excel

Local Group

Preface

Goal: Simple macro to diff two similar spreadsheets.

This time, using LibreOffice, instead of openpyxl.

Constraint: in the same workbook.

Why Diff?

Tracking Changes

How do you catch up with the latest update from your team?

The macro shall be able to show difference between one sheet and another.

You can read the long why here:

Python - Excel - Diff

How to Track?

Brute force compare, then coloring.

Consider the input. These required two sheets: First sheet, and then the Second sheets. Both on the same workbook.

Python Macro: Input: First Sheet

Then, where is wally?

Python Macro: Input: Second Sheet

Both are pretty similar, but if you look closer there are differences. As shown in output below:

Python Macro: Output: Result Sheet

The figure above is a simplification of real data.

Example Data

You can download here

sample.xlsx

Why Automation?

Time savings.

It is pretty much easier than doing manually.

Shortly, we should invest on thinking, rather than doing tedious repetitive task.

Diff Code

Enough with introduction. Let’s get into the code.

There is only consist one short file required.

gitlab.com/…/diff/Diff.py

Skeleton

I’m using class.

# coding: utf-8
from __future__ import unicode_literals

class SheetDiff:
  def __init__(self, ...):
    ...

  def compare(self, cell_fst, cell_snd):
    ...

  def diff(self):
    for col in self.columns:
      for row in self.rows:
        ...

def main():
  sample = SheetDiff(...)
  sample.diff()

Very simple, it only have one method diff, beside the constructor.

Import Header

Since this using library library, we commonly declare this at the top.

# coding: utf-8
from __future__ import unicode_literals

Python Macro: ViM: Impot Header

Constructor

The constructor is also simple.

  def __init__(self, sheet_fst,
                     sheet_snd, cols, rows):

This will be called in main later, for example as below:

  sample = SheetDiff(
             'First', 'Second',
             r_columns, r_rows)

Python Macro: ViM: Constructor

Consider go back to our constructor, it is initialize a few stuff, such as open both sheet.

    document       = XSCRIPTCONTEXT.getDocument()
    self.sheet_fst = document.Sheets[sheet_fst]
    self.sheet_snd = document.Sheets[sheet_snd]

And also keep other variable as well, bundled as class properties.

    self.rows      = rows
    self.columns   = cols

At last, add some constant, that will be used throught out the loop later.

    self.color     = 0xCDDC39 # lime5

The Diff Method

This double loop compare all. And finally save the result to new spreadsheet.

  def diff(self):
    for col in self.columns:
      for row in self.rows:
        cell_fst = self.sheet_fst \
          .getCellByPosition(col, row)
        cell_snd = self.sheet_snd \
          .getCellByPosition(col, row)
        self.compare(cell_fst, cell_snd)

Python Macro: ViM: Diff Method

The detail horseworks comes in compare method.

The Compare Method

the compare method is just this code

  def compare(self, cell_fst, cell_snd):
    if (cell_fst.String != cell_snd.String):
      print(f'{cell_snd.AbsoluteName:16} : '
            f'{cell_fst.String:8} -> '
            f'{cell_snd.String:8}')
      cell_snd.CellBackColor = self.color

Python Macro: ViM: Compare Method

If the value is different for both sheet, there are two actions that will be taken:

Print in terminal.
Color the cell, so we can see the difference.

Main

Program entry Point

This is also only consist one file

def main():
  r_columns = range(0, 8)
  r_rows    = range(0, 42)
  sample = SheetDiff(
             'First', 'Second',
             r_columns, r_rows)
  sample.diff()

Python Macro: ViM: Program Entry Point

With the result similar as below:

APSO python console [LibreOffice]
3.10.2 (main, Jan 15 2022, 21:33:02) [GCC 11.1.0]
Type "help", "copyright", "credits" or "license" for more information.
>>> main()
$Second.$B$21    : Female   -> Mouse   
$Second.$C$11    : 0.55     -> 0.54    
$Second.$C$20    : 0.57     -> 0.36    
$Second.$C$24    : 1        -> 0.9     
$Second.$C$25    : 1        -> 1.1     
$Second.$D$7     : Female   -> Male    
$Second.$D$22    : Female   -> Dog     
$Second.$E$14    : 0.5      -> 0.77    
$Second.$F$25    : Juvenile -> Cat     
$Second.$G$22    : 0.66     -> 0.49    
$Second.$G$42    : 0.38     -> 0.58

Python Macro: APSO: LibreOffice Macro

The Result

Finally we have peace, with complete coloring.