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

[Lippi]

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.

Components:

1. Spreadsheet with 4 sheets, only the last (2018) in near final format.
2. Python macro code (aa_N3.py). 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

def msgBox(msg): 
 from com.sun.star.awt.MessageBoxType import MESSAGEBOX, INFOBOX, WARNINGBOX, ERRORBOX, QUERYBOX
 from com.sun.star.awt.MessageBoxButtons import BUTTONS_OK, BUTTONS_OK_CANCEL, BUTTONS_YES_NO, BUTTONS_YES_NO_CANCEL, BUTTONS_RETRY_CANCEL, BUTTONS_ABORT_IGNORE_RETRY
 from com.sun.star.awt.MessageBoxResults 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:
  print("OK")

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

def N3(*args):

# *args needed to run script from cmd line, as follows:
# scalc c:\temp\booksRead_2014_2018.ods 'vnd.sun.star.script:aa_N3.py$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':
   continue

  i = 0
  opmonth = ''
 
  while True:
   oCell = oSheet.getCellByPosition(0, i) # i = row; 0 = title; 3 = month; 4 = author; 5 = ISO language 
   if oCell.String == "":
    break
   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')
  
 msgBox('Done!')
 f.close()
 desktop.terminate()

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

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 'vnd.sun.star.script' 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

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

scalc c:\temp\booksRead_2014_2018.ods 'vnd.sun.star.script:aa_N3.py$N3?language=Python&location=share'