LibreOffice - Macro - Split

Preface

Goal: Split worksheet from one ODS into multiple ODS.

Splitting and Merging worksheet is, a very common task that can be automated. Fortunately, this task is simple with UNO API.

Copy Sheet Within Workbook

In order to split worksheet, we require to copy from one workbook to another.

Example Script

Consider have a look at this simple script below. Before we copy to to other workbook. We need to know that the mechanism, to copy within the same workbook is, very simple.

def context_test():
  ...

def copy_sheet_test():
  all_sheets = model.Sheets
  all_sheets.copyByName(
    "Blewah", "BlewahKopi", len(all_sheets))

# Main
context_test()

You can examine the result in below diagram.

Create New Sheet Instance

We also need to create new sheet instance.

Load Component

In the previous article, we can get the current active worksheet as below.

  desktop = XSCRIPTCONTEXT.getDesktop()
  model = desktop.getCurrentComponent()

We need to adjust the code a little:

def open_calc_test():
  desktop = XSCRIPTCONTEXT.getDesktop()
  model = desktop.loadComponentFromURL(
    "private:factory/scalc", "_blank", 0, ())

URL

If you curious about the URL part. Yeah, me too.

The URL may vary such as:

• private:factory/scalc, or
• file:///home/epsi/Test.ods, or

Now you know.

Result

Assuming you have already open a calc worksheet. You can execute this open_calc_test().

With the result as a new workbook below:

Copy Sheet to Another Workbook

We can combine both method:

• Source Sheet: getCurrentComponent()

• Destination Sheet: New instance. By using this code: loadComponentFromURL("private:factory/scalc")

Example Script

def copy_out_sheet_test():
  desktop = XSCRIPTCONTEXT.getDesktop()
  
  model_src = desktop.getCurrentComponent()  
  first_sheet = model_src.Sheets[0]

  model_dst = desktop.loadComponentFromURL(
    "private:factory/scalc", "_blank", 0, ())

  model_dst.Sheets.importSheet(
    model_src, first_sheet.Name, 0)

How does it works?

The copy code is the importSheet method below:

  model_dst.Sheets.importSheet(
    model_src, first_sheet.Name, 0)

This will copy current sheet to another Workbook.

Handling URL

UNO API in LibreOffice is different in handling files. It use URL instead of plain location.

In this exaple we are going to use the sheet name, as a base of file naming.

The Source URL

With the coe above, we can append additional code, to examine the location of the original sheet.

  print(model_src.getLocation())

And get the result in console.

Save The Result

We are not finished yet, We can finally save the file to somewhere.

  model_dst.storeToURL(
    "file:///home/epsi/Test.ods", ())

Now we have gone further, from just copying to another workbook, to saving the sheet in another file.

Sheet Name

How about using sheet name as filename? We can keep the name of the first sheet.

  model_src   = desktop.getCurrentComponent()  
  first_sheet = model_src.Sheets[0]
  sheet_name  = first_sheet.Name

And use it later, using prepared monthly directory, to place the result.

  base_url = "file:///home/epsi/monthly/"
  full_url = "%s%s.ods" % (base_url, sheet_name)
  print(full_url)

  model_dst.storeToURL(full_url, ())

Do not forget to close the sheet.

  model_dst.close(True)

Copy Out First Sheet

We can gather all as a complete script below:

def copy_out_first_sheet_test():
  desktop = XSCRIPTCONTEXT.getDesktop()
  
  model_src   = desktop.getCurrentComponent()  
  first_sheet = model_src.Sheets[0]
  sheet_name  = first_sheet.Name

  model_dst   = desktop.loadComponentFromURL(
    "private:factory/scalc", "_blank", 0, ())

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

  base_url = "file:///home/epsi/monthly/"
  full_url = "%s%s.ods" % (base_url, sheet_name)
  print(full_url)

  model_dst.storeToURL(full_url, ())
  model_dst.close(True)

Split All Sheets

We already have all the skill required. It is time to gather all the code together within a loop. So we can split all the sheets into different files.

The code is very similar with above code.

def split_sheets():
  base_url = "file:///home/epsi/monthly/"
  desktop = XSCRIPTCONTEXT.getDesktop()

  model_src   = desktop.getCurrentComponent()
  all_sheets = model_src.Sheets
  print("Total of %d sheets" % len(all_sheets))

  for sheet in all_sheets:
    sheet_name  = sheet.Name

    model_dst   = desktop.loadComponentFromURL(
      "private:factory/scalc", "_blank", 0, ())
    first_sheet_name = model_dst.Sheets[0].Name

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

    full_url = "%s%s.ods" % (base_url, sheet_name)
    print(full_url)

    model_dst.storeToURL(full_url, ())
    model_dst.close(True)

REPL Output

Folder Result

What is Next 🤔?

We have done with splitting. How about merging?

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