About
gmfill simplifies gnumeric workbook and graph creation. gmfill is a python script to fill a gnumeric file (used as template) with values from other programs. It “executes” the commands specified in a file, the filename of which is passed as the only argument. It can be used to automate graph creation while maintaining the simplicity of the chart visual editor provided by gnumeric.
Script commands:
in “templatename” → opens a new template
push “value” → puts value on the stack
push output “command” → puts the output of the command on the stack
reverse → reverses the stack
sheet “name” → sets the default sheet
count index → sets the default count
from index → sets the default from index
write column [index] [from xx] [count xx] [in sheetname] → writes a column data starting from the specified row
write row [index] [from xx] [count xx] [in sheetname] → writes a row data starting from the specified column
clear → clears the stack
pop → pops a value from the stack
set vname vvalue → sets the value of a variable that will be replaced in every string when prefixed by $$ ($$0…$$n are bound to command line args)
out “outputfile” → saves an output gnumeric file
graph “prefix” → creates svg files of the graphs in the current directory
;; → used for comments
Source code
#!/usr/bin/python # -*- coding: utf-8 -*- # GnuMericFILLer # Fills a gnumeric workbook with values # Copyright (C) 2011-2012 Amos Brocco <amos.brocco@gmail.com> # # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program. If not, see <http://www.gnu.org/licenses/>. # version 1.03, Sat Sep 29 2012,4:57 PM import sys import gzip import os import random from subprocess import call from xml.dom.minidom import parse """ Script commands: ================== in "templatename" -> opens a new template push "value" -> puts value on the stack push output "command" -> puts the output of the command on the stack reverse -> reverses the stack write column [index] [from xx] [count xx] [in sheetname] -> writes a column data write row [index] [from xx] [count xx] [in sheetname] -> writes a row data clear -> clears the stack pop -> pops a value from the stack set vname vvalue -> sets the value of a variable that will be replaced in every string when prefixed by $$ ($$0...$n are bound to command line args) out "outputfile" -> saves an output gnumeric file sheet "name" -> sets the default sheet name from index -> sets default from value count index -> sets default count value graph "prefix" -> creates svg files of the graphs in the current directory ;; -> used for comments mem -> saves stack to temp restore -> restores stack Example: ================== ;; Load the template in "degreetemplatedrop.gnumeric" ;; Change to the data directory cd "simulations/results" ;; Set input files variables set a ChordRandom1024.output.txt set b GIARandom1024.output.txt set c BlatantSRandom1024.output.txt ;; Get output from commands (normally this is a list of values, one per line) push output "cat $$a | grep --line-buffered LCC | awk '{print $12}'" write column B from 2 in "Chord" push output "cat $$a | grep --line-buffered LCC | awk '{print $12}'" write column C from 2 in "GIA" push output "cat $$b | grep --line-buffered LCC | awk '{print $12}'" write column A from 2 in "BlatantS" ;; Generate output graph graph "/home/brocco/Desktop/MyOutputDataGraphs" ;; Write to output out "myoutputdata.gnumeric" """ class GMFill: def __init__(self, script): self.script = script self.stack = [] self.template = None self.mem = [] self.tvars = {} self.defaultsheet = None self.defaultfrom = 1 self.defaultcount = -1 for i in range(0, len(sys.argv)): self.tvars["$$%d" % i] = sys.argv[i] def tokenize(self, line): tokens = [] ctoken = "" inStr = False escape = False for c in line: if (not inStr) and ((c == " ") or (c == "\n")): ctoken.strip() if ctoken != "": tokens.append(ctoken) ctoken = "" if (c == "\n"): if inStr: raise Exception, "Unterminated string" return tokens elif inStr: if c == "\\": escape = not escape elif not c == '"': if escape: raise Exception, "Invalid escape" ctoken += c else: if escape: escape = False ctoken += c continue inStr = False ctoken.strip() for i in self.tvars.keys(): ctoken = ctoken.replace(i, self.tvars[i]) tokens.append(ctoken) ctoken = "" elif (c == '"'): inStr = True else: ctoken += c def doIn(self, tokens): print "Loading template", tokens[0] ifile = gzip.open(tokens.pop(), 'r') try: self.template = parse(ifile) finally: ifile.close() def doSheet(self, tokens): self.defaultsheet = tokens.pop() def doFrom(self, tokens): self.defaultfrom = int(tokens.pop()) def doCount(self, tokens): self.defaultcount = int(tokens.pop()) def doPush(self, tokens): count = 1 tmp = tokens.pop() if (tmp == "output"): oscmd = tokens.pop() print "Pushing output of command \"%s\" on stack" % oscmd h = os.popen(oscmd, "r") data = h.read() data.strip() data = data.split("\n") data.reverse() h.close() print "Got", len(data)," samples" for o in data: self.stack.append(o) else: self.stack.append(tmp) def doReverse(self, tokens): self.stack.reverse() def doPop(self, tokens): self.stack.pop() def doClear(self, tokens): self.stack = [] def doGraph(self, tokens): tplate = tokens.pop() tempFile = "gmfill-tempfile-%s" % str(random.random() * 1000000000000) print "Generating gnumeric temporary file", "/tmp/"+tempFile+".gnumeric" data = self.template.toxml(encoding='utf-8') f = gzip.open("/tmp/"+ tempFile + ".gnumeric", 'wb') f.write(data) f.close() print "Converting to ODT" oscmd = "ssconvert --recalc --export-type=Gnumeric_OpenCalc:odf /tmp/%s.gnumeric /tmp/%s.zip" % (tempFile, tempFile) call(oscmd, shell=True) print "Inflating images" oscmd = "unzip -qd /tmp/%s.tempdir /tmp/%s.zip Pictures/* -x *.png " % (tempFile, tempFile) call(oscmd, shell=True) gfiles = os.listdir("/tmp/%s.tempdir/Pictures" % tempFile) for gf in gfiles: nFile = gf.replace("Graph",tplate) print "Storing /tmp/%s.tempdir/Pictures/%s as %s.svg" % (tempFile, gf, nFile) oscmd = "mv /tmp/%s.tempdir/Pictures/%s %s.svg" % (tempFile, gf, nFile) call(oscmd, shell=True) print "Removing temporary files" call("rm -f /tmp/%s.zip" % tempFile, shell=True) call("rm -f /tmp/%s.gnumeric" % tempFile, shell=True) call("rmdir /tmp/%s.tempdir/Pictures" % tempFile, shell=True) call("rmdir /tmp/%s.tempdir" % tempFile, shell=True) def doOut(self, tokens): fout = tokens.pop() print "Generating", fout data = self.template.toxml(encoding='utf-8') f = gzip.open(fout, 'wb') f.write(data) f.close() def doCd(self, tokens): tpath = tokens.pop() print "Changing directory to", tpath os.chdir(tpath) def doDup(self, tokens): if (len(tokens) > 0): count = int(tokens.pop()) val = self.stack[-count:] self.stack.extend(val) else: self.stack.extend(self.stack[0:count]) def getColIndex(self,base26): digits = [] base26 = base26.upper() l = len(base26) for i in range(0,l): digits.append(ord(base26[l-i-1]) - ord('A')) power = 0 value = 0 for i in digits: value += i * 26**power power += 1 return value def getSheetIndex(self,name): sheets = self.template.getElementsByTagName("gnm:Sheet") indx = 0 for s in sheets: sname = s.getElementsByTagName("gnm:Name") if (sname[0].firstChild.nodeValue == name): return indx indx += 1 return -1 def doSet(self, tokens): vname = tokens.pop() vvalue = tokens.pop() self.tvars["$$%s" % vname] = vvalue def doMem(self, tokens): self.mem = [] for e in self.stack: self.mem.append(e) def doRestore(self, tokens): for e in self.mem: self.stack.append(e) def doWrite(self, tokens): if (tokens.pop() == "column"): doColumn = True else: doColumn = False startCol = "" if (doColumn): startCol = tokens.pop() index = self.getColIndex(startCol) else: index = int(tokens.pop()) - 1 start = 0 end = -1 sheet = -1 ctype = -1 end = -1 count = self.defaultcount sheetname = self.defaultsheet if doColumn: start = int(self.defaultfrom) - 1 else: startCol = self.defaultfrom start = self.getColIndex(startCol) while len(tokens) > 0: tmp = tokens.pop() if (tmp == "from"): if doColumn: start = int(tokens.pop()) - 1 else: startCol = tokens.pop() start = self.getColIndex(startCol) if (tmp == "count"): count = int(tokens.pop()) if (tmp == "in"): sheetname = tokens.pop() if (tmp == "type"): ctype = int(tokens.pop()) if (count <=0): count = len(self.stack) end = start + count sheet = self.getSheetIndex(sheetname) if (sheet < 0): raise Exception, "Non existing sheet to write to: %s" % sheetname if (doColumn): print "Writing to sheet", "\"%s\"" % sheetname, "in column", startCol, "from row", start+1, "for", count, "rows" else: print "Writing to sheet", "\"%s\"" % sheetname, "in row", index+1, "from column", startCol, "for", count, "columns" sheetElement = self.getSheetElement(sheet) if sheetElement is None: raise Exception, "Unknown sheet with index %d" % sheet cells = self.getCells(sheetElement) for k in range(start, end): if doColumn: col = index row = k else: col = k row = index rcell = self.getCellChildIndex(cells, col, row) newcell = self.template.createElement("gnm:Cell") newcell.setAttribute("Row", str(row)) newcell.setAttribute("Col", str(col)) data = self.template.createTextNode(str(self.stack.pop())) if (ctype >= 0): newcell.setAttribute("ValueType", str(ctype)) newcell.appendChild(data) if (rcell is None): cells.appendChild(newcell) else: cells.replaceChild(rcell,newcell) def getSheetElement(self, sheet): return self.template.getElementsByTagName("gnm:Sheet")[sheet] def getCells(self, sheet): return sheet.getElementsByTagName("gnm:Cells")[0] def getCellChildIndex(self, cellselement, col, row): cells = cellselement.getElementsByTagName("gnm:Cell") for i in range(0,len(cells)): cell = cells[i] attr = cell.attributes if (attr[u'Row'] == str(row)) and (attr[u'Col'] == str(col)): return cell return None def execute(self): sf = open(self.script,'r') for l in sf: if l[0:2] == ";;": continue tk = self.tokenize(l) tk.reverse() if len(tk) != 0: cmd = tk.pop() if (cmd == "in"): self.doIn(tk) elif (cmd == "push"): self.doPush(tk) elif (cmd == "reverse"): self.doReverse(tk) elif (cmd == "write"): self.doWrite(tk) elif (cmd == "clear"): self.doClear(tk) elif (cmd == "pop"): self.doPop(tk) elif (cmd == "set"): self.doSet(tk) elif (cmd == "cd"): self.doCd(tk) elif (cmd == "out"): self.doOut(tk) elif (cmd == "sheet"): self.doSheet(tk) elif (cmd == "count"): self.doCount(tk) elif (cmd == "from"): self.doFrom(tk) elif (cmd == "graph"): self.doGraph(tk) elif (cmd == "dup"): self.doDup(tk) elif (cmd == "mem"): self.doMem(tk) elif (cmd == "restore"): self.doRestore(tk) if len(sys.argv) < 2: sys.exit('Usage: %s scriptname' % sys.argv[0]) gf = GMFill(sys.argv[1]) gf.execute()