HowTo "Hello World" Python macro for Calc

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
I was (a few years ago) a fairly strong programmer in VBA macros for Excel. I also am reasonably proficient in Python. I am presently totally confused about using Python to write Calc macros. I do most of my work in Mint 15 Mate (32 bit) LiveDVD which includes LibreOffice Calc 4.0.2.2 and Python 2.7.4.

Could somebody please post everything needed to execute a macro IN PYTHON to do the following:

Assume any open/active Calc sheet, where cell A2 contains text "TEST" (or any other text) and cell E6 contains the number "1" (or any other integer) (all other cells empty):

On event "double-click" on cell A2:
write text in default format "Hello world" in cell C4
increment cell E6

Name the Macro "hellotest" and allow it to be accessed through:
Tools > Macros > Macro Selector > "hellotest"

This is not a complex task and it should help a beginner to get a feeble start. Just getting this far would explain a lot to me.

Thank you for any and all comments, suggestions, and assistance in this endeavor.

Blessings in abundance, all the best, & ENJOY!
Art in Carlisle, PA USA

[FJCC]

Here is code that changes the cell strings and values as you'd like to see. I don't know how to trigger a macro on a double click on a cell. That would seem to involve a mouse click listener and I don't see how to add one of those to a cell. Perhaps someone else knows how to achieve that or you could explain more about what you want to do.

Code: Select all

import uno
oDoc = XSCRIPTCONTEXT.getDocument()

def hellotest():
  oSheet = oDoc.CurrentController.ActiveSheet
  oCell1 = oSheet.getCellRangeByName("C4")
  oCell1.String = "Hello world"
  oCell2 = oSheet.getCellRangeByName("E6")
  oCell2.Value = oCell2.Value + 1

[cleanman2]

Building on what FJCC posted. I think the following code will do what you want. it uses a single click not a double click on cell A2 though. I'm sure it is not the best code in the world as I am still quite new at this, but it does work on my machine.

Code: Select all

# Python macro to react to a mouse click in a cell

import uno, unohelper
from com.sun.star.awt import XMouseClickHandler
from com.sun.star.awt.MouseButton import LEFT as MB_LEFT

ctx = uno.getComponentContext()
doc = XSCRIPTCONTEXT.getDocument()


class Handler(unohelper.Base, XMouseClickHandler, object):
    """ Handles mouse click on the document. """
    def __init__(self, ctx, doc):
        self.ctx = ctx
        self.doc = doc
        self._register()
        
    def _register(self):
        self.doc.getCurrentController().addMouseClickHandler(self)
        self.ctx = uno.getComponentContext()  
      
    def unregister(self):
        """ Remove myself from broadcaster. """
        self.doc.getCurrentController().removeMouseClickHandler(self)

    def disposing(self, ev):
        global handler
        handler = None

    def mousePressed(self, ev):
        return False
    
    def mouseReleased(self, ev):
        if ev.Buttons == MB_LEFT:
            selected = self.doc.getCurrentSelection()
            addr = selected.getRangeAddress()
            if addr.EndColumn == 0 and addr.EndRow == 1:  # cell A2
                oSheet = doc.CurrentController.ActiveSheet
                oCell1 = oSheet.getCellRangeByName("C4")
                oCell1.String = "Hello world"
                oCell2 = oSheet.getCellRangeByName("E6")
                oCell2.Value = oCell2.Value + 1   
        return False 
handler = None

def Start(*args):
   """ starting to listen about mouse click on the document. """
   ctx = XSCRIPTCONTEXT.getComponentContext()
   doc = XSCRIPTCONTEXT.getDocument()
   osheets = doc.getSheets()
   osheet = osheets.getByName('Sheet1')
   ocell = osheet.getCellRangeByName('C4')
   global handler
   handler = Handler(ctx, doc)  
   
def stop(*args):
   """ remove the handler from the document. """
   global handler
   if handler:
      handler.unregister()
      handler = None   

Regards, Jim

[Villeroy]

Wow. Thank you, Jim.
I learned that using a global var for the handler makes it possible to keep a reference to it so we can detach it from our controller. BUT: spreadsheets may have more controllers than the current one and users may decide at runtime that they want to have another window for that spreadsheet. We need to write another listener to catch the "view created" event.
And then there is a chance that the current selection is not A2 when we click on A2 which requires some error handling. There is an EnhancedMouseEvent which passes the object under the mouse cursor. May be this event would eliminate any uncertainties about the current selection on the current controller.
Finally, the double-clicked A2 would set to edit mode which may be the reason why you implemented a single-click handler.

IMHO, this is a perfect example why writing interactive macros in a clean and comprehensive manner is close to impossible.

A hyperlink with an URL pointing to the macro (protocol vnd.sun.star.script:) is by far easier to implement. A single click on the cell text would call a simple macro without the need to register any listener objects.

[cleanman2]

Villory, thanks for the kind words.

I wrote that macro for a couple of reasons. I've received alot of help on this forum but my programming skills arn't good enough to give much help in return. This seemed like I could learn something and give a little help in return. I have noticed that what I have learned about macro programming has come from finding examples and then understanding them by working back to the documentation. Maybe this example will help someone.

And then there is a chance that the current selection is not A2 when we click on A2 which requires some error handling.

I don't quite understand this sentence. It was my thought that by clicking on A2 I made it the current selection. In fact if you click on any other cell nothing happens. Could you explain?

As to double click vs single click and the edit mode, I really didn't think that through. I just figured as a learning experience single click would be easier to implement. I'm afraid your other points, while interesting, are a little above my skill level as I am just beginning to learn macro programming with Python.

Regards, Jim

[Villeroy]

Select multiple sheets and your selection includes many cells. It is a com.sun.star.sheet.SheetCellRanges collection.
You may also select some range(s) on the same sheet and then Ctrl+Click on A2.

A click on A2 having
=HYPERLINK("vnd.sun.star.script:pyCalc|MyModule.py$MyMacro?language=Python&location=user")
simply calls "MyMacro" in file <user_profile>/Script/python/pyCalc/MyModule.py

[Charlie Young]

I had been playing with this, but Jim beat me with some better code than I had.

We can, it seems, modify it to require double-click as follows

Code: Select all

    def mouseReleased(self, ev):
        if ev.Buttons == MB_LEFT and ev.ClickCount == 2:
            selected = self.doc.getCurrentSelection()
            addr = selected.getRangeAddress()
            if addr.EndColumn == 0 and addr.EndRow == 1:  # cell A2
                oSheet = doc.CurrentController.ActiveSheet
                oCell1 = oSheet.getCellRangeByName("C4")
                oCell1.String = "Hello world"
                oCell2 = oSheet.getCellRangeByName("E6")
                oCell2.Value = oCell2.Value + 1  
                return True
        return False

The return True prevents edit mode from being entered, since I don't think we want that. My original attempt along these lines caused a crash.

[Villeroy]

Code: Select all

    def mouseReleased(self, ev):
        if ev.Buttons == MB_LEFT and ev.ClickCount == 2 and ev.Modifiers==0:
            selected = ev.Model.getCurrentSelection()
            if selected.supportsService("com.sun.star.sheet.SheetCell"):
                addr = selected.getCellAddress()
                if addr.Column == 0 and addr.Row == 1:  # cell A2
                    oSheet = selected.getSpreadsheet()
                    oCell1 = oSheet.getCellRangeByName("C4")
                    oCell1.String = "Hello world"
                    oCell2 = oSheet.getCellRangeByName("E6")
                    oCell2.Value = oCell2.Value + 1 
                    return True
        return False

We can check that no modifier is pressed (multi-selection) and that the selection is a single cell. Our class does not even need a reference to the document. The calling view (ev.Source) provides everything we need.

[cleanman2]

Cool stuff guys. Now I have some more code to learn from.

Thanks, Jim

[Villeroy]

Cool stuff guys. Now I have some more code to learn from.

Thanks, Jim

If I ever wanted a simple incrementing counter on a sheet I would simply draw a spin button and bind it to a cell.

[Christopher5106]

A Tutorial here http://christopher5106.github.io/office/2015/12/06/openoffice-libreoffice-automate-your-office-tasks-with-python-macros.html http://christopher5106.github.io/office/2015/12/06/openoffice-libreoffice-automate-your-office-tasks-with-python-macros.html