
Show HN: Spreadsheet_server - ristic
https://github.com/robsco-git/spreadsheet_server
======
ristic
For anyone interested, I went through a few iterations before settling on
LibreOffce Calc and Python:

Initially I was using: PHPExcel
([https://phpexcel.codeplex.com/](https://phpexcel.codeplex.com/)) which
loaded a given spreadsheet on demand - this was before I though of changing it
into a client server model. In a web application context, the response time
for anything other than relatively small spreadsheets was simply unacceptable.

I then moved to using Apache POI
([https://poi.apache.org/](https://poi.apache.org/)) and used the client
server model. This worked quite well but I ran into some problems as some
functions had not been implemented yet.

My current thinking is that LibreOffice will continue to improve both in terms
of function feature completeness with Microsoft Excel. It may also improve in
terms of performance and memory usage.

Any solution that re-implements excel/calc functions, other than LO, most
probably has a smaller user/developer base. In my mind, the smaller the
projects user/developer base, the more likely active development could slow to
a crawl or stop entirely. Not such a great solution for years down the line.

~~~
fiatjaf
Doesn't LibreOffice Calc have a simple, standalone implementation of its
formula functions, so that they can be copied instead of re-implemented by
other projects?

~~~
fiatjaf
What a shame. I can't even find the source code for Calc anywhere.

~~~
ristic
I think you may find what you are looking for here:
[http://download.documentfoundation.org/libreoffice/src/](http://download.documentfoundation.org/libreoffice/src/)

------
thomasfromcdnjs
A friend of mine built something in the same vein.

" The Problem:

You have complex computations that you want to generatively test, and you
can't test for correctness.

The Solution:

Use an LibreOffice Calc Spreadsheet as your 'obviously correct' calculation
engine, and test your computations in a format that even your clients will be
familiar with. Sounds crazy, but think more on it. A Spreadsheet is one of the
most powerful ways to visualise number crunching, and pretty much everyone
knows how to use it. If you have a reliable spreadsheet that accepts input
data, and outputs data, you have yourself a testable computation that the
client can understand.

"

[https://bitbucket.org/MalucoMarinero/cellacceptance](https://bitbucket.org/MalucoMarinero/cellacceptance)

~~~
ristic
Great, I'll have to take a look into their solution to see if I can learn or
improve anything. Thanks for the heads up.

------
cm2187
It's not free but works very well so I thought I should mention it:
spreadsheetgear does something similar except that they have their own
calculation engine, so you can run multiple instances, it does not rely on
Excel or OpenOffice. You also go around the locking problem when two threads
need to use the same spreadsheet (it just opens two instances of the
calculation engine). But it doesn't support VBA or any non Excel function.

Now none of these solutions will be super fast, the overhead of doing the calc
in Excel is pretty heavy. I have seen suggested here and suggested it myself
that it would be useful to have a tool that reads a spreadsheet and converts
the logic between selected input and output cells in code. Then the business
could own the logic (in Excel) but it would run with zero overhead on a
server.

That's not a trivial thing to do. Excel allows some pretty messy things and
users aren't shy about using them. For instance the INDIRECT function allows
you to arbitrary refer to any cell, which defeats a statically compiled
dependency tree between cells.

[Edit] I think Aspose cells also works in a similar way and has its own
calculation engine. Haven't tried it though.

------
dragonshed
Interesting project! It's nice to see some better examples for this sort of
thing. At work I've helped build something similar using javascript. We needed
something that works in-browser, and have since made sure it also works within
web-workers as well as node.

It's not as nice as directly running calculations from a magically scalable
and performant spreadsheet black box, but importing data and formulae from
various spreadsheets has gotten quite easy.

Closed source, but imagine using the core and expressions support from mathjs
[0] and crossfilter [1] together to display both raw and calculated data in
various contexts, and you wouldn't be too far off the mark.

[0] [http://mathjs.org/](http://mathjs.org/)

[1]
[http://square.github.io/crossfilter/](http://square.github.io/crossfilter/)

[edit for grammar]

------
planckscnst
Wow, I love this for letting business-oriented folks maintain their business
logic in the spreadsheet. If they want to change a calculation, they just edit
the spreadsheet.

~~~
ristic
That's exactly the use case the tool arose from.

The main con is the RAM overhead introduced by running LibreOffice instead of
splitting the application up into a database and code. For me con this is
nullified by the amount of time saved not having to re implement the logic and
now, like you mention, the onus is on the team who created the spreadsheet to
fix any bugs in their logic.

------
known
Reminds me of [https://open365.io/](https://open365.io/)

~~~
mxuribe
I think open365.io sounds quite interesting - in particular the self-hosting
option.

------
jldugger
Interesting. I'd be futher amused if someone wrote a Django backend using
spreadsheets.

------
fiatjaf
"A given spreadsheet is locked when it is accessed"

"By default, the './spreadsheets' directory is polled every 60 seconds for
file changes."

What does that mean? Can I edit the spreadsheet manually and the changes will
be detected by the server or not?

~~~
ristic
I probably need to be more descriptive. The locking is just within Python to
prevent simultaneous reads and writes to the spreadsheet messing with expected
results. This makes sense for my use case but probably not all use cases.

Currently it simply check for any new spreadsheet(s) in the ./spreadsheets
directory and opens them in LO Calc. Any spreadsheet that has been removed
will be closed in LO Calc. There currently is not support for what you
described. There is potential for that sort of thing to be implemented
though...

------
honua
Sorry but I really don't understand what this is for. Can someone explain?

~~~
ristic
I've been using it for a fairly specific use case: Creating web applications
from business logic that has already implemented in a spreadsheet. So instead
of trying to figure out what the spreadsheet is doing (they can get fairly
complex and the flow of calculation can be difficult to follow) I just treat
it as a black box. Input some values here and read the resultant values from
somewhere else. Integration with flask/django and you have a prototype within
a day.

~~~
tixocloud
Basically, I could almost replicate Google Docs/Excel 365?

~~~
ristic
If you built the front end for it then sure. In its current state this tool
just deals with reading and writing cell values.

~~~
tixocloud
Sorry but I'm still slightly confused.

Would you mind sharing the user flow starting from business folks with their
calculations in a spreadsheet?

I'm guessing if I write a front-end spreadsheet like interface, grab their
input, have LibreOffice process it, then spit it back out into the interface,
it would essentially be my very own spreadsheet program on top of LibreOffice?

I'd really love to learn what's the benefit of making that abstraction because
I've been on both sides (business and tech).

~~~
ristic
The example_client may help answer your question: [https://github.com/robsco-
git/spreadsheet_server/blob/master...](https://github.com/robsco-
git/spreadsheet_server/blob/master/example_client.py)

I don't quite follow your question but I'll give it a shot. Here is a high
level version of how I am currently using the tool: A user is presented with a
bunch of input variables on a web page that are then set by the user. These
variables are sent to the server and the server sets the relevant cells using
spreadsheet_server. Resultant values from other cells are retrieved by
spreadsheet_server and then sent back to the web page and presented to the
user in the form of a graph or table etc.

Pretty much all of the logic to get from input to output is handled in the
spreadsheet by LibreOffice.

------
ghiewa
now, we use Filemake acting like a logic engine. In contrast, indeed, writing
logic within excel is much easier than FMA.

~~~
pritambaral
> within excel

Perhaps you knew, but nevertheless, I have to point out: this works with
LibreOffice Calc and not MS Excel

~~~
ristic
Yes, it is true that it is running a LO Calc binary.

Not implying that you don't already know, but this may be of interest in terms
of Calc/Excel compatibility:
[https://wiki.documentfoundation.org/Feature_Comparison:_Libr...](https://wiki.documentfoundation.org/Feature_Comparison:_LibreOffice_-
_Microsoft_Office#Spreadsheet_applications:_LibreOffice_Calc_vs._Microsoft_Excel)

I have used the tool predominantly with xlsx format spreadsheets created in
Excel.

------
steve371
I think you could add a demo or screenshot linkd in README.

~~~
ristic
I have just updated the example_client and README. Combined they should
resolve your suggestion.

Here is the example client: [https://github.com/robsco-
git/spreadsheet_server/blob/master...](https://github.com/robsco-
git/spreadsheet_server/blob/master/example_client.py)

