Embedding a Spreadsheet

[dbickin]

Hi,

For a project I want to do, I need to embed a "reasonably functional" spreadsheet inside a program window.

My google skills are not great, but I have not found anything that will do what I want.

gtksheet provides a spreadsheet that can be embedded in a gtk window, but as near as I can tell it does not support any kind of functions. You can only store text, and if that text looks like a number, it can format the result to include thousand separators and fixed decimals.

fltk has an example "spreadsheet" program, but it can't even format text that looks like numbers.

IUP has an example program that does support functions, but its parser can only accept a formula whose name is exactly three letters long, and takes exactly one range as an argument. (The sample implement the sum(range) function.) It can't even handle =A1+A2

I haven't search any other toolkits to see what they support.

I thought I might get away with running a text spreadsheet like sc or oleo inside an embedded terminal. GTK does have a virtual terminal, but I have bad history with GTK, and sc and oleo probably won't work for me anyway.

Okay, so what I need is a spreadsheet that will allow entry of text, numbers, dates and formulas. I need to be able to create user defined functions to use in the spreadsheet. If a cell a function uses changes, then the value result of the function needs to change as well.

I am thinking my best bet is try to find an expression parser to replace the brain dead one in the iupcells example.

Does anyone know a simple parser I could use?

Or a better way to do what I want to do?

Thanks,
David

[jdebord]

You may check SEE (Simple expression evaluator) by krcko :

http://www.unilim.fr/pages_perso/jean.d ... th/see.zip

[dodicat]

A short windows expression parser via vbscript.
(Maybe I could use mshta vbscript:Execute, I'll experiment later)

Code: Select all

Function eval(fn As String) As Double
  Dim As String s="Set h = CreateObject(""Scripting.Dictionary"")"+ Chr(13,10)
  s+="Wscript.Echo eval("+Chr(34)+fn+Chr(34)+")"+ Chr(13,10)
  s+="h.RemoveAll"
  Dim As String tmp
  Var n=Freefile
  If Open ("Tmp.vbs" For Binary Access Write As #n)=0 Then
    Put #n,,s
    Close #n
  End If
  s=""
  Open Pipe "cscript.exe /Nologo "+ "Tmp.vbs" For Input As #n
  Do Until Eof(n)
    Line Input #n,tmp
    s+=tmp+Chr(10)
  Loop
  Close #n
  Function= Val(s)
  Kill "Tmp.vbs"
End Function


Function SAR(stringin As String,find As String,replace As String) As String 'for the example
    dim s As String=stringin
    dim As long position=Instr(s,find)
    While position>0
        s=Mid(s,1,position-1) & replace & Mid(s,position+Len(find))
        position=Instr(position+Len(replace),s,find)
    Wend
    return s
End Function

Print eval("sin(.6)");tab(40);sin(.6)
Print eval("exp(-3)");tab(40);exp(-3)
Print eval("log(.3/tan(1))");tab(40);log(.3/tan(1))
print
print
dim as string fn="5*x^2-3.2*x+2.2-sin(x^2)"

for x as double=0 to 10 step .67
  var fn2=SAR(fn,"x",str(x))
  print eval(fn2);tab(40);5*x^2-3.2*x+2.2-sin(x^2)
next
print
print "press a key to end . . ."
  

sleep



  

[TJF]

Does anyone know a simple parser I could use?

You could embed a LUA interpreter for formula evaluation.

[jj2007]

Or a better way to do what I want to do?

Use Excel?

[dbickin]

@krcko :

Thanks, I'll see if I can work with SEE. Since I need arguments and results to be either numbers or text, I will have to change the doubles in the code to variant-types. And then figure out how to translate cell and range references to something that SEE can use.
SEE actually scares me with it's shared variables. But I guess they are not exposed outside the library code/module. I've never grokked modules and how they affect variable scopes. I have to read up on that.

@TJF:
Actually at one point I thought I saw a version of the IUPCells example program that DID use LUA to evaluate the functions, but when I look now, I can't find it again.

@jj2007:
Maybe if Microsoft releases a Linux version of Excel.

And under windows, at one point, I recall I did try to embed an Excel Sheet inside a form, and I can't recall exactly what the results were, but I deemed them unusable at the time. I think it is because it pulled in the Excel Toolbar, or perhaps because it didn't have the toolbar, the features of Excel I wanted to exploit at that time weren't available. It has been a good decade or so since I tried and I don't remember.

One hopeful side effect of this project is that it will let me stop using Excel. I mainly code using VBA in Excel, and horrible things happen when my code mixes with the code from the literally hundreds of other spreadsheets the users insist on having open at the same time.

David

[dodicat]

Here is a Lua example, plot a function and list roots on console, function is input via keyboard.
You need Lua53.dll and call lua53.dll from lua.bi, line 30.
(There are many parsers on the forum which are independent of external libraries).

Code: Select all

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

#Include Once "Lua/lauxlib.bi"
#Include Once "Lua/lualib.bi"
Dim  Lua  As lua_State Ptr  =   luaL_newstate 
dim shared as long luaerror 

luaL_openlibs(Lua)
'available math functions
dim as string _d(1 to 29)= {"abs","acos","asin","atan","atan2","ceil","cos","cosh","deg","exp","floor","fmod","frexp", _
                            "huge","ldexp","log","max","min","modf","pi","pow","rad","random","randomseed","sin","sinh", _
                            "sqrt","tan","tanh"}
         dim shared as string _d_(1 to ubound(_d))
         for n as long=1 to ubound(_d_)
         _d_(n)=_d(n)
         next

Function SAR(s0 As String,s1 As String,s2 As String) As String
    Dim s As String=s0
    var position=Instr(s,s1)
    While position>0
        s=Mid(s,1,position-1) & s2 & Mid(s,position+Len(s1))
        position=Instr(position+Len(s2),s,s1)
    Wend
    SAR=s
End Function

sub _format(s as string) 'put the math. before a function if needed.
    s=lcase(s)
    dim as string tmp=s
    for n as long=1 to ubound(_d_)
     if instr(s,_d_(n)) then s=SAR(s,_d_(n),"math." + ucase(_d_(n)))
    next
    s=lcase(s) 
end sub

Function Evaluate(fname As String,ByVal Expression As String,StringVariable As String,DoubleVariable As Double,ByRef lua As lua_state Ptr) As Double
   static func As String
   static as string nf
   If len(func)=0 or nf<>expression or len(nf)=0 Then                        'setup and compile function 
        nf=expression
       _format(Expression)
      func = "function " + fname +"("+StringVariable+")" +Chr(13)+Chr(10) _
      + "   return " +Expression + Chr(13)+Chr(10) _
      + "end"
     ' Print  func
      If luaL_dostring(Lua,func) Then
          PRINT "Error: " & *lua_tostring(Lua, -1),__LINE__   
          luaerror=1
      End If
   End If
   lua_getglobal(Lua, fname)
   lua_pushnumber(Lua, DoubleVariable)
   IF lua_pcall(Lua, 1, 1, 0) Then
      Print "Error: " & *lua_tostring(Lua, -1),__LINE__
      luaerror=1
   End If
   Evaluate = lua_tonumber(Lua, -1)
   lua_pop(Lua, 1)
End Function

function eval(byval e as string,x as double,byref lua as lua_State Ptr ) as double
    static as string nf
    static as string title
    static as integer i
    if nf<>e then title="f"+str(i):i+=1
    eval= evaluate(title,e,"x",x,lua)
     nf=e
    end function

'=================== END OF PARSER =======================
'approx roots
sub bisectlast(byval f as string,min as double,max as double,byref O as double,byref lua as lua_State Ptr)
     dim as string f2=f
    dim as double last,st=(max-min)/50000
    for n as double=min to max step st
        var v=eval(f2,n,lua)
        if sgn(v)<>sgn(last) then cprint( (n+(n+st))/2 ):O=n+2*st:exit sub
        last=v
        next
    end sub

sub bisect(byval f as string,min as double,max as double,byref O as double,byref lua as lua_State Ptr)
    dim as string f2=f
    dim as double last,st=(max-min)/50000
    for n as double=min to max step st
        var v=eval(f2,n,lua)
        if sgn(v)<>sgn(last) and n>min then bisectlast(f,n-st,n,O,lua)
        last=v
        next
    end sub

sub roots(byval f as string,min as double,max as double,c as string,byref lua as lua_State Ptr)
    cprint (c)
    dim as string f2=f
    dim as double last,O,st=(max-min)/50000
    for n as double=min to max step st
        var v=eval(f2,n,lua)
        if sgn(v)<>sgn(last) and n>min then bisect(f,n-st,n,O,lua):n=O
        last=v
    next
    end sub

'===============================================================================


Dim As String e,g,laste,copy
Dim As Double v
Dim As Double minx,maxx,miny,maxy,stp
dim as string sminx,smaxx
Screen 20

Do
    start:
    luaerror=0
  cprint("Roots")
    Cls
    miny=1e20
    maxy=-1e20
    locate 2,1
    print "Example of a function in x  sin(x)*cos(x)" 
    if len(laste) then
    Locate 5,8
    print "Previous function ";laste
    print "Enter <enter> to use previous function"
    end if
    locate 10
    Input "Enter a math function in x       ",e
   if len(e) then copy=e
     if len(e)=0 then e=laste
    e=lcase(e)
  
    print "Chosen function ";copy
    lbl:
    Locate ,10
    Input "Enter x axis range  e.g.   -4,7   ",sminx,smaxx
    minx=val(sminx)
    maxx=val(smaxx)
    if minx>=maxx then print "Please Redo!":goto lbl
    stp=(maxx-minx)/1000  
      dim as double t=timer
      
    For n As Single=minx To maxx Step stp
        v= eval(e,n,lua)
        if luaerror then print "Start again -- ERROR, press a key ":sleep:goto start
        If miny>v Then miny=v
        If maxy<v Then maxy=v
    Next
   
    Window(minx,miny)-(maxx,maxy)
    
    For n As Single=minx To maxx Step stp
            v=eval(e,n,lua)
            If n=minx Then Pset(n,v) Else Line -(n,v)
    Next
    'axis
    If minx<0 And maxx>0 Then Line(0,maxy)-(0,miny),4
    If miny<0 And maxy>0 Then Line(minx,0)-(maxx,0),4  
    Window
    Draw String(500,0),Str(maxy)
    Draw String(500,768-16),Str(miny)
    Draw String(0,768\2),Str(minx)
    Draw String(1024-8*Len(Str(maxx)),768\2),Str(maxx)
 roots(e,minx,maxx,copy,lua)
 laste=copy
 
 print "Press <spacebar> to continue"
 print "Press <esc> to end"
    Sleep
    
    If Inkey=Chr(27) Then Exit Do
Loop 
End
 

[TJF]

Here is a Lua example, plot a function and list roots on console, ...

As always: nice example!

But note: the context from the table entries needs to get extracted in to the formular. Ie. a spreadsheet function like =SUM(b2:c4) has to extract the values from b2, b3, b4, c2, c3, c4 before computing the summe. So if you don't want to parse the function in FreeBASIC, it may be better to do all evaluation on the LUA side, just passing the pointer of the table and the formular to evaluate. GtkSheet does not support formulars, so the FB code has to store the formular (ie in a property of the cell) and fill the cell with the result.

Regards

[dbickin]

it may be better to do all evaluation on the LUA side, just passing the pointer of the table and the formular to evaluate.

I don't know LUA.
Is parsing B1 to a value from the table something that LUA simply knows how to do, or is that something I would have to code?
The IUPCells sample broke "=sum(a1:b2)" apart using scanf into "sum", "a", 1, "b", 2 then ran nested for loops:

Code: Select all

case "sum":for i = asc("a")-asc("a") to asc("b")-asc("a"):fro j = 1 to 2: result += table(i,j):next:next.

Which ia fine, except most spreadsheets allow sum(a1) or sum(1,3) both of which won't parse in the example program, and I am at a lost how to make its parser more robust.

I've never been good at parsing text.

David

[MrSwiss]

I don't know LUA.

LUA is a script interpreter, made by the same people that did IUP.
Because of that, they integrate very well with each other (both by: PUC-RIO).

Is parsing B1 to a value from the table something that LUA simply knows how to do, or is that something I would have to code?

Yes, you'll have to write the script, that LUA is to interpret.

... spreadsheets allow sum(a1) or sum(1,3) both of ...

You must decide on the syntax, that is to be allowed to be parsed ...

In order to reduce complexity, above metioned rather weird syntax, should just be disallowed.
The simpler it is setup, the more robust it will be. (classical case of: a trade-off)

[TJF]

The more I think about the target, the LUA interpreter is not really helpful. It's better to customise an existing FB formula parser to the special spreadsheet requirements.

Regards

[dbickin]

I agree.

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.

But I am starting to figure out what I need to do to write the code.

David

[caseih]

Someone wrote a very simple spread sheet application in QBASIC some years ago which you might be able to borrow or get ideas from. I saw it here: https://www.instructables.com/id/DIY-co ... -template/

It runs under FB with the qb language flag, but it's super slow.

[dbickin]

Wow! Thanks. Well, at least is DOES handle cell references in its expression parser.

Unrelated, I was checking out the packages in a Linux distro, and saw "Gnu spreadsheet Widget" Excitedly I went to its website, only to see "Documentation is currently not available." Undeterred, I download the source, and it does have docs, including the memorable line "Gnu Spreadsheet Widget is not a spreadsheet..." Oh well.

If nothing else, this project is giving me some things to laugh at.

David

[paul doe]

...
But I am starting to figure out what I need to do to write the code.

How about a Forth-like expression parser? The syntax is fairly easy to parse (you have either 'words' or numbers), and it's a simple scheme that is nonetheless insanely powerful. All you need is a stack, and a collection of words (which would be the 'functions' of the spreadsheet) to execute.

Now, the only 'caveat' would be that you'll need to use Reverse Polish Notation to express functions. For example, this:

Code: Select all

sum( 1, 2 )

would become:

Code: Select all

1 2 sum

Implementing new functions for it is extremely simple and orthogonal, and you can easily expose them to the expression evaluator. You can even have code in place to allow the user to create variables and new functions!