Show HN: Spreadsheet_server | Hacker News Reader

Show HN: Spreadsheet_server

github.com

64 pointsristic29 comments

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/) 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/) 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.

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?

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

I think you may find what you are looking for here: http://download.documentfoundation.org/libreoffice/src/

That seems like an interesting avenue to peruse. I would imagine you would end up reimplementing a lot of what Calc has already solved. I'm thinking about mapping cells to cell references (A1:C57), how to handle VLOOKUP, sheets etc. If I had to do this, my approach would probably be to take the headless Calc code and strip out the parts that are unnecessary.

This tool looks great, but I'm not very familiar with LibreOffice --- does it support macros from Excel? I have a use-case in mind but it would require logic not directly in the cell's formulas.

I don't have any experience working with macros between Excel and Calc. That said I did find some resources that may answer your question. First off: https://help.libreoffice.org/Common/Using_Microsoft_Office_a...

There seems to be at least one tool that can convert macros from Excel to Calc for you: http://www.business-spreadsheets.com/vba2oo.asp Having done no testing, I have no idea how well this will work.

Also, there seems to be a good resource on specifically LibreOffice macros available here: http://www.debugpoint.com/category/programming/libreoffice

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

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.

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.

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/

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

[edit for grammar]

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.

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.

Reminds me of https://open365.io/

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

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

"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?

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...

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

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.

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

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

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).

The example_client may help answer your question: https://github.com/robsco-git/spreadsheet_server/blob/master...

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.

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

> within excel

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

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...

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

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

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...

Reply on news.ycombinator.com