Python macro for Calc to correlate two columns with string data

English
, , , ,
#1

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?

connect cells with lines
#2

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

#
#
# NB: https://forum.openoffice.org/en/forum/viewtopic.php?f=10&t=82218
# "OpenOffice (but not LibreOffice) can trigger a Basic routine by means of a hyperlink. The routine gets the full URL passed as argument."
# https://bugs.documentfoundation.org/show_bug.cgi?id=70959 Works again with: Version: 5.2.0.0.alpha0+
from com.sun.star.beans import PropertyValue
from com.sun.star.sheet.CellFlags import STRING
import string
import traceback

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

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

  # https://wiki.openoffice.org/wiki/Python/Transfer_from_Basic_to_Python
  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 (com.sun.star.table.CellRangeAddress){ 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( "com.sun.star.frame.Desktop",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)
  # https://stackoverflow.com/questions/33912147/search-strings-and-line-breaks-with-pyuno
  search = active_sheet.createSearchDescriptor() # exception in calc
  # [https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=63933 [Solved] Need help understanding some Python macros (View topic) • Apache OpenOffice Community Forum]
  dispatchHelper = ctx.ServiceManager.createInstanceWithContext( 'com.sun.star.frame.DispatchHelper', ctx )
  frame = desktop.getCurrentFrame()
  #search.SearchRegularExpression = True
  #for x in range(current_selection_range.StartRow, current_selection_range.StartRow-current_selection_range.EndRow)
  irow=current_selection_range.StartRow;
  while irow <= current_selection_range.EndRow:
    icol=current_selection_range.StartColumn;
    while icol <= current_selection_range.EndColumn:
      origCell = active_sheet.getCellByPosition(icol, irow)
      if not origCell.String: # do not process if empty string:
        continue
      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 com.sun.star.table.XCellRange
        #~ try:
        fSel_address = fSel.getCellAddress() # only exists if it is a single cell, but it might be a range (getRangeAddress exists regardless); still, this should be a single cell only!
        #~ except Exception as er:
          #~ oDisp = str(traceback.format_exc()) + '\n' + str(er) + "\n" + str(fSel.getRangeAddress())
          #~ printMessageBox(oDoc_cc, smgr, oDisp)
        fCell = active_sheet.getCellByPosition(fSel_address.Column, fSel_address.Row)
        areObjectsEqual = (fCell == origCell)
        areLocationsEqual = ((origCell.getCellAddress().Column == fSel_address.Column) and (origCell.getCellAddress().Column == fSel_address.Column))
        trep = "Orig (%d,%d); fSel[%d] (%d,%d); obj ==? %s; .Row/.Column ==? %s; %s"%(origCell.getCellAddress().Column, origCell.getCellAddress().Row, sel_index, fSel_address.Column, fSel_address.Row, areObjectsEqual, areLocationsEqual, fCell.AbsoluteName)
        # due to differing pointer addresses, fCell == origCell is never true, even if it refers to the same cell
        # just compare row&column instead
        #~ rep += trep + str(fCell == origCell) + " " + str(fCell) + " " + fCell.String + "\n" # fSel.String also works
        rep += trep + " " + fCell.String + "\n"
        if areLocationsEqual:
          continue # skip rest/do not process if we're at same location as origCell
        # so here, locations are different; add a link to origCell at fSel_address.Column+1, fSel_address.Row
        # note: .AbsoluteName is the address in format $Sheet1.$E$6
        # "# FormulaLocal is a true property (not a pseudo-property) of a single cell" https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=16493&p=76659
        linkedCell = active_sheet.getCellByPosition(fSel_address.Column+1, fSel_address.Row)
        linkedCell.FormulaLocal = "=" + origCell.AbsoluteName
        # "show dependents"
        oProp = PropertyValue()
        oProp.Name = 'ToPoint'
        oProp.Value = origCell.AbsoluteName
        properties = (oProp,)
        dispatchHelper.executeDispatch( frame, '.uno:GoToCell', '', 0, properties )
        dispatchHelper.executeDispatch( frame, '.uno:ShowDependents', '', 0, () )
      #~ printMessageBox(oDoc_cc, smgr, rep) # + str(origCell) + " " + str(origCell.String))
      icol += 1
    irow += 1
  #printMessageBox(oDoc_cc, smgr, active_sheet)

# lists the scripts, that shall be visible inside OOo. Can be omited, if
# all functions shall be visible, however here printMessageBox shall be surpressed
g_exportedScripts = correlateCells,

# BASIC recording:
# dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
# dim args5(0) as new com.sun.star.beans.PropertyValue
# args5(0).Name = "ToPoint"
# args5(0).Value = "$I$3"
# dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
# dispatcher.executeDispatch(document, ".uno:ShowDependents", "", 0, Array())
#3

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.