cunnane / xloil Goto Github PK

Would it be possible to support bulk editing of cell formulas using the range object? It is very slow to change large amounts of formulas individually.

It could very much be converted to 2d numpy string / dtype('O') array and back.

Alternatively, is there an equivalent of stopping and re-enabling workbook calculation and redraw for the duration of the macro just as it could be done in pure VBA?

Thanks in advance for any ideas!

Hi! I was trying to assist a new user to install the library with no success.

• Fresh xloil install
• Using Anaconda with Python 3.10
• The following error occurs: Failed LoadLibrary for: xlOil_Python39.pyd
• Tried to include xlOilPythonVersion="3.10", but the error keeps happening

Any tips? Thanks in advance!

I would like to display a MsgBox using xlOIl

In Excel VBA, I can use the MsgBox function

Sub MsgBox()
MsgBox "xlOil is a great lib"
End Sub

I read MS win32Api and pywin32-docs

@ xlo.func(command=True)
def msg_box():
    xlo.app().MessageBox(0, "xlOil is a great lib")
    xlo.app().win32gui.MessageBox(0, "xlOil is a great lib")

are both giving me a module not found error

AttributeError: '<win32com.gen_py.Microsoft Excel 16.0 Object Library._Application instance at 0x2345992157072>' object has no attribute 'MessageBox'

AttributeError: '<win32com.gen_py.Microsoft Excel 16.0 Object Library._Application instance at 0x1712010462880>' object has no attribute 'win32gui'

is the pywin32com library fully accessible

@cunnane, tx for helping me getting up and running with xloil

this is my starting point ( a simplified version of ccxt recommended usage ) and my goal is to use xloil to write binance_pr and ccxt_pr to excel every 10 seconds.

any help would be appreciated

import xloil as xlo
import datetime, asyncio

binance_pr = 1
ccxt_pr = 10

class worker():
    def __init__(self, name, duration,):
        self.__stopped = False
        self.name = name
        self.duration = duration

    async def fetch_binance_pr(self):  # simulating a ws connection
        while not self.__stopped:
            global binance_pr
            binance_pr = binance_pr + 1
            print(self.name, 'received a pr from binance. Pr is', binance_pr)
            await asyncio.sleep(self.duration)

    async def fetch_ccxt_pr(self):
        while not self.__stopped:
            global ccxt_pr
            ccxt_pr = ccxt_pr + 10
            print(self.name, 'received a pr from ccxt. Pr is', ccxt_pr)
            await asyncio.sleep(self.duration)


async def main():
    con_1 = worker('ws_binance', 2)
    con_2 = worker('ws_ccxt', 5)
    loops = [
            con_1.fetch_binance_pr(),
            con_2.fetch_ccxt_pr(),
        ]
    # Here we have two coroutines running parallely which are supposed to run forever
    await asyncio.gather(*loops) 

if __name__ == "__main__":
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())

asyncio.run(main())

Using windows 10, python 3.9, Excel 2021, xloil 0.17.17

Hi, I am experimenting with the latest version of xloil following your Getting started

a couple of questions

1. the excel ribbon disappeared after I created My first xlOil module and changed directory and load modules

2. xloil cannot find the RtdServer() using your example code

import xloil, datetime, asyncio
# First create a new RTD COM server so the `topic` strings don't collide
_rtdServer = xloil.RtdServer()

@xloil.func
def pyClock2(secs):

    async def fetch() -> dt.datetime:
        while True:
            await asyncio.sleep(secs)
            yield dt.datetime.now()

    return xloil.rtd.subscribe(_rtdServer, "Time:" + str(secs), fetch)

3 ) problem when closing excel file as cross linked to 22

Thankyou

I'm trying to function that return matploilib figure.
My own and sample function both cause com error.
Is there anything wrong with my procedure?

import xloil as xlo
import xloil.matplotlib
from matplotlib import pyplot
from fem_2d import *

# my own function
@xlo.func(macro=True)
def plot_model():
    fig, ax = plt.subplots()
    mdl = make_portal_frame2_for_model_plot()
    mdl.analyze()
    mdl.plot_model(ax)
    return fig

# sample function
@xlo.func(macro=True)
def pyTestPlot(x, y, **kwargs):
    fig = pyplot.figure()
    ax = fig.add_subplot(111)
    ax.plot(x, y, **kwargs)
    return fig

Please refer the log file.
In log, Japanese message "種類が一致しません。" means "Type mismatch."
log_2023-0209-2.txt

windows10
python3.8.10(WinPython)
xlOil 0.16.4
(due to the issue#32 of 0.17.1)

aiohttp Version: 3.8.1
The following python code request first pokemon name and measure latency.

When called from python Average latency is usually 55 mls

when called from XLL using xloil latency deteriorates and becomes around 200 mls

I am not really interested in pokemons :-) but I noticed the problem sending orders to crypto exchanges using CCXT.
CCXT underlying networking lib is aiohttp.

Unfortunately I still have some problem as in issue 42 therefore I cannot use Xloil to write to excel. I just discovered github does not support xlsx and I do not know how I can send you the excel file

D4 =IFERROR(py_start_latency_test(C4),"Test is running")
D7 =IFERROR(py_get_latency_new(C7),"Test is running or waiting to start")
D7 select and copy and past to D8:D16
D18  =IFERROR( AVERAGE(D7:D16), "Test is running or waiting to start")

So far I managed to use xloil to to build a very nice and useful back end. I can now monitor my algos and I did it just using async function.
I am looking forward to explore further the potential offered by your library.
Latency is essential for my trading logic so I really need help on this.

Thank you

import asyncio
import xloil as xlo
import aiohttp
import time
results = []
elapsed = 0
num_orders = 10
print('aiohttp Version:', aiohttp.__version__)

""" request first pokemon name and measure latency. Can call it from XLL/xloil"""

# RuntimeError: Event loop is closed.  The error typically causes only a noisy shutdown
# https://github.com/Azure/azure-sdk-for-python/issues/9060
# https://github.com/aio-libs/aiohttp/issues/4324

async def main():
    global results
    global elapsed
    results = []
    elapsed = 0
    async with aiohttp.ClientSession() as session:
        pokemon_url = f'https://pokeapi.co/api/v2/pokemon/{1}'
        for i in range(0, num_orders):
            start = time.time_ns() // 1000000
            async with session.get(pokemon_url) as resp:
                pokemon = await resp.json()
                print(i, pokemon['name'])
                end = time.time_ns() // 1000000
                elapsed = int(end - start)
                results.append(elapsed)
                print(elapsed, 'ms')
                await asyncio.sleep(1)
        s = sum(results)
        average = s / len(results)
        print('Average:', average, 'ms')

@xlo.func
async def py_start_latency_test(on_off):
    if on_off == 1:
        await main()
        return 'back to 0 then 1 for another test'
    else:
        return '1 to start the test'

@xlo.func
async def py_get_latency_new(num):
    num = int(num)
    while True:
        if len(results) < num_orders:
            yield 'Test is running or waiting to start'
        else:
            yield results[num - 1]
        await asyncio.sleep(2)

# uncomment To run from python
# asyncio.run(main())

Hi,

I'm trying it with Excel 365 MSO (16.0.14326.21116) 64-bit and Python 3.7.4.

Create the file MyTest.py with:

import xloil as xlo

@xlo.func
def Greetings(who):
    return f'Hello {who}!'

and saved a MyTest.xlsm in the same folder.

After calling =Greetings("anything") excel breaks, without leaving any relevant log in file.

Could you help me understand what's going on?

Thanks a lot

Hi Steven,

thank you so much for this excellent package. I had speed issues with xlwings which are now history. xloil is truly a hidden gem in this regard.

Now to the issue:
I ran into a minor issue when running xloil on environments where python was installed only for the current user.

more precisly a directory corresponding to something like this:
%USERPROFILE%\AppData\Local\Programs\python\python39

Then the registry key for Software\Python only exist in HKEY_CURRENT_USER and not on HKEY_LOCAL_MACHINE

As a result, the xloil.log reports an error on
xloil_ribbon.py on line 144:
with reg.OpenKey(reg.HKEY_LOCAL_MACHINE, "Software\Python") as kPythons:

As a quick-fix, i just replaced it:
with reg.OpenKey(reg.HKEY_CURRENT_USER, "Software\Python") as kPythons:
and it worked. It might be the easiest to just include both locations?

Thanks in advance
Dominik

I have implemented an RTD function that consumes data from a kafka topic and streams this data to excel. My RTD function is provided below.

import json
import asyncio
import datetime
import xloil
import xloil.pandas
import xlwings as xw
import pandas as pd
import numpy as np

from config import Config
from aiokafka import AIOKafkaConsumer

_rtdServer = xloil.RtdServer()

@xloil.func
def subscribe():
    '''
    Main User Defined Function for subscribing to kafka consumer.
    '''

    async def fetch() -> float:
        while True:
            try:
                # Initialize kafka consumer.
                consumer = AIOKafkaConsumer('TopicName', 
                                value_deserializer=lambda m: json.loads(m.decode('utf-8')),
                                bootstrap_servers=Config.kafka_socket)
                await consumer.start()
                async for msg in consumer:
                    data = msg.value
                    
                    yield float(data['values']['value'])
            except Exception as e:
                print(f"Error in kafka consumer: {e}")
                asyncio.sleep(2)
            finally:
                await consumer.stop()

    return xloil.rtd.subscribe(_rtdServer, "rtd_subscription", fetch)

I have a problem where the rtd function stops updating data in excel. And excel has to be restarted to be able to automatically update data again (Resubscribing by deleting function does not work. A full restart of excel is the only thing working).

I get the following error in the logs (Unsure if it is related to the above mentioned problem):
[2023-08-03 08:39:39.958] [logger] [error] [ComEventSink.cpp:338] COM Error 0x800ac472: Unknown error 0x800AC472

What could be the cause of this behaviour?

Hi,

My UDF add-in works fine on 0.16.4, but I update to 0.17.1 got some error at registering udf functions.

Please refer the log file.
log_2023-0209.txt

Thanks in advance.

windows10
python3.8.10(WinPython)
xlOil 0.17.1

Thank you for the excellent library. A couple of minor issues spotted that I think are actually problems with python 3.8, but worth mentioning in case helpful to new users of xlOil.

1. xlOil couldn't auto-detect the python version after I installed it from my anaconda environment. I had to manually enter it at XLOIL_PYTHON_VERSION
2. My python code makes various standard imports including numpy. When loading my library from xlOil, python 3.8 could not find the dlls for these libraries. I worked around it by adding the following lines at the top of my script before importing xloil:

import os
s_path = os.environ['HOMEPATH']
loc_path = s_path.replace('\','/')
os.add_dll_directory('C:/' + loc_path + '/anaconda3/envs/env1.0/Library/bin')
#this is to fix a DLL location bug that appears to be prevalent in python 3.8

import xloil as xlo

Hello,
I'm using a UDF that gets a range variable.

I declare the sup_range variable as an xlo.Range and the variable type i get in the function is xloil_core._XllRange.
The variable type i wish to receive is xloil_core.Range which is more convenient for me to work with.
So I must do the conversion as seen in the code below.

Is there a way to get the variable in xloil_core.Range directly without the need for conversion?

@xlo.func
def xlo_rfq_sups_list(sup_range: xlo.Range, next_sup: int):

    # default var type
    print(f'{type(sup_range)}') # xloil_core._XllRange

    # convert 'xloil_core._XllRange' to 'xloil_core.Range'
    sup_range = xlo.Range(sup_range.address()) 
    print(f'{type(sup_range)}') # xloil_core.Range

@ xlo.func()
def py_test_array() -> xlo.Array:
    arr = np.array( [[ 1, 2, 3],
                 [ 4, 2, 5]])
    return arr

When uploading xloil I have this error. Same error if I use numpy.ndarray.

[2023-08-26 19:39:39.504] [logger] [error] [logging.py:25] Failed determing spec for 'py_test_array': Traceback (most recent call last):
  File "C:\shieldtrade\ave\venv\lib\site-packages\xloil\register.py", line 423, in decorate
    spec.return_converter = find_return_converter(return_type)
  File "C:\shieldtrade\ave\venv\lib\site-packages\xloil\register.py", line 131, in find_return_converter
    ret_con = Return_object()
NameError: name 'Return_object' is not defined

Turning on the debugger option in ribbon causes the start of a new excel instance. Then, a series of these messages follow:

After dismissing them, another instance of excel opens with the same message (presumably because the debugger is enabled there as well). This continues indefinitely until all excel processes are killed and debug server is disabled in the .ini file.

Clearly this looks like some command-line problem, where the debug server arguments are passed to excel executable.

Meanwhile, console shows these errors:

[23:08:26] [error] Error during coroutine: Traceback (most recent call last):
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\debugpy\server\api.py", line 237, in listen
    sock, _ = endpoints_listener.accept()
  File "c:\users\airgu\appdata\local\programs\python\python37\Lib\socket.py", line 212, in accept
    fd, addr = self._accept()
socket.timeout: timed out

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\xloil\register.py", line 139, in _logged_wrapper_async
    return await coro
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\xloil\xloil_ribbon.py", line 266, in set_debugger
    port=int(_settings.python['DebugPyPort']))
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\xloil\debug.py", line 163, in use_debugger
    debugpy_listen(**kwargs)
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\xloil\debug.py", line 60, in debugpy_listen
    connection = debugpy.listen(port)
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\debugpy\public_api.py", line 31, in wrapper
    return wrapped(*args, **kwargs)
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\debugpy\server\api.py", line 143, in debug
    log.reraise_exception("{0}() failed:", func.__name__, level="info")
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\debugpy\server\api.py", line 141, in debug
    return func(address, settrace_kwargs, **kwargs)
  File "c:\users\airgu\appdata\local\programs\python\python37\lib\site-packages\debugpy\server\api.py", line 251, in listen
    raise RuntimeError("timed out waiting for adapter to connect")
RuntimeError: timed out waiting for adapter to connect

I do have Python 3.7, debugpy package is installed. Windows 11, Excel 2010.

Hello,

during my experiments of running this on different versions of Python I have randomly encountered this error message during excel start:

[2022-12-16 15:40:20.366] [logger] [error] [Main.cpp:333] xloil_python init failed: Cannot set PYTHONHOME: failed to get the Python codec of the filesystem encoding. sys.path=
[2022-12-16 15:40:20.379] [logger] [error] [PluginLoader.cpp:193] Plugin load failed for c:\xloil-env\share\xloil\xlOil_Python.dll: Initialisation failed

Once I have enabled the trace level of logging, I think the error slightly changed, but the main point probably remains.

[2022-12-16 16:23:28.941] [logger] [info] [AddinLoader.cpp:57] Found core settings file '\\fs1\Users\<REDACTED>\AppData\Roaming\xlOil\xlOil.ini' for 'c:\xloil-env\share\xloil\xlOil.dll'
[2022-12-16 16:23:28.941] [logger] [debug] [FuncRegistry.cpp:188] Registering "xloRunInXLLContext" at entry point xloRunInXLLContext with 0 args
[2022-12-16 16:23:28.942] [logger] [debug] [FuncRegistry.cpp:188] Registering "xloVersion" at entry point xloVersion with 0 args
[2022-12-16 16:23:28.942] [logger] [debug] [FuncRegistry.cpp:188] Registering "xloLog" at entry point xloLog with 1 args
[2022-12-16 16:23:28.942] [logger] [debug] [FuncRegistry.cpp:188] Registering "xloHelp" at entry point xloHelp with 1 args
[2022-12-16 16:23:28.942] [logger] [debug] [FuncRegistry.cpp:188] Registering "xloRef" at entry point xloRef with 1 args
[2022-12-16 16:23:28.942] [logger] [debug] [FuncRegistry.cpp:188] Registering "xloVal" at entry point xloVal with 1 args
[2022-12-16 16:23:28.942] [logger] [debug] [FuncRegistry.cpp:188] Registering "RegistrationInfo_c285df7d28de5a46bee151c178bb4382" at entry point IntellisenseRegistrationInfo with 1 args
[2022-12-16 16:23:29.096] [logger] [debug] [Connect.cpp:81] Made COM connection to Excel at 'C:\Program Files\Microsoft Office\root\Office16' with hwnd=592568
[2022-12-16 16:23:29.096] [logger] [info] [PluginLoader.cpp:107] Loading plugin xlOil_Python
[2022-12-16 16:23:29.096] [logger] [debug] [PluginLoader.cpp:131] Setting environment variable: PYTHONCASEOK='1'
[2022-12-16 16:23:29.096] [logger] [debug] [PluginLoader.cpp:131] Setting environment variable: XLOIL_PYTHON_PATH=''
[2022-12-16 16:23:29.096] [logger] [debug] [PluginLoader.cpp:131] Setting environment variable: XLOIL_PYTHON_VERSION=''
[2022-12-16 16:23:29.096] [logger] [debug] [PluginLoader.cpp:131] Setting environment variable: PYTHONHOME='c:\xloil-env'
[2022-12-16 16:23:29.096] [logger] [debug] [PluginLoader.cpp:131] Setting environment variable: PYTHONPATH='%PYTHONPATH%;c:\xloil-env\Lib;c:\xloil-env\DLLs'
[2022-12-16 16:23:29.096] [logger] [debug] [PluginLoader.cpp:131] Setting environment variable: PATH='C:\Program Files\Microsoft Office\Root\Office16\;C:\Program Files\Python310\Scripts\;C:\Program Files\Python310\;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files\dotnet\;C:\Users\<REDACTER>\AppData\Local\Programs\Python\Launcher\;C:\Users\<REDACTED>\AppData\Local\Microsoft\WindowsApps;;C:\Program Files\Microsoft Office\root\Client;c:\xloil-env\share\xloil;c:\xloil-env;c:\xloil-env\Library\bin'
[2022-12-16 16:23:29.096] [logger] [debug] [PluginLoader.cpp:131] Setting environment variable: PYTHONPATH='%PYTHONPATH%;c:\xloil-env\Lib;c:\xloil-env\DLLs;%XLOIL_PYTHON_PATH%'
[2022-12-16 16:23:29.096] [logger] [debug] [PluginLoader.cpp:131] Setting environment variable: QT_QPA_PLATFORM_PLUGIN_PATH='c:\xloil-env\Library\plugins\platforms'
[2022-12-16 16:23:29.099] [logger] [debug] [Main.cpp:104] Python interpreter starting
[2022-12-16 16:23:29.104] [logger] [debug] [Throw.cpp:17] Python init failed: failed to get the Python codec of the filesystem encoding (in Main.cpp:66 during `anonymous-namespace'::checkReturnStatus)
[2022-12-16 16:23:29.104] [logger] [error] [Main.cpp:356] xloil_python init failed: Python init failed: failed to get the Python codec of the filesystem encoding. sys.path=
[2022-12-16 16:23:29.113] [logger] [debug] [Throw.cpp:17] Initialisation failed (in PluginLoader.cpp:161 during xloil::loadPluginsForAddin)
[2022-12-16 16:23:29.113] [logger] [error] [PluginLoader.cpp:193] Plugin load failed for c:\xloil-env\share\xloil\xlOil_Python.dll: Initialisation failed
Path=C:\Program Files\Microsoft Office\Root\Office16\;C:\Program Files\Python310\Scripts\;C:\Program Files\Python310\;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files\dotnet\;C:\Users\<REDACTED>\AppData\Local\Programs\Python\Launcher\;C:\Users\<REDACTED>\AppData\Local\Microsoft\WindowsApps;;C:\Program Files\Microsoft Office\root\Client;c:\xloil-env\share\xloil;c:\xloil-env;c:\xloil-env\Library\bin

This time the Python is 3.10.9 and Excel 365 MSO Version 2211.

I remember randomly getting the same message in my dev environment (with excel 2010 and different system locale), but during my constant reinstalls it eventually went away without me finding out a specific reason.

Thanks in advance for any help!

I was really looking forward for xlOil to work here, it looks really great!

When I try to follow the getting started with xlOil, I run into some problems.

The "xlOil install" doesn't work for me, I assume because my work environment doesn't allow access to the Windows registry.

I dropped xloil.xll on an Excel window, which gave the following message:

Which made me wonder: Would it be possible to get that digital signature?

When I clicked on "Enable this add-in for this session only" I got the following errors:

For if the screenshot doesn't come through well:
[15:12:35] [error] Failed to register func xloImport: bad allocation
[15:12:35] [error] Failed to register func xloAttrObj: In Win32 builds, there is a limit of 16 args for non-local UDFs. Raise a github issue if this is a problem!
[15:12:35] [error] Failed to register func xloPyDebug: bad allocation
[15:12:35] [error] Failed to register func xloAttr: In Win32 builds, there is a limit of 16 args for non-local UDFs. Raise a github issue if this is a problem!
[15:12:35] [error] Registration failed for: xloImport
[15:12:35] [error] Registration failed for: xloAttrObj
[15:12:35] [error] Registration failed for: xloPyDebug
[15:12:35] [error] Registration failed for: xloAttr

I get an "xlOil Py" menu:

But the test function does not get recognized.

It seems like it is quite close to working well.
I wonder: Can this only work with admin privilege or might it be possible to get it to work with a bit of tinkering?

I got xlOil version 0.17.0 with a pip install; using Python 3.10 32 bits [xlOil wouldn't install with Python 3.11 32 bits - 32 bits because MS Office installed here is Microsoft Excel for Microsoft 365 MSO (Version 2212 Build 16.0.15928.20196) 32-bit] under Windows 10 Pro.

I can do.

@ xlo.func(command=True)
def write_to_ws():
    xlo.worksheets['Sheet1']["A1"] = 'Oil'

I would like to do. ( I.e Py macro which fetches data using aiohttp . When data are available print them to a range.)

@ xlo.func(command=True)
async def get_data_from_connection_then_write():
    await asyncio.sleep(3)   # simulating a connection which takes 3 sec to return data
    xlo.worksheets['Sheet1']["A1"] = 'Oil'

I did many experiments. This is one of the most promising.

@ xlo.func(command=True)
def get_data_from_connection_then_write():
    loop = xlo.get_event_loop()
    loop.run_until_complete(get_data())

async def get_data():
    await asyncio.sleep(1)
    xlo.worksheets['Sheet1']["A1"] = 'Oil'
    return

data are printed but then I get this

[2023-09-01 20:32:28.060] [logger] [error] [EventLoop.h:88] Failed to initialise python worker thread: RuntimeError: RuntimeError: This event loop is already running
Traceback (most recent call last):
  File "C:\shieldtrade\ave\venv\lib\site-packages\xloil\importer.py", line 94, in _pump_message_loop
    loop.run_until_complete(wait())
  File "C:\Users\lucio\AppData\Local\Programs\Python\Python39\lib\asyncio\base_events.py", line 618, in run_until_complete
    self._check_running()
  File "C:\Users\lucio\AppData\Local\Programs\Python\Python39\lib\asyncio\base_events.py", line 578, in _check_running
    raise RuntimeError('This event loop is already running')
RuntimeError: This event loop is already running

[2023-09-01 20:32:28.061] [logger] [warning] [logging.py:17] sys:1: RuntimeWarning: coroutine '_pump_message_loop.<locals>.wait' was never awaited

XLL does not crashes and if I close the notepad I can call again get_data_from_connection_then_write and It works without any more error.

Is this just a warning message I can ignore or I am doing something wrong?
What would be the best practice to do it?

I have this error. I can see Range class in xloil\stubs\xloil_core_init_.py.

Dear Steven,

I have the following code that extract the number from string array:

import re
import numpy as np
import xloil as xlo


@xlo.converter(range=True)
def array_2d(x):
    if isinstance(x, xlo.Range):
        x = np.array(x.value, ndmin=2)
        x[x == None] = 0
    if isinstance(x, xlo.ExcelArray):
        x = np.atleast_2d(x.to_numpy())
    elif isinstance(x, xlo.SingleValue):
        x = np.array(x, ndmin=2)
    return x


num_pattern = re.compile(r"[+-]?\d*\.?\d+")


@xlo.func
def str2num(arr: array_2d, prefix=None):
    arr = arr.astype(str)

    if prefix is not None:
        pattern = re.compile(prefix)

    result = np.zeros_like(arr, dtype=float)

    for i in range(arr.shape[0]):
        for j in range(arr.shape[1]):
            string = arr[i, j]

            if prefix is None or pattern.search(string):
                match_num = num_pattern.search(string)
                if match_num:
                    result[i, j] = float(match_num.group())

    return result

It works correctly:

But if I reopen excel, cell can not persist the value of calculation until recalculation:

@xlo.func
def crash(X: list):
    return X

Also the below returns a strange error: PString buffer too short

@xlo.func
def weird(X: xlo.Array(str)):
    return X

Hello again! :)

When trying to install xloil on another machine, it doesn't seem to load during startup. The .xll file has been copied to XLSTART directory all ok. The Excel is 2013, python 3.9.5. Ribbon does not show up, xloil cell functions are not available. No log is written even on trace setting.

The strange thing is, that when trying to drag & drop the .xll into excel or open ti manually from XLSTART, it shows a warning message that the .xll file contents do not match its extension, and whether I still want to open it. Subsequently it opens the xll file like this:

Unfortunately I do not have a precise wording of the message, because once I allowed it to open, it no longer shows up.

Other things that I tried that do not work:

• Tested both 0.16 and 0.15.4, no difference.
• Tried .xll file from my own machine (where it works) in case it was corrupted somehow.
• Complete machine restart
• Disable other addins
• Move .xll from user startup folder to system startup folder
• Disable antivirus

I am trying to move my VBA to xloil using the com object

xlo.app().Range("A4", "B5").Value = ((1, 2), (3, 4))

I have this error
ModuleNotFoundError: No module named "pythoncom"

I manually installed pywin32 library, which includes pythoncom in my venv but again no luck

[2023-08-19 18:50:50.335] [logger] [error] [PyFunctionRegister.cpp:140] py_change_font: AttributeError: AttributeError: module 'win32com.gen_py.00020813-0000-0000-C000-000000000046x0x1x9' has no attribute 'CLSIDToClassMap'
Traceback (most recent call last):
File "C:\shieldtrade\ave\venv\lib\site-packages\win32com\client\gencache.py", line 455, in EnsureModule
module = GetModuleForTypelib(typelibCLSID, lcid, major, minor)
File "C:\shieldtrade\ave\venv\lib\site-packages\win32com\client\gencache.py", line 283, in GetModuleForTypelib
mod = _GetModule(modName)
File "C:\shieldtrade\ave\venv\lib\site-packages\win32com\client\gencache.py", line 726, in _GetModule
mod = import(mod_name)
ModuleNotFoundError: No module named 'win32com.gen_py.00020813-0000-0000-C000-000000000046x0x1x4'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\ShieldTrade\Ave\Oil\Oil_1.py", line 51, in py_change_font
xlo.app().Range("A4", "B5").Value = ((1, 2), (3, 4))
File "C:\shieldtrade\ave\venv\lib\site-packages\win32com\client\gencache.py", line 471, in EnsureModule
module = GetModuleForTypelib(
File "C:\shieldtrade\ave\venv\lib\site-packages\win32com\client\gencache.py", line 287, in GetModuleForTypelib
AddModuleToCache(typelibCLSID, lcid, major, minor)
File "C:\shieldtrade\ave\venv\lib\site-packages\win32com\client\gencache.py", line 661, in AddModuleToCache
SetTypelibForAllClsids(mod.CLSIDToClassMap)
AttributeError: module 'win32com.gen_py.00020813-0000-0000-C000-000000000046x0x1x9' has no attribute 'CLSIDToClassMap'

When attempting to launch a second instance of Excel, the second instance crashes. The crash happens even before xlOil ribbon tab is displayed, and before even a blank spreadsheet is loaded. The problem disappears when I disable the xloil by moving the xll file out of xlstart.

In the event viewer the python310.DLL is named as a culprit. There are some error logs produced by the system, but they mostly only contain OS / program info, I haven't found any specific error message related to the dll. I am attaching one of the logs as an example:
Report.txt

Office 2010, Python 3.10.9, xloil 0.16.4

Hello,

is there a way to create the wheel file for Macos ?
If there are some recipes, can try to use 

Hello,
As a new library, i hope that you will appreciate some ideas to improve.

The work with slicing a xlo.range object is not very convenient.

To quote from the docs about ways to access and slice ranges:

x[1, 1] # The *value* at (1, 1) as a python type: int, str, float, etc.
x[1, :] # The second row as another Range object
x[:-1, :-1] # A sub-range omitting the last row and column

It would be much more convenient if all the options above (including the first) will return range.
Also there is a problem with slicing one dim range:

rng = xlo.range("A1:D1")
rng[0]  # Returns error, expected to return range "A1"
rng[0, :] # Returns range "A1" (The ', :' is redundant)

Another thing that will help a lot is to enable a way that will do the opposite of to_com() function.
In order to work with the more convenient object xlo.Range instead of win32com.Range (for slicing and more things that you've worked on), I find myself doing this action several times:

rng = xlo.Range(other_range.Range.Address)

If it was possible to convert win32com.Range to xlo.Range in a simpler manner it would save a lot of coding.

Thanks!

Hello,
I have a specific question regarding RTD functions.
I wrote a UDF that sends call to an API.

The function parameters are sent to the api and then an array with the call's results is returned to the sheet.
The function call look like this:

=xlo_check_fedex_api(AB26, AA26, K26, AD26, E26, AC26, "USD")
AB26: =IF($D26<>"",VLOOKUP($D26,priOringsSuppliers,MATCH(AB$25,OFFSET(priOringsSuppliers,0,0,1),0),FALSE),"")
AA26: =IF($D26<>"",VLOOKUP($D26,priOringsSuppliers,MATCH(AA$25,OFFSET(priOringsSuppliers,0,0,1),0),FALSE),"")
etc...

The issue is that all of the cells that are the inputs of this formula contain formulas themselves so every change I do in the workbook, causes the function to re-calc.

When the function's parameters are constants, the function re-calc occurs only when one of the parameters are changed:

=xlo_check_fedex_api(AB26, AA26, K26, AD26, E26, AC26, "USD")
AB26: ="24060"
AA26: ="IT"
etc...

The UDF function:

@xlo.func
async def xlo_check_fedex_api(shipper_post_code: str, shipper_country_code: str, service_type: str,
                                                 weight_kg: float, quantity: int, value: float, value_curr: str='USD'):
    """Get quick rate from FedEx API"""

    services_dict = {
        'Economy': 'INTERNATIONAL_ECONOMY',
        'Priority': 'FEDEX_INTERNATIONAL_PRIORITY',
        'Priority Express': 'FEDEX_INTERNATIONAL_PRIORITY_EXPRESS'
    }
    service_code = services_dict[service_type]

    ret_dict = quick_rate_quote(shipper_post_code=shipper_post_code, shipper_country_code=shipper_country_code,
                                recipient_post_code=7565427, recipient_country_code='IL',
                                service_type=service_code, preferred_currency='USD',
                                weight_kg=weight_kg, quantity=quantity, value=value, value_curr=value_curr)
    
    ret_list = list(ret_dict.values())

    return [ret_list]

What are the changes I need to do in the function so it will be called only if one of the ranges in the formula input has been changed?

I've tried to figure it out from the examples about the the RTD topic but it wasn't clear how do I implement it on my function.

Thank you for your help!

Hello,
I'm using the powerful option to return cached objects from UDFs.

My problem is when I try to return cached objects from subroutines, it seems that it doesn't work properly.

@xlo.func(command=True)
def xlo_xrt_df():

    wb = xlo.active_workbook()
    xrt_df_rng = xlo.Range(wb.Names['priExchangeRates'].RefersTo) # Defines the range to return the Dataframe

    try:
        qry_df = get_xrt_dataframe() # Returns a pandas Dataframe
    except Exception as e:
        xrt_df_rng.value = str(e)
    
    if qry_df.empty:
        xrt_df_rng.value = xlo.CellError.NULL
    else:
        xrt_df_rng.value = xlo.cache(qry_df)

The function returns a value to the cell that isn't representing a sheet, column and a row like cached values returned from UDFs.
The returned value is a string and not a cached dataframe.

instead of:
欣[xrt_wb.xlsm]sheet1!R3C4,A
The function returns:
欣xx,A

My question is: Is it even possible to return a cached object from such a function? Or this capability is limited only for UDFs?

Thanks.

@ xlo.func()
def pd_dataframe_large_nubers() -> pd.DataFrame:
    rate_history = [
        {
            'symbol': 'BTC/USD:BTC',
            'fundingRate': 0.0001,
            'timestamp': 1693166400000, # mls
            },
        {
            'symbol': 'BTC/USD:BTC',
            'fundingRate': 0.0003,
            'timestamp': 1694114889000,
            }]
    df = pd.DataFrame(rate_history)
    return df

Timestamp large number are incorrectly returned.

my sys.maxsize = 9223372036854770000

I tried the following with no result
df = df.astype({"timestamp": np.int64}) and df = df.astype({"timestamp": int}) .

I could do df = df.astype({"timestamp": str}). correct display but I could no longer use them as numbers-

If I use Sec instead of Mls, smaller numbers are correctly returned-

Windows 10, python 3.9, Excel 2021, xloil 0.17.17

I have xloil correctly installed in my venv C:\ShieldTrade\Python_projects\venv\lib\site-packages\xloil\

It looks to me that xloil is trying to use the interpreter in C:\Users\lucio\AppData\Local\Programs\Python\Python39 while trying to import module while it should use C:\ShieldTrade\Python_projects\venv\scripts\python.exe ( same venv).

looking at the sys.path=['C:\ShieldTrade\Python_projects', 'C:\ShieldTrade\Python_projects', ..... ] I think there is something conflicting with xloil setting as in yours 8

If you have any python directories in your system environment variables (not recommended) then this may well conflict with xlOil's settings.

Traceback (most recent call last):
  File "C:\ShieldTrade\Python_projects\venv\lib\site-packages\xloil\_core.py", line 61, in <module>
    mod = importlib.import_module(pyd_name)
  File "C:\Users\lucio\AppData\Local\Programs\Python\Python39\lib\importlib\__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1030, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1007, in _find_and_load
  File "<frozen importlib._bootstrap>", line 984, in _find_and_load_unlocked
ModuleNotFoundError: No module named 'xlOil_Python39'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\ShieldTrade\Python_projects\_MULTY_BOTS.py", line 27, in <module>
    import xloil as xlo   #   se run non posso importare oil
  File "C:\ShieldTrade\Python_projects\venv\lib\site-packages\xloil\__init__.py", line 2, in <module>
    from ._core import *
  File "C:\ShieldTrade\Python_projects\venv\lib\site-packages\xloil\_core.py", line 63, in <module>
    raise ModuleNotFoundError(f"Failed to load {pyd_name} with " +
ModuleNotFoundError: Failed to load xlOil_Python39 with sys.path=['C:\\ShieldTrade\\Python_projects', 'C:\\ShieldTrade\\Python_projects', 'C:\\Users\\lucio\\AppData\\Local\\Programs\\Python\\Python39\\python39.zip', 'C:\\Users\\lucio\\AppData\\Local\\Programs\\Python\\Python39\\DLLs', 'C:\\Users\\lucio\\AppData\\Local\\Programs\\Python\\Python39\\lib', 'C:\\Users\\lucio\\AppData\\Local\\Programs\\Python\\Python39', 'C:\\ShieldTrade\\Python_projects\\venv', 'C:\\ShieldTrade\\Python_projects\\venv\\lib\\site-packages', 'C:\\ShieldTrade\\Python_projects\\venv\\share\\xloil'] and PATH=C:\ShieldTrade\Ave\venv\Scripts;C:\Users\lucio\AppData\Local\Programs\Python\Python39\Scripts;C:\Users\lucio\AppData\Local\Programs\Python\Python39\;C:\Program Files\Python39\Scripts\;C:\Program Files\Python39\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;C:\Program Files\Git\cmd;C:\Program Files\Amazon\AWSCLIV2\;C:\Program Files\nodejs\;C:\Program Files\PuTTY\;C:\Users\lucio\AppData\Local\Programs\Python\Python39\Scripts\;C:\Users\lucio\AppData\Local\Microsoft\WindowsApps;C:\Users\lucio\AppData\Roaming\npm;C:\Program Files\OpenSSL-Win64\bin

Hi,

Fantastic project! I've been looking at Excel add-ins for a long time and have never seen xlOil before. I just found xlOil through your answer on a StackOverflow question.

This deserves to be more well known and you should try and publicise it as much as you can. I'll try to assist.

https://twitter.com/pry0cc/status/1619068952944218113?t=t14JG4x6MIUG3OQlitZc0g&s=19

xloil.app().WorksheetFunction.TIMEVALUE("8/27/2023 20:00")
or
xloil.app().WorksheetFunction.TimeValue("8/27/2023 20:00") # vba name

I have this error no attribute error

AttributeError: AttributeError: '<win32com.gen_py.Microsoft Excel 16.0 Object Library.WorksheetFunction instance at 0x2074068800656>' object has no attribute 'TIMEVALUE'

xloil.app().WorksheetFunction.Sum(1, 3) works correctly

Hi, After 'pip install xlOil' and 'xloil install', When i open Excel , xlOil always ERROR following info：
' [logger] [error] [PluginLoader.cpp:193] Plugin load failed for D:\anaconda3\share\xloil\xlOil_Python.dll: Initialisation failed
Path=C:\Program Files\Microsoft Office\Root\Office16;C:\Program Files\Common Files\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0;C:\WINDOWS\System32\OpenSSH;C:\Program Files\dotnet;D:\anaconda3;D:\anaconda3\Scripts;D:\anaconda3\Library\bin;C:\Program Files\Google\Chrome\Application;C:\Program Files\Java\jdk-18.0.1.1\bin;C:\Program Files\Java\jdk-18.0.1.1\jre\bin;D:\anaconda3\Library\mingw-w64\bin;D:\anaconda3\Library\usr\bin;C:\Users\XXX\AppData\Local\Microsoft\WindowsApps;C:\Users\XXX\AppData\Local\Programs\Microsoft VS Code\bin;C:\Program Files\Microsoft Office\root\Client;D:\anaconda3\share\xloil;D:\anaconda3;D:\anaconda3\Library\bin'

My Python Version：3.8.13
pls help me solve this，thks！

I get the following error when opening excel with xloil pointing to a Python environment with python 3.11 installed:

Failed LoadLibrary for: xlOil_Python310.pyd

Does xloil work with python 3.11?
Do I have to revert to python 3.10?

Do you have any suggestion?

Many thanks for developing and sharing xloil :)

Best regards

Stefano

HI,
I am trying to debug python code from Excel using xloil, but getting an error
" [error] During Event Event_PyUserException: TypeError: _Handler_pdb_window.init() takes 1 positional argument but 4 were given" .

I am calling xlopydebug("pdb") from excel. and used "raise Exception("debug") " on python code.
can anyone please help, thanks a lot.

I have some ideas for integrations with xlOil which would be a lot easier with Rust. I could go the pyIodide route but directly from C++ would be better.

I haven't used C++ in 25 years so I don't know anything about that side. Would be great if we could collaborate on this.

Hello,
I'm trying to use xlo.callon a function written with VBA in my workbook.

This function is under module named 'Priority' in my workbook VBA Project library

Public Function TestFunc()
    TestFunc = "Success"
End Function

When i try to call it from a cell i recive the expected output.

=TestFunc()  # Cell value is "Success"

When I try to call it from python I receive error message

@xlo.func()
def xlo_call_test():
    x = xlo.call('TestFunc')

Returns error message:
#Unrecognised function 'TestFunc'

For all the standard worksheet functions xlo.call works properly.

What seems to be the issue?

Hi Steven,
Thank you for your great work!

I have created a UDF add-in (xs_udf_xloil.xll) and it normally works without any problems.
But for add-in distribution , I'm editing a XLL-specific ini file (xs_udf_xloil.ini) with LoadBeforeCore=true, Excel crashes on startup.
(loading xll by drop)

Here is the log file.

[2023-01-12 17:02:50.330] [logger] [info] [AddinLoader.cpp:57] Found core settings file 'C:\Users\UUUUU\OneDrive - XXXXX\YYYY\EXCEL\xloil_sample\xlset_udf_winpython\xs_udf_xloil.ini' for 'C:\Users\UUUUU\OneDrive - XXXXX\YYYY\EXCEL\xloil_sample\xlset_udf_winpython\xs_udf_xloil.xll'
[2023-01-12 17:02:50.331] [logger] [info] [AddinLoader.cpp:57] Found core settings file 'C:\Users\UUUUU\AppData\Roaming\xlOil\xlOil.ini' for 'D:\WPy64-38100\python-3.8.10.amd64\share\xloil\xlOil.dll'
[2023-01-12 17:02:50.331] [logger] [debug] [FuncRegistry.cpp:190] Registering "xloRunInXLLContext" at entry point xloRunInXLLContext with 0 args
[2023-01-12 17:02:50.331] [logger] [debug] [FuncRegistry.cpp:190] Registering "xloVersion" at entry point xloVersion with 0 args
[2023-01-12 17:02:50.331] [logger] [debug] [FuncRegistry.cpp:190] Registering "xloLog" at entry point xloLog with 1 args
[2023-01-12 17:02:50.331] [logger] [debug] [FuncRegistry.cpp:190] Registering "xloHelp" at entry point xloHelp with 1 args
[2023-01-12 17:02:50.331] [logger] [debug] [FuncRegistry.cpp:190] Registering "xloRef" at entry point xloRef with 1 args
[2023-01-12 17:02:50.331] [logger] [debug] [FuncRegistry.cpp:190] Registering "xloVal" at entry point xloVal with 1 args
[2023-01-12 17:02:50.331] [logger] [debug] [FuncRegistry.cpp:190] Registering "RegistrationInfo_d5a424b6966b5f75bd3e6b99ea363a57" at entry point IntellisenseRegistrationInfo with 1 args
[2023-01-12 17:02:50.339] [logger] [debug] [Connect.cpp:81] Made COM connection to Excel at 'C:\Program Files\Microsoft Office\Root\Office16' with hwnd=1905292
[2023-01-12 17:03:21.567] [logger] [debug] [Throw.cpp:17] COM Error -0x7ff5f821 (in XllContextInvoke.cpp:80 during xloil::runInXllContext::<lambda_2fe688e41d0eef07f9c0210faea801eb>::operator ())
[2023-01-12 17:03:21.567] [logger] [error] [ExcelThread.cpp:107] Internal error running main thread queue: COM Error -0x7ff5f821

Is there something wrong with my procedure?

windows10
python3.8.10(WinPython)
xlOil 0.16.1

Example

I'm not able to call wrapped functions, I'd expect the following to pass:

import xloil as xlo

def example():
    return "HELLO"

@xlo.func()
def example_wrapped():
    return "WORLD"
    
def test_callable():
    assert example() == "HELLO"
    assert example_wrapped() == "WORLD"

self = <xloil.register._WorksheetFunc object at 0x037814B0>, args = ()
kwargs = {}

def __call__(self, *args, **kwargs):
\>     return self.func(*args, **kwargs)
E       AttributeError: '_WorksheetFunc' object has no attribute 'func'

.venv\Lib\site-packages\xloil\register.py:241: AttributeError

I believe this snippet could be worked into a simple test case

Suggested Fix

We change __call__ in _WorksheetFunc (register.py:L241)

def __call__(self, *args, **kwargs):
        return self.func(*args, **kwargs)

to

def __call__(self, *args, **kwargs):
        return self.__wrapped__ (*args, **kwargs)

I did a quick sweep and didn't see an alternative code path which was setting func explicitly

Hello,
I try to return the error #N/A as a UDF result. Similar to Excel's =NA() function.
How can I achieve this using xloil or win32com library?

the class xloil.CellError(value: int) seems to have this ability, but I'm not sure how to use it as a return #N/A value.
I've found out that xlo.CellError(7) returns #DIV/0!
And for xlo.CellError(x) (x!=7) it returns #VALUE!
But it doesn't seems to be by design.

# Write result to sheet
    try:
        return filtered_df[ret_col_name].iloc[0]
    except KeyError:
        return xlo.CellError(7)  # Returns #DIV/0!
    except IndexError:
        return ???  # How to return #N/A

I was talking to some colleagues that use xlwings today and are experimenting with xloil, together with my experience.
Some suggestions:

• To have a configuration ribbon like xlwings, such that adding/changing modules in different paths would be simpler. Maybe a CLI should be an easier workaround (xloil add module, etc)

• Sometimes when several Python versions are installed, conflicts happen (Failed LoadLibrary for xlOil_Python3.xx). The way I solved is to include the version in the .ini file (xlOilPythonVersion), but maybe this could be simpler/clearer (maybe a default interpreter selector during xloil install, similar to VSCode).

Hi Steven,

Thank you for your great work!
I want to create a UDF with a variable number of arguments:

@xloil.func
def add(*numbers):
    total = 0
    for num in numbers:
        total += num
    return total

However, I got the error below:

[18:10:04] [error] Failed determing spec for 'add': Traceback (most recent call last):
File "C:\Users\JohnnyS\miniconda3\lib\site-packages\xloil\register.py", line 356, in decorate
func_args, return_type = Arg.full_argspec(fn)
File "C:\Users\JohnnyS\miniconda3\lib\site-packages\xloil\func_inspect.py", line 88, in full_argspec
args = [cls.from_signature(name, param) for name, param in params.items()]
File "C:\Users\JohnnyS\miniconda3\lib\site-packages\xloil\func_inspect.py", line 88, in
args = [cls.from_signature(name, param) for name, param in params.items()]
File "C:\Users\JohnnyS\miniconda3\lib\site-packages\xloil\func_inspect.py", line 78, in from_signature
raise Exception(f"Unhandled argument '{name}' with type '{kind}'")
Exception: Unhandled argument 'numbers' with type 'VAR_POSITIONAL'

Could you please give me a example!

Thanks and Regards,
Johnny

Hi Steven,

This is a great project and very much needed for the Excel community. I was using xlWings before this and have seen amazing performance improvement with xlOil.

The latest version 0.15.2 seems to be crashing Excel when I close it. It works fine until then (when Excel is open).
My python version is 3.8.10.

If I remove xloil from XLSTART folder, Excel does not crash anymore.
If i downgrade xloil to 0.12.0, Excel does not crash anymore.

Please let me know if I can provide any additional information to help debug this.

Thanks and Regards,
Harish

Discussed in #5

Opening an issue just to be more organized!

Ok, so I tried a lot of things and found 2 main problems for VSCode (should be similar for VS 19/22):

• In the launch settings, the localRoot and remoteRoot folders needed to both point to ${workspaceFolder} (or else the breakpoints in the UI won't be found)
• It is needed to add to each function debugpy.debug_this_thread(). I tried to embed this in a decorator (debug_func in the example below), but with no success. But shouldn't be difficult to add inside the original xloil.func code.

Example: https://github.com/lukedays/xloil-samples/tree/main/hot_reload

• Clone
• Point xlOil.ini to watcher module
• Open Excel and add =Watcher()
• Open folder in VSCode and connect
• Changes and breakpoints should work

I was thinking, could this mechanic be packaged into something like xloil serve?

Hello,
I'm new to your project. Very impressive.
I initially tried it to write a Asynchronous UDF (I'll post a question regarding this issue in the future) and stayed for more!

I'm writing an UDF that runs a query and saves the query results as a pandas Dataframe.
I want to write the df back to a specific range without the index column.
I find it extremally difficult to do.
The closest i get is this way:

rng_test = xlo.Range('AM4:AZ6')
order_df = order_qry.get_res_dataframe()  # This function returns pandas df

# Two ways to write that doesn't do what I need:
rng_test.value = order_df  # Writes to the range the df with the unwanted index column
rng_test.value = order_df.values.tolist()  # Writes to the range the df without the index column, but the header row is missing

Can you help me to find a way to write the df results without the index column and with the header row?

Thanks!

Python 3.9

No conversion when pd.DataFrame is returned from xlOil.

@ xlo.func()
async def py_timestamp()-> pd.DataFrame:
    timestamp= '2023-09-08T16:59:58.246Z'
    ts = pd.Timestamp(timestamp)
    ts = ts.tz_convert(tz='Europe/Berlin')
    d = {'converted_time': ts}
    df = pd.DataFrame([d])
    print(df)
    return df

From Python it works.

[2023-09-15 11:36:38.360] [info] [type_converters.py:168] Added arg converter for type <class 'pandas.core.frame.DataFrame'>
[2023-09-15 11:36:38.360] [info] [type_converters.py:301] Added return converter _CustomReturn for types <class 'pandas.core.frame.DataFrame'>
[2023-09-15 11:36:38.361] [info] [type_converters.py:168] Added arg converter for type <class 'pandas._libs.tslibs.timestamps.Timestamp'>
[2023-09-15 11:36:38.361] [info] [type_converters.py:301] Added return converter _CustomReturn for types <class 'pandas._libs.tslibs.timestamps.Timestamp'>
                    converted_time
0 2023-09-08 18:59:58.246000+02:00

( Firstly thanks for all the hardwork put into this project! I've recently been trialing xlOil and have been blown away. )

I've recently begun encountering errors at launch due to missing xlAutoFree12.

This error occurs in 0.17.11 but not 0.17.10 from my testing using python 3.11.4 32-bit on 64bit Windows.

I'm not a c++ guru but poked around. Here's what I've pieced together ( and apologies if this is super obvious ):

I think this is tied to commit 439e708 and that this stack overflow post (x64 DLL export function names
) discussing exported C++ name decoration highlights the issue.

     const auto autoFreeFunc = GetProcAddress(moduleHandle, "xlAutoFree12");

I believe under 32bit, the function name, "xlAutoFree12", needs to be updated. Since __stdcall is used, the name would be something like _xlAutoFree12@2 rather than just prepending an underscore per __cdecl.

Unfortunately I don't know the best way to address this cleanly or exactly what the tradeoffs are between __cdecl or __stdcall. SO post on cdecl-or-stdcall-on-windows , microsoft Calling Conventions docs

[2022-12-20 13:44:10.268] [logger] [error] [logging.py:25] Error during _import_and_scan: Traceback (most recent call last):   File "D:\anaconda3\lib\site-packages\xloil\register.py", line 129, in logged_func     return func(*args, **kwargs)   File "D:\anaconda3\lib\site-packages\xloil\importer.py", line 115, in _import_and_scan     result.append(_import_and_scan(m, addin))   File "D:\anaconda3\lib\site-packages\xloil\importer.py", line 106, in _import_and_scan     module = importlib.import_module(what)   File "D:\anaconda3\Lib\importlib\__init__.py", line 127, in import_module     return _bootstrap._gcd_import(name[level:], package, level)   File "<frozen importlib._bootstrap>", line 1014, in _gcd_import   File "<frozen importlib._bootstrap>", line 991, in _find_and_load   File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked   File "<frozen importlib._bootstrap>", line 671, in _load_unlocked   File "D:\anaconda3\lib\site-packages\xloil\importer.py", line 183, in exec_module     super().exec_module(module)   File "<frozen importlib._bootstrap_external>", line 843, in exec_module   File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed   File "D:\anaconda3\lib\site-packages\xloil\xloil_ribbon.py", line 346, in <module>     _ribbon_ui = xloil.ExcelGUI(ribbon=r''' RuntimeError: Caught an unknown exception!

Hello Steven, after using the xloil module, Excel will always fail to close the last workbook normally, this is my xloil log(xlOil.log), please help me to see how to solve it, thank you.

I have the following function:

@xlo.converter(range=True)
def arg_array(x):
    if isinstance(x, xlo.Range):
        x = np.array(x.value, ndmin=2)
        x[x == None] = 0
    else:
        x = np.array(x, ndmin=2)
    return x


@xlo.func
def vstack(a: arg_array, b: arg_array):
    return np.vstack((a, b))

It worked well, however I want to get variable arguments, as below:

@xlo.func
def vstack(*args: arg_array):
    return np.vstack(args)

This time, the type arg_array seems not work!

I am trying to implement a handler to the SheetBeforeDoubleClick event. The documentation seems to be non-existing, but it would be enough if there are some very general guidelines on how to approach the event parameters and what is the event handler expected to return. So far with this test code:

def click_handler(worksheet, target, cancel):
    worksheet['P4'].value = 'TEST'
    return cancel
    
xlo.event.SheetBeforeDoubleClick += click_handler

I have

[error] During Event SheetBeforeDoubleClick: return_value_policy = copy, but type is non-copyable! (compile in debug mode for details)