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

[Inopem]

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 4.2.7.2 on Kubuntu 14.04.

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

Thanks in advance.

[JohnSUN]

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.

[karolus]

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
            28	

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)', ''))

[Inopem]

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.

[karolus]

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…