Some code structures for implementing Python Macros
in LibreOffice and OpenOffice

1...Getting object references to documents and sheets
2...Reading and writing to cells
3...Clear cell contents
4...Hide a column
5...Delete columns(s)
6...Set horizontal alignment in a cell
7...Format a cell
8...Insert Date into a cell
9...Format font
10..Copy background colour of a cell to another cell
11..To change cell font colour
12..To set Borders
13..Modify Status Indicator
14..Set Hyperlink in cell
15..To sort a range of cells
16..Document directory
17..Using threads to see new values in cells while macro is running
18..Load dialogs embedded in document
19..Getting Python scripts working in LibreOffice and Ubuntu
20..Display a message box

======================================================================================

1...Getting object references to documents and sheets
	# This is pretty much standard in all scripts
	oDoc = XSCRIPTCONTEXT.getDocument()
	oSheets = oDoc.getSheets()
	oSheet = oSheets.getByName("Sheet1")	# get sheet named "Sheet1"
	#or 
	oSheet = oDoc.getSheets().getByIndex( 0 )		# get first sheet

2...Cell objects, and reading and writing to cells
	# Some basic code structures
	oCellRangeByName = oSheet.getCellRangeByName("A15")
	oCellRangeByName.setValue(15.64)
	oCellRangeByPos = oSheet.getCellByPosition(0,4)		# (col, row)
	oCellRangeByPos.String = 'My String'
	oSheet.getCellByPosition(8,rowNum).Value = 28.2
	oSheet.getCellByPosition(10,9).Formula = "=J10 + 1"

3...Clear cell contents
	oSheet.getCellRangeByName('A1:B17').clearContents(5)	# clears Values and Strings
	oSheet.getCellRangeByName('A1:B17').clearContents(7)	# clears Values, Datetimes and Strings
	#1 = VALUE, 2 = DATETIME, 4 = STRING, 8 = ANNOTATE, 16 = FORMULA, 32 = Formats
	
	oSheet.getCellRangeByName('A1:B' + str(17)).clearContents(7)

4...Hide a column
	oSheet.getColumns().getByName( "C" ).IsVisible = False 

5...Delete columns(s)
	oSheet.getColumns().removeByIndex(startCol, number of columns)

6...Set horizontal alignment in a cell
	class cCellHoriJust():
		from com.sun.star.table.CellHoriJustify import (STANDARD, LEFT, CENTER, RIGHT, BLOCK, REPEAT)	
		
	def test1():	
		oDoc = XSCRIPTCONTEXT.getDocument()
		oSheets = oDoc.getSheets()
		oSheet = oSheets.getByName("Sheet1")
		oSheet.getCellByPosition(0,0).HoriJustify = cCellHoriJust.LEFT

7...Format a cell
	oDoc = XSCRIPTCONTEXT.getDocument()
	oSheets = oDoc.getSheets()
	oSheet = oSheets.getByName("Sheet1")
	
	ooLocale = oDoc.getPropertyValue("CharLocale")
	ooLocale.Language = "en"
	ooLocale.Country = "gb"
	
	NumberFormats = oDoc.NumberFormats
	NumberFormatStringGBP = "£##0.000##"
	# or NumberFormatStringGBP = '[$GBP] 0.000##'
	
	NumberFormatIdGBP = NumberFormats.queryKey(NumberFormatStringGBP, ooLocale, True)
	
	if NumberFormatIdGBP == -1:
		NumberFormatIdGBP = NumberFormats.addNew(NumberFormatStringGBP, ooLocale)
	
	oSheet.getCellByPosition(0,0).NumberFormat = NumberFormatIdGBP
	
8...Insert Date into a cell
	oSheet.getCellByPosition(1,1).FormulaLocal = '%s-%s-%s' %( 'Jul', 20, 2013 )
	or
	oSheet.getCellByPosition(1,1).FormulaLocal = '%s-%s-%s' %( 20, 'Jul', 2013 )

9...Format font
	from com.sun.star.awt.FontSlant import ITALIC
	from com.sun.star.awt.FontWeight import BOLD, NORMAL
	
	oCell.setPropertyValue("CharPosture", ITALIC)
	oCell.setPropertyValue("CharWeight", BOLD)

10..Copy background colour of a cell to another cell
	oCell = oSheet.getCellByPosition(0,0)
	existing_colour = oCell.getPropertyValue("CellBackColor")

	oCella = oSheet.getCellByPosition(1,0)
	oCella.setPropertyValue("CellBackColor", existing_colour)

11..To change cell font colour
	oCellb = oSheet.getCellByPosition(1,1)
	oCellb.setPropertyValue("CharColor", existing_colour)
	oCell.setPropertyValue("CharColor", -1)	# set to black
	oCell.setPropertyValue("CharColor", 1000)	# set to blue
	oCell.setPropertyValue("CharColor", 50000)	# set to green
	oCell.setPropertyValue("CharColor", -50000)	# set to red

12..To set Borders
	oCell.setPropertyValue("TopBorder", (16711680,0,36,0,0,36))	# thin red top border
	oCell.setPropertyValue("BottomBorder", (0,0,36,0,0,36))		# thin black bottom border
	
	or
	
	oCell = oSheet.getCellByPosition(myCol,myRow)
	Border = oCell.TopBorder
	Border.OuterLineWidth = 36
	Border.Color = 0
	oCell.TopBorder = Border
	
13..Modify Status Indicator
	Controller = oDoc.getCurrentController()
	DocFrame = Controller.getFrame()
	oStatusIndicator = DocFrame.createStatusIndicator() 
	oStatusIndicator.start( "", 10 ) 		# values allowed between 0 and 10
	oStatusIndicator.setText( "Processing started")
	oStatusIndicator.Value(1)
	oStatusIndicator.end()					# restore status bar

14..Set Hyperlink in cell

	url = 'http://www.bbc.co.uk/news/business/'
	str = oCell.getString()

	fld = oDoc.createInstance("com.sun.star.text.TextField.URL")
	fld.setPropertyValue("URL", url);
	fld.setPropertyValue("Representation", str);

	# We are replacing the contents of the cell with exactly the same text,
	# but storing it in the URL textfield with the same display value.
	oCell.setString("")

	# You can only insert textfields via a text cursor, so we need to create one
	txt = oCell.getText()
	txt.insertTextContent(txt.createTextCursor(), fld, False)

15..To sort a range of cells

	import uno

	def mysort():
		oDoc = XSCRIPTCONTEXT.getDocument()
		oSheets = oDoc.getSheets()
		oSheet = oSheets.getByName("Sheet1")

		rSortArea = oSheet.getCellRangeByName("D2:F5")
		sortField = uno.createUnoStruct('com.sun.star.table.TableSortField')
		sortField.IsAscending = True
		sortField.Field = 0
		sortDesc = rSortArea.createSortDescriptor()

		for PropVal in sortDesc:
			if PropVal.Name == 'SortFields':
				PropVal.Value = uno.Any('[]com.sun.star.table.TableSortField',(sortField,))
			elif PropVal.Name == 'ContainsHeader':
				PropVal.Value = uno.Any('boolean', False)
		rSortArea.sort(sortDesc)
	
16..Document directory
	import uno
	import os

	def PyTest1():
		oDoc = XSCRIPTCONTEXT.getDocument()
		myurl = oDoc.URL
		syspath = uno.fileUrlToSystemPath( myurl )
		directory = os.path.dirname( syspath )
	
17..Using threads to see new values in cells while macro is running
	# If cells are updated while running a macro, the updated values in the cells
	#  are normally not visible until the macro has finished
	# This technique displays new values in cells as soon as they are updated
	
	import uno 
	from time import sleep
	from threading import Thread


	def test():
		desktop = XSCRIPTCONTEXT.getDesktop()
		t = Thread(target = test_worker, args = (desktop,))
		t.start()

	def test_worker(desktop):
		oDoc = desktop.getCurrentComponent()
		oSheets = oDoc.getSheets()
		oSheet = oSheets.getByName("Sheet1")
		
		for i in range(10):
			oSheet.getCellByPosition(0,i).setValue(i)
			sleep(1)
	
18..Load dialogs embedded in document
	See: https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=74508
	
19..Getting Python scripts working in LibreOffice and Ubuntu
	http://askubuntu.com/questions/325163/missing-python-in-libreoffice-organize-macros-menu/327438?noredirect=1#327438

20..Display a message box
	# This can be slightly messy. Different versions of OpenOffice and LibreOffice require different
	# processes. Luckily, it seems there are only two processes, so we can try one and if that doesn't work, we 
	# can use the other

	from com.sun.star.awt import Rectangle
	from com.sun.star.awt import WindowDescriptor
	from com.sun.star.awt.WindowClass import MODALTOP
	from com.sun.star.awt.VclWindowPeerAttribute import OK, OK_CANCEL, YES_NO, YES_NO_CANCEL, RETRY_CANCEL, DEF_OK, DEF_CANCEL, DEF_RETRY, DEF_YES, DEF_NO

	# Message box test for OO or LO version
	def messageBox(msgText, msgTitle):
		doc = XSCRIPTCONTEXT.getDocument()
		parentwin = doc.CurrentController.Frame.ContainerWindow

		try:
			messageBoxLO4(parentwin, msgText, msgTitle)
		except:
			messageBoxOO4(parentwin, msgText, msgTitle)
		else:
			pass

	### following works with OO4 but not LO4 or OO Portable 3.2
	def messageBoxOO4(ParentWin, MsgText, MsgTitle):
		#doc = XSCRIPTCONTEXT.getDocument()
		ctx = XSCRIPTCONTEXT.getComponentContext()
		MsgText = MsgText+"      OO"
		
		toolkit = ctx.getServiceManager().createInstanceWithContext("com.sun.star.awt.Toolkit", ctx)
		msgbox = toolkit.createMessageBox(ParentWin, 0, 1, MsgTitle, MsgText)
		
		msgbox.execute()
		#msgbox.dispose()

	# Show a message box with the UNO based toolkit. Works with LO4 and OOP3.2
	def messageBoxLO4(ParentWin, MsgText, MsgTitle):
		MsgText = MsgText+"      LO"
		#describe window properties.
		aDescriptor = WindowDescriptor()
		aDescriptor.Type = 1   					# MODALTOP
		aDescriptor.WindowServiceName = "infobox"
		aDescriptor.ParentIndex = -1
		aDescriptor.Parent = ParentWin
		aDescriptor.WindowAttributes = 4194304	#MsgButton = OK
		
		tk = ParentWin.getToolkit()
		msgbox = tk.createWindow(aDescriptor)
		
		msgbox.setMessageText(MsgText)
		msgbox.setCaptionText(MsgTitle)
			
		return msgbox.execute()

	# This is the main call to create a message box
	def mainFunction():
		messageBox("Message Text","MessageBox Title")


#################################################################################
Odd bits and pieces:

==================================================================
Using MRI with Python:
https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=50199

==================================================================
Module to aid use of Python with OpenOffice

https://wiki.openoffice.org/wiki/Danny.OOo.OOoLib.py