Try to integrate better with xlwings Lite

[gdementen]

I played with xlwings Lite (an addon for Excel). It is impressively simple to install and get going. Using larray works fine too (you only need to add it to the requirements), but we could integrate it better by providing some toolbox functions.

Here is what I tried:

@func(namespace="larray")
def slice(arr: la.Array, s: str):
    if ';' in s:
        s = tuple(s.split(';'))
    return arr[s]


@func(namespace="larray")
def sum(arr: la.Array, s: str):
    return arr.sum(s)


@func(namespace="larray")
@arg("data", ndim=2)
def ARRAY(data):
    return la.from_lists(data)

Then, I defined a simple 2D array (ndtest((2, 3))

a\b | b0 | b1 | b2
--- | -- | -- | --
 a0 |  0 |  1 |  2
 a1 |  3 |  4 |  5

and used the following formulas with success:

=LARRAY.SLICE(LARRAY.ARRAY(A1:D3); "a0;b1,b2")

 b | b1 | b2
-- | -- | --
   |  1 |  2

=LARRAY.SUM(LARRAY.ARRAY(A1:D3); "b0,b1 >> b01; b1,b2 >> b12")

a\b | b01 | b12
--- | --- | ---
 a0 |   1 |   3
 a1 |   7 |   9

I also tried working directly on the range object (to simplify the writing and allow specifying only the top-left corner and let the function expand the range), but that does not work (and I got confirmation from the addin author that it is a fundamental limitation).

NONE of the following work. Depending on the case, those were meant to work with =RNG_ARRAY(A1:D3) or =RNG_ARRAY("A1:D3") as formulas :

@func
def RNG_ARRAY(rng: xw.Range):
    return la.asarray(rng.value)

@func
@arg('rng', expand="table")
def RNG_ARRAY(rng):
    return la.asarray(rng.value)

@func
def RNG_ARRAY(rng: str):
    rng = xw.Range(rng)
    full_rng = rng.expand()
    return la.asarray(full_rng.value)

@func
def RNG_ARRAY(rng: str):
    sh = xw.sheets.active
    rng = sh[rng]
    return la.asarray(rng.value)

What I could do though is

1. implement more methods. I will probably opt for all-in-one methods which convert to array, do something, convert back to cells
2. monitor xlwings Lite progress. There are a couple features on their roadmap which would make it a lot more interesting for us:
   • Custom functions: add support for streaming functions and object handles (AFAIU, this would allow us to store Arrays in a cell without having it expand to cells)
   • Enable access to local files.
   • Optionally allow to store the Python code externally like Office Scripts do.
   • ...
3. fix issue Better support for reading files without using any column as index, or any row as column names #1102 (because it is especially annoying in this context)

Self-reminder: if I have more questions/issues about xlwings Lite, I should use https://github.com/xlwings/xlwings-lite