| View previous topic :: View next topic |
| Author |
Message |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
 Posted: Tue Jul 10, 2007 12:02 pm Post subject: Cell functions in Python scripts (called through Basic) |
 |
|
Thanks to rvc44 who asked for an example how to use c.s.s.script.ArrayWrapper in thread http://www.oooforum.org/forum/viewtopic.phtml?t=59514. I was not aware of such a thing.
With regards to badzil, caresia, ms777, thom314 in thread
http://www.oooforum.org/forum/viewtopic.phtml?t=41162&highlight=omasterscriptprovider
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.
| Code: |
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$"
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("com.sun.star.script.provider.MasterScriptProviderFactory")
g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
endif
getMasterScriptProvider = g_MasterScriptProvider
End Function
|
Now the Python function
| Code: |
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('com.sun.star.script.ArrayWrapper')
# =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:
try:
column.append(val * 2)
except:
column.append(None)
rows.append(tuple(column))
# 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
else:
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):
| Code: |
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
endif
next j
next i
basGetDoubleOf = a()
elseif isNumeric(data) then
basGetDoubleOf = data *2
else
basGetDoubleOf = Null
endif
|
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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Jan 30, 2009 6:46 am Post subject: |
|
|
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 http://code.activestate.com/recipes/52213/
Add this function to the above Basic module:
| Code: |
Function SOUNDEX(s$, optional iLen%)
sURL = URL_Main & "soundex" & URL_Args
oMSP = getMasterScriptProvider()
oScript = oMSP.getScript(sURL)
if isMissing(iLen) then
i = 4
else
i = cInt(iLen)
endif
x = oScript.invoke(Array(s,i),Array(),Array())
SOUNDEX = x
End Function
|
and the code from http://code.activestate.com/recipes/52213/ to Python-module "sheetFunction.py" 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, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
|
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
|
