[Haskell-cafe] Speculation, OT: Program a Spreadsheet

[trent shipley]

On Tue, Nov 21, 2017 at 1:50 PM Joachim Durchholz <jo at durchholz.org> wrote:

> Am 21.11.2017 um 08:13 schrieb trent shipley:
> >     Which one it is that you are interested in?
> >     "Three-dimensional" as in "we have row, column, and layer", or as in
> "we
> >     have hierarchical workbooks"?
> >
> > They are not mutually exclusive. A workbook can contain sheets--just
> > like now. IN ADDITION, you can put workbooks in workbooks, until you
> > reach the root workbook, which is also a file.
>
> Not an answer that helps me understand your point.
>

One more try.

+---+---+
|   |   | Sheet. 2d. rows & colums.
+---+---+
|   |   |
+---+---+



    +---+---+
   /   /   /|
  +---+---+ +
 /   /   /|/|
+---+---+ + +
|   |   |/|/   Worksheet or Virtual Worksheet. 3d.
+---+---+ +    Stacked sheets. Maximum for mainstream spreadsheets
|   |   |/
+---+---+



root workbook (file)
+---------------------------------------+
|        +---+---+     +---+---+        |
| [0]   /   /   /|    /   /   /|  [1]   |
|      +---+---+ +   +---+---+ +        |
|     /   /   /|/|  /   /   /|/|        |
|    +---+---+ + + +---+---+ + +        |
|    |   |   |/|/  |   |   |/|/         |
|    +---+---+ +   +---+---+ +          |
|    |   |   |/    |   |   |/           |
|    +---+---+     +---+---+            |
|                                       |
+---------------------------------------+

File workbook "root" contains, rectangular (not jagged) virtual workbooks 0
and 1. It is 4d. Assuming a theoretical infinte machine, a root workbook
could contain virtual workbook containers that contain virtual workbooks to
an arbitrary depth. Virtual workbooks can contain either sheets, or virtual
workbooks (or, at the application designer's option, both sheets and
virtual workbooks.

A virtual workbook is just a container. Like arrays or matrixes virtual
workbooks are a convenient way to model n-dimensions.

Note, that it would be convenient if the spreadsheet application numbered
columns, rows, sheets, and virtual workbooks by default instead of naming
sheets like Excel seems to do.

If you were to allow function libraries or objects in a spreadsheet
application you could store them in workbooks or virtual workbooks,
extending the concept of putting a proper function in sheet marked with the
function property.

-------------------------------------------------------


> >      > Also, if sheets can be marked as being functions, virtual
> >     workbooks can
> >      > support libraries of functions, or objects, if you want such
> things.
> >
> >     Sometimes I think Excel etc. got it all wrong.
> >     People routinely reserve areas inside the spreadsheet for different
> >     tasks: input, intermediate results, output. Increasing the size of
> each
> >     area becomes a pain.
>

You would need some objects.  The idea of linked areas. I'm guessing that
Excel tables, kinda do this, but only for one contiguous area on one sheet.


> >
> >     I'd want to have a spreadsheet where it's easy to define multiple
> sheets
> >     on the same "table". Extending the input sheet with another row
> >     automatically extends the output sheet by a row (and if you have
> rollup
> >     then we get more than one row).


So this would be a three dimensional Excel table basically? It would
involve the program taking a best guess at what you want in a lot of cases.



> Intermediate sheets could be replaced by
> >     a function definition.
> >
> >     It's a bit of an ergonomic challenge; functions are much more
> abstract,
> >     and users will want to see how the results of applying a function to
> an
> >     input area look like. So the mental model might be that we still have
> >     intermediate sheets, but with just a single function,


It has been suggested that you can write functions when you would want a
complex chain of calculations. You would call the function like any other
function. You would still have cell by cell replication, but the user
defined function would be defined one, and called many times.

How would you bind one function to a column/row/range. You would need a
visual representation of the binding. Current spreadsheets don't do that.
(Except Emacs Org Mode spreadsheets have column functions.)

In the existing paradigm, you would try your best to generate a plausible
solution, then copy it to each cell in the new range in the calculation
layer(s). My intuition is to get that working, THEN conquer the single
function bound to many cells problem.


> and the
> >     intermediate sheets can be create, destroyed, shown and hidden
> easily.
>

Are you hoping to autogenerate the functions?

Data -> range bound mono-function on intermediate function page -> Results

>     The other challenge would be dealing with headers. Headers are
> >     important, but for the intermediate we have just the function so it
> >     needs to provide the headers. I.e. the function result would have to
> be
> >     a named tuple, and if you want multiline headers, we get a
> hierarchical
> >     named tuple.
>

Concatenated names, generated functions get numbers added to the
concatenated name. I don't think you can make the names you generate
terribly pretty or human readable.

The intermediate function guessed and generated for the addition isn't
transient, it's durable data too.


> >     Manipulating the column order would also manipulate the tuple
> >     definition, i.e. you'd have to think about how GUI manipulation
> affects
> >     the function definition.
>

That's more heuristics, and UX/UI testing. Excel guesses wrong on this
frequently, and when it does it's a pain. But mostly it's behaves well. I'd
want to leverage the current engine that deals with GUI implications for
the model.

I'm still not certain I'm on the same page as you, but that's a little more
effort.


> >
> > (No longer Haskell discussion, but anything civil goes in Cafe)
> >
> > Sounds both interesting and relevant, but I don't quite get it. Can you
> > provide a simple, concrete, narrative example--with pictures (ASCII
> > OK)--if possible.
>
> Nah, I'm only good at walls of text.
> And at answering directed questions - start asking about what exactly
> you didn't understand.
> Guessing what you might have not understood is going to be a waste of
> everybody's time.
> (Besides, if you need to tell where exactly you didn't understand may
> help your understanding. Well, the keyword being "may"; I know it worked
> for me on various occasions.)
>
> >     There's pivot tables, so the difference between row and column isn't
> as
> >     clear-cut as the above assumes.
> >
> > Don't worry about pivot tables. Simple cases first.
>
> You have to design for the maximum complication.
> Otherwise, stuff will not fit in well, and you'll be stuck with either a
> half-assed solution, nothing at all, or a full rewrite.
> Trust me. I've been coding and designing software for decades.
>
> >     There are a few more things to consider. It's firmly in the GUI
> >     ergonomics area.
> >     For this kind of stuff, you don't care whether the programming
> language
> >     evaluation semantics matches the problem domain (spreadsheet
> functional
> >     semantics is easy to do in any language, it was done in Assembler
> with
> >     Lotus 1-2-3), you care whether it is easy to prototype GUI
> approaches,
> >     which means GUI libraries that make it easy to create new widgets.
> >
> > Any suggestions for a GUI library--that kind of implies C++ or Java.
> > Ideally your GUI would be cross platform.
>
> I know only the Java GUI landscape well enough to say anything.
> In that area, dialog boxes, menus and such will be easy enough with any
> lib, but you'll be mostly on your own for the grid. Also if you aim for
> unlimited size you'll get into a lot of hairy memory management issues -
> a 64kx64k grid has 1 million cells, multiply that with 1KB per cell and
> you end with a memory footprint of a gigabyte. Evaluating a formula for
> each cell is going to get you a slideshow-like performance.
>

<https://en.wikipedia.org/wiki/List_of_spreadsheet_software>
https://en.wikipedia.org/wiki/List_of_spreadsheet_software

With the usual cautions about Wikipedia, there is a chart relevant to your
argument on this page. It's probably really out of date.

Does anyone know anything about Lotus Improv or Flexisheet?


> So... managing which cells are actually needed and which aren't is going
> to be a major point of the software.
>
> Did I mention that coding a spreadsheet is a huge project? ;-)
>
> >      > Programmers have to learn multiple languages, and most wind up
> liking
> >      > it. They also have favorite languages. LibreOffice Calc has about
> >     four
> >      > bolt-on languages. As I recall, none of them are a functional
> >      > programming language. Maybe a few programmers should get together
> and
> >      > add one as a sub-project.
> >
> >     The problem is that any additional language needs to be installed. So
> >     either the new language needs to be integrated in the LO project, or
> it
> >     is not and anybody who passes on a spreadsheet in the new language
> needs
> >     to tell the recipients how to install the plugin for the language.
> >
> >     Maybe that's the reason why only the four languages exist.
> >
> > Doubt it.
> >
> > A given plugin's installation could be automated and check for
> dependencies.
>
> Heh. Famous last words.
> The Java folks got a 90% solution working - just for managing
> dependencies, mind you; for some people it doesn't work at all because
> they don't know how to get past the corporate firewall. Besides, most
> corporate firewalls do a man-in-the-middle certificate nowadays and will
> inspect and reject some packets and not others, so any update mechanism
> must be able to deal with wilfully unreliable internet connections.
>
> So that's another area that's going to bind a lot of developer resources.
>

The last few companies I've worked for had the policy: IT owns your tech.
DON'T put software on it. If you need software we haven't given you, you
don't really need it, but if you really, really need it, then it will go to
the change committee who will evaluate your request, audit the software you
want for security threats, price, and maintainability, and MIGHT give it to
you in several months, if you are lucky. I can't imagine trying to maintain
my own software stack it a big enterprise. That's some poor sys admin's job.


> _______________________________________________
> Haskell-Cafe mailing list
> To (un)subscribe, modify options or view archives go to:
> http://mail.haskell.org/cgi-bin/mailman/listinfo/haskell-cafe
> Only members subscribed via the mailman list are allowed to post.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.haskell.org/pipermail/haskell-cafe/attachments/20171122/02abc990/attachment.html>