LibreOffice - Macro - Merge

   
   
 macro  
 automation  
Python Automation Article Series Test

LibreOffice

Python - Excel

Table of Content
Where to Discuss?

Local Group

Preface

Goal: Merge worksheet from multiple ODS into one ODS.

Here we are, after split, we are going to merge some worksheets. Gathered into one workbook.

Opening ODS File

We have discussed about saving files. How about loading ODS file? Sure we also have to use URL.

def open_ods_file():
  desktop  = XSCRIPTCONTEXT.getDesktop()
  file_url = "file:///home/epsi/monthly.ods"
  model = desktop.loadComponentFromURL(
    file_url, '_default', 0, ())

Query Source Files

Since we already know how to open ODS files, we need to query all the ODS files in a folder, Now we can use monthly folder as a source.

def query_files_test():
  import os
  from os import listdir
  from os.path import isfile, join
  import pprint

  path = '/home/epsi/monthly/'

  onlyfiles = [f for f in listdir(path) if isfile(join(path, f))]
  onlyods   = [f for f in onlyfiles if '.ods' in f]
  onlyods.sort()

  my_print  = pprint.PrettyPrinter(width=60, compact=True)
  my_print.pprint(onlyods)

To avoid long list, I prefer to output using pretty print.

Python Macro: Query Files

Merge Sheets

Now almost the final part.

Loop For Each Sheet

It basically is just importSheet from UNO API, with the right file name arrangement.

  for file_name in onlyods:
    sheet_name = os.path.splitext(file_name)[0]
    file_url = "file://" + path + file_name
    print(file_url)

    model_src = desktop.loadComponentFromURL(
      file_url, '_default', 0, ())
    model_dst.Sheets.importSheet(
      model_src, sheet_name, 0)
    model_src.close(True)

Remove The First Sheet

We finally have to remove unused sheet. But how? We just put removeByName on the last line.

  first_sheet = model_dst.Sheets[0]
  
  # write something, do not delete this line
  first_sheet.getCellRangeByName(
    "A1").String = "Hello World!"

  ... # loop here

  model_dst.Sheets.removeByName(
    first_sheet.Name)

Why do I need the Hello World! stuff. Because LibreOffice will automatically close our newly created instance, if it is completey empty.

Sorting

FIFO or LIFO

Since we put each new sheet at the first sheet tab (zero). The newly created sheet will be put first. It means what first in, will put last.

    model_dst.Sheets.importSheet(
      model_src, sheet_name, 0)

We can solve by reverse the sort.

  onlyods.sort(reverse=True)

Or you might want to put the sheet last by using length of sheets.

Complete Code

Finally the complete code.

def merge_sheets():
  import os
  from os import listdir
  from os.path import isfile, join
  import pprint

  desktop     = XSCRIPTCONTEXT.getDesktop()
  model_dst   = desktop.loadComponentFromURL(
      "private:factory/scalc", "_blank", 0, ())
  first_sheet = model_dst.Sheets[0]
  
  # write something, do not delete this line
  first_sheet.getCellRangeByName(
    "A1").String = "Hello World!"

  path = '/home/epsi/monthly/'

  onlyfiles = [f for f in listdir(path) if isfile(join(path, f))]
  onlyods   = [f for f in onlyfiles if '.ods' in f]
  onlyods.sort(reverse=True)

  my_print  = pprint.PrettyPrinter(width=60, compact=True)
  my_print.pprint(onlyods)

  for file_name in onlyods:
    sheet_name = os.path.splitext(file_name)[0]
    file_url = "file://" + path + file_name
    print(file_url)

    model_src = desktop.loadComponentFromURL(
      file_url, '_default', 0, ())
    model_dst.Sheets.importSheet(
      model_src, sheet_name, 0)
    model_src.close(True)

  model_dst.Sheets.removeByName(
    first_sheet.Name)

The result is about similar to figure below:

Python Macro: Example Workbook

Thank You

My personal gratitude to the owner of this tutolibro site.

What is Next 🤔?

We can go further with row, column, and cell.

Consider continue reading [ LibreOffice Calc - Python Macro - Coloring ].