[Calc] An EVAL function for evaluate a function in Basic.

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

[Calc] An EVAL function for evaluate a function in Basic.

Post by Sébastien C »

Hello team,

Because it is very useful to me, I continue to explore my little code snippet here. The establishment of checkboxes becomes very simple since it is obviously a simple copy and paste. For the sake of simplicity, I have just produced a new version that changes the module’s name from “Module1” to “checkBoxes”. This simplifies the setup in other files (and copy/paste checkboxes too).

But above all...

As I am using this thing quite intensely, I have also been able to test its limits. And the first of them is the exclusive use of the mouse. So I said that it would be nice if we can check or uncheck the boxes with the keyboard, for example with the spacebar. For this, a listener type KeyHandler is obviously necessary. I had never played this game, but it is well documented to be no problem.
The goal of the game is to listen the spacebar and, if the selected cell contains a formula with a HYPERLINK function, typical of the checkboxes, make sure that the edition is diverted to:
  • Reconstitute the URL that contains the function.
  • Call the correct procedure, checkBox() or radioButton().
The URL’s reconstitution contained in the HYPERLINK function is not easy ... In the context of the call of the checkboxes, it is still relatively easy because the selection is, by definition, in place on the cell that receives the call from the spacebar. Recovering the “type” of the checkbox can also be done relatively easily, even if I specify, in the comment, that it is, according to my view of things, a bit coded with my feet...

It is quite different for the radio buttons which, in addition to communicating the location of the clicked button, obviously also give the range of cells in which they act. And as I still want the copy/paste is done very easily, I use ADDRESS, ROW and COLUMNS functions.

To recover the formula of a cell is obviously very simple. But part of its result is much more complicated... Here, what I want to recover is the result of the URL passed to HYPERLINK. I would need a function of the type “EVAL” ... that I did not find. I'm coming out of this pitfall by:
  • Taking the complete formula HYPERLINK as a string of characters.
  • Eliminating his head and tail ...
  • Putting this in an empty cell of the spreadsheet, which causes its interpretation.
  • Recovering, finally, the result.
  • Cleaning the cell of its contents immediately.
It works, it is fast enough to be discreet, but it is terribly laborious and very ugly...

We go to the cell with the arrow keys, we press on the spacebar for switches the control in hyperlink form. Set up 500 checkboxes in a column take few seconds. The incremental validation in check or uncheck with the arrow keys and the space bar is ecstasy that no normal control allows... Even the most reluctant to use the spreadsheet cannot fail to admit this obvious...

While I'm very happy that this set works perfectly, even if the spreadsheet is protected, the final cleaning of the cell does not work if the sheet is protected. We are therefore obliged, for the empty cell with temporary content, to hide its contents.

BUT, in any case, it remains that the reading/analysis of the HYPERLINK URL is a pure shame of programming.

Would some of you have ideas for more elegance? I have seen quoted a thousand times the link “www.oooforum.org/forum/viewtopic.phtml?t=58809” ; but we know all that the oooforum.org is dead and buried. Same I know that JavaScript has an eval function; but you understand that JavaScript do not know the functions of Calc...

:crazy: :?: :idea:

Code: Select all

Private myKeyHandler As Object

Const emptyCellForEval = "$A$1"
Const      myFormula_0 = "vnd.sun.star.script:Standard.checkBoxes."
Const      myFormula_1 = "?language=Basic&location=document"


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Listener for check or uncheck a checkbox or a radio button with the space bar.   ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub addKeyHandler
	myKeyHandler = createUnoListener("KeyHandler_", "com.sun.star.awt.XKeyHandler")
	thisComponent.currentController.addKeyHandler(myKeyHandler)
End Sub

Sub KeyHandler_disposing : End Sub

Sub removeKeyHandler
	thisComponent.currentController.removeKeyHandler(myKeyHandler)
End Sub

Function KeyHandler_keyPressed(myKeyEvent As New com.sun.star.awt.KeyHandler) As Boolean
	Dim myFunction As Object 
	Dim  myFormula As String, myAddressCell As String, myType As String, myURL As String

	KeyHandler_keyPressed = False

	' Just ONE cell must be selected.
	If not thisComponent.currentSelection.supportsService("com.sun.star.sheet.SheetCell") Then Exit Function

	myFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
	myFormula = thisComponent.currentSelection.formula

	If myKeyEvent.keyCode = com.sun.star.awt.Key.SPACE Then
	 If inStr(myFormula, myFormula_0 & "checkBox"    & myFormula_1) > 0 Or _
	    inStr(myFormula, myFormula_0 & "radioButton" & myFormula_1) > 0 Then

	  KeyHandler_keyPressed = True

	  Select Case inStr(myFormula, "checkBox?")
	   Case > 0                                                   ' Cell with checkbox.
	    With thisComponent.currentSelection.cellAddress
	     myAddressCell = myFunction.callFunction("ADDRESS", Array((.row + 1), (.column + 1), 1))
	    End With

	    myType = left(getArgumentFromURL(myFormula, "myType"), 1) ' A bit coded with my feets...

	    myURL = myFormula_0 & "checkBox" & myFormula_1 & "&myCell=" & myAddressCell & "&myType=" & myType

    	checkBox(myURL)

	   Case 0                                                     ' Cell with radio button.
	    mid(myFormula,                  2, 9) = ""                ' Delete the word HYPERLINK at start.
	    mid(myFormula, len(myFormula) - 5, 5) = ""                ' Delete the chars ; "" or ; "" at end.

	    With thisComponent.currentController.activeSheet.getCellRangeByName(emptyCellForEval)
	     .formula = myFormula                                     ' Place the new formula in an empty cell.
	        myURL = .string                                       ' It is evaluated and saved right away.
	      .string = ""                                            ' Cleanup.
	    End With

       	radioButton(myURL)
	  End Select

	 End If
	End If
End Function

Function KeyHandler_keyReleased(myKeyEvent As New com.sun.star.awt.KeyHandler) As Boolean
	KeyHandler_keyReleased = False
End Function


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Checkoxes management.                                                            ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub checkBox(myURL As String)
 Dim    mySheet As Object,    myCell As Object
 Dim myCheck(1) As String, myFormula As String

 Select Case cInt(getArgumentFromURL(myURL, "myType"))
  Case 0 : myCheck(0) = "☐" : myCheck(1) = "☒"
  Case 1 : myCheck(0) = "☐" : myCheck(1) = "☑"
  Case 2 : myCheck(0) = "◄" : myCheck(1) = "►"
  Case 3 : myCheck(0) = "M" : myCheck(1) = "F"
 End Select

   mySheet = thisComponent.currentController.activeSheet
    myCell = mySheet.getCellRangeByName(getArgumentFromURL(myURL, "myCell"))
 myFormula = myCell.formula

 If mid(myFormula, (len(myFormula) - 2), 1) = myCheck(0) Then     ' Mid is here a FUNCTION.
    mid(myFormula, (len(myFormula) - 2), 1) = myCheck(1)          ' Mid is here an INSTRUCTION.
 Else
    mid(myFormula, (len(myFormula) - 2), 1) = myCheck(0)          ' Mid is here an INSTRUCTION.
 End If

 myCell.formula = myFormula
End Sub


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Radio buttons management.                                                        ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub radioButton(myURL As String)
 Dim    mySheet As Object,   myCell1 As Object, myCell2 As Object, myGroup As Object
 Dim myRadio(1) As String, myFormula As String
 Dim          i As Integer

 Select Case cInt(getArgumentFromURL(myURL, "myType"))
  '        Unchecked                 Checked                        BE CAREFUL : the character font is WINGDINGS (not UTF8).
  Case 0 : myRadio(0) = chr(61601) : myRadio(1) = chr(61604)      ' U+F0A1 and U+F0A4
                                                                  ' Imagine here other sort of radio buttons...
 End Select

 mySheet = thisComponent.currentController.activeSheet
 myGroup = mySheet.getCellRangeByName(getArgumentFromURL(myURL, "myGroup")).rangeAddress
 myCell1 = mySheet.getCellRangeByName(getArgumentFromURL(myURL, "myCell" )).rangeAddress

 With myGroup
  For i = .startColumn To .endColumn
     myCell2 = mySheet.getCellByPosition(i, .startRow)
   myFormula = myCell2.formula

   If i = myCell1.startColumn Then
    mid(myFormula, (len(myFormula) - 2), 1) = myRadio(1)          ' Mid is here an INSTRUCTION.
   Else
    mid(myFormula, (len(myFormula) - 2), 1) = myRadio(0)          ' Mid is here an INSTRUCTION.
   End If

   myCell2.formula = myFormula
  Next i
 End With
End Sub


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Extracts the values of variables (text) passed in argument with the URL call.    ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Function getArgumentFromURL(sURL$, sName$) As String
 On Error Goto exitErr:

 Dim iStart%, i%, l%, sArgs$, a()
 
 iStart = instr(sURL, "?")
      l = len(sName)

 If (iStart = 0) or (l = 0) Then Exit Function

 sArgs = mid(sURL, iStart + 1)                                    ' sArgs behind "?".

 a() = split(sArgs, "&")

 For i = 0 To uBound(a())
  If instr(1, a(i), sName & "=", 1) = 1 Then                      ' Not case sensitive.
   getArgumentFromURL = Mid(a(i), l + 2)
   Exit for
  Endif
 Next i

 exitErr:                                                         ' Return "".
End Function


' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Just a little demonstration for say that the help is not always absolutely true. ║█
' ║                                                                                  ║█
' ║ The help say: "Tips are always enabled."                                         ║█
' ║ https://help.libreoffice.org/Common/Tips_and_Extended_Tips                       ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
'  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

Sub swichDisplayHelpTip()
 Dim oDisp As Object 
 oDisp = createUnoService("com.sun.star.frame.DispatchHelper") 
 oDisp.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:HelpTip", "", 0, array())
End Sub
Attachments
checkBox20180619.ods
(16.93 KiB) Downloaded 174 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] An EVAL function for evaluate a function in Basic

Post by JeJe »

You can find several Eval functions written in VB6 like this one. Minor adaptation may be needed - often none.

http://ftp.planet-source-code.com/vb/sc ... 6&lngWId=1
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] An EVAL function for evaluate a function in Basic

Post by Villeroy »

eval(str) is a built-in Python function.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: [Calc] An EVAL function for evaluate a function in Basic

Post by Sébastien C »

@ JeJe
Thank you a lot for the link ; it is yet a way. Of course, if I decide to walk on it, I will write just for the functions that I need. Your example speaks about “tokens”... I searched in Xray

Code: Select all

xray thisComponent.currentSelection
and for each cell, there are a property Tokens. It is an array of structure: com.sun.star.sheet.FormulaToken. Type in a cell “=123”, the array contains one element. Type in a other cell “=123+1”, the array contains three elements. Put spaces between “+”, and you will notice that there are accounted for. I absolutely do not know how to make fruit of this thing ... But I imagine that it is not put in place for the small birds... If anyone knows this little music...

@ Villeroy
Thank you too for your answer. I really do not know Python. But do you want to say that the Python eval(str) function, built-in OpenOffice/LibreOffice, can parsing an entire formula of Calc’s cell with references like range cells? As I say above, JavaScript has an eval(str) function. Years ago, I use it in this type of Web page; it worked so simple... for so simple things...
If it is not the case, I think also it is maybe more interesting to run on the way of JeJe, with the combination of the Uno service com.sun.star.sheet.FunctionAccess. Since the Basic, it could have style that should make me fun despite that I understand as well that this type of researches are not exactly your taste ... :lol:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] An EVAL function for evaluate a function in Basic

Post by Villeroy »

With an object oriented programming language (C++, Java, Python, JavaScript, Rexx) you can write Calc add-ins. An add-in is an extension with additional spreadsheet functions.
Python is very easier, far easier than Basic anyway, and it is equipped with dozends of libraries for regular expressions, string manipulation, array manipulation, email, www and machine related stuff. Python handles the same arithmetic operators as Calc/StarBasic. However, with eval and a powerful language it is always possible to pass a malitious command to the eval function.
Have a look at this extension which is extremely powerful by simply redirecting function calls to the Python runtime: viewtopic.php?f=20&t=83856&p=389632
Install the extension and then open the attached test sheet. It is really impressing and simple to program. The most difficult part is how to fabricate a valid extension package.
 Edit: In fact, pystring is one function where you pass a function name plus arguments. The function name is evaluated. 
Since 20 years at least there are extensions for MS Excel which support the full set of formula expressions such as EVAL("VLOOKUP(this;that;3;0)"). This is extremely simple because Excel-VBA has an Evaluate function which works like a formula parser and returns the result. I don't give any thought on how to do this with UNO. Any such function should support localization, relative referencing, all the spreadsheet operators and nested function calls.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] An EVAL function for evaluate a function in Basic

Post by Villeroy »

Instead of an add-in.
Attachments
pyEval.ods
Sheet function calling Basic calling Python
(15.07 KiB) Downloaded 243 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
reikred
Posts: 4
Joined: Thu Oct 11, 2018 5:55 am

Re: [Calc] An EVAL function for evaluate a function in Basic

Post by reikred »

Villeroy wrote:Instead of an add-in.
I downloaded pyEval.ods and loaded in libreoffice. The formulas do not evaluate. Column B says #VALUE!.

"This document contains one Basic module and a Python module."
"The spreadsheet makes use of Basic’s simple function calls without extension package."

So I don't need any extensions? Why do I get #VALUE! then, something is amiss here. Sorry, I am a beginner in oocalc "programming" although decent in csh/sh, C/C++, Perl. Maybe even a little Python if hard pressed. What I dont understand is what I have to do in libreoffice to get the pyEval.ods to "run".
Libreoffice 5.2.7.2 on Fedora 25
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] An EVAL function for evaluate a function in Basic

Post by Villeroy »

Sorry, I forgot how to address an embedded Python macro and while testing this thing, the Basic code pulled the code from my user profile.
FInd the user profile of your LibreOffice.
It might be ~/.config/libeoffice/4/user or ~/.libreoffice/4/user
There you find a folder "Scripts". Add subfolders python/pyCalc to the Scripts folder.
Close my document and open it with a zip tool. Unzip Scripts/SheetFunctions.py to the pyCalc folder in your profile. Now the Basic code finds the Python code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
reikred
Posts: 4
Joined: Thu Oct 11, 2018 5:55 am

Re: [Calc] An EVAL function for evaluate a function in Basic

Post by reikred »

Villeroy wrote:Sorry, I forgot how to address an embedded Python macro and while testing this thing, the Basic code pulled the code from my user profile.
FInd the user profile of your LibreOffice.
It might be ~/.config/libeoffice/4/user or ~/.libreoffice/4/user
There you find a folder "Scripts". Add subfolders python/pyCalc to the Scripts folder.
Close my document and open it with a zip tool. Unzip Scripts/SheetFunctions.py to the pyCalc folder in your profile. Now the Basic code finds the Python code.
Hmm, it did not work for me. No eval, still. #VALUE! only. I tried following the recipe, as follows

Code: Select all

mkdir ~/src/pyEval/
cp -p pyEval.ods ~/src/pyEval/pyEval.ods
cd  ~/src/pyEval
unzip pyEval.ods
mkdir -p ~/.config/libreoffice/4/user/Scripts/python/pyCalc
cp -p ~/src/pyEval/Scripts/python/SheetFunctions.py ~/.config/libreoffice/4/user/Scripts/python/pyCalc
Libreoffice 5.2.7.2 on Fedora 25
Post Reply