Run 'batch-mode' Python macro in spreadsheet from PowerShell

Creating a macro - Writing a Script - Using the API

Run 'batch-mode' Python macro in spreadsheet from PowerShell

Postby Lippi » Tue Jan 08, 2019 6:47 pm

Having spent some time over several days on this I thought I'd share my results. Comments welcome.

Environment: Windows 8, Open Office 4.

Goal: Automate export of contents of spreadsheet in N3 format, for subsequent load as RDFLib graph. Note my N3 output here is work-in-progress, I need to work on the spreadsheet / refine the script further.

Challenges: Trying to find out how to do this based around Google searching. There didn't seem to be anywhere authorititive. I ended up trying things based on trial and error, then further searching. (I even ended up editing 'pythonscript' in the OO program directory, for debug purposes - didn't need that in the end though). People more expert than me looking at this may think I put too many constraints on myself. All I can say is – this now works. :D


1. Spreadsheet with 4 sheets, only the last (2018) in near final format.
2. Python macro code ( This is stored at C:\Program Files (x86)\OpenOffice 4\share\Scripts\python, which in my environment is the folder for 'Open Office macros / Python'. I owe thanks to someone probably on this forum for the msgBox function code. That code is still there to show that some processing has occurred, I'll remove it in due course. The N3 function shows navigation of sheets and within each sheet its rows and cells, also text file output. Note that the preview seems to have lost my first indent - sorry.

Code: Select all   Expand viewCollapse view
def msgBox(msg):
from import OK, YES, NO, CANCEL

desktop = XSCRIPTCONTEXT.getDesktop()
model = desktop.getCurrentComponent()
parentwin = model.CurrentController.Frame.ContainerWindow
box = parentwin.getToolkit().createMessageBox(parentwin, MESSAGEBOX,  BUTTONS_OK, "msgBox", msg)

result = box.execute()
if result == OK:

# ----------------------------------------------------------------------------------------------

def N3(*args):

# *args needed to run script from cmd line, as follows:
# scalc c:\temp\booksRead_2014_2018.ods '$N3?language=Python&location=share'

from datetime import datetime
desktop = XSCRIPTCONTEXT.getDesktop()
model = desktop.getCurrentComponent()

oSheets = model.Sheets
oSheetEnum = oSheets.createEnumeration()
f = open("c:/temp/gash.n3","w+")

while oSheetEnum.hasMoreElements():
  oSheet = oSheetEnum.nextElement()
  if oSheet.Name != '2018':

  i = 0
  opmonth = ''

  while True:
   oCell = oSheet.getCellByPosition(0, i) # i = row; 0 = title; 3 = month; 4 = author; 5 = ISO language
   if oCell.String == "":
   f.write(genS(oCell.AbsoluteName) + ' dc:Title "' + oCell.String.encode('ascii', 'xmlcharrefreplace') + '"')
   oCell = oSheet.getCellByPosition(4, i)
   if oCell.String:
    f.write(' ;\n     dc:Creator "' + oCell.String.encode('ascii', 'xmlcharrefreplace') + '"')
   oCell = oSheet.getCellByPosition(5, i)
   if oCell.String:
    f.write(' ;\n     dc:Language "' + oCell.String + '"')
   oCell = oSheet.getCellByPosition(3, i)
   if oCell.String:
    dto = datetime.strptime(oCell.String + ' ' + oCell.AbsoluteName[2:6], '%b %Y')
    opmonth = datetime.strftime(dto, '%Y-%m')
   if opmonth:
    f.write(' ;\n     dc:Date "' + opmonth + '"')
   f.write(" .\n\n")
   i += 1
  oAnno = oSheet.getAnnotations()
  Enum = oAnno.createEnumeration()
  while Enum.hasMoreElements():
   Comment = Enum.nextElement()
   f.write(genS(Comment.Parent.AbsoluteName) + ' dc:Description "' + Comment.String.encode('ascii', 'xmlcharrefreplace') + '" .\n')

# ----------------------------------------------------------------------------------------------

def genS(absname):
return absname.replace("$'", "pf:").replace("'.", "_").replace("$", "")

# ----------------------------------------------------------------------------------------------

g_exportedScripts = N3,

3. Windows Powershell script (.ps1 file) to run OO and Python macro. The first three lines put OO on the path for the duration of the script. The last line is the key one. I started out with '//macro' syntax but later searching turned up the '' way of doing things. It was searching on that which showed me how to specify language and location. The last thing I had to get right was the single quotes round that parameter.

Code: Select all   Expand viewCollapse view
$ooHome = "C:\Program Files (x86)\OpenOffice 4\program"
$newPath = [Environment]::GetEnvironmentVariable("Path") + ";" + $ooHome + ";"
[Environment]::SetEnvironmentVariable("Path", $newPath)

scalc c:\temp\booksRead_2014_2018.ods '$N3?language=Python&location=share'
Open Office 4 on Windows 8
Posts: 1
Joined: Tue Jan 08, 2019 6:30 pm

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 1 guest