Ask Your Question
1

Python macro for Calc to correlate two columns with string data

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

sdaau 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 close merge delete

1 Answer

Sort by » oldest newest most voted
1

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

sdaau gravatar image

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

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 ...
(more)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 1,252 times

Last updated: Oct 14 '17