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()