Ask Your Question

Python macro for Calc to correlate two columns with string data [closed]

asked 2017-10-14 23:16:13 +0100

sdaau gravatar image

updated 2020-10-17 18:27:57 +0100

Alex Kemp gravatar image

Just wanted to post this, since it took me forever to find the right commands; but to put it formally as a question: Assume in calc I have two columns of string data - one of them in column B, the other in say E, the space between them is assumed to be empty.

I can manually make a visual correlation of the values there as follows:

  • Select "original" cell in column E, Ctrl-C to copy
  • Find on which row is the same value in column B
  • Select the (empty) cell right next to the value on that row (so on found_row, column C)
  • Edit / Paste special ... , Options: Link
  • Select again "original" cell in column E; then
  • Tools / Detective / Trace Dependents

Obviously, this gets to be very long and tedious process as soon as you have more than four values to correlate.

Are there examples to automate this process in a, say, Python macro script?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-17 18:28:16.803523

1 Answer

Sort by » oldest newest most voted

answered 2017-10-14 23:19:22 +0100

sdaau gravatar image

updated 2017-10-14 23:21:00 +0100

Here is an example, you can save this as /usr/lib/libreoffice/share/Scripts/python/ on Ubuntu, so it shows in Tools / Macros / Organize Macros / Python..., under "LibreOffice Macros" (tested on LibreOffice Version:, Ubuntu 14.04):

# NB:
# "OpenOffice (but not LibreOffice) can trigger a Basic routine by means of a hyperlink. The routine gets the full URL passed as argument."
# Works again with: Version:
from import PropertyValue
from import STRING
import string
import traceback

def printMessageBox( oDoc_cc, oSM, inobj ):
  # oSM = uno.getComponentContext().getServiceManager() # name 'uno' is not defined
  oToolkit = oSM.createInstance( "" )
  oParentWin = oDoc_cc.getFrame().getContainerWindow()
  oMsgBox = oToolkit.createMessageBox( oParentWin, "infobox", 1, "Mensaje", str(inobj) )

def correlateCells( ):
  """Iterate through a selection of cells; find the original (selected) string cell content elsewhere in doc (ignoring self); if found, paste as link next to the found cell (at [row,column+1]; then can observe with Tools/Detective/Trace Dependents..."""

  # The context variable is of type XScriptContext and is available to
  # all BeanShell scripts executed by the Script Framework
  oDoc = XSCRIPTCONTEXT.getDocument() # xModel

  ctx = XSCRIPTCONTEXT.getComponentContext()
  smgr = ctx.getServiceManager()

  #the writer controller impl supports the css.view.XSelectionSupplier interface
  oDoc_cc = oDoc.getCurrentController() # xSelectionSupplier

  #see section 7.5.1 of developers' guide
  current_selection = oDoc_cc.getSelection()  # xIndexAccess
  current_selection_range = current_selection.queryContentCells(STRING).getRangeAddresses()[0] #getRangeAddress()
  # note, if a column selected, then current_selection.getRangeAddress() is ({ Sheet = (short)0x0, StartColumn = (long)0x8, StartRow = (long)0x0, EndColumn = (long)0x8, EndRow = (long)0xfffff } - selects 0xfffff rows! And in that case, libreoffice freezes trying to iterate through them all! No need for that...
  # note: queryVisibleCells still has EndRow = (long)0xfffff, use queryContentCells(STRING) for getting only the actual populated cells
  #~ printMessageBox(oDoc_cc, smgr, current_selection_range)
  #~ if ((current_selection_range.StartRow == 0) and (current_selection_range.EndRow == 0)):
  #~ curcomp = ctx.ServiceManager.createInstanceWithContext( "",ctx).getCurrentComponent() #model1
  desktop = XSCRIPTCONTEXT.getDesktop()
  curcomp = desktop.getCurrentComponent()
  active_sheet = curcomp.CurrentController.ActiveSheet  # or: document.Sheets.getByIndex(0)
  # NB: links have content: =$Sheet1.$I$3 (source at I3)
  search = active_sheet.createSearchDescriptor() # exception in calc
  # [ [Solved] Need help understanding some Python macros (View topic) • Apache OpenOffice Community Forum]
  dispatchHelper = ctx.ServiceManager.createInstanceWithContext( '', ctx )
  frame = desktop.getCurrentFrame()
  #search.SearchRegularExpression = True
  #for x in range(current_selection_range.StartRow, current_selection_range.StartRow-current_selection_range.EndRow)
  while irow <= current_selection_range.EndRow:
    while icol <= current_selection_range.EndColumn:
      origCell = active_sheet.getCellByPosition(icol, irow)
      if not origCell.String: # do not process if empty string:
      search.SearchString = origCell.String
      selsFound = active_sheet.findAll(search)
      rep = ""
      for sel_index in range(0, selsFound.getCount()):
        fSel = selsFound.getByIndex(sel_index) # this is
        #~ try:
        fSel_address = fSel.getCellAddress() # only ...
edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-10-14 23:16:13 +0100

Seen: 1,614 times

Last updated: Oct 14 '17