Writing your own Python functions

To scribe new magic you must write your spells in places where Gnumeric will find them. That place is in folders under: ~/.gnumeric/<version>/plugins/ Each folder under here is one "spellbook" of new plugin functions. You may put all your spells in one spellbook, or group them neatly depending on your tastes. Each spellbook must have two files. We'll create a spellbook called "myfuncs". A pedestrian name for pedestrian spells. When I have more skill, perhaps I'll make some with better names. Several suggest themselves:

  • Transformations: of obvious value for a spreadsheet
  • Illusions: statistical functions, clearly
  • Charms: presentation graphics
  • Necromancy: file repair and missing values?
  • Divination: data mining! Prepare the spellbook

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.

  1. Make the folder: First we make the folders and get into the right one. As noted above, we'll call our folder (spellbook) myfuncs.

    1. If they don't already exist:

      1. mkdir ~/.gnumeric

      2. mkdir ~/.gnumeric/<version>

    2. mkdir ~/.gnumeric/<version>/plugins/myfuncs/

    3. cd ~/.gnumeric/<version>/plugins/myfuncs/

  2. Make the files: 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.

    1. touch my-func.py

    2. touch plugin.xml

  3. Write the master spells The 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">
    		<name>Other Python functions from HOWTO</name>
    		<description>A few extra python functions demonstrating the API.</description>
    	<loader type="Gnumeric_PythonLoader:python">
    		<attribute name="module_name" value="my-func"/> 3
    		<service type="function_group" id="example"> 4
    			<category>Local Python</category>

    The value of "name" determines the name of your python script (file). In this case, it must be "my-func.py"

    The value of "id" here determines the name of the function dictionary in your python script. In this case, it must be "example_functions" because here the value is "example".

  4. Prepare to write the spells: Next we'll create a minimal python file. As noted above, we must name the file my-func.py and it must have a dictionary called example_functions. 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 = {
 Writing new spells

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

  1. Write the python function in your copy of my-func.py.

  2. Insert the function info into the example_functions dictionary at the end of my_func.py

  3. Insert the function name into the functions list at the end of plugin.xml.

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):
        '@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'\

        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.

  1. First, define func_sum (in my-func.py):

    def func_sum(gRange):
    	'@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'\
    		sum = Gnumeric.functions['sum']
    		val = sum(gRange)
    		#  val = reduce(lambda a,b: a+b, vals)
    	except TypeError:
    		raise GnumericError, GnumericErrorVALUE
    		return val
  2. Then insert it into your functions dictionary. That dictionary now looks like this (with 'r' denoting a range type):

    example_functions = {
    	'py_add': ('ff','num1,num2',func_add),
    	'py_sum': ('r', 'values', func_sum)
  3. Finally, make an entry in the XML list, so that it now looks like:

    				<function name="py_add"/>
    				<function name="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. Do it yourself (mostly)

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 and end tuple. Then we can write sum2:

def func_sum2(gRange):
	'@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'\
		[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
		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.

		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:

  • It fails the first time with "could not import gobject". Just run again, I don't know what that's about.
  • It will only work for Workbook 1 and Sheet 1. JK thinks that there may be no way to get the current Workbook/Sheet in the Python API. Hrm....
  • As noted, it should do some simple trapping to skip blank or text-filled cells. That can be done! I just didn't. It's late. More help

Relative to the source tree:

  • The Python interface is defined in: plugins/python-loader/py-gnumeric.c That file also has good notes at the beginning.
  • There are interesting things about the way it used to be in: doc/developer/python-gnumeric.txt. Program Listings

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