Hi Keith,
First - thanks for your work here! Looks very interesting.
I’ve got an =PY() implementation that runs in Collabora Online and seems
close to being ready to submit to Gerrit, but I wanted to discuss a few
things before we go too far.
I expect Quikee will ultimately want to review this.
- This design treats PY() as a spreadsheet formula with explicit
dataflow. Conceptually it is PY(code, data…): the Python code (or a
cell reference to shared code) is one argument, spreadsheet ranges
are passed explicitly as formula arguments, which define the
dependencies in the Calc DAG. For usability, single-row and single-
column inputs are normalized to 1D data, while true rectangular
blocks remain 2D. The script publishes its output through result =
…, and the returned value is mapped back into Calc scalars or spill
ranges.
That seems like a useful feature; but I expect we will want (for interop. reasons) to make =PY() behave like the Excel one - this brings a number of interesting issues.
In particular that from a quick skim of the docs - it seems like Microsoft builds all your python into (effectively) a single script that executes in Excel’s defined calculation order: left-to-right,top-to-bottom - and we are dramatically more flexible in terms of the calculation graph.
Microsoft’s =PY() is closer to an embedded Python cell than to a
conventional spreadsheet function. Its public syntax is =PY(python_code,
return_type), where python_code is static text and return_type selects
either an Excel value projection or a Python object result. Spreadsheet
data is typically accessed indirectly from inside the Python code via
xl(…), rather than through explicit formula arguments. As a result,
dependency discovery and recalculation semantics are tied to Excel’s
Python integration layer rather than to ordinary formula argument flow.
Right; so it’s necessary to re-calculate everything python in one shot I think (?); that is pretty weird to a spreadsheet-guy but perhaps there is some sensible rationale around data-frames, pandas type stuff and so
on: not entirely clear. Its not entirely clear to me what happens if other cells depend on python results - it would (really!) suck to have to sort our calculation
order into excel-order and block unrelated calculations on the results
of PY().
There is also a scheduling difference. Our model is intended to follow
normal spreadsheet dependency semantics: if a Python cell depends on
A1:B10, that dependency is visible in the formula signature itself.
Microsoft’s Python-in-Excel model has specialized worksheet behavior,
including row-major evaluation of Python cells, which is not the same as
simply adding another pure function node to the formula graph.
I must confess that reading the docs on the Python integration feature in Excel looks extremely contrived, different and looks like a classic of product graunching. Still I’ve not really had much time to think through why it might be useful to do this.
The result model is also different. Our contract is assignment-oriented
(result = …) and aimed at producing Calc-compatible scalar or matrix
values. Microsoft’s model distinguishes between “Excel value” and
“Python object” return modes, which means the cell may either
materialize a grid/spillable Excel value or hold an object-backed Python
result for downstream Python use.
Right; putting a new set of complex data-types eg. data-frame into calc formula results is ‘fun’ 
The benefit of the xl() function is that it’s a pain to pass in lots of
parameters, but you can mirror various random config values into a
range, and just pass that range in.
Another question has to do with the namespace. Right now the =PY is
in the org.extension.writeragent.PythonFunction namespace, but for
the C++ code I currently have:
com.sun.star.sheet.addin.PythonComputeFunctions.getPy. Should it be
ORG.OPENOFFICE.PY?
Great question; I’d leave to quikee; org.collaboraoffice. is a namespace we have a moral claim on though.
- Another issue has to do with the server side. I’ve got the C++ side
working but the compute service side is a Python HTTP listener which
calls into the existing plugin routines for the AST sandbox, auto-
imports, various helper routines, etc. LibrePy.oxt is 27 kloc, and
it’s not clear how much of it you will want in your builds. Maybe
you could keep the compute service in your tree (which is small and
security related) and just pull the rest from my tree for your
builds? I evolve it constantly still and I’d rather not have to go
through a huge process to push new versions. You would want a
different config / location for the parameters, but otherwise the
code should be usable as-is.
I’m really rather curious how this works; clearly we have an in-process python interpreter that we can rely on in CODA and also COOL (when enabled etc.) and we’d want to re-use that wherever possible. I’d appreciate some advice! more poisonous excel mis-features, calculation order, inter-PY()
invocation variable persistence (perhaps better called one big
script’iness 
and so on.
If we want to do something nicer with a custom =PY_CO() type thing we should think that through carefully of course so we don’t make such a
mess for the future ourselves. But I expect what I’m missing is the way
that data-analysists got used to using pandas and iterating / stepping
through large complex scripts - there must be some benefits to what they
have done.
Thoughts much appreciated but I’d love to see some twisted xlsx
using pathological =PY() stuff, so we can estimate the impact of forcing
calculation ordering etc.
Thanks for digging deeply into this !
Best regards,
Michael. (who annoyingly had to edit his indentation on-line to get discord to do something sensible here.)
