Hacker News Reader

Comment by hugi | Hacker News Reader

A few years back, I was tasked with a similar thing. A government ministry was creating a complex calculator for a (very anticipated) public project that was supposed to go on their website. We started out using pure JS but the mathematician working on the project kept giving us new Excel documents with extremely heavy changes to the algorithms.

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.

View on HN

The Microsoft Graph APIs in Microsoft 365/Office 365 give you pretty much all of the Excel execution engine as REST endpoints "in the Cloud" if you just store your Excel files in SharePoint.

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.

> turducken business applications

Great analogy

As a rapid prototyping tool, it doesn’t sound terrible, honestly. Many people are comfortable with Excel, so let them use it! You’re gonna use some calculation engine on the backend, might as well be the tool that contains the ”reference” calculations.

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

Love it.

It's simple and effective until a part of the rube goldberg machine breaks...

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.

No.

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.

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.

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.

I did something similar for a few clients, but mainly for automating documents. People were copying and pasting between Excel and Word, so I made them systems that link the two together. I had enough clients asking for something similar that I made a SaaS product that does it. Gives them a nice little interface that links web form fields to named ranges, and then a simple templating language to insert those fields into a Word document. Instead of writing a calculation engine for our webforms, I just used Excel. It's pretty powerful, and more than I could have implemented if starting from scratch.

Reply on news.ycombinator.com