HowTo "Hello World" Python macro for Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Webtest
Posts: 1
Joined: Sat Sep 21, 2013 8:23 pm

HowTo "Hello World" Python macro for Calc

Post by 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
Art in Carlisle PA USA
LibreOffice 4.0.2.2 in Mint 15 (also earlier versions back to Mint 8)
Linux Mint Gnomw/Mate running from locked USB drive.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: HowTo? "Hello World" Python macro for Calc???

Post by 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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
cleanman2
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: HowTo "Hello World" Python macro for Calc

Post by 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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HowTo "Hello World" Python macro for Calc

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
cleanman2
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: HowTo "Hello World" Python macro for Calc

Post by 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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HowTo "Hello World" Python macro for Calc

Post by 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: HowTo "Hello World" Python macro for Calc

Post by 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.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HowTo "Hello World" Python macro for Calc

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
cleanman2
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: HowTo "Hello World" Python macro for Calc

Post by cleanman2 »

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

Thanks, Jim
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HowTo "Hello World" Python macro for Calc

Post by Villeroy »

cleanman2 wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Christopher5106
Posts: 1
Joined: Sun Dec 06, 2015 8:58 pm

Re: HowTo "Hello World" Python macro for Calc

Post by 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
Last edited by thomasjk on Mon Dec 07, 2015 6:18 am, edited 1 time in total.
Reason: disabled live link
OpenOffice 4.0 with MacOS 10.4
Post Reply