In Excel there is a Eval function for evaluating expressions given as String. Is there a way for achieve a goal similar to that one in LO Calc? For example, an user is able to evaluate expressions like tan(50)+sin(90)/4
In Excel, the evaluation of arbitrary expressions is implemented through the Application.Evaluate method.
In LibreOffice you can use the corresponding techniques from the Python language.
LO Basic language function can call a function written in Python.
As far as I remember, VBA Eval can also evaluate formula expressions. IMHO, there is no way to do this with the UNO API.
F9 partially implements this functionality for you.
Additionally, as @Villeroy pointed out in the same discussion, the Function Wizard allows you to see all the steps in a formula’s calculation.
By the way, your formula tan(50)+sin(90)/4 will not return the result you expect. You mean that 50 and 90 are degrees? Calc’s trigonometric functions expect arguments in radians. So, =TAN(RADIANS(50))+SIN(RADIANS(90))/4
So, users are relegated to Python scripts.
The central point is if the “expressions” shall be allowed to contain references. If so I cannot imagine a solution.
Otherwise there is a (highly inefficient) way to do it with the help of an additional (hidden?) Calc model or -accepting a few restrictions- a temporary or dedicated additional sheet.
Could somebody tell me if Python actually knows how to handle Calc expressions or which Calc services it uses in what way for the purpose?
The expression can have variables. So an expression like x+7*y would be accepted.
I have to leave the thread. No clue.
1 Like
def evaluate(s):
try:
r = eval(s)
except:
r = None
return r
Go to your profile folder, subfolder Scripts, create a folder python with subfolder pyCalc and save the above text as SheetFunctions.py.
Create a Basic macro in the Standard library of your document or under My Macros.
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:pyCalc|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
Function EVAL(s)
sURL = URL_Main & "evaluate" & URL_Args
oMSP = getMasterScriptProvider()
oScript = oMSP.getScript(sURL)
x = oScript.invoke(Array(s),Array(),Array())
EVAL = x
End Function
Enter some expression in a cell and try =EVAL(A1).
Disclaimer: This is not the regular way to integrate cell functions written in Python. It is just a simple demo calling a Python function through the simplified StarBasic interface which allows to call Basic functions in Standard lib from sheet cells.
2 Likes
With this approach, can we use variables in a string expression?
You can introduce parameters on sheet cell level.
pyEval.ods (17.1 KB)
Apache OpenOffice Community Forum - [Calc][oxt] A function for all python string methods - (View topic) introduces an extension package with a sheet function providing the string functions of Pythons standard library. This the way how Eval should be implemented.
1 Like
Understood. In short, variables must be provided in cells and stored in a spreadsheet. The solution involves:
- Parsing the input string
- Put variables in corresponding cells
- Invoke Python function of choise
I think this solution is unrelated to my issue. But, as far I can see, there is no easy solution to the problem.
on the contrary … there is a stupid simple solution:
put a = equal_sign in front of your expression
Ok, so if I put a equal sign in front of the expression I can evaluate x + 2y - cos(60) using LO Basic. I don’t know how to do that, please, show the working solution.
your topic is tagged with calc but not with Basic !