Ask Your Question

Calc: [Python] Find row based on value of column and return the row's contents [closed]

asked 2015-02-19 19:32:01 +0200

Inopem gravatar image

updated 2020-08-15 23:25:38 +0200

Alex Kemp gravatar image

Hi all.

I've got a spreadsheet in Calc containing people's details. What I need to do is write a Python program (I've got a good grasp of Python, and would prefer to do this in it rather than StarBasic) that can be fed the person's ID number and it will return the person's other details in the spreadsheet (doing this running LO headlessly).

So essentially, how from Python, can I lookup a value in a specific column in a Calc spreadsheet and return the associated details (perhaps only specific ones [based on column number?]) to the Python script to manipulate.

I'd prefer to use the python "uno" library directly, because from what I can see the various higher-level frontends don't have as active development.

I don't know if there's any platform specifics, but I'm using LibreOffice on Kubuntu 14.04.

Also, are there any good guides to writing python interfaces/macros to LO?

Thanks in advance.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-15 23:26:32.802029


For the problem you described, macros are not required. Built-in Сalc's function is enough. If you are interested should look like Python code for any action, simply install extension MRI - it generates code for the specified language.

JohnSUN gravatar imageJohnSUN ( 2015-02-19 21:19:29 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-02-19 22:42:17 +0200

karolus gravatar image

updated 2015-02-20 13:31:10 +0200

export your Spreadsheet as .csv-file.

in this case you can do all you want with less than 30 lines of code, without running a 200 MB Office-suite in headless-mode.

some simple Examples for LO-python running via GUI

# get the actual Document
doc = XSCRIPTCONTEXT.getDocument()
# the sheet`container` 
sheets = doc.Sheets
# index-access
sheet = sheets.getByIndex(0) #first sheet
# name-access
othersheet = sheets.getByName( 'Tabelle2' )

consider we have data in sheet.A1:C8 like:

20.02.15    1   Entry_1
21.02.15    2   Entry_2
22.02.15    3   Entry_3
23.02.15    4   Entry_4
24.02.15    5   Entry_5
25.02.15    6   Entry_6
26.02.15    7   Entry_7

The 28 in B8 is derived by Formula `=sum(B1:B7)'

cellrange = sheet.getCellRangeByName('A1:C8')
print( cellrange.DataArray )

# literally in python
((42055.0, 1.0, 'Entry_1'),
(42056.0, 2.0, 'Entry_2'),
(42057.0, 3.0, 'Entry_3'),
(42058.0, 4.0, 'Entry_4'),
(42059.0, 5.0, 'Entry_5'),
(42060.0, 6.0, 'Entry_6'),
(42061.0, 7.0, 'Entry_7'),
('', 28.0, ''))

the Dates are here the days since LO-Zero-Date 1899-12-30 as float's same thing as FormulaArray instead DataArray

# all Data converted into Strings, also the real Formula from B8
(('42055', '1', 'Entry_1'),
('42056', '2', 'Entry_2'),
('42057', '3', 'Entry_3'),
('42058', '4', 'Entry_4'),
('42059', '5', 'Entry_5'),
('42060', '6', 'Entry_6'),
('42061', '7', 'Entry_7'),
('', '=SUM(B1:B7)', ''))
edit flag offensive delete link more


The spreadsheet is updated a lot, so the script would probably have to start up LO to export the spreadsheet to .csv, anyway.

As a learning experience, I would rather use LibreOffice to do the footwork. When it comes to anything beyond trivial I do in the future, "export to csv" likely means I'd have to implement spreadsheet features on the Python end. So I'd rather not go that way.

Inopem gravatar imageInopem ( 2015-02-19 23:23:07 +0200 )edit

While you run LO headless, the spreadsheet cannot "updated a lot" except through some scripting, but in that case you should also be able to put the data directly....

karolus gravatar imagekarolus ( 2015-02-20 12:43:00 +0200 )edit

Question Tools

1 follower


Asked: 2015-02-19 19:32:01 +0200

Seen: 1,556 times

Last updated: Feb 20 '15