Using OOSheet

Developing with OOSheet

There are two ways of using OOSheet. You can either make a python script that manipulates an instance of LibreOffice, or you can make python macros that will be executed directly by OO. Macros can be installed in a global path for all users and documents, in user’s home directory for a single user or inside a document.

No matter what your choice is, the python code is the same and works in any of those environments. So, it’s always best to start your development by manipulating an instance of Openoffice.org, so that you don’t have to restart it to test your routines.

You must launch LibreOffice Spreadsheet allowing socket connections. To do so, use the following command line (in GNU/Linux):

$ libreoffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

Since this command is very complicated to remember, a reminder command is included:

$ oosheet-launch

OOSheet with Spreadsheets

Hello world

After launching libreoffice, in a python shell:

>>> from oosheet import OOSheet as S
>>> S('a1').string = 'Hello world'

Cells selectors

OOSheet uses selectors to determine which cells you are working with. Each instance of OOSheet class receives a selector as parameter.

Examples of possible selectors:

>>> S('b2') # one single cell at first sheet
>>> S('a1:10') # one column from a1 to A10 at first sheet
>>> S('a1:a10') # same thing
>>> S('a1:g1') # one row with six columns
>>> S('a1:g7') # a 7x7 square with 49 cells
>>> S('Sheet2.a2:3') # cells A2 and A3 in sheet named "Sheet2"

and so on. Finally, if no selector is passed, OOSheet object will be initialized with user’s selection:

>>> S() # gets the user selection

Data manipulation

Each cell has three relevant attributes concerning its data: value, string and formula. Besides that, OOSheet adds the “date” attribute, that is a wrapper around value to work with datetime.datetime objects and properly format date cells. Setting one of these 4 attributes will modify all of them accordinly.

Value is always a float and is not seen by the user. String is the representation you see inside the cell. Formula is the one you see at the formula box on top of the spreadsheet and generates a value and string. Dates are represented as values internally, counting the number of days since 30/12/1899, but that is transparent to developers using OOSheet.

The following code illustrates how to deal with those types:

>>> S('a1').value = 1
>>> S('a1').value
1.0
>>> S('a1').string
u'1'
>>> S('a1').formula
u'1'
>>> S('a1').date
datetime.datetime(1899, 12, 31, 0, 0)

>>> S('a1').string = 'Hello world'
>>> S('a1').value
0.0
>>> S('a1').formula
u'Hello world'
>>> S('a1').date
datetime.datetime(1899, 12, 30, 0, 0)

>>> S('a1').value = 1
>>> S('a2').formula = '=a1+10'
>>> S('a2').value
11.0
>>> S('a2').string
u'11'
>>> S('a2').formula
u'=A1+10'
>>> S('a2').date
datetime.datetime(1900, 1, 10, 0, 0)

>>> S('a1').date = datetime.datetime(2011, 01, 19)
>>> S('a1').value
40562.0
>>> S('a1').string
u'01/19/2011'
>>> S('a1').formula
u'40562'

Alternatively, you can use set_value(), set_string(), set_formula() and set_date() methods:

>>> S('a1').set_value(1)
>>> S('a2').set_string('Hello')
and so on

This is useful for cascading calls.

You can use flatten() to consolidate values and strings generated by formulas to be independent of the formulas:

>>> S('a1').value = 1
>>> S('a2').formula = '=a1+3'
>>> S('a2').formula
u'=A1+3'
>>> S('a2').flatten()
>>> S('a2').formula
u'4'
>>> S('a2').value
4.0

It’s also possible to access value of cells as a 2d-tuple:

>>> S('a1').set_value(2).drag_to('a3').drag_to('b3')
>>> S('a1:b3').data_array
((2.0, 3.0), (3.0, 4.0), (4.0, 5.0))

Acessing Cells

A subgroup of cells can be acessed as arrays:

>>> S('a1:g10')[4]
Sheet1.A5:G5
>>> S('a1:g10')[4][2]
Sheet1.C5
>>> S('a1:g10')[4]['F']
Sheet1.F5
>>> S('a1:g10')[1:2][3:4]
Sheet1.D2:E3
>>> S('a1:g10')[1:2]['B':'F']
Sheet1.B2:F3

As you see, you can access columns either by index number or string. By default, if you first access rows, then columns, but if you access columns using strings, the order does not matter:

>>> S('a1:g10')[1]['F']
Sheet1.F2
>>> S('a1:g10')['F'][1]
Sheet1.F2

Selections can also be iterated:

>>> for cell in S('Sheet1.a1:b10'):
>>>     print str(cell) # will print something like Sheet1.A3,

the example above will iterate over 20 cells, in each iteration cell will hold an OOSheet object with one cell. Exactly the same thing can be obtained with:

>>> for cell in S('Sheet1.a1:b10').cells:
>>>     print str(cell)

You can also iterate over rows or columns:

>>> for row in S('Sheet1.a1:b10').rows:
>>>     print "This loop will be iterated 10 times"

>>> for col in S('Sheet1.a1:b10').columns:
>>>     print "This loop will be iterated twice"

Finding Cells

A selection can be searched for cells matching some criteria:

>>> S('a1:g10').find(lambda cell: cell.string.startswith(u'...'))

The find() method returns an iterator:

>>> for cell in S('a1:g10').find(u'word'):
>>>    # do something with cell

You can also pass a string, integer or float as parameter. Internally, it will be converted to a lambda function depending on type:

>>> S('a1:g10').find(u'word') # same as find(lambda cell: cell.string == u'word')
>>> S('a1:g10').find(17)      # same as find(lambda cell: cell.value == 17)

Simulating user events

Several user events can be simulated: dragging, inserting and deleting rows, cutting and pasting, formatting, undo and redo, saving and quitting.

Dragging does an autofill, as when you drag that little square in the bottom right corner of you selection:

>>> S('a1').value = 1
>>> S('a1').drag_to('a10')
>>> S('a1:a10').drag_to('g10')

Rows can be inserted and deleted. Note that when you insert rows or columns, the selection of the object will grow to include the cells just inserted:

>>> S('a4').insert_row() #insert one row
Sheet1.A4:A5
>>> S('a4').insert_rows(7) #inserts seven rows
Sheet1.A4:A11
>>> S('d1').insert_column()
Sheet1.D1:E1
>>> S('a7').delete_rows()
>>> S('g1').delete_columns()

Cut & paste:

>>> S('a8:b8').cut()
>>> S('a1:4').copy()
>>> S('j5').paste()

The format of a cell can be used to format another cell. Internally, this is done with a “paste special” that copies data from other cell and pastes the format on the current selection:

>>> S('j4').format_as('a2')
(you won't see anything, unless you have previously formatted a2 manually. Try setting its background first)

Undo, redo, save_as and quit:

>>> S().undo()
>>> S().redo()
>>> S().save_as('/tmp/oosheet_sandbox.ods')
>>> S().quit() # this will close LibreOffice

Any LibreOffice event can be generated, not only the ones above. See Recording macros for instructions on how to discover events.

Cascading calls

Most methods can be cascaded. For example:

>>> S('a1').set_value(1).drag_to('a10').drag_to('g10')

This is because these methods returns OOSheet objects. Note that the selection is not necessarily preserved, sometimes it is modified. In the above example, set_value() does not change the selection, but drag_to(‘a10’) expands the selection to ‘a1:a10’, so the whole column is dragged to G10.

The cascading logic is so that the resulting selection should always be as you expect.

Moving, growing and shrinking selections

Sometimes you don’t know exactly where your group of cells is, but know its position relative to a selector you have. In this situation, the selection modificators are handful. With them, you can move, grow or shrink a selection.

Selectors can be moved. For example:

>>> S('sheet1.a1:a10').shift_right()
Sheet1.B1:B10

The result is an OOSheet object with selector Sheet1.B1:B10. The shift_* methods are useful for cascading calls:

>>> S('a1').set_value(1).drag_to('a10').drag_to('g10') #just to setup
>>> S('c1:c10').insert_column().shift_right(2).copy().shift_left(3).paste()

It’s also possible to shift a selector up and down:

>>> S('a1').shift_down(2)
Sheet1.A3
>>> S('a3:c5').shift_up()
Sheet1.A2:C4

You can also shift the selector until a condition is satisfied. The shift_DIRECTION_until() methods are used for this:

>>> S('f1').value = 15
>>> S('a1').shift_right_until(15)
Sheet1.F1

The above example will only work for single cell selectors. For other selectors, you have to specify where to look for a value:

>>> S('g5').string = 'total'
>>> S('a1:10).shift_right_until(row_5 = 'total')
Sheet1.G1:G10
>>> S('a1:z1').shift_down_until(column_g = 'total')
Sheet.A5:Z5

(Note that only one parameter is accepted)

For more complex conditions, you can use lambda functions:

>>> S('g5').string = 'hello world'
>>> S('a1:10').shift_down_until(column_g_satisfies = lambda s: s.string.endswith('world'))
Sheet1.G1:G10

The “s” parameter in lambda function will be a 1 cell OOSheet object.

When looking for cells, you must specify a column if you’re shifting up or down, and a row if right or left. If you specify a column, the row considered will be the last one if you’re going down and the first one if you’re going up, and vice-versa.

Selectors can also be expanded or reduced:

>>> S('a1:10').grow_right()
Sheet1.A1:B10
>>> S('a1:g1').grow_down(2)
Sheet1.A1:G3
>>> S('c3:d4').grow_left()
Sheet1.B3:D4
>>> S('a1:g10').shrink_down()
Sheet1.A1:G9
>>> S('a1:g10').shrink_left()
Sheet1.B1:G10

There are also grow_DIRECTION_until() and shrink_DIRECTION_until() methods, that works similar to shift_until conditions:

>>> S('a1').set_value(1).drag_to('a10').drag_to('g10') #setup

>>> S('a1:b2').grow_right_until(row_2 = 6)
Sheet1.A1:E2
>>> S('a1:e2').shrink_right_until(row_1 = 3)
Sheet1.A1:C2
>>> S('a1:b2').grow_down_until(column_c_satisfies = lambda s: s.value > 10)
Sheet1.A1:B9
>>> S('a1:b9').shrink_down_until(column_c_satisfies = lambda s: s.value < 5)
Sheet1.A1:B2

(Note that the reverse of grow_up is shrink_up and not shrink_down. Authors are not sure which way would be best, but currently shrink_down will remove lines from bottom resulting in an upward moving sensation.)

Moving selections can also be done by arithmetical operations. You can add or subtract tupples of (column, row) to make a shift:

>>> S('a1')
Caixa.A1
>>> S('a1')
Sheet1.A1
>>> S('a1') + (1, 0)
Sheet1.B1
>>> S('a1') + (0, 1)
Sheet1.A2
>>> S('a1') + (2, 3)
Sheet1.C4
>>> S('b5:d7') - (1, 2)
Sheet1.A3:C5

Subtraction can also be used to calculate the shift between two selections. This may be useful after you do a shift_until:

>>> S('b5:d7') - S('a1:c3')
(1, 4)
>>> total_row = S('a1:c10').shift_down_until(col_b = 'Total: ')
>>> cols, rows = total_row - S('a1:c10')

Getting the borders

After shift, grow and shrink operations you may need to get the first or last row or column or your selection. This can be done with first_row, last_row, first_column and last_column properties:

>>> S('a1:g10').first_row
Sheet1.A1:G1
>>> S('a1:g10').last_row
Sheet1.A10:G10
>>> S('a1:g10').first_column
Sheet1.A1:A10
>>> S('a1:g10').last_column
Sheet1.G1:G10

Cells protection

Sheets and cells can be protected and unprotected against editions. When sheet is protected, only unprotected cells can be edited, while if sheet is unprotected, all cells can be modified no matter its protection. Sheet can be protected with a password, so that same password is required to unprotect it.

NOTE: Depending on LibreOffice version, protected cells can be edited by scripts by changing value directly.

To protect and unprotect sheets and cells:

>>> S('Sheet1.a1').protect_sheet()
>>> S('Sheet1.a1').unprotect_sheet()
>>> S('Sheet1.a1').protect_sheet("secretpassword")
>>> S('Sheet1.a1').unprotect_sheet("secretpassword")
>>> S('Sheet1.a1').protect()
>>> S('Sheet1.a1').unprotect()