I would like to understand how a spreadsheet (a group of named or otherwise identified cells containing values or formulas referencing other cells) is solved. I have tried looking at existing projects, but there was so much going on with the GUI, serialization, events, etc. that I couldn't find the spreadsheet.
At its simplest how does it work?
Best Answer
At its core, a spreadsheet is a functional language with dynamic typing and each function or value being able to be referenced as a cell in the matrix.
Instead of things like
(defn some-name ...)
thesome-name
part is placed in a cell itself.If you go to a dynamically updating functional language ide (such as lighttable for clojure), you will see much of the same functionality as a spreadsheet. Bind a value to a name, write a function that uses that value, change the value and the output of the function changes immediately. This is the same as doing something like writing
=A1 + B2
in the location ofC3
in excel.Thus, functional programmers often like to write spreadsheets as toy programs... and the subject of research papers too. (Yes, I'm sorry, they are all behind an ACM.org paywall)
Spreadsheet functional programming
Forms/3: A first-order visual language to explore the boundaries of the spreadsheet paradigm
Implementing function spreadsheets
The start of Spreadsheet at Wikipedia gives some hints as to how to implement one:
Building on this from Outline of Model-View-Controller paradigm as expressed in the Java libraries. The author goes on to mention applets (a bit dated, it was written in '93-'96) and mentions his web page which goes to http://csis.pace.edu/~bergin/Java/applets.htm (yes, applets) for the corresponding spreadsheet code http://csis.pace.edu/~bergin/Java/Spreadsheet.java
I will point out that the entirety of the spreadsheet, is not that big in this applet 570 lines including documentation.
That said, depending on the language, you could probably do it all with just function pointers in a sparse array.