Forum at OOoForum.orgThe Forum
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Cell functions in Python scripts (called through Basic)

Post new topic   Reply to topic Forum Index -> Code Snippets
View previous topic :: View next topic  
Author Message
Super User
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Jul 10, 2007 12:02 pm    Post subject: Cell functions in Python scripts (called through Basic) Reply with quote

Thanks to rvc44 who asked for an example how to use c.s.s.script.ArrayWrapper in thread I was not aware of such a thing.
With regards to badzil, caresia, ms777, thom314 in thread
This is a Basic module which can provide user defined functions. It passes over the job to a Python script.
Sheet functions which want to be usable as array functions, need to return a plain 2D-array rather than a list-of-lists array as used with get/setDataArray(ListOfList). Python can not handle multidimensional arrays. All the built-in types of arrays, which may be nested.
Without the c.s.s.script.ArrayWrapper the Python can not return a valid array.
First the Basic code, called by the sheet cell(s). Unfortunately we can not pass variable arrays like =PYFUNCTION("fncName";{arg1;arg2;...})
But this is reported to be changed in next stable version.

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 = "$"
Const URL_Args = "?language=Python&location=user"
Function getDoubleOf(data)
   sURL = URL_Main & "getDoubleOf" & URL_Args
   oMSP = getMasterScriptProvider()
   oScript = oMSP.getScript(sURL)
   x = oScript.invoke(Array(data),Array(),Array())
   getDoubleOf = x
end Function
Function getMasterScriptProvider()
   if NOT isObject(g_MasterScriptProvider) then
      oMasterScriptProviderFactory = createUnoService("")
      g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
   getMasterScriptProvider = g_MasterScriptProvider
End Function

Now the Python function

import uno

def getDoubleOf(data):
    '''Simple demo-function duplicating plain cell values 1->2, "A"->"AA"
    Thanks to the c.s.s.script.ArrayWrapper it works in array context as well. To be called through a StarBasic wrapper.'''
    # cheap msgbox in scripting context:
    # raise Exception(repr(data))
    if hasattr(data[0], '__iter__'):
        # two lists:
        rows = []
        wrapper = uno.createUnoStruct('')
        # =A1:B2 passes a one-based array to a Basic function. Let's assume the same here:
        # (Apparently it makes no difference if IsZeroIndex or not)
        wrapper.IsZeroIndex = False
        for row in data:
            column = []
            for val in row:
                    column.append(val * 2)
        # returning the mere list of list fails:
        # return tuple(rows)
        # here comes the wrapper into play:
        wrapper.Array = tuple(rows)
        return wrapper
    elif hasattr(data, '__abs__'):
        return data *2
        return None

Remark for non-Pythoniacs:
Contrary to an extensible type [list], a (tuple) is like a "frozen" array. The python bridge passes and handles tuples only. We convert lists to tuples by built-in function tuple(list)

This is the same stuff in Basic (without the side effect of doubling strings):

Function basGetDoubleOf(data)
if isArray(data) then
   iLB1 = lBound(data(),1)
   iUB1 = uBound(data(),1)
   iLB2 = lBound(data(),2)
   iUB2 = uBound(data(),2)
   Dim a(iLB1 to iUB1, iLB2 to iUB2)
   for i = iLB1 to iUB1
      for j = iLB2 to iUB2
         v = data(i, j)
         if isNumeric(v) then
         a(i, j) = v
      next j
   next i
   basGetDoubleOf = a()
elseif isNumeric(data) then
   basGetDoubleOf = data *2
   basGetDoubleOf = Null

Both functions seem to have the same problem in array context. First they return Err:522 (circular reference), after a hard recalculation there remain two errors on top of the array until we change something in the source range.
Back to top
View user's profile Send private message
Super User
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Jan 30, 2009 6:46 am    Post subject: Reply with quote

Just a more useful example. There are reasons why no spreadsheet comes with a built-in soundex-function, but every database engine comes with some implementation of soundex. Even Base's integrated HSQLDB hasa one.
Anyway, people are addicted to spreadsheets and there is a smart and crispy soundex implementation found at

Add this function to the above Basic module:

Function SOUNDEX(s$, optional iLen%)
   sURL = URL_Main & "soundex" & URL_Args
   oMSP = getMasterScriptProvider()
   oScript = oMSP.getScript(sURL)
   if isMissing(iLen) then
      i = 4
      i = cInt(iLen)
   x = oScript.invoke(Array(s,i),Array(),Array())
   SOUNDEX = x
End Function

and the code from to Python-module "" in your <user-profile>/Scripts/python/
Hint: Writer can read and write plain text with Unix line-feeds (LF) using file type "Encoded Text (*.txt)". Paste the code snipped as unformatted text.
Rest in peace,
Get help on
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic Forum Index -> Code Snippets All times are GMT - 8 Hours
Page 1 of 1

Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group