Page 1 of 1
Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 12:23 am
by LinuksGuru
Hi, OOo folks,
I wrote a program in OOo 3.3 Calc (on SuSE Linux) for audio transformer design (using OOo Basic).
However, due to additional features required, and primitive nature of Basic, this program need to be re-written in Python.
There is a bunch of math function made with Basic, and macros launched with buttons embedded on spreadsheet which analyze data and populate spreadsheets with results.
Since I found no definitive Python/OOo guide, its quite possible I have misunderstood something.
In order to control/manipulate data in OOo from external Python script one should use PyUNO interface. However, I would like to embed all Python functions and macros straight into OOo document (like I have done with basic scripts).
For whatever reason Tools -> Organazie Macros -> Python doesn't allow to create new Python macros ("Create" button is always dimmed).
At first attempt, I created a module (embedded into spreadsheet document), pasted Python scripts written by Danny Brewer (Danny.StringOps.py). One of the function in Danny's library is for example this one:
Code: Select all
def IsAlpha( cChars ):
"""Return True if every char in cChars is a letter."""
return AllCharsInSet( cChars, constLetters() )
However, calling
IsAlpha as standard spreadsheet function results in #NAME? error.
Actually I don't need
IsAlpha mentioned above, I use it just for simple test.
Anyone can help me, how to do it correctly?
Thanks in advance for any suggestion(s).
Re: Question-Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 8:39 am
by Villeroy
You can wrap your functions in an extension. Special extensions are called add-ins when they consist of spreadsheet functions. You've got to implement
http://api.openoffice.org/docs/common/r ... AddIn.html so your functions are shown in the GUI with localized names, help files and GUI descriptions for the function wizard.
Your IsAlpha is obsolete because SEARCH supports (kind of) regular expressions: =SEARCH("^[:alpha:]$" ; A1)
Trivial, quick and dirty functions can be written in Basic. Calc will find them when they are stored in the standard library of the respective document or in the global standard library.
Basic is the underdog of the supported scripting languages but it can call real scripts. Don't ask about performance.
http://www.oooforum.org/forum/viewtopic.phtml?t=59534 (includes a SOUNDEX function in Python copied from a web site and called through a Basic wrapper)
Re: Question-Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 9:51 am
by LinuksGuru
Hi, Villeroy,
Thanks a lot for help.
Do I need to put all my Python scripts into
<user-profile>/Scripts/python/>
or there is a way to embed them into ODF document (like OOo Basic modules)? Or at least, in the some sub-directory of main spreadsheet (e.g. pymacros)?
To be honest, the example shown here
Cell functions in Python scripts (called through Basic)
http://www.oooforum.org/forum/viewtopic.phtml?t=59534
is quite cumbersome to.
Its OK for few functions, but I have a LOT of them.
Re: Question-Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 9:59 am
by Villeroy
Python in Writer:
http://user.services.openoffice.org/en/ ... p?id=12112
Modified Python script provider by this forum's most valued memeber hanya:
http://user.services.openoffice.org/en/ ... 20&t=36538
But that will not solve the problem that sheet cells can not call Python unless you implement it as an add-in.
Re: Question-Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 11:57 am
by LinuksGuru
I have tried to install
ModifiedPythonScriptProvider-0.4.2.oxt
EditorKicker-0.1.0.oxt
on LibreOffice 3.4 Mac (I have Mac in the Office and SuSE Linux at home), and got this error (screenshot attached below).
OK, let's make it simple as 1-2-3. If sheet cells cannot call Python macros directly it is not a problem, its still possible to use OOo Basic wrappers.
I made a spreadsheet called BA-TrafoWiz.sxc, and subdir pymacros, where all Python scripts should go. Let's say OOo Basic cell function CalcTrafo(a1, a2, a3) need to call CalcTrafoPy(a1, a2, a3) which performs actual computing, and returns result (float) r1.
How OOo Basic wrapper should look like?
Do I nee to use soffice "-accept=socket,host=localhost,port=2002;urp;"?
Thanks in advance.
Re: Question-Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 1:23 pm
by Villeroy
Python in UNO context requires Unix line feeds.
Don't know why hanya's scriptprovider does not work for you.
Embedding some code in a document is easy. Inspect my Writer document in your preferred zip tool.
There are some lines in META-INF/manifest.xml and there is a subdirectory Scripts/python/ for the code. The entry in content.xml corresponds to the GUI setting in Tools>Customize:tab:Events.
The Basic wrapper has been linked already. A module with some module variables, a function to get the script provider for Python and a trivial wrapper concatenating a macro-URL and calling the script provider to run the code specified by the URL.
Re: Question-Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 3:07 pm
by hanya
[quote=LinuksGuru]I have tried to install
ModifiedPythonScriptProvider-0.4.2.oxt
EditorKicker-0.1.0.oxt
on LibreOffice 3.4 Mac (I have Mac in the Office and SuSE Linux at home), and got this error (screenshot attached below).[/quote]The office for MacOSX uses python 2.3.x and the error cases at new syntax of the catch statement. I tried to fix it on version 0.4.3.
And you can embed your script into the following way:
http://hermione.s41.xrea.com/pukiwiki/i ... o#j8db162d
Re: Question-Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 3:48 pm
by LinuksGuru
Hi, hanya,
Thanks for help! Do you have samples of Basic wrappers for Python on your web site?
Re: Question-Writing Calc Functions in Python
Posted: Fri Sep 02, 2011 4:57 pm
by Villeroy
LinuksGuru wrote:Hi, hanya,
Thanks for help! Do you have samples of Basic wrappers for Python on your web site?
http://www.oooforum.org/forum/viewtopic.phtml?t=59534
API reference for the script provider and used methods to call routines across language barriers:
http://api.openoffice.org/docs/common/r ... le-ix.html
http://api.openoffice.org/docs/common/r ... vider.html
http://api.openoffice.org/docs/common/r ... cript.html
Code: Select all
REM ***** BASIC *****
REM Keep a global reference to the ScriptProvider, since this stuff may be called many times:
Global g_MasterScriptProvider
REM Specify location of Python script, providing cell functions:
Const URL_Main = "vnd.sun.star.script:sheetFunction.py$"
REM &location=document for document embedded Python:
Const URL_Args = "?language=Python&location=user"
Function getMasterScriptProvider()
if NOT isObject(g_MasterScriptProvider) then
oMasterScriptProviderFactory = createUnoService("com.sun.star.script.provider.MasterScriptProviderFactory")
g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
endif
getMasterScriptProvider = g_MasterScriptProvider
End Function
Function PYTHON_FUNCTION(inputArgs)
REM name of the python function to call:
Const fncName = "Python_Function"
REM concatenate the full URL:
sURL = URL_Main & fncName & URL_Args
oMSP = getMasterScriptProvider()
oScript = oMSP.getScript(sURL)
x = oScript.invoke(Array(data),Array(),Array())
getDoubleOf = x
end Function
Re: Question-Writing Calc Functions in Python
Posted: Thu Sep 08, 2011 3:10 pm
by LinuksGuru
Thanks to everyone for helpful replies !
Anyone knows if NumPy/SciPy libs compiled with OOo/LibreOffice 3.3/3.4 Python by default?
Re: Question-Writing Calc Functions in Python
Posted: Thu Sep 08, 2011 4:57 pm
by Villeroy
The Ubuntu folks use to distribute their own compilation of OOo/LibO linked against the distibution's Python. I'd expect that you can simply install scipy from the repositories and use it in the PyUNO context.
All about PyUNO:
http://wiki.services.openoffice.org/wiki/PyUNO_bridge
Re: Question-Writing Calc Functions in Python
Posted: Thu Sep 08, 2011 11:26 pm
by LinuksGuru
Making OOo/OLib basic wrapper for Python function with several arguments works.
For example:
Code: Select all
function Get_Tau_c(Trafo_L1, TW_r1)
script = GetScript("Get_Tau_c")
Get_Tau_c = script.invoke(Array(Trafo_L1, TW_r1), Array(), Array())
end function
Unfortunately, for some reason it doesn't work for one-argument wrapper:
Code: Select all
function M2Db(mval)
script = GetScript("M2Db")
M2Db = script.invoke(Array(mval), Array())
end function
Error: Argument len differ.
I have tried
Code: Select all
M2Db = script.invoke(Array(mval))
M2Db = script.invoke(mval)
not working either.
What could be wrong?
Re: Question-Writing Calc Functions in Python
Posted: Thu Sep 08, 2011 11:30 pm
by Villeroy
The invoke method takes 3 arrays exactly:
Code: Select all
M2Db = script.invoke(Array(mval), Array(), Array())
Re: Question-Writing Calc Functions in Python
Posted: Wed Sep 14, 2011 6:29 pm
by LinuksGuru
OK, thanks to everyone !
Wrote a bunch functions in Python and created Basic wrappers, seem to be working fine.
Now another stumbling point. How to pass an n-dimensional array from basic wrapper to Python function? Values may be numeric or strings (not both at the same time). How Python will "see" for example Basic array passed as argument? For example, as nested lists in case of 2-dimensional basic array?
Re: Question-Writing Calc Functions in Python
Posted: Wed Sep 14, 2011 6:55 pm
by Villeroy
The oooforum link answers that question by means of a trivial array function getDoubleOf(data)