I am writing this to help people who want to write Gnumeric scripts in Python, without digging through the Gnumeric C documentation and source code. The installation section is targetted mainly for Debian, but I hope the alternate instructions will work on other systems. Travis Whitton wrote a nice Python/Gnumeric guide for the old API in Gnumeric 1.0. Here's the new one written when 1.1.18 was being released. The API may change again. With luck I'll update this HOWTO.
If you need help online, you may want to check out:
doc/developer/writing-functions.sgml in the
Gnumeric source tree.plugins/python-loader/py-gnumeric.c
has good comments at the beginning.<gnumeric-list@gnome.org>I'm going to define some variables here so that you can insert the appropriate command or item for your system when they occur. I'll prefix them all with '$'.
su - and hit <Enter>sudofakerootYou need to get Python and Gnumeric, and the Python plugin for Gnumeric. You can get the binaries, the packaged source, or the developing edge CVS.
I've only tested this on sid (unstable). The version you get from stable (woody) may not act quite the same.
apt-get install gnumeric gnumeric-python
pythonapt-get build-dep
gnumericapt-get source gnumericcd gnumeric-$versiondebian/rules build
./debian/rules
binarycd .. to change to that directory.dpkg -i gnum*deb (presuming
you don't have other .deb packages beginning with "gnum" lying
around here.rm gnum*deb)Remember that this is the developing edge. Things may not work. Generally don't do this unless you are subscribed to the mail list and possibly also on the IRC channel.
export
CVSROOT=:pserver:anonymous@anoncvs.gnome.org:/cvs/gnomecvs login (No password -- hit RETURN.)
./autogen.sh./autogen.sh --prefix=/usr --with-gconf-schema-file-dir=/etc/gconf/schemasmakemake installNo longer necessary! (13 June 2003)
cvs -z3 checkout gnumeric -d
gnumeric-headcd gnumeric-head./autogen.sh and wait while it
compiles./autogen.sh --prefix=/usr --with-gconf-schema-file-dir=/etc/gconf/schemasmakemake installOK, you should now have gnumeric! Test it! If you installed the
Debianized version via apt-get, or did "make install", it should
be installed to /usr/bin (or /usr/local/bin on RedHat?) and you
can just type gnumeric. Otherwise you will find it in
gnumeric-head/src/ and you will have to run it from
there.
You can run a Python interpreter from inside Gnumeric, but you have to turn it on. To do this you simply uncomment a line in python-loader/plugins.xml. Now, normally, that file lives in /usr/lib/gnumeric/$version/plugins/python-loader/, but you should probably make a local copy. So:
gnumeric --version to make sure you get the
right version name for the followingcd ~/.gnumeric/$version/plugins/rsync -auvz
/usr/lib/gnumeric/$version/plugins/python-loader
./At the top there is a drop-down menu "Execute in". Right now your only choice will be "Default". After you evaluate functions from other plugins, those environments will become available too (JK says this is called lazy loading). But I'll assume you are using Default. (The only real difference is that you have to import Gnumeric first, and you can't see your plugin functions.)
(Note: prior to the cvs release of (16 June 2003), you need to type "print" to get any output. So "print dir()" rather than "dir()".
Let's start by taking a look at the environment.
>>> import Gnumeric >>> dir() ['Gnumeric', '__builtins__', '__doc__', '__name__']
'Gnumeric' is a module that exists only within Gnumeric, and which defines the Gnumeric Python API. Let's see it:
>>> dir(Gnumeric) ['Boolean', 'CellPos', 'FALSE', 'GnumericError', 'GnumericErrorDIV0', 'GnumericErrorNA', 'GnumericErrorNAME', 'GnumericErrorNULL', 'GnumericErrorNUM', 'GnumericErrorRECALC', 'GnumericErrorREF', 'GnumericErrorVALUE', 'MStyle', 'Range', 'TRUE', 'Workbooks', '__doc__', '__name__', 'functions', 'plugin_info']
Items of note:
dir(Gnumeric.functions) but maybe someone
will bind that soon.So do some exploring. First, let's poke around to figure out how to use CellPos.
# I wonder how to use CellPos (I've glanced at the source, but...)
>>> dir(Gnumeric.CellPos) # shows nothing useful
>>> Gnumeric.CellPos()
TypeError: CellPos() takes exactly 2 arguments (0 given)
>>> Gnumeric.CellPos("a1","a2")
TypeError: an integer is required. # Right.
>>> a=Gnumeric.CellPos(1,2) # It worked!
>>> a
<CellPos object at 0x106b6eb8> # Yeah, I know...
>>> dir(a)
['get_tuple']
>>> a.get_tuple()
(1,2) # Cool. That's (col,row)
Similarly, we can explore Gnumeric.Range:
>>> r = Gnumeric.Range((1,2),(3,4)) TypeError: Range() argument 1 must be CellPos, not tuple >>> r = Gnumeric.Range(a,a) >>> r <Range object at 0x1071d888> >>> dir(r) ['get_tuple'] >>> r.get_tuple() (3, 7, 3, 7)
Faster than reading the source code? Maybe. Maybe not. If you
evaluate in the context of a plugin (rather than in Default), then
dir(Gnumeric.plugin_info) will reveal some simple
informational functions you can call for the local plugin(s).
Note: obviously I don't really know what I'm doing, or I wouldn't be poking around like this.
Jon K. Hellan writes, "Here are some more things you can do from the console:"
# Get a workbook
>>> wb=Gnumeric.Workbooks()[0]
>>> wb
<Workbook object at 0x862a490>
>>> dir(wb)
>>> ['get_sheets']
# Get a sheet
>>> s=wb.get_sheets()[0]
>>> s
<Sheet object at 0x863e8d0>
>>> dir(s)
['cell_fetch', 'get_extent', 'get_name_unquoted', 'rename',
'style_apply_range', 'style_get', 'style_set_pos', 'style_set_range']
# Get a cell. s.cell_fetch(0,0) and s[0,0] are synonyms. First
# coordinate is columns, i.e. s[1,0] is B1.
>>> c=s[0,0]
>>> c
<Cell object at 0x863d810>
>>> dir(c)
['get_entered_text', 'get_mstyle', 'get_rendered_text', 'get_value',
'get_value_as_string', 'set_text']
# Change the cell - it changes in the grid
>>> c.set_text('foo')
# Retrieve the cell - both ways.
>>> c.get_value()
foo
>>> s.cell_fetch(0,0).get_value()
foo
Pretty cool, IMHO. Note, after setting a value, it won't show up until that cell is redrawn. That will happen automatically with plugin functions, but in the console, you may have to click on the cell.
Enable the Python-loader and Python plugins: Go to Tools -> Plugins and then select "Python plugin loader" and "Python functions". Restart Gnumeric.
The quickest way to test whether you now have Python functions
is to type =py_capwords("fred flintstone") in the
first cell. After you hit <Enter>, you should see "Fred
Flintstone". (Oddly enough, the first time I do this, I get an
error. Then it works fine in another cell, and fine again if I
go back to the first cell.)
You can also click on the functions button, and scroll down to the "Python" category. Select that. You should see at least two functions defined: PY_CAPWORDS and PY_PRINTF. They're not very useful, but they prove you've got the plugins. Test them either via the GUI or by typing into the cell.
I'll presume they worked.
To scribe new magic you must write your spells in places where Gnumeric will find them. That place is in folders under:
In many ways it would be easier to start by copying the py_func spellbook to your local .gnumeric folder, and just adding a function to that. But in general it will be more useful to be able to write your own separate spellbooks, so here we go.
First we make the folders and get into the right one. As noted above, we'll call our folder (spellbook) myfuncs.
mkdir ~/.gnumericmkdir ~/.gnumeric/<version>mkdir ~/.gnumeric/<version>/myfuncs/cd ~/.gnumeric/<version>/myfuncs/A spellbook has two files. The first is the python file with the functions. The second is the XML file "plugin.xml". The XML file holds that master spells that tell Gnumeric what functions we've defined, and what the name of the python file is, and one other important item. We'll create these as blank files.
touch my-func.pytouch plugin.xmlThe good news is that you only need to do this once per spellbook. After that you just add spells to it.
Your XML file must tell Gnumeric about your plugin. Here is a simple template. (If you want to learn about internationalization, see the example in the system's py-func spellbook.) Open up plugin.xml and insert the following lines:
<?xml version="1.0"?> <plugin id="Gnumeric_MyFuncPlugin"> <information> <name>Other Python functions from HOWTO</name> <description>A few extra python functions demonstrating the API.</description> </information> <loader type="Gnumeric_PythonLoader:python"> <attribute name="module_name" value="my-func"/> </loader> <services> <service type="function_group" id="example"> <category>Local Python</category> <functions> </functions> </service> </services> </plugin>
Next we'll create a minimal python file. The python file has to be named my-func.py because my-func is the value of module_name in the xml listing above. This file must have a dictionary called example_functions because example is the value of function_group in the xml above. So open up my-func.py and insert the following lines.
# my-func.py
#
from Gnumeric import GnumericError GnumericErrorVALUE
import Gnumeric
import string
example_functions = {
}
To add new functions to Python, you now must do five things (three sir!):
my-func.py.example_functions
dictionary at the end of my_func.pyplugin.xml.Let's do something very simple: add two numbers together. First, edit my-func.py.
# Add two numbers together
def func_add(num1, num2):
return num1 + num2
# Translate the func_add python function to a gnumeric function and register it
example_functions = {
'py_add': func_add
}
Then let the plugin-loader(?) know about your function. Add the following line near the end of plugin.xml (between <functions> and </functions>).
<function name="py_add"/>
Now start Gnumeric and type py_add(2,3) into a
cell. You should get "5". You can also use cell references. If
that was in cell A1, go to cell A2 and type
py_add(A1,3) and you will get "8". But your
function won't show up in the GUI list yet.
To make your function show up in the GUI, you have to tell Gnumeric some things about it via a standard header, like this:
# Add two numbers together
def func_add(num1, num2):
'@FUNCTION=PY_ADD\n'\
'@SYNTAX=py_add(num1, num2)\n'\
'@DESCRIPTION=Adds two numbers together.\n'\
'Look, the description can go onto other lines.\n\n'\
'@EXAMPLES=To add two constants, just type them in: py_add(2,3)\n'\
'To add two cells, use the cell addresses: py_add(A1,A2)\n\n'\
'@SEEALSO='
return num1 + num2
The text after '@DESCRIPTION=' is the description that shows up in the function GUI. You can make it as simple or detailed as you want. I'm not sure how many other fields get used right now, as I haven't seen the EXAMPLES show up anywhere.
But this still isn't quite right. Gnumeric doesn't know how many arguments the function can handle, nor of what type. So the function GUI will prompt for the two values it knows about (as type "Any") and then keep prompting for more. But py_add cannot accept all types, nor can it handle more than two arguments, so unless you give it precisely 2 numbers, you will get an error when you click "OK".
We got away last time just because Gnumeric was forgiving. Now we need to say that we can accept only 2 values, of type floating-point (which will also handle ints).
Where before we had: 'py_add': func_add
we should now put: 'py_add': ('ff','num1,num2',func_add)
This says that Gnumeric should expect two floating-point numbers
('ff') with names 'num1' and 'num2', and pass them to func_add.
Of course, there is no reason an add function shouldn't be able to handle a range. The simplest way to do that is to accept a range, and then call Gnumeric's own SUM function on it! All of Gnumeric's functions are available to you in the dictionary Gnumeric.functions, keyed by name. So here is how to write py_sum.
def func_sum(gRange): '@FUNCTION=PY_SUM\n'\ '@SYNTAX=PY_SUM(range)\n'\ '@DESCRIPTION=Adds a range of numbers together.'\ 'Just like built-in SUM.\n\n'\ '@EXAMPLES=To add values in A1 to A5, just type them in:\n'\ ' py_sum(a1:a5)\n'\ '@SEEALSO=' try: sum = Gnumeric.functions['sum'] val = sum(gRange) # val = reduce(lambda a,b: a+b, vals) except TypeError: raise GnumericError, GnumericErrorVALUE else: return val
example_functions = {
'py_add': ('ff','num1,num2',func_add),
'py_sum': ('r', 'values', func_sum)
}
<functions> <function name="py_add"/> <function name="py_sum"/> </functions>
I told you this was the easy way to do it. Obviously it's not very useful to just duplicate Gnumeric functions. But that's as far as I've made it. From what can tell, range objects are packaged as opaque pointers of type RangeRefObject. There seems to be no way to work with them from within Python, so we must rely on the Gnumeric functions.
All is not lost, despite the opaque pointers. For in Gnumeric we can read about all the functions that have been defined. Some of those take references (including RangeRefs) and return useful information. For example, under "Lookup" we find "Column" and "Row" which return arrays of all the column (or row) indices in the range. So we can redo the sum function.
Presume we can convert our RangeRef to a start tuple and end tuple. Then we can write sum2:
def func_sum2(gRange): '@FUNCTION=PY_SUM2\n'\ '@SYNTAX=PY_SUM2(range)\n'\ '@DESCRIPTION=Adds a range of numbers together,'\ 'without calling built-in SUM.\n\n'\ '@EXAMPLES=To add values in A1 to A5, just type them in:\n'\ ' py_sum(a1:a5)\n'\ '@SEEALSO=' try: [r_begin, r_end] = range_ref_to_tuples(gRange) wb=Gnumeric.Workbooks()[0] # Careful! This is WRONG! It doesn't s=wb.get_sheets()[0] # use the ACTUAL workbook or sheet. val = 0 for col in range(r_begin[0], r_end[0]): for row in range(r_begin[1], r_end[1]): cell = s[col, row] val = val + cell.get_value() # Note: this doesn't skip blank cells etc. except TypeError: raise GnumericError,GnumericErrorVALUE else: return val
That's fine as far as it goes, but we need to define the helper function "range_ref_to_tuples". Although I'm rather ashamed to show this ugly code, here's how I did it (someone suggest a better way, please!):
def range_ref_to_tuples(range_ref): '''I need a function to find the bounds of a RangeRef. This one extracts them from the Gnumeric "column" and "row" commands, and returns them as a pair of tuples. Surely there is a better way? For example, return a list of cells??''' col = Gnumeric.functions['column'] row = Gnumeric.functions['row'] # "column" and "row" take references and return an array of col or row # nums for each cell in the reference. For example, [[1, 1, 1], [2, 2, 2]] # for columns and [[2, 3, 4], [2, 3, 4]] for rows. try: columns = col(range_ref) rows = row(range_ref) begin_col = columns[0][0] - 1 begin_row = rows[0][0] - 1 end_col = columns[-1][-1] end_row = rows[-1][-1] # We subtracted 1 from the begin values because in the API, # indexing begins at 0, while "column" and "row" begin at 1. # We did NOT subtract 1 from the end values, in order to make # them suitable for Python's range(begin, end) paradigm. except TypeError: raise GnumericError,GnumericErrorVALUE except NameError: # right name? raise GnumericError,Gnumeric.GnumericErrorNAME except RefError: # right name? raise GnumericError,Gnumeric.GnumericErrorREF except NumError: # right name? raise GnumericError,Gnumeric.GnumericErrorNUM return [ (begin_col, begin_row), (end_col, end_row) ]
From there, insert the function into the dictionary, and insert
its name into plugin.xml. I leave these as exercises
to the reader (answers in the sample files -- no
peeking!). Restart Gnumeric and you should be able to use
py_sum2!
There are a couple of glitches:
Relative to the source tree:
plugins/python-loader/py-gnumeric.c
That file also has good notes at the beginning.doc/developer/python-gnumeric.txt.You can see my examples in full, with more comments:
To upgrade, first choose any method from the installation section above. But note: when you upgrade your Gnumeric version, it will look for your Python scripts in the corresponding version-named subdirectories. For example, if your scripts are in "~/.gnumeric/1.1.17/plugins", but you just upgraded to 1.1.18, you may need to rename that (or copy them) to "~/.gnumeric/1.1.18/plugins". If you enabled the Python console, you'll probably have to do that again for the new version. I suspect that just copying the old one won't work.
Find the new version with gnumeric --version,
making sure to invoke the proper gnumeric.
To be written....