Extract Calc Engine as a standalone programming language

irunohiln · September 12, 2023, 4:30pm

this analogy of database : sql :: spreadsheets : ? (read: what is same to spreadsheets as sql is to database?)
… can be extrapolated to propose a new language for spreadsheets as well
… as “spreadsheet - formula” model is a prog lang if u ask me:

          SPREADSHEETS : PROGRAMMING LANGUAGE

1.           functions : functions
2.             formula : function call
3.     data formatting : data types & output/print formatting

4.     cell dependency : sequence control & declaration order

5.      cell reference : scalar variable passing
6.          cell range : vector/array/dataframe
7. autofill on a range : function broadcasting on vector

The “5. cell reference / variable” equivalence is also used by freecad as “identify cell by alias” ref: freecad alias

Now, this would be somewhat difficult - to integrate the language model with the spreadsheet and to keep the breaking changes as close to nil but if done, this would be phenomenal & ground breaking.

This post (shown in “Your topic is similar to…”) seems to be touch the same point (calc as server, pass data, get back the results), then shoot off to different tangent (fill up cells, use API, etc): libreoffice-calc-as-a-calculation-engine-server-or-service

irunohiln · September 12, 2023, 4:34pm

building on same motivation as above (using prog langs to serve logical backend for structured part of spreadsheets)

i wanted to do this in a normal existing prog lang. of course, the logic there would only covers the “header rows” part of the concerned table (from t/95715) - the “presentation part” of the table are not normally doable there.

presentation like: cell border, cell coloring (alternate-stripped), concatenations, non-structured data, merged header cells, width/height adjustment, etc.

so, following is how to obtain the same row-headers in julia lang:

Code

# 2023-09-12

# lpad: http://stackoverflow.com/questions/39131826/ddg#39131882
# dates: https://docs.julialang.org/en/v1/stdlib/Dates/
# modules: https://docs.julialang.org/en/v1/manual/modules/

using Dates
using DataFrames

start = Date(2023, 08, 23)
start = Dates.firstdayofweek(start)
finish = Date(2023, 12, 16)

nweeks = Dates.week(finish) - Dates.week(start)
sess_weeks = 0:nweeks
year_weeks = sess_weeks .+ Dates.week(start)

# dates = start:finish
mon_dates = start:Week(1):finish
fri_dates = mon_dates .+ Day(5)
# year_weeks = Dates.week.(mon_dates)

data = DataFrame((
    yw=year_weeks,                  # lpad.(year_weeks, 2, "0"),
    sw=sess_weeks,                  # lpad.(sess_weeks, 2, "0"),
    mon=mon_dates,
    fri=Dates.day.(fri_dates),      # lpad.(Dates.day.(fri_dates), 2, "0")
))

Result of final “dataframe”:

Wanderer · September 14, 2023, 5:36am

At first: Your answer is no answer, but an extension to your question, so usually it is recommended to edit your original question instead (because answers can be re-ordered by voting).
.
Then: Your conclusion is wrong, as Calc is written in a “normal existing prog lang.” this can obviously done. The main problem one will have is converting all the interactive settings to a “prog lang” but in a way the description is already existent: Read the xml-representation of your file in contents.xml in your saved file
.
As Calc is open-source, you are welcome to start your project. But my guess is your potential users are at the moment using tools like Jupyter notebooks etc.

Why? Functional programming languages already exist. Spreadsheets exist. So I don’t see anything ground breaking here…

irunohiln · September 15, 2023, 8:19pm

i am not used to using discourse as q&a, have always used it as forums for discussions. i am trying to get used to this new usecase. (it’s a bit difficult due to habbits - conflating the reply comment post etc button)

anything can be done in a turing-complete prog lang. what i meant was: no language seems to be dedicated to:

providing high level commands
for the level of unstructured table formatting as provided by spreadsheet GUIs
in a dynamic manner i.e. not super tightly coupled with underlying data (one may argue that’s not possible, but i’d say it might very well be)

i know, not all things can be met, but a package can be well made by carefully imposing thoughtful restrictions.

existing ones are majorly latex tables, and html; work well for “formal showcase of data”, not for any functional formatting other than that.

yep, exactly. even if “in a way description is already existent” is not met - this would be great as an initial concept.

yep, afaik jupyter etc deals with totallly structured date - everything in columns and such. like i said above, they do “formal showcase of data” - that’s not my aim is. my aim is the raw daily use of tables in their better glory: “oh, color these cells red, these yellow”; “put a thick border around these cells, but a hairline around here” etc. etc.

i already showed an example of prog lang above, but again - u seem to miss the point entirely.

Continuing the discussion from Pad the data from other cells (used via formula) with leading zeroes

prog langs serve only the logic part of the tables… these fail when any deviation comes in. spreadsheets are BLOBs (binary large objects); if spreadsheets were plain text based, i wouldn’t have much problem.

Wanderer · September 16, 2023, 6:57am

Have you ever looked inside an odc? It is a zip archive and the contents are text based xml-files. As there are libraries available for zip and xml, I guess you will not have much problems…