In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever.
It's not surprising the number of turducken business applications being built exactly this way. With Named Cells you don't even have to hard-code cell numbers, just tell them to name them specific things, and Excel users are very happy with the amount of flexibility to rewrite the spreadsheets at will.
It's not necessarily the sanest approach to building software, but no one ever accused most enterprise software development of being sane.
Great analogy
This is the kind of simple and effective solution that programmers who think they know everything would scoff at.
Love it.
I flinched hard at this, because this only works until it doesn't.
I've done the exact same thing: give a user a location to upload an Excel set up just the way we'd want to parse it.
Good luck dealing with the absolute morass of formatting troubles that Excel throws at you because:
1) The user didn't format a date input correctly and now Excel treats it as a simple string instead of its internal Date representation
2) Excel mysteriously treats a random entry in a numeric or date column (PEBKAC? Who knows! The user denies all wrong-doing!) as a string and now it's got a leading apostrophe
3) The user used someone else's computer which has different Regional Settings, and suddenly:
3a) Commas are decimal points in numbers instead of periods
3b) Date formatting is messed up
3c) Months and days have their names in a different language
...
And these are just the issues that I can remember without having to dredge through painful memories.
The exact kind of know-it-all attitude I was talking about. Thanks for the demonstration :)
When authoring, you have something that shows intermediate results just like Excel, making troubleshooting without dedicated debugging still pretty doable. And then you can still run them headless, and you can check them into version control, and diffs are readable enough.
[1] https://versionxl.com/ [2] https://github.com/terminusdb/terminusdb
If you're deploying large-scale models, https://timelydataflow.github.io/differential-dataflow/ may be of interest (this is used, for instance, by https://materialize.com/)
It may not be that hard to replicate the set of formulas you need to get 90%+ of your excel model.
If someone implements a 90% reimplementation of Excel in Python that would be a really useful library for stuff like this. You could do some neat stuff with dependency identification too.
Do not ask me for source, but I remember opting to do just that and making it work after being unable to replicate Excel's results in an app that was supposed to replicate it. This was using Delphi, and the solution was to load the Excel spreadsheet as a COM object, programmatically write input data and collect the results. Loading the COM object was as slow as starting Excel but no Excel window ever showed up. This was circa Windows NT 4.0, so possibly 1998? And I would bet this would still work.
Yes, it would. You can operate a spreadsheet in that manner using any .NET[1] language. You could do it with any COM aware platform before .NET existed. Today you could use Python if you want to remain among the cool kids while doing it[2]. Judging by the popularity of that repo there are a bunch of people doing exactly that.
This is a late 90's era problem. I have a hard time imagining any programmer having difficulty with this. Then or now. Literally anything that could run a VB macro could programmatically manipulate an Excel spreadsheet.
Such approaches are fragile. No doubt about it. They rarely survive a major version upgrade of any component without some fussing. On the other hand the same basic APIs that emerged in the 90s work today with little conceptual change, so the value of the investment in this knowledge has never been wasted.
[1] https://docs.microsoft.com/en-us/dotnet/api/microsoft.office... [2] https://github.com/mhammond/pywin32
It feels like a subset of this should be an open source app (that is, turn an excel spreadsheet into a C# app) for anyone looking for an idea.