Writing Calc Functions in Python

[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).

[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)

[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.

[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.

[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.

[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.

[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

[LinuksGuru]

Hi, hanya,

Thanks for help! Do you have samples of Basic wrappers for Python on your web site?

[Villeroy]

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

[LinuksGuru]

Thanks to everyone for helpful replies !

Anyone knows if NumPy/SciPy libs compiled with OOo/LibreOffice 3.3/3.4 Python by default?

[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

[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?

[Villeroy]

The invoke method takes 3 arrays exactly:

Code: Select all

   M2Db = script.invoke(Array(mval), Array(), Array())

[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?

[Villeroy]

The oooforum link answers that question by means of a trivial array function getDoubleOf(data)