[Solved] Can a Basic Function Use Python to Return a Value?

[Steve R.]

I am exploring how I can use Python with Base. Before, I go further, can Python interact with a Basic function? With Base, you can write a Basic function to export a value to the function and to return a value. If that function is written in Python instead of Basic, can you still pass a value to the function and have a value returned?

PS: See the post of July 31, 2015.

[Villeroy]

SONDEX for Calc called through Basic:

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 the cell functions:
Const URL_Main = "vnd.sun.star.script:sheetFunctions.py$"
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 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

SOUNDEX for Python taken from here: http://code.activestate.com/recipes/52213/ and wrapped in macro module sheetfunctions.py

[Steve R.]

Thanks. I will experiment with it. It will solve many of the questions that I had previously posed. Between my prior questions and this one, I believe that I am getting a usable understanding of how Python and Base can interact.

Execute a Python Program From Base

[Villeroy]

Basic functions in the "Standard" libraries can be be used directly as spreadsheet functions. Any other spreadsheet functions in other languages need to be distributed as extensions. The experimental Basic-Python bridge may be useful when you want to test arbitrary third-party functions in a spreadsheet without building an extension.
Normally, nobody should ever need to call a Python routine like this, particularly when using Base. Using Base you would drop the code in your module and the module in the Script/python folder and use form events. It is possible to wrap Python code within a form document too. I don't know if Python works embedded in a Base document. I avoid Base macros like pestilence. Macros driven "solutions" have a strong tendency to waste so much time that they can not be considered as useful anymore.

[Steve R.]

A while back I posted: [Solved] How to Determine Free Space on a USB Flash Drive. You provided a sample Python script to solve that issue. At the time, I was trying to get my program just to work so I went with a brute force solution. As the program works, I can now spend time enhancing it. I believe that I am now in a position to begin incorporating the samples that you have generously provided. Thanks very much.

[Villeroy]

Did I write that? Well, I didn't know anything better.
Working with HSQL (again: Base is NOT a database while being a lousy development environment) you can expand the database engine with user-defined Java functions. So you can write a query like this

Code: Select all

SELECT *, getFreeSpace('D:') AS "Free Space" FROM "Somewhere"

And when you google for python +jdbc you find some interesting stuff beyond office suites.

[Steve R.]

My SQL and Python skills, as you can tell, are virtually non-existent. Thanks once again for your guidance.

[Steve R.]

I have "completed" the Science Fiction Magazine Database project. At least until the hidden flaw turns up. Some screen shots are available here. Now that I am "free", I can continue to look into learning Python and expanding my programming capabilities. Thank-you for your assistance.

[Steve R.]

The answer is "yes". Doug posted the answer on AskLibreOffice. Based on the advice provided by Doug I was able to develop the rudimentary code below.

Basic Function below:

Code: Select all

Function dblTestPython(dblValue) As Double
		On Error Goto HandleError1001 
		dim a(0), b(0), c(0) as variant	
		a(0)= Forms("MainForm").Controls("Field1").value
		scpr = ThisComponent.getScriptProvider
		scmod = scpr.getScript("vnd.sun.star.script:call_me.py$call_me?language=Python&location=user")
		returnFromPython = scmod.invoke(a,b,c)
		print returnFromPython		
		Exit Function
	HandleError1001:
       	resume next
End Function

Python Script called by the Basic function above:

Code: Select all

import os
def call_me(a):
	z = int(a) * 5
	return z

Please be aware that this is rudimentary experimental code for the sole purpose of verifying that Python can return a value to Basic.