Programming Gnumeric using Python

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 '$'.

You need to get Python and Gnumeric, and the Python plugin for Gnumeric. You can get the binaries, the packaged source, or the developing edge sources from Git.

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. Normally, that file lives in /usr/lib/gnumeric/$version/plugins/python-loader/, or perhaps /usr/local/lib... on Red Hat. I used to suggest making a local but you should probably make a local copy, but that was pain for little gain. So:

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: older releases required you to type print dir() instead of just dir(). Fixed in cvs 16 June 2003, and certainly in 1.1.20 and higher.

Let's start by taking a look at the environment.

>>> import 1Gnumeric
>>> dir()
['Gnumeric', '__builtins__', '__doc__', '__name__']
>>> dir(Gnumeric)
['Boolean', 'CellPos', 'FALSE', 'GnumericError', 'GnumericErrorDIV0',
'GnumericErrorNA', 'GnumericErrorNAME', 'GnumericErrorNULL',
'GnumericErrorNUM', 'GnumericErrorRECALC', 'GnumericErrorREF',
'GnumericErrorVALUE', 'MStyle', 'Range', 'TRUE', '__doc__',
'__name__', 2'functions', 'plugin_info', 'workbook_new', 'workbooks']
      

'Gnumeric' is a module that exists only within Gnumeric, and which defines the Gnumeric Python API.

Gnumeric.functions is the list of all the Gnumeric functions you would see in the function browser. You cannot yet do dir(Gnumeric.functions) but maybe someone will bind that soon.

RangeRef is not listed. That seems to limit us, though later in the tutorial we'll see how to use regular functions to get inside RangeRefs.

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)

>>> str(a)                                # Super cool.
'B3'                                      # JK hasn't implemented this for tuples yet
       

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)
       

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)
>>> ['gui_add', 'sheet_add', 'sheets']

# Get a sheet
>>> s=wb.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

Very, very nice. 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.

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.

To add new functions to Python, you now must do five things (three sir!):

Writing a simple script: 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.

Tell the GUI: 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".

Know your limits... 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.

...and surpass them 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.

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.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 literal, 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:

You can see my examples in full, with more comments: