#### Previous topic

6. Mathematical notes

8. On Python

# 7. Notes on spreadsheets etc¶

Here notes on spreadsheets are kept. Spreadsheets is an interesting framework for doing scientific/financial calculations.

## 7.1. An example of Method and operator overload in spreadsheets¶

Abstract:

In spreadsheets like [KSpread], [GNumeric] or the Open Office [Calc] dates are handled the following.

1. You type in a date in a date-specific format
2. The spreadsheet saves the date in the cell as an integer, but keep showing the date like the format

This way a date is defined as an integer being the number of days since 1899-12-31 (date = 0). This is inspired by the way it is done in the overall spreadsheet benchmark [msExcel].

The benefit is that it is easy to a number of days to a date or finding the number of days between 2 dates as a difference.

This chapter is about how this way of adding functionality to a new class is generalized in Python. And showing that spreadsheets might benefit from this sort of generalisations.

### 7.1.1. An example:¶

The picture shows how dates are represented in spreadsheets in order to simplify calculations.

The formulas in column A is shown in column B. This way both values and formulas are shown.

• First a date is entered in A1. Even though it is saved as and integer (see 40544 in cell B1) it is shown as a date.
• Next in A2 the formula “=A1+55” (see B2) is entered. The date format is inherited from A1 and the date 55 days ahead is shown. Here a number of days are added to a date.
• Finally in A3 the difference between the 2 dates is calculated by the formula “=A2-A1” (see B3). The result is surpricingly enough 55 days.

Note how the format defines appearance while the fact that a date is actually is an integer makes it possible to do calculations on dates similar to operator overload which is to be presented below.

The change of format on the other hand is in fact a case of method overload which also is to be presented below.

The functionality of dates in spreadsheets is mirrored in the class bankdate in the finance package.

This very intuitive way of working with dates and period we would like to transfer to Python.

### 7.1.2. How things are done in Python¶

#### 7.1.2.1. Some concepts from the finance module¶

To examplify I will introduce some concepts from the finance module.

timeperiod:
A timeperiod is in essence a number and a time unit (d(ays), m(onths), y(ears)).

You can do timeperiod calculations within the same time unit:

```>>> from finance import TimePeriod
>>> 5 * TimePeriod('4m') - 3
17m
```
bankdate:
A bankdate shows a date as string in the format ‘yyyy-mm-dd’.

You can a bankdate with one or more timeperiods and get a new bankdate. And you can subtract 2 bankdates and get the number of days inbetween. Note that no value at instantion means that bankdate becomes current date.

```>>> from finance import BankDate
>>> BankDate('2010-09-14')  # No value set means today
2010-09-14
>>> BankDate('2010-09-14') + '3m'
2010-12-14
>>> BankDate('2010-09-14') + '3m' + '2y'
2012-12-14
>>> BankDate('2010-09-14') + '3m' - BankDate('2010-09-14')
91
```

Inheritance and method overload is one of the cornerstones object oriented programming. Below it is shown used to ease coding with these mathematically inspired objects.

#### 7.1.2.2. Operator and method overload¶

In python it is easy to set up eg bankdate and timeperiod calculations. In every class definition it is possible to define internal methods like __add__ and __radd__ for left and right addition. The methods are used when an instance of this class is added something in the code.

In Python there are also some standard methods used for presentation:

__str__:
Method defining what is shown when print instances of a class. It also defines what is to be shown when eg the str function is used on a instances of the class
__repr__:
Method defining what is shown when an instance is entered at the command promt and the repr function

A lot of times these 2 methods are set to the same. But there is no reason not to shown the values of the properties in eg __repr__ and some modified version in the __str__./reference/datamodel.html

For further reading see eg Python

### 7.1.3. A suggestion for spreadsheet development¶

As it has been demonstrated spreadsheets does have the need for defining new datatypes and new definitions for one or more operator.

It would be nice if the set of standard class functions were available in the scripting within spreadsheets.

This way it would fairly easy to implement mathematical objects like complex numbers, vectors and matrices.

Here the operator definitions are clear. But it could be nice if it was possible to show different information at different times, eg to show a matrix by its values some times and sometimes to show the dimensions of the matrix.

Another line of objects could be objects to build binomial trees, Monte Carlo paths etc.

This is a bit like a formula in spreadsheets. It shows a value most of the time. But in a certain mode the formula is shown instead.

### 7.1.4. A suggestion for Python development¶

A further development in Python could be generic operator overload. Why is the developer limited to specified number of operators. In order to implement new operators it would be nice to specify a operator in the code. The need for new operators could be inspired from mathematical concepts like eg differentiation, summation and integration.

If code objects could more be similar to mathematical objects it create a integration between programming and applied mathematics which yet has to be seen.

It would affect both education and the use of applied mathematics when the writing language are almost the same in theory and in practise.

## 7.2. Python Scripting in Gnumeric¶

Abstract:

Despite most homepages it is actually quite easy to enable Gnumeric for Python in Ubuntu.

Next part is a short resume on how to set up scripting in Python for Gnumeric. All homepages known to me at this point of writing on this subject are erronous at some degree. This should a proper guide.

Finally after considering installation in Ubuntu and scripting setup this text runs though the Gnumeric API.

### 7.2.1. How to enable Gnumeric for Python in Ubuntu¶

Apparently most linux users haven’t discovered that packages like ubuntu actually is a graphical user interface.

1. All that is necessary in ubuntu is to choose Synaptic from the Administration menu under the System menu.
2. Make a search for GNumeric
1. Install gnumeric-plugins-extra from the Synaptic
2. Open Gnumeric
3. Go to Tools -> Plugins and then select “Python plugin loader” and “Python functions”.
4. Restart Gnumeric.

To test whether you now have Python functions by typing

=py_capwords(“python rocks”)

in a cell.

After you hit <Enter>, you should see “Python Rocks” in that cell.

Also, if You can find the function py_capwords in the “Python” category then everything seems to be correct installed.

### 7.2.2. Scripting setup in Gnumeric¶

#### 7.2.2.1. The recipe¶

1. Create a plugin folder (only once): /usr/lib/gnumeric/x.xx.x/plugins where x.xx.x is the version number
2. Create one or more spellbook folders, eg: /usr/lib/gnumeric/x.xx.x/plugins/spellbookFolderName
3. For each spellbook create a plugin xml. This is very tricky to set up correct. Use the the template in /usr/lib/gnumeric/x.xx.x/plugins/py-func as a base. If the test has proven succesfull then the template will work. Then it is just a matter of respecting the naming standards specified at eg HOWTO: Python Programming in Gnumeric , see also below.
4. Create one or more (here I’m guessing) python modules. Also use the standards here

Since there are few but essential standards there is a need for a script doing the standardisation.

#### 7.2.2.2. The Standardising rules¶

The plugin.xml template based on the py-func folder:

```<?xml version="1.0" encoding="UTF-8"?>
<plugin id="Gnumeric_PyFunc">
<information>
<name>ThisIsNotShownAnywere</name>
<description>ThisIsNotShownAnywere</description>
<require_explicit_enabling/>
</information>
<attribute value="MyPythonFunctionModule" name="module_name"/>
<services>
<service type="function_group"
id="StartOfKeyDictionaryInPythonModule">
<category>CategoryNameInFunctionList</category>
<functions>
<function name="GnumericFunctionInModule"/>
</functions>
</service>
</services>
</plugin>```

The MyPythonFunctionModule.py template based on the py-func folder:

```# MyPythonFunctionModule.py
#::

import Gnumeric as g # Isn't used below right now ::

def PythonFunctionInModule(args):
'@FUNCTION=GnumericFunctionInModule\n'\
'@SYNTAX=GnumericFunctionInModule(args)\n'\
'@DESCRIPTION=Description.\n'\
'Look, the description can go onto other lines.\n\n'\
'@EXAMPLES='\
'@SEEALSO='

some python code

# Translate the func_add python function to a gnumeric
# function and register it
**StartOfKeyDictionaryInPythonModule**_functions = {
'GnumericFunctionInModule': PythonFunctionInModule
}```

Regretably the function description isn’t transferred to gnumeric.

It is possible to specify the type of arguments by writing:

```'GnumericFunctionInModule': ('type1type2', 'arg1Name, arg2Name',
PythonFunctionInModule)```

where type is:

f : float
no errors, string conversion attempted
b : boolean
identical to f
s : string
no errors
S : scalar
any non-error scalar
E : scalar
any scalar, including errors
r : cell range
content may not be evaluated yet
A : area
array, range (as above), or scalar
? : anything
any value (scalars, non-scalars, errors, whatever)

### 7.2.3. The Python Gnumeric API guide¶

First of all: To do this you have to be running from within the Gnumeric Python console.

And then in order to get going Gnumeric has to be imported:

```>>> import Gnumeric as g
```

#### 7.2.3.1. Workbooks¶

If Gnumeric is running besides the console then there is an open workbook. To see existing workbooks use the Gnumeric function workbooks:

```>>> wbs = g.workbooks()
>>> wbs
(<Workbook object at 0xb639f2f0>,)
>>> wb = wbs[0]
```

Otherwise create a new workbook by:

```>>> wbNew = g.workbook_new()
>>> g.workbooks()
(<Workbook object at 0xb639f310>, <Workbook object at 0xb639f320>)
```

It is quite limited what can be done workbooks in Gnumeric:

```>>> dir(wb)
```

When a new workbook is created from the Gnumeric Python console it isn’t shown. To see wbNew inside Gnumeric one has to write:

```>>> wbNew.gui_add()
```

#### 7.2.3.2. Sheets¶

As for workbooks you can add a sheet and see the list of sheets. Too see the the sheets in workbook wbNew:

```>>> wbNew.sheets()
(<Sheet object at 0xb639f310>,)
```

So a workbook created from the code has 1 sheet from start whereas the one opened with Gnumeric has 3:

```>>> len(wb.sheets())
3
```

A sheet can be added to eg wbNew by:

```>>> sheet = wbNew.sheet_add()
>>> len(wbNew.sheets())
2
```

So what can be done with a sheet:

```>>> dir(sheet)
['cell_fetch', 'get_extent', 'get_name_unquoted', 'rename', 'style_apply_range', 'style_get', 'style_set_pos', 'style_set_range']
```

At sheet level it is possible to get sheet name and rename a sheet:

```>>> sheet.get_name_unquoted()
'Ark2'
>>> sheet.rename('nhb')
>>> sheet.get_name_unquoted()
'nhb'
```

Also you can refer to a specific cell by:

```>>> c = sheet.cell_fetch(5,3)
>>> c
<Cell object at 0xb639f320>
```

And set the value to the string ‘hi’ (We will look at what can be done with cells just below the handling of sheets):

```>>> c.set_text('hi')
```

Now we will be able to see the text ‘hi’ in cell F4, so rows and columns are numbered starting from 0 and positions are given as (column, row).

Now to get the minimal range on the sheet with cells in use:

```>>> sheet.get_extent()
<Range object at 0xb636ec38>
```

And to see the range as a tuple:

```>>> sheet.get_extent().get_tuple()
(5, 3, 5, 3)
```

Let’s see the get_extent in use one more time:

```>>> sheet.cell_fetch(1,2).set_text('B3')
>>> sheet.get_extent().get_tuple()
(1, 2, 5, 3)
```

So the minimal range has upper left corner at ‘B3’ or position (1, 2) with value ‘B3’ and lower right corner at F4 or position (5, 3) with value ‘hi’.

A range can also be specified with the function Range:

```>>> g.Range(1,2,5,3)
<Range object at 0xb636ec38>
>>> g.Range(1,2,5,3).get_tuple()
(1, 2, 5, 3)
```

Hints

To get a list of sheet names:

```>>> [s.get_name_unquoted() for s in wb.sheets()]
['Ark1', 'Ark2', 'Ark3']
```

To get a sheet with name Ark1:

```>>> s1 = [s for s in wb.sheets() if s.get_name_unquoted() == 'Ark1'][0]
>>> s1.get_name_unquoted()
'Ark1'
```

#### 7.2.3.3. Cells¶

To see the methods for the cell object type:

```>>> F1 = sheet.cell_fetch(5,0)
>>> F1
<Cell object at 0xb639f320>
>>> dir(F1)
['get_entered_text', 'get_rendered_text', 'get_style', 'get_value', 'get_value_as_string', 'set_text']
```

So let’s take a look at these methods. All that can be set is string/text.

```>>> F2 = sheet.cell_fetch(5,1)
>>> F1.set_text('1')
>>> F2.set_text('3.2')
```

Now this is a trap. On my computer the decimal seperator is comma (,) and not the dot (.) so to write a float type into a cell I have to use comma:

```>>> F2.set_text('3,2')
>>> F3 = sheet.cell_fetch(5,2)
>>> F3.set_text('=F1 + F2')
```

Now the workbook looks like:

Now let’s look at the methods.

Return entered text - Formulas are preserved:

```>>> F1.get_entered_text()
'1'
>>> F2.get_entered_text()
'3,2'
>>> F3.get_entered_text()
'=F1+F2'
```

Return rendered text - Formulas are shown with their value as string:

```>>> F1.get_rendered_text()
'1'
>>> F2.get_rendered_text()
'3,2'
>>> F3.get_rendered_text()
'4,2'
```

Return value - Formulas are shown as floats:

```>>> F1.get_value()
1.0
>>> F2.get_value()
3.2000000000000002
>>> F3.get_value()
4.2000000000000002
```

Return value as string - Formulas are shown with their value as string:

```>>> F1.get_value_as_string()
'1'
>>> F2.get_value_as_string()
'3,2'
>>> F3.get_value_as_string()
'4,2'
```

Another way of referring a cell object is to use CellPos:

```>>> sheet.cell_fetch(1,1)
<Cell object at 0xb6735330>
>>> g.CellPos(1,1)
<CellPos object at 0xb6735340>
```

But CellPos only haves the method get_tuple.

Syntactic sugar

You can’t see the position in then usual spreadsheet notation using the str function on CellPos:

```>>> cp = g.CellPos(1,2)
>>> str(cp)
'B3'
```

Instead of sheet.cell_fetch(column, row) You can write sheet[column, row]:

```>>> c = sheet[1,2]
>>> dir(c)
['get_entered_text', 'get_rendered_text', 'get_style', 'get_value', 'get_value_as_string', 'set_text']
```

But You can’t see the position in then usual spreadsheet notation using the str function on a cell object:

```>>> str(c)
'<Cell object at 0xb6481300>'
```

#### 7.2.3.4. Accessing the function library in Gnumeric¶

Gnumeric.functions is the dictionary of all the Gnumeric functions shown in the function browser. You can ONLY do lookups!! Ie you can retrieve a function but beforehand You must know what You want.

Some help can be found at Gnumeric function libary

#### 7.2.3.5. Styles¶

How to set styles for a range

First define workbook, sheet, range and style (container):

```>>> import Gnumeric as g
>>> wb=g.workbooks()[0]
>>> sheet=wb.sheets()[0]
>>> r = g.Range(0,0,1,1)
>>> style = g.GnmStyle()
```

Change text size from 10 to 14 in style:

```>>> style.get_font_size()
10.0
>>> style.set_font_size(14)
>>> style.get_font_size()
14.0
```

Apply the new style to the range on the sheet:

```>>> sheet.style_apply_range(r,style)
```

Shift to bold font in style:

```>>> style.get_font_bold()
0
>>> style.set_font_bold(1)
>>> style.get_font_bold()
1
```

Apply the new style to the range on the sheet:

```>>> sheet.style_apply_range(r,style)
```

style_get must refer to a CellPos:

```>>> sheet.style_get(g.CellPos(0,0)).get_font_size()
14.0
```

What happens here?:

```>>> sheet.style_set_pos(g.CellPos(2,2), style)
>>> sheet.style_set_range(r,style)
```

I don’t know. Arguments are ok! What is the difference to style_apply_range?