You are here: start » gmfill

Table of Contents

gmfill

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()
gmfill.txt · Last modified: 2024/05/27 12:21 by 127.0.0.1
Kleine Websites, die ein Wiki als CMS verwenden.de