[HN Gopher] Array languages vs. the curse of the spreadsheet
___________________________________________________________________
Array languages vs. the curse of the spreadsheet
Author : PaulHoule
Score : 45 points
Date : 2024-03-05 20:32 UTC (2 hours ago)
(HTM) web link (blog.dhsdevelopments.com)
(TXT) w3m dump (blog.dhsdevelopments.com)
| Bostonian wrote:
| I wonder if someone has worked on automatic translation of Excel
| spreadsheets with formulas to a proper programming language.
| Using the Python openpyxl package, you can read an Excel
| spreadsheet and get the contents of each cell, either a value or
| a formula. Having stored this data, it should be possible to
|
| (1) look for changes in formulas, which could indicate an error.
| Why is the formula for cell A99 different from that in A100?
|
| (2) write formulas in Python/NumPy or another language that
| replicates the formulas.
| azhenley wrote:
| It is a proper programming language.
|
| For #1, there has been research on linting and automated
| semantic error or smell detection in Excel (e.g.,
| https://github.com/ExceLint/ExceLint). Some features are live
| in Excel today!
|
| For #2, Excel now supports Python.
|
| I don't think you need to do any conversions, all of the
| necessary information exists in the spreadsheet.
| paulddraper wrote:
| > It is a proper programming language.
|
| How do you define a reusable function?
|
| ---
|
| (I know the answer....it's a proper programming language.)
| azhenley wrote:
| Excel supports reusable lambdas:
| https://support.microsoft.com/en-us/office/lambda-
| function-b...
| skissane wrote:
| Lambda support in Excel is quite new though, isn't it? If
| the argument is "Excel is a proper programming language
| because it now supports lambdas", that implies that for
| most of its existence it hasn't been one
| wizzwizz4 wrote:
| We've had VLOOKUP for a while.
| paulddraper wrote:
| That's a dictionary, not a general function.
| paulddraper wrote:
| If we are talking Excel 365 after Feb 2022, then yes.
|
| If we're talking Excel 2021, Excel 2019, etc. then no.
| blowski wrote:
| So Excel supports reusable Lambdas then? In the same way
| that many tools introduce new features and users need to
| upgrade versions to get them.
| sam2426679 wrote:
| VBA
| kevingadd wrote:
| Definitely has me wondering: there are lots of regex compilers
| out there, so where are the spreadsheet compilers? I should be
| able to convert a spreadsheet wholesale to Java or c# that
| clearly expresses the cell relationships, then go in and add
| names (if they aren't already there)
| KMag wrote:
| 20 years ago, I was told there was such a beast for turning
| an Excel spreadsheet into a C# application, targeted at
| accelerating/productionizing financial models. I never used
| it, though.
| lukasb wrote:
| The example of "why spreadsheets bad" made me wonder - why don't
| spreadsheets have a visual indicator for cells with a formula
| rather than a static value?
| kevingadd wrote:
| Iirc Excel has a dependency visualizer that will highlight
| formulas. It's called Trace dependents
| wslh wrote:
| I think the spreadsheet metaphor is right but the spreadsheet
| UX/UIs has not aged well. Why Microsoft would do important
| changes into one of its cash cows and suffer a new wave of bugs
| and incompatibilities?
| timeagain wrote:
| It's true that a new change can intriduce more bugs, but I
| have a suspicion that the excel team is working with brains
| and a test harness that most software projects don't have the
| time or money for.
| jamses wrote:
| Ctrl+` will show the underlying formula, or you could use
| conditional formatting to apply whatever style you want to
| static values =NOT(ISNUMBER(FIND("=",FORMULATEXT(A1)))), or you
| could write a VBA macro to do that and more (e.g. find all the
| formula that have been zeroised, etc).
| n_plus_1_acc wrote:
| Why not ISFORMULA
| binarymax wrote:
| Excel does do a good job of showing inconsistent cells in
| columns, it shows a green corner.
|
| But I do like the idea of different kinds of cells having a
| different style entirely. You can style them yourself but a
| default theme would be a cool feature.
| wrs wrote:
| I recently discovered that Excel gained "array formulas" a few
| years ago. [0] It's a little hard to grasp for me because arrays
| are still flattened out as individual values in the sheet, but it
| has been useful to write a single formula to process a group of
| values rather than filling across/down and perhaps losing sync
| when the formula is changed.
|
| [0] https://support.microsoft.com/en-us/office/guidelines-and-
| ex...
| 1980phipsi wrote:
| Excel has had array formulas since at least 2003.
| blowski wrote:
| I believe the OP is referring to array formulas which
| effectively explode the results into multiple cells, which
| was introduced in 2018.
| jd3 wrote:
| I'm surprised that there was no mention of Chris Pearson's Shakti
| (k9 by Arthur Whitney) powered mesh spreadsheet.
|
| https://www.youtube.com/watch?v=CEG9pFNYBCI
|
| https://mesh-spreadsheet.com/
| karmakaze wrote:
| This discussion wouldn't be complete without covering Lotus
| Improv on NeXT. It did two things differently: data, formulaz,
| and presentation were separate layers and the formulas weren't
| applied per cell but rather as a relational invariant between all
| sets of cells in a direction for the full range. One last thing
| it used was naming everything with natural names for columns and
| tables much like a database schema.
|
| The Windows version of Improv wasn't nearly as good, possibly
| because it seemed too advanced for PC users familiar with 1-2-3,
| or because it might disrupt an already very profitable revenue
| stream.
|
| I believe there's a spiritual successor in Quantrix.
___________________________________________________________________
(page generated 2024-03-05 23:00 UTC)