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,
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'