Embedding a Spreadsheet

General FreeBASIC programming questions.
jdebord
Posts: 529
Joined: May 27, 2005 6:20
Location: Limoges, France
Contact:

Re: Embedding a Spreadsheet

Postby jdebord » Jun 30, 2020 6:08

dbickin wrote:Unfortunately, all of the FB parsers I have come across have made the tacit assumption that the formula is going to return a number and that is arguments are going to be numbers. Not true for my problem domain. Actually, I don't think I've seen any that can have the argument of a function, be another function, or even allow more than 1 argument to a function.


TCC (Tiny C compiler) may be useful here since it compiles "on the fly" and can be run from FB :

viewtopic.php?f=14&t=24699&p=257935&hilit=tcc#p257935
TJF
Posts: 3599
Joined: Dec 06, 2009 22:27
Location: N47°, E15°
Contact:

Re: Embedding a Spreadsheet

Postby TJF » Jun 30, 2020 7:58

paul doe wrote:would become:

Code: Select all

1 2 sum

And =SUM(b2:c4) would become something like

Code: Select all

b2 b3 sum b4 sum c2 sum c3 sum c4 sum
Fetching the variables from spreadsheed cells is the feature that needs to be implemented in existing parsers (and saving the result). This shouldn't be complicated, since the variable parser is always and only at the lowest level of the recursive parsing chain.

Regards
paul doe
Posts: 1255
Joined: Jul 25, 2017 17:22
Location: Argentina

Re: Embedding a Spreadsheet

Postby paul doe » Jun 30, 2020 10:44

TJF wrote:
paul doe wrote:would become:

Code: Select all

1 2 sum

And =SUM(b2:c4) would become something like

Code: Select all

b2 b3 sum b4 sum c2 sum c3 sum c4 sum
Fetching the variables from spreadsheed cells is the feature that needs to be implemented in existing parsers (and saving the result). This shouldn't be complicated, since the variable parser is always and only at the lowest level of the recursive parsing chain.

Regards

Ah, indeed. But, you can even extend the syntax a little, to allow for variadic calls:

Code: Select all

( 1 2 3 4 5 ) sum

In Forth, parens are for comments, but this kind of syntax is a little less cumbersome. Regarding cells, again the syntax could be extended:

Code: Select all

( :b1 :c3 :b6 ) sum

That is, all cell references start with a colon. Also:

Code: Select all

( :b4;b8 :c7;c12 ) sum

Could be used to express ranges. The simplicity of the syntax (in Forth, the only valid separator is whitespace) allows for a lot of variation.
TJF
Posts: 3599
Joined: Dec 06, 2009 22:27
Location: N47°, E15°
Contact:

Re: Embedding a Spreadsheet

Postby TJF » Jun 30, 2020 12:56

In the
paul doe wrote:... Reverse Polish Notation ..
there's no syntax to extend. It contains only values and operators/functions. The brackets get evaluated and eliminated by the parser.

The RPN always starts with a value, and always ends by an operator/function. An operator (like '+') needs two values, and a function (like 'ABS()') only one.

I was a bit vague in my example. The parser should generate from a formular like =3.1*SUM(b2:c4)
  • a value stack

    Code: Select all

    V0 = b2
    V1 = b3
    V2 = b4
    V3 = c2
    V4 = c3
    V5 = c4
    V6 = 3.1
  • and the RPN stack

    Code: Select all

    0 1 + 2 + 3 + 4 + 5 + 6 *
After parsing the solver loads the main register from the value stack at position 0, and the second register from the value stack at position 1. On the main register it performs the operation += second register, so that the result is in the main register. Then it loads the second register from the value stack at position 2 and performs the second += operation, ... At the end it loads the constant 3.1 in to the second register from the value stack at position 6 and performs the *= operation. The final result is in the solvers main register.
dodicat
Posts: 6644
Joined: Jan 10, 2006 20:30
Location: Scotland

Re: Embedding a Spreadsheet

Postby dodicat » Jun 30, 2020 14:04

Here is a sum function (line 142) and I have incorporated sum into an old eval.

Code: Select all

 

#include "crt.bi"
#include "string.bi"

Function tally(somestring As String,partstring As String) As Integer
    Dim As Integer i,j,ln,lnp,count,num
    ln=Len(somestring)
    lnp=Len(partstring)
    count=0
    i=-1
    Do
        i+=1
        If somestring[i] <> partstring[0] Then continue do
        If somestring[i] = partstring[0] Then
            For j=0 To lnp-1
                If somestring[j+i]<>partstring[j] then continue do
            Next j
        End If
        count+=1
        i=i+lnp-1
    Loop Until i>=ln-1
    Return count
End Function

Function findAndReplace(original As String , find As String , replace As String) As String
    If Len(find) = 0 Then Return original
    Var t = tally(original,find)               'find occurencies of find
    Dim As long found, n, staid,m
    Var Lf = Len(find) , Lr = Len(replace) , Lo = Len(original)
    Dim As long x = Len(original) - t * Lf + t * Lr 'length of output string
    dim As String res = String(x,0)            'output string
    Do
        st:
        If original[n] = find[0] Then            'got a possible
            For m = 0 To Lf - 1
                If original[n + m] <> find[m] Then Goto lbl 'nope
            Next m
            found = 1                            'Bingo
        End If
        If found Then
            For m = 0 To Lr - 1
                res[staid] = replace[m]          'load the replacerment
                staid += 1
            Next m
            n += Lf
            found = 0
            goto fin
        End If
        lbl:
        res[staid] = original[n]
        staid += 1
        n += 1
        fin:
    Loop Until n >= Lo
    Return res
End Function


#macro cprint(stuff)
Open Cons For Output As #1
Print #1, (stuff) '& "  ";
Close #1
#endmacro

Dim Shared e_input    As String
Dim Shared e_tok      As String
Dim Shared e_spelling As String
Dim Shared e_error    As Integer
dim shared as string summing

'=======================================================
'==============  PARSER START  ==================================
Function SEC(Byval x As Double) As Double
    SEC = 1 / Cos(x)
End Function

Function COSEC(Byval x As Double) As Double
    COSEC = 1 / Sin(x)
End Function

Function COT(Byval x As Double) As Double
    COT = 1 / Tan(x)
End Function

Function ARCSEC(Byval x As Double) As Double ''''''
    ARCSEC = Atn(x / Sqr(x * x - 1)) + Sgn((x) -1) * (2 * Atn(1))
End Function

Function ARCCOSEC(Byval x As Double) As Double
    ARCCOSEC = Atn(x / Sqr(x * x - 1)) + (Sgn(x) - 1) * (2 * Atn(1))
End Function

Function ARCCOT(Byval x As Double) As Double
    ARCCOT = Atn(x) + 2 * Atn(1)
End Function

Function sech(Byval x As Double) As Double
    sech = 2 / (Exp(x) + Exp(-x))
End Function

Function cosech(Byval x As Double) As Double
    cosech = 2 / (Exp(x) - Exp(-x))
End Function

Function coth(Byval x As Double) As Double
    coth = (Exp(x) + Exp(-x)) / (Exp(x) - Exp(-x))
End Function

Function arcsinh(Byval x As Double) As Double
    arcsinh = Log(x + Sqr(x * x + 1))
End Function

Function arccosh(Byval x As Double) As Double
    arccosh = Log(x + Sqr(x * x - 1))
End Function

Function arctanh(Byval x As Double) As Double
    arctanh = Log((1 + x) / (1 - x)) / 2
End Function

Function arcsech(Byval x As Double) As Double
    arcsech = Log((Sqr(-x * x + 1) + 1) / x)
End Function

Function arccosech(Byval x As Double) As Double
    arccosech = Log((Sgn(x) * Sqr(x * x + 1) +1) / x)
End Function

Function arccoth(Byval x As Double) As Double
    arccoth = Log((x + 1) / (x - 1)) / 2
End Function

Function HAVERSINE(Byval x As Double) As Double
    HAVERSINE = (Sin(x/2))^2
End Function

function pie(byval x as double=1) as double
    return (4*atn(1))*x
end function

Function sum(n As String) As Double
  #macro summer(z,a...)
  Scope
    Dim As Double d
    Dim As Long p=1,i
    Do
      i=Instr(p,a,",")
      d+=Val(Mid(a,p))
      p=i+1
    Loop Until i=0
    z=d
  End Scope
  #endmacro
  Dim As Double x
  summer(x,n)
  Return x
End Function


Function e_function(Byref fun As String,Byval arg As Double) As Double
    Dim n As Double
   
    Select Case Lcase(fun)
    Case "abs": n = Abs(arg)
    Case "atn": n = Atn(arg)
    Case "cos": n = Cos(arg)
    Case "exp": n = Exp(arg)
    Case "ezp": n = Exp(arg)
    Case "fix": n = Fix(arg)
    Case "int": n = Int(arg)
    Case "log": n = Log(arg)
    Case "rnd": n = Rnd(arg)
    Case "sgn": n = Sgn(arg)
    Case "sin": n = Sin(arg)
    Case "sqr": n = Sqr(arg)
    Case "tan": n = Tan(arg)
    Case "haversine":n=haversine(arg)
    Case "cosec":n=cosec(arg)
    Case "sec":n=sec(arg)
    Case "cot": n=cot(arg)
    Case "asin":n=Asin(arg)
    Case "acos":n=Acos(arg)
    Case "atn":n=Atn(arg)
    Case "arcsec":n=arcsec(arg)
    Case "arccosec":n=arccosec(arg)
    Case "arccot":n=arccot(arg)
    Case "sinh":n=sinh(arg)
    Case "cosh":n=cosh(arg)
    Case "tanh":n=tanh(arg)
    Case "sech":n=sech(arg)
    Case "cosech":n=cosech(arg)
    Case "coth":n=coth(arg)
    Case "arcsinh":n=arcsinh(arg)
    Case "arccoth":n=arccoth(arg)
    Case "arctanh":n=arctanh(arg)
    Case "arcsech":n=arcsech(arg)
    Case "arccosech":n=arccosech(arg)
    Case "pi"      :n=pie(arg)
    case "sum"     :n=sum(summing)
    Case Else
        If Not e_error Then
            'Locate 1,1
            Print "UNDEFINED FUNCTION " + fun
            Print
            e_error = -1
        End If
    End Select
    e_function = n
End Function

Sub e_nxt()
    Dim is_keyword As Integer
    Dim c As String
    e_tok = ""
    e_spelling = ""
    Do
        c = Left(e_input, 1)
        e_input = Mid(e_input, 2)
    Loop While c = " " Or c = Chr(9) Or c = Chr(13) Or c = Chr(10)
   
    Select Case Lcase(c)
   
    Case "0" To "9", "."
        e_tok = "num"
        Do
            e_spelling = e_spelling + c
            c = Left(e_input, 1)
            e_input = Mid(e_input, 2)
        Loop While (c >= "0" And c <= "9") Or c = "."
        e_input = c + e_input
       
    Case "a" To "z", "_"
        Dim As Integer is_id
        e_tok = "id"
        Do
            e_spelling = e_spelling + c
            c = Lcase(Left(e_input, 1))
            e_input = Mid(e_input, 2)
            is_id = (c >= "a" And c <= "z")
            is_id = is_id Or c = "_" Or (c >= "0" And c <= "9")
        Loop While is_id
        e_input = c + e_input
        is_keyword = -1
        Select Case Lcase(e_spelling)
        Case "and"
        Case "eqv"
        Case "imp"
        Case "mod"
        Case "not"
        Case "or"
        Case "xor"
        Case Else: is_keyword = 0
        End Select
        If is_keyword Then
            e_tok = Lcase(e_spelling)
        End If
       
    Case "<", ">"
        e_tok = c
        c = Left(e_input, 1)
        If c = "=" Or c = ">" Then
            e_tok = e_tok + c
            e_input = Mid(e_input, 2)
        End If
       
    Case Else
        e_tok = c
    End Select
   
    If e_spelling = "" Then
        e_spelling = e_tok
    End If
End Sub

Sub e_match (Byref token As String)
   
    If Not e_error And e_tok <> token Then
        'Locate 1,1
        if len(summing)=0 then
        Print "EXPECTED " + token + ", got '" + e_spelling + "'"
        e_error = -1':end
        end if
    End If
    e_nxt()
End Sub

Function e_prs (Byval p As Integer) As Double
    Dim n   As Double
    Dim fun As String
    If e_tok = "num" Then
        n = Val(e_spelling)
        e_nxt()
    Elseif e_tok = "-" Then
        e_nxt()
        n = -e_prs(12)   ''   11 before
    Elseif e_tok = "not" Then
        e_nxt()
        n = Not e_prs(6)
    Elseif e_tok = "(" Then
        e_nxt()
        n = e_prs(1)
        e_match(")")
    Elseif e_tok = "id" Then
        fun = e_spelling
        e_nxt()
        e_match("(")
        n = e_prs(1)
        e_match(")")
        n = e_function(fun, n)
    Else
        If Not e_error Then
            'Locate 1,1
            Print "syntax error, at '" + e_spelling + "'"
            e_error = -1':end
        End If
    End If
   
    Do While Not e_error
        If     p <= 11 And e_tok = "^"   Then
            e_nxt(): n = n ^ e_prs(12)
        Elseif p <= 10 And e_tok = "*"   Then
            e_nxt(): n = n *   e_prs(11)
        Elseif p <= 10 And e_tok = "/"   Then
            e_nxt(): n = n /   e_prs(11)
        Elseif p <= 9  And e_tok = "\"   Then
            e_nxt(): n = n \   e_prs(10)
        Elseif p <= 8  And e_tok = "mod" Then
            e_nxt(): n = n Mod e_prs(9)
        Elseif p <= 7  And e_tok = "+"   Then
            e_nxt(): n = n +   e_prs(8)
        Elseif p <= 7  And e_tok = "-"   Then
            e_nxt(): n = n -   e_prs(8)
        Elseif p <= 6  And e_tok = "="   Then
            e_nxt(): n = n =   e_prs(7)
        Elseif p <= 6  And e_tok = "<"   Then
            e_nxt(): n = n <   e_prs(7)
        Elseif p <= 6  And e_tok = ">"   Then
            e_nxt(): n = n >   e_prs(7)
        Elseif p <= 6  And e_tok = "<>"  Then
            e_nxt(): n = n <>  e_prs(7)
        Elseif p <= 6  And e_tok = "<="  Then
            e_nxt(): n = n <=  e_prs(7)
        Elseif p <= 6  And e_tok = ">="  Then
            e_nxt(): n = n >=  e_prs(7)
        Elseif p <= 5  And e_tok = "and" Then
            e_nxt(): n = n And e_prs(6)
        Elseif p <= 4  And e_tok = "or"  Then
            e_nxt(): n = n Or  e_prs(5)
        Elseif p <= 3  And e_tok = "xor" Then
            e_nxt(): n = n Xor e_prs(4)
        Elseif p <= 2  And e_tok = "eqv" Then
            e_nxt(): n = n Eqv e_prs(3)
        Elseif p <= 1  And e_tok = "imp" Then
            e_nxt(): n = n Imp e_prs(2)
        Else
            Exit Do
        End If
    Loop
    e_prs = n
End Function

Function eval(Byref sp As String ) As Double
    summing=""
    if instr(lcase(sp),"sum") then
        summing=ltrim(sp,"sum("):summing=rtrim(summing,")")
    end if
    Dim As Double value
    e_error = 0
    e_input = sp
    e_nxt()
    value = e_prs(1)
    If Not e_error Then Return value else e_error=0
End Function

function fn(x as long) as long
    return x
end function


print eval("sum(3,3,6,6,9,3)")
print sum("3,3,6,6,9,3")

dim as string g
dim as double check
for n as double=1 to 500 step .702
    g+=str(n)+","
    check+=n
next
'print g

print eval("sum("+g+")"),sum(g),check
print "Done"
sleep
   
sleep
 

I have never used spreadsheets, I don't know how functions are represented in them.
sorry.
srvaldez
Posts: 2482
Joined: Sep 25, 2005 21:54

Re: Embedding a Spreadsheet

Postby srvaldez » Jun 30, 2020 15:24

perhaps this might be of some value https://wiki.freepascal.org/RPN_Formula ... preadsheet
BasicCoder2
Posts: 3576
Joined: Jan 01, 2009 7:03
Location: Australia

Re: Embedding a Spreadsheet

Postby BasicCoder2 » Jun 30, 2020 17:06

dodicat wrote:I have never used spreadsheets, I don't know how functions are represented in them. sorry.

How I would imagine it is as a 2d array of values. It can assume a cell contains an expression and the value in that cell will be the result of the evaluation of that expression. A simple expression might be just a value like 4.5 or "hello" or something more complex like =C1 + D1 where C1 and D1 are the labels for the variable in the cells at locations C1 and D1. A spreadsheet array can be displayed as an array of edit boxes. If you type an expression into one of the edit boxes the resulting value will be displayed. You can look at the actual expression rather than its returned value by selecting the edit box.

I wrote a roll your own GUI as the first part of a spreadsheet. You could enter data and change the size of individual columns and rows but I didn't have a nice evaluate("expression") function to go any further.
paul doe
Posts: 1255
Joined: Jul 25, 2017 17:22
Location: Argentina

Re: Embedding a Spreadsheet

Postby paul doe » Jun 30, 2020 21:53

TJF wrote:In the
paul doe wrote:... Reverse Polish Notation ..
there's no syntax to extend.
...

Says who? =D
TJF wrote:...
It contains only values and operators/functions. The brackets get evaluated and eliminated by the parser.

The RPN always starts with a value, and always ends by an operator/function. An operator (like '+') needs two values, and a function (like 'ABS()') only one.

I was a bit vague in my example. The parser should generate from a formula like =3.1*SUM(b2:c4)

Note that you're assuming that I was talking about parsing such an expression as RPN. What I'm talking about is working explicitly with it, and concretely suggested Forth's expression evaluation and execution model for it.
So, the expression =3.1*SUM(b2:c4) is to be stated explicitly in RPN, not compiled to it for a stack VM to interpret. Thus, the expression could be:

Code: Select all

=3.1 b2:c4 sum *

or

Code: Select all

=b2:c4 sum 3.1 *

The extension I mentioned was to use parens to bracket a list of parameters (Forth uses it for comments), and using them like this would allow for variadic calls (Forth's words always take a fixed number of parameters from the stack, as RPN; having variadic calls might be useful in the context of a spreadsheet app). It is precisely the simplicity of expression (and evaluation) that Forth does what I suggested, not simply compile some infix expression to RPN. Why Forth? Because its syntax is trivial to parse (the OP stated that he/she is not good at it), and implementing a pretty powerful evaluator modeled after it is relatively simple.

I can elaborate a little on request, since I've already implemented Forth compilers/interpreters before ;)
dbickin
Posts: 52
Joined: Aug 03, 2005 16:40

Re: Embedding a Spreadsheet

Postby dbickin » Jun 30, 2020 22:07

srvaldez wrote:perhaps this might be of some value https://wiki.freepascal.org/RPN_Formula ... preadsheet


This looks like exactly what I want. Assuming you can put two sWorkSheetGrid controls on one form, with other non-spreadsheet related controls in the same form.

Is there an "easy" way to link the FP form library to freebasic?

David
TJF
Posts: 3599
Joined: Dec 06, 2009 22:27
Location: N47°, E15°
Contact:

Re: Embedding a Spreadsheet

Postby TJF » Jul 01, 2020 8:19

paul doe wrote:
TJF wrote:In the
paul doe wrote:... Reverse Polish Notation ..
there's no syntax to extend.
...

Says who? =D
Says ie. Wikipedia. You suggest to give up the main advantage of RPN:
Wikipedia wrote:It does not need any parentheses as long as each operator has a fixed number of operands.


dbickin wrote:This looks like exactly what I want.
This Pascal solution implements a complex custom type for the RPN stack, which makes the solver complicated. IMHO it's better to operate with two stacks (value/RPN). When the solver finds a positive value on RPN stack, it grabs one value from the value stack. In contrast a negative value on the RPN stack calls the related operation/function. In that case the solver needs only two registers, and is pretty easy to extend.

PS:
Sorry, my above example is wrong. The constant '3.1' should go to the first place in the value stack, since it's the first operand (value) in the formular =3.1*SUM(b2:c4)
  • a value stack

    Code: Select all

    V0 = 3.1
    V1 = b2
    V2 = b3
    V3 = b4
    V4 = c2
    V5 = c3
    V6 = c4
  • and the RPN stack

    Code: Select all

    1 2 + 3 + 4 + 5 + 6 + 0 *
paul doe
Posts: 1255
Joined: Jul 25, 2017 17:22
Location: Argentina

Re: Embedding a Spreadsheet

Postby paul doe » Jul 01, 2020 10:40

TJF wrote:Says ie. Wikipedia. You suggest to give up the main advantage of RPN:
...

I'm NOT suggesting to give up the advantages of RPN, but extending it to support variadic operators. You're either just not getting the concept, or I'm not explaining it well enough. Nevermind; I've lost interest in the topic. Carry on.
caseih
Posts: 1518
Joined: Feb 26, 2007 5:32

Re: Embedding a Spreadsheet

Postby caseih » Jul 01, 2020 23:52

I don't think it's worth it to support variadic operators in RPN. My trusty HP 48 certainly doesn't bother with it.

If you want algebraic expression parsing, many years ago (like nearly 30 years ago) I wrote a recursive-descent parser to parse expressions (one character lookahead I think), roughly based on operator precedences of my scientific calculator at the time. It might form something you could modify to work with cells as variables. I got it working with FB using the qb language dialect. I had to pull two functions from the C runtime library, but other than that it's pure QB dialect FB code.

If I were to do it again, I'd make a tokenizer and then have the parser work on tokens rather than have the parser do that in an ad hoc fashion.

Here it is, if you're interested in it. You could modify the Factor#() function to reference cells as variables (there's a read-only "X" variable defined for an example). Of course the tricky bit with a spreadsheet is you have to maintain a dependency tree so that your recalculations happen in the right order. This requires a bit of help from the expression parser to know what the dependencies are.

Code: Select all

'This is public domain code.  Use it however you see fit.  No attribution
'required.

DEFINT A-Z

DECLARE Sub GetNextChar
DECLARE Sub GetChr
DECLARE Function GetFunction$

DECLARE Function Factorial#(Number&)

DECLARE Function Solve#(Eq$)
DECLARE Function Expression#()
DECLARE Function Rank12#()
DECLARE Function Rank11#()
DECLARE Function Rank10#()
DECLARE Function Rank9#()
DECLARE Function Rank8#()
DECLARE Function Rank6#()
DECLARE Function Rank5#()
DECLARE Function Rank3#()
DECLARE Function Rank2#()
DECLARE Function Factor#()

'import from c standard library
DECLARE Function log10 cdecl ALIAS "log10f" (x as single) AS single
Declare Function exp10 cdecl ALIAS "exp10f" (x as single) AS single

DIM Shared NextChar$, SyntaxE, Begin, CPos, DivError, Equation$, DP
DIM Shared LBracket, RBracket, Func$
Dim SHARED pi as Double, Ans as Double
Dim SHARED x as Double

x=5

CONST True=1
CONST False=0

Do
   Input "Eq: ",Eq$
   If Eq$<>"quit" and eq$<>"" Then
      A#=Solve#(Eq$)

      If SyntaxE=True Then
            Print "Syntax Error at position";CPos
      ElseIf DivError<>False Then
            Print "Mathematical Error"
      Else
         Print A#:Ans=A#
      End If

   End If
   SyntaxE=False
   DivError=False
Loop Until Eq$="quit"

Sub GetNextChar
   Do
      CPos = CPos + 1
      If CPos>Len(Equation$) Then
         NextChar$=Chr$(0)
      Else
         NextChar$=Mid$(Equation$,Cpos,1)
      End If
   Loop While NextChar$=" "
End Sub

Sub GetChr
   CPos = CPos + 1
   If CPos>Len(Equation$) Then
      NextChar$=Chr$(0)
   Else
      NextChar$=Mid$(Equation$,Cpos,1)
   End IF
End Sub

Function GetFunction$
   If Instr("%*+-/<=>[\]^ûýüóò",NextChar$)>0 Then
      Funct$=NextChar$
      GetNextChar
   Else
      While Instr("%)(*+-./0123456789<=>[\]^ûüýòó "+Chr$(0),NextChar$)=0
         Funct$=Funct$+NextChar$
         GetChr
      Wend
      If NextChar$=" " Then GetNextChar
   End If
   GetFunction$=Funct$
End Function

Function Factorial#(Number&)
   If Number&>1 Then
      N#=Number&*Factorial#(Number&-1)
   Else
      N#=1
   End If
   Factorial#=N#
End Function


Function Solve#(Eq$)
   Dim Value as Double
'   On Local Error Goto ErrorTrap

   Equation$=Eq$
   CPos=0
   GetNextChar
   Solve#=Expression#
   Exit Function

   'ErrorTrap:
'   If Err Then DivError=True
'   Resume TheEnd
'   TheEnd:
'   Solve#=0
End Function

Function Expression#()
   Dim Value as Double
   Value=Rank12
   Do                             '** DO loops mean left to right execution
      If Func$="" Then
         Select Case NextChar$
            Case "A" to "Z", "a" to "z"
               Func$=UCase$(GetFunction$)
            Case Else
               Expression#=Value
               Exit Function
         End Select
      End If

      Select Case Func$
         Case "OR"
            Func$=""
            Value=Value OR Rank12
         Case "XOR"
            Func$=""
            Value=Value XOR Rank12
         Case Else
            Expression#=Value
            Exit Function
      End Select
   Loop
End Function

Function Rank12# ()
   Dim Value as Double
   Value=Rank11
   Do
      If Func$="" Then
         Select Case UCase$(NextChar$)
               Case "A" to "Z"
               Func$=Ucase$(GetFunction$)
            Case Else
               Rank12#=Value
               Exit Function
         End Select
      End If

      If Func$="AND" Then
         Func$=""
            Value=Value And Rank11
      Else
         Rank12#=Value
         Exit Function
      End If
   Loop
End Function

Function Rank11# ()
   Dim Value as Double
   Value=Rank10
   Do
      If Func$="" Then
         Select Case NextChar$
            Case "<"
               GetNextChar
               Select Case NextChar$
                  Case ">"
                     GetNextChar
                     Value=(Value<>Rank10)
                  Case "="
                     GetNextChar
                     Value=(Value<=Rank10)
                  Case Else
                     Value=(Value<Rank10)
               End Select
            Case ">"
               GetNextChar
               If NextChar$="=" Then
                  GetNextChar
                  Value=(Value>=Rank10)
               Else
                  Value=(Value>Rank10)
               End If
            Case "="
               GetNextChar
               If NextChar$=">" Then
                  GetNextChar
                  Value=(Value>=Rank10)
               Else
                  Value=(Value=Rank10)
               End If
            Case Else
               Rank11#=Value
               Exit Function
         End Select
      Else
         Rank11#=Value
         Exit Function
      End If
   Loop
End Function

Function Rank10# ()
   Dim Value as Double
   Value=Rank9
   Do
      If Func$="" Then
         Select Case NextChar$
            Case "+"
               GetNextChar
               Value=Value+Rank9#
            Case "-"
               GetNextChar
               Value=Value-Rank9#
            Case Else
               Rank10#=Value
               Exit Function
         End Select
      Else
         Rank10#=Value
         Exit Function
      End If
   Loop
End Function

Function Rank9#()
   Dim Value as Double
   Dim Divisor as Double

   Value=Rank8
   Do
      If Func$="" Then
         Select Case NextChar$
            Case "*"
               GetNextChar
               value=value*Rank8
            Case "/","\"
               Div$=NextChar$
               GetNextChar
               Divisor=Rank8
                  If Div$="\" Then
                     Value=Value\Divisor
               Else
                     Value=Value/Divisor
               End If
            Case Else
               Rank9#=Value
               Exit Function
         End Select
      Else
         Rank9#=Value
         Exit Function
      End If
   Loop
End Function

Function Rank8# ()
   Dim Value as Double
   Value=Rank6 'Rank7
   Do
      OldCPos=Cpos
      If Func$="" Then
         Select Case Ucase$(NextChar$)
            Case "P","C"
               Func$=Ucase$(GetFunction$)
            Case Else
               Rank8#=Value
               Exit Function
         End Select
      End If
      Select Case Func$
         Case "P"
            Func$=""
            Value=Factorial(int(Value))/Factorial(Value-Rank6)
         Case "C"
            Func$=""
            R#=Rank6   'Rank7
            Value=Factorial(int(Value))/Factorial(int(Value-R#))/Factorial(int(R#))
         Case Else
            Rank8#=Value
            Exit Function
      End Select
   Loop
End Function

'Rank7=abbrieviated multiplication in front of #6 functions

Function Rank6# ()
   Dim Value as Double
   If Func$="" Then
      Select Case UCase$(NextChar$)
         Case "-"
            GetNextChar
            Rank6#=-Rank6
            Exit Function
         Case "+"
            GetNextChar
            Rank6#=Rank6#
            Exit Function
         Case "A" to "Z", "û"
            Func$=Ucase$(GetFunction$)
         Case Else
            Rank6#=Rank5
            Exit Function
      End Select
   End If
   Select Case Func$
      Case "SQRT","û"
         Func$=""
         T#=Rank6             '** eval right to left
         If T#<0 Then
            Rank6#=1
            DivError=True
         Else
            Rank6#=SQR(T#)
         End If
      Case "CBRT"
         Func$=""
         Rank6#=Exp(Log(Rank6)/3)
      Case "SIN"
         Func$=""
         Rank6#=Sin(Rank6*pi/180)
      Case "COS"
         Func$=""
         Rank6#=Cos(Rank6*pi/180)
      Case "TAN"
         Func$=""
         Rank6#=Tan(Rank6*pi/180)
      Case "ATAN"
         Func$=""
         Rank6#=ATN(Rank6)*180/pi
      Case "ASIN"
         Func$=""
         Rank6#=ASIN(Rank6)*180/pi
      Case "ACOS"
         Func$=""
         Rank6#=ACOS(Rank6)*180/pi
      Case "LOG"
         Func$=""
         Rank6#=Log10(Rank6)
      Case "ALOG"
         Func$=""
         Rank6#=Exp10(Rank6)
      Case "LN"
         Func$=""
         Rank6#=Log(Rank6)
      Case "EXP"
         Func$=""
         Rank6#=Exp(Rank6)
      Case "INT"
         Func$=""
         Rank6#=Int(Rank6)
      Case "FRAC"
         Func$=""
         n#=Rank6
         Rank6#=n#-int(n#)
      Case Else
         Rank6#=Rank5
   End Select
End Function

Function Rank5# ()  '** abbrieviated multiplying befor pi, brackets, etc
   Dim Value as Double

   If Func$="" Then
      If NextChar$="ã" Then
         OldCPos=CPos
         GetNextChar
         If Instr("*+-/;:<=>?@{|}"+Chr$(0),NextChar$)=0 Then
            Rank5=pi*Rank3
            Exit Function
         Else
            CPos=OldCPos-1
            GetNextChar
         End If
      End If
   End If

   Value=Rank3
   Do
      If Func$="" Then
         Select Case NextChar$
            Case "0" to "9","."
               Value=Value*Rank3
            Case "(","ã"          'Abbrieviated multiplying in front of brackets
               Value=Value*Rank3
            Case Else
               Rank5=Value
               Exit Function
         End Select
      Else
         Rank5=Value
         Exit Function
      End If
   Loop
End Function

Function Rank3# ()
   Dim Value as Double
   Value=Rank2

   If Func$="" Then
      Select Case UCase$(NextChar$)
         Case "^"
            GetNextChar
            value=value^Rank3#            'right to left
         Case "P"
            Func$=UCase$(GetFunction$)
      End Select
   End If
   If Func$="PWRT" Then
      Func$=""
      value=EXP(Log(Rank3#)/value)
   End If

   Rank3#=Value
End Function

Function Rank2# ()
   Dim Value as Double
   Value=Factor
   If Func$="" Then
      Select Case NextChar$
         Case "ý"
            GetNextChar
            Value=Value^2
         Case "!"
            Value=Factorial(Int(Value))
      End Select
   End If
   Rank2#=Value
End Function

Function Factor# ()
   Dim Value as Double
   Dim C as Integer, I as integer

   If Func$="" and (Asc(NextChar$)<=57 and Asc(NextChar$)>=48) Then
      While Asc(NextChar$)<=57 and Asc(NextChar$)>=48
         Value=Value*10+Val(NextChar$)
         GetNextChar
         If DP>0 Then DP = DP + 1
         If NextChar$="." Then
            If Dp=0 Then
               GetNextChar
               DP=1
            Else
               SyntaxE=True
               Factor#=1
               Exit Function
            End If
         End IF
         If UCase$(NextChar$)="E" Then
            GetNextChar
            OldDp=Dp:Dp=0
            Value=Value*10^Expression
            Dp=OldDp
         End If
      Wend

      For i=2 to DP
         value=value/10
      Next
      DP=False%
      Factor#=Value
      Exit Function
   Else
      If Func$="" Then
         Select Case Ucase$(NextChar$)
            Case "ã"
               GetNextChar
               Factor#=pi
               Exit Function
            Case "P","X","A"
               Func$=Ucase$(GetFunction$)
            Case "("
               GetNextChar
               Value=Expression
                  GetNextChar
               Factor#=Value
               DP=False
               Exit Function
            Case "."
               DP=1
               GetChr
               If Asc(NextChar$)>47 and Asc(NextChar$)<58 Then
                  Factor=Factor 'we have a number starting with a decimal place
                  Exit Function
               Else
                  SyntaxE=True
               End If
            Case Else
               SyntaxE=True
               Factor#=1
               Exit Function
         End Select
      End If
      Select Case Func$
         Case "PI"
            Func$=""
            Factor#=pi
         Case "X"
            Factor#=x
            Func$=""
         Case "ANS"
            Func$=""
            Factor#=Ans
         Case Else
            SyntaxE=True
            Factor#=1
      End Select
   End If
End Function


Basically the top level of parsing is the expression, and below that are ranks of operator precedence, some are left to right associative, such as addition, subtraction, etc, and some are right to left. The lowest level is Factor#(), which is where you'd reference cells and variables.
oyster
Posts: 216
Joined: Oct 11, 2005 10:46

Re: Embedding a Spreadsheet

Postby oyster » Jul 02, 2020 2:15

Since IUP does not support "reasonably functional" spreadsheet, I don't think it is easy to implement such a thing. There are 2 reasons
1. you, or the the user, will broaden the supported functions as time goes
2. tedious code is used even for some simple, limited-ranged functions

I suggest to foucs on your owen application, and embed JavaScript code in application, since we may find some very nice JS library, for example
1. https://github.com/olifolkerd/tabulator
2. https://github.com/paulhodel/jexcel

now the question has been change to "how to embed JS in my freebasic/C/... application". So bad, I don't know the solution
dbickin
Posts: 52
Joined: Aug 03, 2005 16:40

Re: Embedding a Spreadsheet

Postby dbickin » Jul 02, 2020 3:27

oyster wrote:I suggest to focus on your own application, and embed JavaScript code in application


Would that JS run in client in a embedded html viewer in my code, or would a server have to be set up?

David
dbickin
Posts: 52
Joined: Aug 03, 2005 16:40

Re: Embedding a Spreadsheet

Postby dbickin » Jul 02, 2020 3:45

TJF wrote:
dbickin wrote:This looks like exactly what I want.
This Pascal solution implements a complex custom type for the RPN stack, which makes the solver complicated. IMHO it's better to operate with two stacks (value/RPN).


This answer confuses me.... not too hard to do.
I suspect because you are answering a question I never really asked. Frankly I don't care what kind of stack the solution uses behind the scene.
The given link was to the RPN facet of the pascal class, but I was looking at the whole thing: namely a spreadsheet component that I think lets me embed it in a form, does functions, and other things a good spreadsheet would do.
It sounds like what I want. (Actually sounds too good to be true. Testing will tell.)

So, were you saying the way they do RPN is not as good as the way you would do it, or that the way they do it simply DOESN'T work, or that you have tried it and it is too slow to be useful?

No one has piped up if I would be somehow able to use this control, which I gather is a part of LCL, from a freebasic program. From searching the forum here (and slightly the freepascal forum) it sounds like the answer is no. (But the most solid answers were from as far back as 2011 so that may have changed.)
If the answer is No, then unless I want to abandon the code I have already written and rewrite it in pascal, the FPSpreadsheet is of no use to me.

Thanks,
David

Return to “General”

Who is online

Users browsing this forum: Bing [Bot] and 5 guests