HowTo "Hello World" Python macro for Calc

Keyboard macros or custom scripts

HowTo "Hello World" Python macro for Calc

Postby Webtest » Sun Sep 22, 2013 2:15 am

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.
Webtest
 
Posts: 1
Joined: Sat Sep 21, 2013 8:23 pm

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

Postby FJCC » Sun Sep 22, 2013 4:21 am

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   Expand viewCollapse view
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
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 5994
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: HowTo "Hello World" Python macro for Calc

Postby cleanman2 » Wed Sep 25, 2013 4:31 am

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   Expand viewCollapse view
# 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
cleanman2
 
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: HowTo "Hello World" Python macro for Calc

Postby Villeroy » Wed Sep 25, 2013 12:26 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24257
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HowTo "Hello World" Python macro for Calc

Postby cleanman2 » Wed Sep 25, 2013 5:33 pm

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
cleanman2
 
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: HowTo "Hello World" Python macro for Calc

Postby Villeroy » Wed Sep 25, 2013 6:29 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24257
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HowTo "Hello World" Python macro for Calc

Postby Charlie Young » Wed Sep 25, 2013 7:20 pm

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   Expand viewCollapse view

    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
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: HowTo "Hello World" Python macro for Calc

Postby Villeroy » Wed Sep 25, 2013 8:46 pm

Code: Select all   Expand viewCollapse view
    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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24257
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HowTo "Hello World" Python macro for Calc

Postby cleanman2 » Wed Sep 25, 2013 11:44 pm

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

Thanks, Jim
cleanman2
 
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: HowTo "Hello World" Python macro for Calc

Postby Villeroy » Thu Sep 26, 2013 10:37 am

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24257
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HowTo "Hello World" Python macro for Calc

Postby Christopher5106 » Sun Dec 06, 2015 9:01 pm

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
Christopher5106
 
Posts: 1
Joined: Sun Dec 06, 2015 8:58 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 6 guests