Open arshdeep87 opened 5 months ago
Thanks!
I can't replicate this - it works for me. Do any error messages appear in the log? (You can open if via the ribbon) Which version of Excel are you using? Which editor are you using for the file? Are you saving to a local drive or something OneDrive or synced with OneDrive?
I also need to close and re-open excel to pick up changes, and hadn't realised the design is for changes to automatically feed. There are no errors logged and I use office365.
Two possible reasons in my case:
Here is the code I use for 2: def register_functions(): functions = [obj for name, obj in inspect.getmembers(pa_xlwings) if inspect.isfunction(obj)] for function in functions:
if not function.__name__ in excluded_functions:
xlo.import_functions('main.pa_xlwings.py', function.__name__, 'od_' + function.__name__)
return True
register_functions()
It would be challenging for me if changes in my python code automatically went straight to the spreadsheet, because I am developing simultaneously with using it in production(!) Also, the sheet has some heavy function calls that run once and cache to memory, so I wouldn't want them to get cleared every time I make a code change (would this happen?)
It would be very helpful though to have a function call that would reload the entire python project without re-starting the sheet
Thank you as always for the fantastic project!
I use Microsoft office 365. No errors in logs. Only 1 python environment on machine (i.e. base). Python is 3.10.11. All files are saved locally.
I just started using the library...thus was starting the simple add example..
@arshdeep87 can you try changing the logging level to debug and edit the file containing the xloil.func declaration using notepad to return a different value from the function and see if that's reflected. You should see events in the log as the file changes are detected.
@pausti2 xlOil only looks for changes in files containing xloil.func declarations; I could extend this to all imported modules, but it is not straightforward. Also, as you say, this is not always ideal: if the module import is expensive, performance will suffer, however that can be worked around. Python hot reloading has some caveats, as noted in the IPython implmentation which already implements several workarounds.
If you know the module which has changed you can force a reload using =xloImport(...)
or by opening the console from the xlOil ribbon and using importlib.reload(...)
. This works, at least in simple cases!
@cunnane, editing the associated .py file (containing the xloil.func declaration) in Notepad++ works...that is changes in function are reflected in Excel cell upon recalculation.
PyCharm 2023.1.2 (Community Edition) Build #PC-231.9011.38, built on May 16, 2023 Runtime version: 17.0.6+10-b829.9 amd64 VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o. Windows 10.0 GC: G1 Young Generation, G1 Old Generation Memory: 2048M Cores: 8 Registry: debugger.new.tool.window.layout=true ide.experimental.ui=true
Thanks, seems ideal. At some point I will write my own function to reload all modules by calling xloImport
@arshdeep87 I use PyCharm myself and see the same behaviour, which is why I asked you to check with notepad :) I'm not currently sure how PyCharm is the only editor which manages to stealth any file modifications past the Win32 API but it's good to know it's a global problem and I'll try to look into it.
@pausti2 It turns out that writing a global module change and reload detection was not very difficult since xlOil already hooks the python import system and has the ability to watch for file changes. With the auto-reload switched off, it should be straightforward to write the function you describe using the list of candidates for reloading which xlOil maintains. (Note, I haven't pushed the code yet!)
Fantastic news thank you!
Thank you so much @cunnane ! I tried Spyder as well and it has same problem as Pycharm :)
@arshdeep87 This should be fixed in 0.18.6
@pausti2 I've added global autoreloading in 0.18.6 and there is a new parameter in the ini file to control this. It won't get automatically added to your existing ini, but you can see it in the template ini file your_env\share\xloil\xloil.ini
I have tested this using 0.18.6 and found that if I modify any python file, my functions in excel all start to return hashNAME? error This includes modifications in the file the xlo function is defined in
Separately, I am struggling to switch off the behaviour using the ini file Currently I am trying this: AutoReloadExcludePaths = ["main"] as a lot of my code is imported with statements like from main import basics
Is there a way to globally switch off auto reload? (I have reverted to 0.18.3 for now)
Thank you very much!
That suggests that the import is failing, in which case hopefully you'll see an error in the log, but I'll double check this is working as expected.
For the exclude paths I had in mind something like ["C:\users\me\my_python_env"]. It's also possible to completely switch off in code by doing
def does_nothing(*args, **kwargs): return None
xloil.importer.ImportHelper()._reloader = does_nothing
Unfortunately, these approaches aren't working for me.
If I use either the code snipit, or the ini file, my sheet works when I first open it, but I subsequently get #NAME? for any function defined in a file I modify. (But functions with no definition or implementation in files I modify are OK). I now realise the #NAME? error means the function is not registered and no longer recognised by excel.
Worth mentioning I have two types of function:
The above #NAME? behaviour is the same for both approaches
(all these experiments done in 0.18.6) Thanks again
I've not been able to replicate this, even using import_functions
. The latter doesn't work correctly - it doesn't pick up the changed function, which isn't surprising. The #NAME? errors suggest that functions are being deregistered but not re-registered. This is particularly surprising with import_functions
where I'd expect nothing at all to happen. Could you crank up your log level and see if any errors show up?
I also tested AutoReloadExcludePaths
and this works for me - are you escaping the backslashes in paths or using triple quotes?
Awesome library @cunnane . Thanks!
I am trying a simple function in python module and have excel file by the same name as well.
@xlo.func def pySum(x, y, z): '''Adds up numbers''' return x + y + z
If i change the function, even if I refresh sheet, it does not reflect updated values. I need to close and reopen the workbook for changes to be reflected, which is not very elegant way. Is there a way to resolve this? Thanks in advance!
I am using latest version of the library and have tried calculation modes for excel workbook.