
How to implement a spreadsheet - kristianp
http://semantic-domain.blogspot.com/2015/07/how-to-implement-spreadsheet.html
======
david-given
If you're interested in spreadsheets, don't miss sc, the Standard Unix
Spreadsheet™ which nobody has ever heard of (the un-Googleable name doesn't
help). It was originally written by James Gosling in the 1980s and is in
Debian. It runs in a terminal and uses vi keybindings:

[http://www.linuxjournal.com/article/10699](http://www.linuxjournal.com/article/10699)

~~~
oever
Is there a more recent version than 7.16 from 2002?
[http://ibiblio.org/pub/Linux/apps/financial/spreadsheet/](http://ibiblio.org/pub/Linux/apps/financial/spreadsheet/)

~~~
beefsack
The package version in Arch is 7.16, so I assume it's the latest.

------
zarq
Back in 2000, a winner of the International Obfuscated C Code Contest (IOCCC),
had an implementation of an X-based graphical spreadsheet in 2kB of very
unreadable C:
[http://www.ioccc.org/2000/jarijyrki.c](http://www.ioccc.org/2000/jarijyrki.c)
. Details here:
[http://www.ioccc.org/2000/jarijyrki.hint](http://www.ioccc.org/2000/jarijyrki.hint)

~~~
leni536
This is amazing. It compiles and runs under cygwin fine too.

~~~
fit2rule
How'd you get it to compile?

    
    
        gcc -I/usr/include/X11 jarijyrki.c  -o jarijyrki
        jarijyrki.c:15:33: error: ‘U’ undeclared here (not in a function)
        int   q,P,W,Z,X,Y,r,u; char   E[U][U][T+1] ,D[T];   Window J; GC k;  XEvent  w;
                                         ^

~~~
leni536
I downloaded the Makefile from the same site [1]. I changed CC to gcc (not
sure if necessary) then just ran "make jarijyrki". The relevant build command
makes it clear why your compile command fails.

    
    
       	${CC} ${X11CCFLAGS} ${CFLAGS} -DNeedFunctionPrototypes \
       	    -DU=40 -DT=98 '-Dz=(T+1)*U*U' -DQ=80 -DS=20 -DN=10 -DB=5 -DG=23 \
       	    -Dp=7 '-DM=((p+1)*Q)+S' '-DH=(G*S)+S+S' -DC=XK_Up -DL=XK_Down \
       	    -DO=XK_Left -DV=XK_Right -DR=XK_Escape -D_=XK_BackSpace \
       	    $? -o $@ ${X11LDFLAGS} -lX11
    

I guess there was a limitation on the length on the compilation command too
and the winner made use of it fully. You have to run it as "./jarijyrki <
sheet1.info > myedits.info", sheet1.info is in the same site[2].

[1] [http://www.ioccc.org/2000/Makefile](http://www.ioccc.org/2000/Makefile)
[2]
[http://www.ioccc.org/2000/sheet1.info](http://www.ioccc.org/2000/sheet1.info)

~~~
fit2rule
Oh geeze, I didn't even see there was a Makefile there .. sorry about that.

------
rfw
Tangentially, the Löb function over (Haskell) functors implements spreadsheet-
like computations in an unexpected way:
[https://github.com/quchen/articles/blob/master/loeb-
moeb.md](https://github.com/quchen/articles/blob/master/loeb-moeb.md)

ex.

    
    
        loeb [const 1,
              const 2,
              \xs -> x !! 0 + x !! 1]
    

gives you [1, 2, 3].

Naturally, it doesn't allow for mutations or anything fancy, but it is an
interesting curiosity.

~~~
cousin_it
Isn't that the same as using vanilla laziness:

    
    
        > let xs = [1, 2, (xs !! 0) + (xs !! 1)] in xs
        [1, 2, 3]
    

It's order independent as well:

    
    
        > let xs = [(xs !! 1) - 1, (xs !! 2) - 1, 3] in xs
        [1, 2, 3]
    

Why do you need loeb?

~~~
rfw
I guess it finds fixed points over a functor without requiring you to have a
reference to the functor (having about the same usefulness as `fix`).

------
todd8
Emacs comes with a couple of built in spreadsheets: ses-mode supports emacs
lisp formula in cells, org-mode tables allow simple formulas in table cells.
Furthermore, there are half a dozen or so other packages that users have
written that are older or are more specialized in some way. See
[http://www.emacswiki.org/emacs/SpreadSheet](http://www.emacswiki.org/emacs/SpreadSheet).

All of these spreadsheets are, naturally, written in emacs lisp.

------
pjmlp
Actually, the Borland's Turbo C 2.0 for MS-DOS had an example how to implement
one.

Nice to see it in OCaml though.

~~~
Narishma
I believe all of Borland's compilers all the way back to the original Turbo
Pascal came with it.

~~~
pjmlp
I don't remember seeing it in the Turbo Pascal manuals. Used 3, 5.5, 6 and TPW
1.5.

But it might have been there as well.

~~~
Narishma
It's definitely in TP3 for PC. I still have the original floppy disk. I don't
remember if it was in the earlier CP/M versions though.

------
kawsper
One of my good friends worked with some researcher to offload the work to a
GPU ten years ago.

I have stumbled on massive spreadsheets in the wild where most of the cells
needed to be recalculated when you changed something, and that could take a
long time to recalculate.

It seems like most software out there today does this, which is pretty cool.

------
zitterbewegung
I wish there was a interface for data analysis that people could cut and paste
any data into it and it outputs a magical spreadsheet that business people can
use.

~~~
huskyr
Maybe not quite what you're looking for, but i was surprised at how well
Microsoft Excels handles pasting HTML tables.

------
zimpenfish
I once had to implement a tiny spreadsheet with cell references and simple
ranged formula in Perl given 2 hours. Possibly the most interesting
programming test I've had for a job yet.

------
moron4hire
Grok this and you will know everything you need to about spreadsheets:
[http://jsfiddle.net/ondras/hYfN3/](http://jsfiddle.net/ondras/hYfN3/)

You'll probably also end up learning a thing or two about JavaScript. Even
now, 2 years after I first saw this, I'm seeing new things in it, and I've
been doing JS for 20 years.

~~~
Trufa
This thing is getting me confused, there seems to be some black magic going
on, I can't understand how it can possibly work.

~~~
moron4hire
Look at the calls to defineProperty, then focus on figuring out why the with()
block has been used in the `getter` function.

------
anton-107
Does anybody know any good open source spreadsheet implementation with JS+DOM?

~~~
ohlain
We´re using [http://handsontable.com/](http://handsontable.com/) in our
project and are very happy with it so far.

------
bluedino
I remember good old CALC.PAS that Borland included as a demo program with
early versions of Turbo Pascal

[http://z80cpu.eu/files/archive/rlee/B/BORLAND/TURBO%20PASCAL...](http://z80cpu.eu/files/archive/rlee/B/BORLAND/TURBO%20PASCAL/V1-00/8086/CALC.PAS)

------
arethuza
I've been thinking recently it would be nice to have an easy to work with open
standard for spreadsheets - maybe based on JSON.

NB I know you can get Google spreadsheet data as JSON, but I was more thinking
of something where you could have "headless" operation that could be embedded
in other applications.

~~~
oever
You can use OpenDocument Format files. These are supported by most spreadheet
implementations. It comes in two variants: 1) a zip file with XML inside 2) a
single XML file

[http://docs.oasis-
open.org/office/v1.2/os/OpenDocument-v1.2-...](http://docs.oasis-
open.org/office/v1.2/os/OpenDocument-v1.2-os.html)

------
qznc
Why the "reads" field? It is just filled and cleared, but never used. This
also solves GC issues.

~~~
reycharles
It seems "reads" is used in "invalidate" to remove the cell from "observers"
in the cells in "reads".

~~~
qznc
Yes. I missed that.

Nevertheless, removing the "reads" seems more worthwhile than removing
"observers". You want to model the data flow efficiently, not the data
dependencies. The dependencies are provided indirectly in the code anyways.

------
wooby
Spreadsheets rule. For ClojureScript there is the Javelin library:
[https://github.com/tailrecursion/javelin](https://github.com/tailrecursion/javelin)

It has a few novel features like transactional input.

We use Javelin for state management in the hoplon.io web framework.

------
kwhitefoot
Why do all the simple spreadsheet implementations forbid circular references?
A colleague of mine made a very nice solver for temperature and pressure
calculations in a transformer which would have been much more complicated
without circular references.

~~~
alexchamberlain
Presumably a naive implementation would run the risk of having an infinite
loop? Have you got any references of the theory proving them safe and/or
removing the references?

~~~
kwhitefoot
I don't know if an arbitrary set of cell formulas could be proven to converge
but I do know from experience that it is a useful technique. The trick is in
setting the initial conditions; usually there will have to be one or more
cells that contain a check for a wildly out of range value that will force the
cell value into a reasonable range. Excel, and other spreadsheet programs,
have counters that are used to halt circular calculations after an arbitrary
limit. This is really no different than using a while loop to compute a value
iteratively.

------
lindig
I feel there is an interesting bit (or example) missing: how does the (OCaml)
code look that implements the expressions that are evaluated? The code returns
a value and the list of cells it read - how is obtaining this list best
organised?

------
_pmf_
Every time I read about reactive patterns, I hope that some data dependency or
cells like behavior is included, and every time it's just some thin wrapper
around basic pub/sub.

~~~
tonyg
Can you expand on what you mean by "data dependency" and "cells-like
behaviour" in this context? I'm afraid I'm drawing a blank on the terminology.

~~~
_pmf_
By cells, I mean something like this: [https://common-
lisp.net/project/cells/](https://common-lisp.net/project/cells/) (there's even
a HN discussion about it here:
[https://news.ycombinator.com/item?id=1885974](https://news.ycombinator.com/item?id=1885974))

which basically allows to express interdependencies between data (a.k.a.
formulas) in a way that updates sink variables when source variables are
changed.

At the moment, I think not even Rx extensions for .NET (which I consider to be
the most advanced reactive implementation in a mainstream language) do support
this style of computation.

------
nickstefan12
I implemented a spreadsheet in react, flux, immutable, and used a command
pattern here:
[https://github.com/NickStefan/rixif](https://github.com/NickStefan/rixif)

I did not take the approach of cells actually observing each other. Instead I
had a recursive function that worked from the entered cell to:

\-- parsed a string DSL of "=sum(B4:B8)" into a javascript function, that
pulls some premade functions like sum, vlookup etc, and makes a string list
representing what an arguments getter will need to fetch ("a_r3c1_r7c1" ie
array, row 3, col 1 to row 7 col 1). The user still just sees "=sum(B4:B8)"

\-- note who "I depend on",

\-- note who "depends on me"

\-- recurse 'outwards', skipping cells in "depends on me" that are still
waiting on 'needs recalc' of their own "I depend on".

The update algorithm is not the hard part. Microsoft also has a very detailed
documentation of their own update algorithm online (cant find it at the moment
though).

The hardest part was updating the string representations of the formulas when
you insert a new column or row, and then re-updating each cell's dependencies
arrays.

Definitely not a performance problem, but more of a "how the fuck do I wrap my
head around all the different ways someone could want to insert, cut, copy and
paster here".

One mistake I made was trying to impliment the undo/redo to be totally
reversable at every step. So every command stores the way to go both back and
forward. In hindsight, I should have just stored forward commands and rebaked
from the beginning when someone wanted to go back in time.

I wish I had more time to work on the project, but I've been busy with other
work.

notes: \- an excel to JSON parsing library in node :)
[https://github.com/NickStefan/parsexcel.js](https://github.com/NickStefan/parsexcel.js)

\- A lot of people mentioning Handsontable.js. That library has some major
design flaws. We used it in our git style version controlled spreadsheet app
([http://www.gridhub.xyx](http://www.gridhub.xyx)). Handsontable only takes
simple 'number' or 'string' value for each cell. It should have been an object
that could store CSS for that cell, formula for that cell, and the value for
that cell. I made a few pull requests, but they largely ignored me. The code
is odd (they use labels, continue, and other C style code).Handsontable is
great only for presenting tables. Its terrible for implementing a full excel
clone.

------
artenix
Twitter detects the URL as dangerous. O_o

------
pantulis
Well I think the title should be "How to implement a spreadsheet in OCaml"
It's impossible for me to follow the article :(

~~~
dspillett
Any programmer with much experience should be able to follow the structure of
that article and translate the concepts to the programming language of their
preference.

There is nothing language specific here, just think of the OCaml as pseudo-
code.

------
reitanqild
Totally of topic to spreadsheets but why oh why does blogspot still change the
domain based on ip? (Yes, I know that it is related to selectively blocking
certain blogs in certain areas but there has to be a better way to do this,
seriously.)

~~~
frik
BlogSpot is owned by Google, they should know better.

~~~
reycharles
google.com does the same.

