HowTo "Hello World" Python macro for Calc
HowTo "Hello World" Python macro for Calc
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
===============================
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.
LibreOffice 4.0.2.2 in Mint 15 (also earlier versions back to Mint 8)
Linux Mint Gnomw/Mate running from locked USB drive.
Re: HowTo? "Hello World" Python macro for Calc???
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: HowTo "Hello World" Python macro for Calc
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.
Regards, Jim
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
Re: HowTo "Hello World" Python macro for Calc
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: HowTo "Hello World" Python macro for Calc
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.
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
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.
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?And then there is a chance that the current selection is not A2 when we click on A2 which requires some error handling.
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
Re: HowTo "Hello World" Python macro for Calc
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: HowTo "Hello World" Python macro for Calc
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
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.
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
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: HowTo "Hello World" Python macro for Calc
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: HowTo "Hello World" Python macro for Calc
Cool stuff guys. Now I have some more code to learn from.
Thanks, Jim
Thanks, Jim
Re: HowTo "Hello World" Python macro for Calc
If I ever wanted a simple incrementing counter on a sheet I would simply draw a spin button and bind it to a cell.cleanman2 wrote:Cool stuff guys. Now I have some more code to learn from.
Thanks, Jim
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 1
- Joined: Sun Dec 06, 2015 8:58 pm
Re: HowTo "Hello World" Python macro for Calc
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
Reason: disabled live link
OpenOffice 4.0 with MacOS 10.4