Possible to create my own functions, to use in a cell?

[mascip]

Hi all,
in Google Docs spreadsheets i can create my own Javascript functions, and then call any of my functions from a cell.
And I can use the values from other cells as parameters for a function.

For example i can have the cells A1, A2, A3 as follow :

A1 : 9
A2 : 3
A3 : =my_function(A1,A2)

where my_function() is a Javascript function defined in a separate file.

This doesn't seem possible with OpenOffice Calc. Is it ?
Thank you

[FJCC]

You can write your own functions in OpenOffice Basic. There is a bit about this in the Calc Help section under "functions; user-defined". The Basic code can be stored either in the calc file or at the application level in My Macros.

[mascip]

Thank you FJCC.

Any easy way to do it with Javascript functions ?
It would make life a lot easier for me, as i could just copy-paste what i already have.
Otherwise, i guess i'll have to learn OpenOffice Basic and code it all over again.

[Charlie Young]

Thank you FJCC.

Any easy way to do it with Javascript functions ?
It would make life a lot easier for me, as i could just copy-paste what i already have.
Otherwise, i guess i'll have to learn OpenOffice Basic and code it all over again.

See Using javascript functions in calc, and note especially Bernard Marcelly's caveats.

I have played with this a bit myself, and I'll illustrate some of the problems here.

Here is Basic to wrap a Javascript function to sum an array. I found it necessary to "flatten" the array, that is to put its columns end to end giving a one dimensional version.

Code: Select all

Function JSum(r) As Double
	Dim oSP
	Dim oScript
	Dim s, flatrange
	flatrange = flattenRange(r)	
	oSP = ThisComponent.getScriptProvider()
	oScript = oSP.getScript("vnd.sun.star.script:Library1.JavaSum.js?language=JavaScript&location=document")
	s = oScript.invoke(flatrange,Array(),Array())
	JSum = s
   	
End Function

Function flattenRange(r)

	Dim i as Long
	Dim j as Long
	Dim Size(2) as Integer
	Dim element
	Dim n As Long
	Dim v(0)
	Dim aRow
	Size(1) = UBound(r,1)-LBound(r,1)
	Size(2) = UBound(r,2)-LBound(r,2)
	'v = r(LBound(r),1)
	n = 0
	for i = 0 to Size(1)
		for j = 0 to Size(2)
			ReDim Preserve v(n)
			if isNumeric(r(LBound(r, 1) + i, LBound(r, 2) + j)) then 
				element = r(LBound(r, 1) + i, LBound(r, 2) + j)
			else
				element = 0
			endif
			v(n) = element
			n = n + 1
		Next j
	Next i
			
	flattenRange = v
end function

Here is JavaSum.js, the flattened array is in ARGUMENTS, and eval() is the only way I could find to get the return value back to oScript.invoke (in the Basic), though there may well be something I don't know.

Code: Select all

x = new Array(ARGUMENTS.length);
for(i = 0;i < x.length;i++)
	x[i] = parseFloat(ARGUMENTS[i]);

eval(jSum(x));

function jSum(x)
{
	s = 0;
	for(i = 0;i < x.length;i++)
		s += x[i];
	return s;
}

I have attached a spreadsheet containing this stuff, using it to add random numbers. It will give an error when first loaded, but after it loads you can do Ctrl-Shift-F9 to recalculate, though I think you'll want to save it first so it isn't read only.

[mascip]

Thanks a lot Charlie !
I don't have the time now, will try and make this work on Monday or Tuesday.
Ooooh that's exciting

[karolus]

Hallo
Is there a special reason to do this in JavaScript ?
And of course the same Stuff in Python:

Code: Select all

from itertools.chain import from_iterable as flat
def py_sum(rrange):
    return sum(flat(rrange))

thats all.( you don't need the flattenRange Function in Basic )

Karolus

[Villeroy]

Yes, calling functions through a Basic wrapper is possible. Basic functions are directly callable from the Standard library because Basic is not object oriented. You can not implement any classes in Basic.
The usual way to integrate your functions would be an extension (add-in) where your own classes implement the AddIn service. Then you need some XML glue, wrap everything in an extension package (zip with ending .oxt) and install it. Your functions will appear in the function wizard, you can add localized function names and localized help files.

[Charlie Young]

Hallo
Is there a special reason to do this in JavaScript ?
And of course the same Stuff in Python:

Code: Select all

from itertools.chain import from_iterable as flat
def py_sum(rrange):
    return sum(flat(rrange))

thats all.( you don't need the flattenRange Function in Basic )

Karolus

Obviously the array sum is done just to provide a simple example, and of course you can do it in Python. The key point about the flattening though is that when passing the range to JavaScript, the .js just gets a one dimensional array of strings passed to ARGUMENTS (note the need for parseFloat to convert the strings). Without the flattening then, each row of the range is one messed-up string.

With Python, the flattening is just a convenience since the range is passed as a tuple of tuples. We could also do

Code: Select all

def py_sum(rrange):
    s = 0
    for l in rrange:
       s += sum(l)
    return s
	
g_exportedScripts = py_sum,

and wrap that in Basic like so

Code: Select all

Function PySum(r)
	Dim oMSP
	Dim oScript
	
	oMSP = getMasterScriptProvider()
	oScript = oMSP.getScript("vnd.sun.star.script:PySum.py$py_sum?language=Python&location=user")
	PySum = oScript.invoke(Array(r),Array(),Array())
End Function


Function getMasterScriptProvider()
	Dim g_MasterScriptProvider
	Dim oMasterScriptProviderFactory
	
   if NOT isObject(g_MasterScriptProvider) then
      oMasterScriptProviderFactory = createUnoService("com.sun.star.script.provider.MasterScriptProviderFactory")
      g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
   endif
   getMasterScriptProvider = g_MasterScriptProvider
End Function 

Though yes, Python is pretty easy to use for genuine add-in functions (though this example would be pretty useless, since we could just use SUM). In fact, coincidentally, I've been playing with one that I've been planning to post in code snippets (maybe tomorrow). It could probably use some attention from the resident Pythonians even though it's working.