>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.
This is the kind of simple and effective solution that programmers who think they know everything would scoff at.
I have used the google sheets API to implement something similar when working with a nonprofit. They needed a fairly complex listing on their website that needed search/sort/filter/mapping and needed to update this list regularly. So I just took their existing google sheets document, and accessed it as a read-only database in the browser using Google's REST api and it was fairly painless! If they ever broke anything I could easily go into the spreadsheet and fix it. This approach really reduced the effort needed. If I had to write a "proper" interface for them to enter and update their data I wouldn't have had time to work on their project.
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.
Most of these are easy to work around our aren't unique to Excel:
1 & 2) isn't unique to Excel, incorrect inputs will give incorrect results in code too
3, 4 & 5) worked around by getting the internal raw numeric value then formatting it as required.
Sure, you know better than the person that actually implemented it and tells us it worked out fine. Because you've done something similar before and failed.
The exact kind of know-it-all attitude I was talking about. Thanks for the demonstration :)
To be honest I also had bad experiences with processes like this - very simple data entry mistakes which were basically invisible to the user made the entire thing fail.
This is the kind of simple and effective solution that programmers who think they know everything would scoff at.
Love it.