Embedding a Spreadsheet

General FreeBASIC programming questions.
TJF
Posts: 3599
Joined: Dec 06, 2009 22:27
Location: N47°, E15°
Contact:

Re: Embedding a Spreadsheet

Postby TJF » Jul 04, 2020 18:42

dbickin wrote: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.
Yes, I answered an unasked question. If the developers use complicated and bloated data structures, this will lead to complicated and bloated code. Nothing to learn from.

I just posted a proposal how your solution could look like. You may be interested in this thread:

Yet another expression parser (spreadsheet)

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

Re: Embedding a Spreadsheet

Postby dbickin » Jul 06, 2020 1:47

Thanks TJF. I finally had time to try to understand your code.

I read the code, but haven't tried to compile it, or test it.

I think I have convinced myself that it does allow for COS(A1). (ie the cosine of the value in cell A1)

But I am guessing for simplicity you omitted processing any functions that return Text instead of numbers.
To handle text, I guess I would need a text buffer and push the address of the text into the operand stack. Ditto for the result stack. And of course I would have to add looking for and handling quotation marks into the parser. Don't know why, but quotation marks always stump me.

I am not sure exactly how to use your eval. I see it is linked to gtk_sheet to get values, but it doesn't get the formula from the sheet, nor attempt to store the result. But then again, gtk_sheet appears to only be able to store the text of the formula, but I guess it could be hacked to store the result in the description field. But that would also require changing the way it "formats" data. Kind of why I dismissed consideration for gtk_sheet - zero built in support for functions.

Your eval appears to be a once and done evaluation of the formula, while in a spreadsheet, the formula needs to be re-eval'd every time any referenced cell changes.

Dos your code handle that in any way, and I am just missing it?

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

Re: Embedding a Spreadsheet

Postby TJF » Jul 06, 2020 7:41

dbickin wrote:Thanks TJF. I finally had time to try to understand your code.

I read the code, but haven't tried to compile it, or test it.

I think I have convinced myself that it does allow for COS(A1). (ie the cosine of the value in cell A1)
Correct. That's one of the features.

dbickin wrote:But I am guessing for simplicity you omitted processing any functions that return Text instead of numbers.
To handle text, I guess I would need a text buffer and push the address of the text into the operand stack. Ditto for the result stack. And of course I would have to add looking for and handling quotation marks into the parser. Don't know why, but quotation marks always stump me.
String manipulation is trivial. There're no operator precedences, just left to right execution.

dbickin wrote:I am not sure exactly how to use your eval. I see it is linked to gtk_sheet to get values, but it doesn't get the formula from the sheet, nor attempt to store the result. But then again, gtk_sheet appears to only be able to store the text of the formula, but I guess it could be hacked to store the result in the description field. But that would also require changing the way it "formats" data. Kind of why I dismissed consideration for gtk_sheet - zero built in support for functions.

Your eval appears to be a once and done evaluation of the formula, while in a spreadsheet, the formula needs to be re-eval'd every time any referenced cell changes.

Dos your code handle that in any way, and I am just missing it?
Sure it does not. I already mentioned that the cell can only hold the numerical result (in order to allow further computation)
TJF wrote:... so the FB code has to store the formular (ie in a property of the cell) and fill the cell with the result.
and the formula context can only show up when the cell is active (and no computation can occur).

Regards
oyster
Posts: 216
Joined: Oct 11, 2005 10:46

Re: Embedding a Spreadsheet

Postby oyster » Jul 10, 2020 7:50

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

Re: Embedding a Spreadsheet

Postby dbickin » Jul 10, 2020 14:43

oyster wrote:an old post, may be useful
https://ewbi.blogs.com/develops/popular ... rsing.html


Thanks. I ended up realizing I needed to figure out exactly what "minimally functional" spreadsheet would need to do.
Most of the Gui toolkits could handle displaying and entering data in a grid.
And after a lot of work I could likely expand one of the parsers to handle text functions. I'm sure to TJF adding text functions is trivial, but it isn't to me.
Then I realized that a basic minimum for me is to have autofilters, and I probably could fit something into one of the GUI girds.
I once wrote code to insert subtotals into a list, so I know I could handle that, but as I recall that code (written in scheme for the SIAG spreadsheet) couldn't handle anything unexpectedly complicated. I'm sure polishing it would be trivial to some, but not for me.
I probably could figure out how to have absolute and relative cell references in formulas. And this is what killed it for me. I could no longer just store the original formula, but instead a tokenized version so if a row or column got inserted, I could change the relative cell references to the new letter+number representation. I'm sure some wouldn't find that hard, but it is for me.

In the end, for the personal projects I had in mind, embedding was a nice to have, but not nice enough to warrant all that work.
For my part-time employer, I couldn't justify the cost of my programming time. Plus they love pivot-tables, but I hate them, so there is no way anything I wrote would suit their needs.

So I appreciate any tips or links so that I can reevaluate the do-ability given my programming skills, but at this time it is a big fat "Not Doable."

Particularly thanks for the link above, the linked parser does handle text and it does handle absolute and relative cells references, as well as references to cells in other tables. But trying to understand the C++ is more work that this project is worth to me.

David
marcov
Posts: 3004
Joined: Jun 16, 2005 9:45
Location: Eindhoven, NL
Contact:

Re: Embedding a Spreadsheet

Postby marcov » Jul 29, 2020 8:45

Note that Free Pascal has multiple RPN expression engines. Another one is written by me and called the "symbolic" package (fpc/packages/symbolic). You are welcome to convert/use that code as you wish.

Return to “General”

Who is online

Users browsing this forum: No registered users and 5 guests