[Discussion] Formulas and scripting support

[quigleyj97]

I'm opening this as a discussion item to gauge interest in a few different directions that this plugin might take.

Before we go any further, I should note that this is a major undertaking. Even regular formulas pose a significant challenge, and VBA macros seem largely intractable without interfacing with a pre-existing Excel installation.

With that in mind, I can think of a few different options. I'd like to hear your opinions on them, as ultimately what matters most is the end user experience.

1. Interface with Excel to do the calculations

   • Pros:
     • Guaranteed base level of support- We can support whatever Excel does, and can assure a certain level of compatibility with the desktop application
     • Likely the easiest option
   • Cons:
     • Users must have Excel installed for this to work
     • Cross platform: I don't have a Mac, and we'd lose Linux compatibility.

2. Create our own formula parser/runner, executing on the client only

   • Pros:
     • Keeps the plugin from depending on a kernel or locking into a particular kernel language
     • 80:20 rule: By supporting simple formulas we can get 80% of worksheets functioning for only 20% of the effort
   • Cons:
     • Shuts out more complex user stories

3. Roll our own scripting framework, using a Jupyter kernel as a back-end

   • Pros:
     • Allows us to have a fully open-source stack
     • Gives users a novel workflow within the Jupyter ecosystem
     • Allows the plugin to integrate more deeply with Jupyter, providing a nicer experience (conceptually, we could integrate with IPyWidgets, Matplotlib, notebooks, consoles, etc)
   • Cons:
     • Significant amount of effort required
     • DataFrames don't express spreadsheets well
       • Spreadsheets impose no restrictions on formats or datatypes, and may have arbitrary calculations anywhere
       • If we wanted to provide a kernel-side user API, we'd need to address this somehow
     • Duplicated effort w/ IPySheet?

I'm leaning towards option 2 myself, but option 3 excites me a lot.

[quigleyj97]

Revisiting this, I'm now more inclined to staying within the Python ecosystem using xlwings. Talking with a few real-world users of that library, it seems well-suited for production Excel and will be far more battle-tested than anything I could roll.

This leaves a major gap in the current editor w.r.t. editing and distribution;

• Limited editing is supported in SlickGrid, but requires some investment and isn't always as nice as an Excel pro might want
• The Model does not support live-updates, and will be very sluggish without some changes to it's interface
• Using xlwings as a backend would require distributing a serverextension or kernel, neither of which can be done using NPM.