LibreOffice - Macro - Transform Field

   
   
 macro  
 automation  
Python Automation Article Series Test

LibreOffice

Python - Excel

Table of Content
Where to Discuss?

Local Group

Preface

Goal: Transform plain row based data into cute form view.

  • Using LibreOffice UNO Macro,

  • Using Faktur Pajak (Indonesian Tax) as Example,

  • This article is the last part of two articles.

5: The Challenge

How to Automate?

Back to the Challenge This picture below is common in any business.

Worksheet: Photo: Compact

But how to speed up your workflow? Doing manually is extremely daunting task.

You need to spend your time to important task, rather than compiling data, right?

Of course you can do it with simpler script, so you can have the document compiled the worksheet, automatically for you.

All you need is just export the sheet to PDF. And you got the summary as below:

PDF: The Whole Year

Provided Example Data

You can download the artefact for simple range copying here below. Each sheet, I provide two languages. You should choose only one over another.

Worksheet Tab: Template

Then we can continue combine with sheet with long columns, along with dummy data.

Worksheet Tab: Data

With these template and data, wen can finally get our final result, the completely filled form.

Worksheet Tab: Result

Fields in Detail

Dual Language

Since I my original tutorial based on goverment tax field in Indonesia, I have to create the english version, so you understand, what the meaning of each example.

Consider have a look at all fields in detail. I provide two language for each: English and Indonesia.

  • Fields: Screenshot One

Worksheet: Source: Page One

  • Fields: Screenshot Two

Worksheet: Source: Page Two

  • Fields: Screenshot Three

Worksheet: Source: Page Three

Excuse my english, also apologize for my lack knowledge of tax terminology.

Prepare: English

Empty Template and Data Source

it is up to your choice whether you sue English or Indonesia. I provide both language.

  • Empty Template

Worksheet: Empty Template: English

  • Source Data

Worksheet: Source Data: English

After each exection of each python code, you will get additional Target sheet:

Prepare: Indonesia

Empty Template and Data Source

  • Empty Template

Worksheet: Empty Template: Indonesia

  • Source Data

Worksheet: Source Data: Indonesia

6: Simple Transformation Class

Just make sure you place each field correctly.

We can begin translating each fields, to predefined location.

Skeleton

class TransformRow01:
  def __init__(self, eName, sName, tName):
  def getFieldsMapping(self):
  def copySheet(self):
  def copyField(self, pair):
  def run(self):

def main():

Class Initialization

We have three set of sheet names.

  • Empty Template,
  • Source sheet, and
  • Target sheet (Result)

Worksheet Tab: Source

As shown in below code:

Python Macro: Vim: Class Initialization

class TransformRow01:
  def __init__(self, eName, sName, tName):
    desktop     = XSCRIPTCONTEXT.getDesktop()
    model       = desktop.getCurrentComponent()
    self.sheets = model.Sheets

    # save initial parameter
    self.eName = eName
    self.sName = sName
    self.tName = tName

Main: Program Entry Point

Here comes all the parameter argument. This function responsible for class instantiation, is shown in below code:

Python Macro: Vim: Main: Program Entry Point

def main():
  sample = TransformRow01('Empty-en', 'Source-en', 'Target-en')
  sample.run()

You can swith to other language, en for english, and id for Indonesia. just by modify the class initialization parameter, for example:

def main():
  sample = TransformRow01('Empty-id', 'Source-id', 'Target-id')
  sample.run()

Run: Execute

The run contain no parameter.

Python Macro: Vim: Main: Run: Execute

  def run(self):
    self.copySheet()
    fields = self.getFieldsMapping()

    for field, pair in fields.items():
      self.copyField(pair)

Copy Sheet

In order to work in other sheet, we need to duplicate the Empty sheet to a new sheet. This new sheet would be our working sheet.

Python Macro: Copy Sheet

  def copySheet(self):
    sheets = self.sheets
    sheets.copyByName(
      self.eName, self.tName, len(sheets))

    self.eSheet = sheets.getByName(self.eName)
    self.sSheet = sheets.getByName(self.sName)
    self.tSheet = sheets.getByName(self.tName)

After the new sheet created, we can freely make a reference to each sheet, to be used later.

This include these three sheet:

  • Empty Template.
  • Data Source: containing row based data.
  • Target Result: working place to create form based result.

Mapping Definition

In the heart, it is just field position transformation

Again, just put source table, and form view, side by side. And manually defined the field one by one, into a script.

Worksheet: Source to Target Translation

So you can have this one to one relationship as below:

Python Macro: Field Mapping

For example in English:

  def getFieldsMapping(self):
    # declare all field coordinate
    return {
      'id'    : { 'source': 'A7', 'target': 'B5' },
      'name'  : { 'source': 'B7', 'target': 'C5' },
      'taxpayer_id'     : { 'source': 'C7', 'target': 'F5' },
      'tax_inv_id'      : { 'source': 'D7', 'target': 'I5' },
      'tax_inv_date'    : { 'source': 'E7', 'target': 'D6' },
      'month_period'    : { 'source': 'F7', 'target': 'G7' },
      'year'            : { 'source': 'G7', 'target': 'G8' },
      'tax_inv_status'  : { 'source': 'H7', 'target': 'I10' },
      'price'  : { 'source': 'I7', 'target': 'J7' },
      'vat'    : { 'source': 'J7', 'target': 'J8' },
      'vat_lux': { 'source': 'K7', 'target': 'J9' },
      'approval_status' : { 'source': 'L7', 'target': 'I11' },
      'approval_date'   : { 'source': 'M7', 'target': 'D7' },
      'description'     : { 'source': 'N7', 'target': 'I12' },
      'signatory_user'  : { 'source': 'O7', 'target': 'D8' },
      'reference'       : { 'source': 'P7', 'target': 'I6' },
      'record_user'     : { 'source': 'Q7', 'target': 'D10' },
      'record_date'     : { 'source': 'R7', 'target': 'D9' },
      'update_user'     : { 'source': 'S7', 'target': 'D12' },
      'update_date'     : { 'source': 'T7', 'target': 'D11' },
    }

You can ignore the name completly. I just put the field name as annotation.

Copy Field

Using Mapping

For first row, it is as simply as copy value from source to target.

The process of field translating field written in code below:

Python Macro: Copy Field

  def copyField(self, pair):
    addrSource = pair['source']
    addrTarget = pair['target']

    sourceCell = self.sSheet.getCellRangeByName(addrSource)
    targetCell = self.tSheet.getCellRangeByName(addrTarget)

    print("Cell %s: Type = %s, String = %s" \
      % (addrSource, sourceCell.Type.value, sourceCell.String))

    match sourceCell.Type.value:
      case "VALUE" : targetCell.Value  = sourceCell.Value
      case "TEXT"  : targetCell.String = sourceCell.String

I also add print statement, so you can happily debug in APSO console (python terminal).

Result

The result in APSO console would be similar as below:

Python Macro: APSO Console

Make sure there target-en worksheet exist. And the result in target-en sheet would be as below:

Worksheet: Target Data: English

Simple right?

We have successfully move one row into its form representative.

7: Handling Multiple Data

Offset Position?

The next step would focus on multiple row data. In order to do this we need to handle offset position, to get the right coordinate.

Since in this example,the height of the form is nine. For every one index increment, we will have ten offset increment.

Skeleton

class TransformRow02:
  def __init__(self, eName, sName, tName):
  def getFieldsMapping(self):
  def copySheet(self):
  def copyField(self, pair, rowHeight, index):
  def run(self, rowHeight, rowCount):

def main():

Class Initialization

Similar as previous class.

Copy Sheet

Similar as previous class.

Mapping Definition

Similar as previous class.

Main: Program Entry Point

This function responsible for class initialization.

Python Macro: Vim: Main: Program Entry Point

def main():
  sample = TransformRow02('Empty-en', 'Source-en', 'Target-en')
  sample.run(9, 3)

Run: Execute

We pour our loop logic here.

There are two parameter argument:

  • rowHeight : Height of the form
  • rowCount : Number of data in source sheet to be processed.

Python Macro: Vim: Main: Run: Execute

  def run(self, rowHeight, rowCount):
    self.copySheet()
    fields = self.getFieldsMapping()

    masterIndices = range(1, rowCount + 1)
    for index in masterIndices:
      for field, pair in fields.items():
        self.copyField(pair, rowHeight, index)

Calculate Cell Address

Mapping Multiple Data with Help of Offset.

There are to row positions to be considered:

  • row in source: using index
  • field in target: using offset

The relationship is as below:

    Offset = (rowHeight+1) * (index-1)

First we need to save the position of base position

    addrSource = pair['source']
    addrTarget = pair['target']

    sCellBase = self.sSheet.getCellRangeByName(addrSource)
    tCellBase = self.tSheet.getCellRangeByName(addrTarget)
    scAddress = sCellBase.CellAddress
    tcAddress = tCellBase.CellAddress

Then we can continue to get ourdata position based on index:

    Offset = (rowHeight+1) * (index-1)

    sCell  = self.sSheet.getCellByPosition(\
      scAddress.Column, scAddress.Row + (index-1))
    tCell  = self.tSheet.getCellByPosition(\
      tcAddress.Column, tcAddress.Row + Offset)
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-python" data-lang="python">And <span style="color:#66d9ef">finally</span> do the assignment<span style="color:#f92672">.</span></code></pre></div>
    match sCell.Type.value:
      case "VALUE" : tCell.Value  = sCell.Value
      case "TEXT"  : tCell.String = sCell.String

Copy Field Method

The process of field translating field can be written in code below:

Python Macro: Copy Field

  def copyField(self, pair, rowHeight, index):
    addrSource = pair['source']
    addrTarget = pair['target']

    sCellBase = self.sSheet.getCellRangeByName(addrSource)
    tCellBase = self.tSheet.getCellRangeByName(addrTarget)
    scAddress = sCellBase.CellAddress
    tcAddress = tCellBase.CellAddress

    Offset = (rowHeight+1) * (index-1)

    sCell  = self.sSheet.getCellByPosition(\
      scAddress.Column, scAddress.Row + (index-1))
    tCell  = self.tSheet.getCellByPosition(\
      tcAddress.Column, tcAddress.Row + Offset)

    match sCell.Type.value:
      case "VALUE" : tCell.Value  = sCell.Value
      case "TEXT"  : tCell.String = sCell.String

Result

We have succesfully migrate all the three rows. The result in our working sheet would be similar as below:

Worksheet: Target Data: English

Take back you smile!

But hey, where is the formattingšŸ¤”?

8: Using Both Class

Make worksheet fun again.

Come out and play. This is our final form in this article.

Skeleton

Dual Class

Just puth both class here in one file.

Python Macro: Dual Class

class CopyRange03:
  def __init__(self, sName, tName):
  ...

class TransformRow03:
  def __init__(self, eName, sName, tName):
  ...

def main():

Class Initialization

Stay intact. No changes.

Copy Sheet

Stay intact. No changes.

Mapping Definition

Stay intact. No changes.

Copy Field

Stay intact. No changes.

Main: Program Entry Point

Here comes all the parameter argument.

Python Macro: Vim: Main: Program Entry Point

def main():
  sample = TransformRow03(\
    'Empty-en', 'Source-en', 'Target-en')
  sample.run('B4:K13', 'A1:L33', 3)

Now it is already containing properties of the copied range.

Run: Execute

This run method responsible for CopyRange class instantiation. Consider name the object as template.

    template  = CopyRange03(self.eName, self.tName)
    rowHeight = self.calculateHeight(stringRange)

Then we can duplicate the form for each row using loop.

    masterIndices = range(1, rowCount + 1)
    for index in masterIndices:
      template.run(stringRange, index)

Python Macro: Vim: Main: Run: Execute

  def run(self, stringRange, printArea, rowCount):
    self.copySheet()
    fields = self.getFieldsMapping()

    template  = CopyRange03(self.eName, self.tName)
    rowHeight = self.calculateHeight(stringRange)

    masterIndices = range(1, rowCount + 1)
    for index in masterIndices:
      template.run(stringRange, index)
      for field, pair in fields.items():
        self.copyField(pair, rowHeight, index)

    template.setPrintArea(printArea)

Calculate Height

Form height calculation require a few lines.

    template  = CopyRange03(self.eName, self.tName)
    rowHeight = self.calculateHeight(stringRange)

So I decide to put the calculation in its own method:

Python Macro: Vim: Main: Height Calculation

  def calculateHeight(self, stringRange):
    eCellRange = self.eSheet.getCellRangeByName(stringRange)
    eRangeAddr = eCellRange.RangeAddress

    rStart  = eRangeAddr.StartRow
    rEnd    = eRangeAddr.EndRow

    return rEnd - rStart

Result

Party Time

Worksheet: Target Data: English

Get yourself more productive with script automation. I’m not thinking of salary raise, but rather more things to do in limited time. So I can think of in strategic area, instead of boring, cumbersome, tedious task.

Using Other Language

Just like previous example, you can set the language in main(), for example Indonesia or English.

The result will be reflected in sheet tab name.

Worksheet Tab: Target

Working with worksheet is fun. I feels like, my accounting division looks even cooler now.

Conclusion

I think that’s all. Maybe another article, another time.

We are done. That is all. Thank you for reading.

What do you think?