Evaluating a Calc formula in Basic

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Alex6361
Posts: 24
Joined: Fri Jun 05, 2015 2:15 am

Evaluating a Calc formula in Basic

Post by Alex6361 »

When the Content of a NamedRange is a formula (e.g., "=$Sheet1.$A$1 + $Sheet1.$A$2") how can one obtain the results of evaluating that formula in a Basic macro (i.e., get the same results as if it were entered directly into a cell)?
LibreOffice Version: 5.0.0.5
OpenSUSE Linux 3.11.10-29 & Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Evaluating a Calc formula in Basic

Post by Villeroy »

Put it in a cell and read the cell.
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
Alex6361
Posts: 24
Joined: Fri Jun 05, 2015 2:15 am

Re: Evaluating a Calc formula in Basic

Post by Alex6361 »

I considered that, but hoped that there was a more elegant solution! For, after all, the process of evaluating formulas is one which Calc needs to do often and efficiently. I thought that perhaps there was an API which would expose that method / service to programmers. However, I could not find anything that seemed like it might do the job, short of trying to use the tokens and that looked very ugly.
LibreOffice Version: 5.0.0.5
OpenSUSE Linux 3.11.10-29 & Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Evaluating a Calc formula in Basic

Post by Villeroy »

Abstain completely from spreadsheets if you want to write your own program. Without spreadsheets programming becomes much easier.
Either you use the spreadsheet as your quick&dirty simplified language with its simple types, formulas, visible variables and referencing scheme or you write your own program. Writing programs on top of spreadsheets tends to be highly unproductive.
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: Evaluating a Calc formula in Basic

Post by Villeroy »

I forgot to mention one UNO service because I've never seen any implemention of it:
com.sun.star.sheet.FormulaParser which can be instanciated from a spreadsheet.
and the com.sun.star.sheet.FormulaOpCodeMapper which is somehow related.
Good luck. And don't forget to share your findings with us.
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
Alex6361
Posts: 24
Joined: Fri Jun 05, 2015 2:15 am

Re: Evaluating a Calc formula in Basic

Post by Alex6361 »

I had seen the FormulaParser API, but it appeared to me that the tokens were already readily available. Not really understanding at that point what the tokens were for, it sort of seemed like they might be what Calc actually uses when it evaluates any cell formula. If so, then what I thought I needed was the opposite of a FormulaParser, e.g., a process which takes the tokens and evaluates the results of applying the token OpCodes to the Operands. I was unable to find such an API. But, I don't claim to correctly understand this, anyway!

Which gets me your previous reply containing the advice not to augment spreadsheets with programming. I've spent about 200 hours or more now trying to replicate with Calc what has been working successfully for me with Excel 5.0 for 2 decades. It's been enormously frustrating, and if it weren't for kind people such as you answering my questions (and those of others in this forum), I wouldn't have even gotten as far as I have. When one opens up Calc and starts to use it, it appears to have all the requisite requirements for a spreadsheet program. Indeed, my success in using it for routine stuff gave me confidence I could migrate the more complex workbooks I use regularly over to Calc from Excel 5.0 -- hopefully before that 20-year program stopped working on some new OS I was forced to "upgrade" to. I have very sadly and reluctantly come to the realization -- summarized tersely in your advice above -- that the current state of Calc is inadequate for this task. I had to figure out one work-around after another for missing capabilities or API's in Calc, but got to a point with one of the more complicated workbooks where all of the data shows up in the right places, and the main macros which assist in updating it more or less work. This wokbook employs a few User-Defined-Functions to perform various mathematical analysis on the data, one of which solves its "equations" in an iterative manner. Excel 5.0 performs a complete calculation in about 2 seconds. Calc takes over 4 minutes, pinning one of the 2 processors on this computer to max during that time. Could the UDF's be redesigned or re-coded in C++ as Add-ins to improve this? Maybe so, but I didn't have to do it with Excel, so why do I have to do it for Calc? Your advice to avoid programming with spreadsheets seems to be right-on for Calc, but was not a problem 20 years ago for Microsoft's Excel 5.0 and its VBA. I am ignorant of the full history and management of StarOffice, but having looked under the covers for the past few weeks, it certainly appears to me that this is a suite developed by and for Computer Scientists. The "by" is to be expected, but the "for" certainly limits its usefulness. It's hard for me to believe that the creators actually use the product in more than superficial ways, or they wouldn't be satisfied with the results. Thanks very much for your help to this point, and for the advice to abandon the use of Calc when more than casual programming is necessary. I intend to follow it and stay with Excel for the time being.
LibreOffice Version: 5.0.0.5
OpenSUSE Linux 3.11.10-29 & Windows XP
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Evaluating a Calc formula in Basic

Post by Zizi64 »

I've spent about 200 hours or more now trying to replicate with Calc what has been working successfully for me with Excel 5.0 for 2 decades.
The StarBasic + OpenOffice API never was and never will equivalant or compatible with microsoft Excel VBA.


I think, You MUST choose an another method for achieve this task. I do not know your task, and I do not know why you want use the Basic language as a "Sheet without cells". Why you want to make a "Virtual sheet"? In this case, you must write a full 'Formula interpreter' routine...

Tips:
There are real sheets, and real Cells in the Calc. Use the Cell formulas on the sheets manually: Put all of desired formulas into a helper cell range and choose the one from results by your Basic code (as Villeroy suggested in the first answer of this topic).

Otherwise: You can use the CELLFUNCTIONS (as the SUM() function or others) with the AOO API and StarBasic, but not with the original format (syntax) as in the cell formulas.

Another tip: you can SET, EDIT a Cell formula of a real a cell by your Basic code on a real sheet. Then the result will be changed as you desired.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Evaluating a Calc formula in Basic

Post by Villeroy »

For a virtual sheet you can use get/setDataArray and get/setFormulaArray and use your programming language which should not be StarBasic because StarBasic support for arrays is very poor.
Of course this is just another case of http://en.wikipedia.org/wiki/Cargo_cult_programming
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
Alex6361
Posts: 24
Joined: Fri Jun 05, 2015 2:15 am

Re: Evaluating a Calc formula in Basic

Post by Alex6361 »

Tibor, thanks for your efforts to help here, and in the past! You and Villerory are wonderful treasures to this forum!

I do find it dissapointing, though, to read that it is not Libre/OpenOffice's eventual goal to become comparable to Excel. I certainly realize that the open style of program creation doesn't have the revenue stream of MS Office, and that makes a considerable difference. It's also undoubtedly true that not everything about MS Office is worthy of emmulation.

In the subject of this original posting, I was not seeking to create a "virtual" spreadhseet or cell, but to work around the lack of remote access to local NamedRanges in Calc. There are certainly other ways to do this, and before abandoning the project, I did more or less what Villeroy suggested above -- not programmatically, but by replacing the named range formulas with direct cell references in real cells. What I developed over 20 years with Excel and its VBA was, of course, constained and shaped by the features and capabilities of Excel and its VBA. I didn't expect to exactly carry that over to Calc, and I was prepared for the different manner in which the various objects, services and properties of documents in general, and spreadsheets, in particular, are referenced in Calc. I did expect, though, that there would be API's for basic things like Paste Special, to give just one of many possible examples. If not having such things is due to a lack of resources and/or ability to set priorities for volunteer contributors, then I guess I can understand that. However, if the view of those guiding Libre/OpenOffice is that providing a VBA-like environment where significant spreadsheet users can easily augment their normal user interface with their own programs to enter, manage or analyse spreadsheet data is somehow undesirable, then I believe they will forever turn away potential converts from MS. Microsoft showed that it is possible to do this 20 years ago. And, if I'm not mistaken, StarOffice has been around for at least half that time, if not longer, going back to the days when Sun considered itself a competitor to MicroSoft.

Over the years I have programmed in now ancient languages like Fortran, COBOL and ADA, a little in C++, and most recently in Python. I use programming to solve problems, just like I use a hammer to drive nails. I don't consider myself a programmer or a carpenter, but a problem solver. Because I don't do it regularly, I don't remember the special vocabularies, punctuation, syntax and construction of each programming language. VBA and StarBasic are simple enough that I can remember enough between sessions to be productive and effective. If all I needed to do to make Calc successful for my needs was to reprogram one iterative number cruncher module from Basic to C++, I'd do it. But, the void is far deeper than that.

Villeroy, I had never heard of Cargo Cult Programming before. I won't take offense, though: if it fits, it fits!
I like your up-to-date really far-out photo. That fly-by was an amazing technical accomplishment, with data still coming to analyze for years. (Probably not with Calc spreadsheets, though!)
LibreOffice Version: 5.0.0.5
OpenSUSE Linux 3.11.10-29 & Windows XP
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: Evaluating a Calc formula in Basic

Post by karolus »

Hallo
API's for basic things like Paste Special,
dont think in terms how users describe actions.

Im pretty sure most of things you want to do in Spreadsheet, you only need an pattern like:

Code: Select all

...
data = someCellRange.getDataArray() 'or ..getFormulaArray()'
..
do_something_with( data ) and perform out
..
someCellRange.setDataArray( out )
...

AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Evaluating a Calc formula in Basic

Post by Zizi64 »

Alex6361,
do you want to achieve something similar thing:
Setformula.ods
(20.18 KiB) Downloaded 364 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Evaluating a Calc formula in Basic

Post by Villeroy »

karolus wrote:Hallo
MRI is indispensible.
C++ can do very fast array manipulation. UNO objects can be accessed through C++.

Without any office suite being installed, you may utilize your favourite XML libraries to dump data and formulas from and to ODF spreadsheets.
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
Alex6361
Posts: 24
Joined: Fri Jun 05, 2015 2:15 am

Re: Evaluating a Calc formula in Basic

Post by Alex6361 »

dont think in terms how users describe actions.
I'm a spreadsheet user! So, of course I think first in terms of what I can do with spreadsheets as a user! And until I can figure out which API's will achieve what I want to do, I have no other terminolgy to use.

Karolus, I'm familiar with and have used the constructs you suggested. Get/setDataArray works well, but SetFormulaArray doesn't adjust any formula references, so it is not the same as a PasteSpecial-Formulas-only. I have yet to find an easy way to copy and paste formats. I tried using the dispatcher, but it didn't complain and didn't work. Undoubtedly I didn't set something correctly, but it's hard to trouble-shoot the dispatcher when it gives no feedback.

I'll take a closer look at MRI. I had considered using Python when I began the migration attempt, but read that the PyUno bridge was still experimental, so didn't pursue it. The bottom line, though, is that whether one uses Python, C++, Java or even just StarBasic, doing any programming with the present state of the OpenOffice APIs and documentation thereof is just a lot more work than Excel and VBA, and one gets no extra benefit for that extra effort beyond being free from Microsoft. Villeroy's advice above (and to other users in other responses) is right-on.

Tibor, I'm posting a revised version of the spreadsheet document you offered above. I tried to explain in it what the differences are between that and what I was trying to achieve as a work-around so that I didn't have to completely redesign spreadsheets that have worked well for me in Excel. In the same way that names are important to programmers, names in spreadsheets are also very useful in many ways, and in some applications I use them heavily. Spreadsheets have worked so well for me that I have no XML libraries, let alone any favorite ones!

Thanks to all for your interest , suggestions and encouragement!
Attachments
Setformula_2.ods
(24.96 KiB) Downloaded 345 times
LibreOffice Version: 5.0.0.5
OpenSUSE Linux 3.11.10-29 & Windows XP
brackenhill_mob
Posts: 1
Joined: Thu Mar 07, 2019 6:04 pm

Re: Evaluating a Calc formula in Basic

Post by brackenhill_mob »

I know I'm reviving an ancient thread but today I had exactly the same need to get the value of a named cell which contained a formula pulling data from another spreadsheet. Google threw up this topic and various others around com.sun.star.sheet.FormulaParser which sent me down various rabbit holes from which I struggled to escape! I thought there had to be a simpler way to do this and I believe I've found one. My code is below - not elegant (I've deliberately made it long-winded to aid readability and understanding) but it works well for my needs and if I've made any stupid mistakes, please tell me nicely <grin>. Anyway i hope this helps someone, or starts the process of coming up with a better solution.

What I discovered was that when a cell is blank (no formula, date, string, number etc), the Type is zero so there is nothing to do. If the cell contains a formula, then the evaluation of that formula is saved to the String and Value fields - Value holds the evaluated value of String so if isn't a number it is set to 0. This is what I test for the the nType = 3 code block.

Code: Select all

'
' Get cell reference from a named cell 
'
Function GetCellByName(cCell as String) as String
  GetCellByName = ReplaceString(ThisComponent.NamedRanges.getByName(cCell).getContent(), "", "$")
End Function

Function GetCellContentByName(cCell as String) as Variant
  '
  ' function should get contents regardless of cell type (value, text etc)
  '
  Dim oS, oCell as Object
  Dim a, v as Variant
  Dim nType as Integer
  Dim f, s as String
  Dim d as Double

  a = split(GetCellByName(cCell), ".")
  oS = ThisComponent.Sheets.getByName(a(0))
  oCell = oS.getCellRangeByName(a(1))
  nType = oCell.Type
  d = oCell.getValue()
  s = oCell.getString()
  f = oCell.getFormula()

  '
  ' nType = 0 => empty cell
  '
  if nType > 0 then
    if nType = 1 then		' value
      v = d
    elseif nType = 2 then	' string
      v = s
    elseif nType = 3 then	' formula
      if (d = Val(s)) then
        v = d
      elseif (d = 0) and (s = "") then
        v = f
      else
        v = s
      end if
    end if
  end if

  GetCellContentByName = v
End Function
LibreOffice 6.1 on Ubuntu 18.10
Post Reply