Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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":

#
#
# 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())

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":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())