6

I have written a python code, which takes 3 inputs, and return one output val. I try to write an excel function, which passes the three inputs to the python function and returns the output.

I have looked into XLwings, but there is so many issues (and the documentation is insanely poor/poorly written) thus it seems useless.

So: is there any other way to call a python function (which takes inputs) from excel?

[SOLVED (ish):] I managed, after roughly 8 hours of trying, 4 youtube videos and the xlwings homepage, to make it work.

Video for installing: https://training.zoomeranalytics.com/courses/xlwings/lectures/4231276

Video for making a function which takes input and returns output: https://www.youtube.com/watch?v=qn8xGrDuRCg&t=16s

CutePoison
  • 4,679
  • 5
  • 28
  • 63
  • In my experience, `xlwings` is the standard. That's not to say it's clean (very messy IMO), but I haven't seen a better way. – jpp Jun 26 '18 at 14:20
  • 1
    it is insanely(!) messy - I would say almost useless – CutePoison Jun 26 '18 at 14:55
  • *I try to write an excel function, which passes the three inputs to the python function and returns the output.* ... please show some code of this attempt. There is indeed ways to call between both Excel and Python. What are the inputs and output? – Parfait Jun 27 '18 at 17:42
  • See [Return result from Python to Vba](https://stackoverflow.com/a/39517658/1422451). No *xlWings* needed. – Parfait Jun 27 '18 at 17:46
  • I am not connected with xlwings, other than being a user for a couple of years, but I disagree with the negative comments. How does the installation video differ from the on-line written documentation? How could this be improved? How could the UDF procedure be improved to make it less "messy"? – Doug Jenkins Aug 28 '18 at 03:19

2 Answers2

4

You could try xlOil (disclaimer: I wrote it). The docs are here, but to write a simple three input function, you would install xlOil using:

pip install xloil
xloil install

Then write:

import xloil

@xloil.func
def myfunc(x, y, z):
    return x + y * z

Put this code either:

  1. In a py file in the same directory as your spreadsheet, named SpreadsheetName.py
  2. In a py file on your python module path, then edit %APPDATA%\xlOil\xlOil.ini to load it as described in the docs.

Start Excel, open your spreadsheet and the function will be available as myfunc.

stevecu
  • 336
  • 2
  • 7
  • +1 , Documentation looks good. Tried installing but received error : `FileNotFoundError: [Errno 2] No such file or directory: 'C:\\Users\\testBench\\AppData\\Roaming\\xlOil\\xlOil.ini'` – ankit7540 Feb 18 '21 at 05:27
  • 1
    Thanks @ankit7540, this looks like the install powershell script failed to copy the xlOil.ini file to that location. It could be a permission error, possibly powershell execute permssion has not been granted. You can contact me at the obfuscated email [here](https://pypi.org/project/xlOil/) or raise an issue at [gitlab](https://gitlab.com/stevecu/xloil/-/issues) and I'll troubleshoot. – stevecu Feb 20 '21 at 17:02
  • 1
    Sorry it took me a while to notice your issue, but I closed it out now. Thanks for giving it a try! – stevecu May 08 '21 at 11:32
0

You could also try xlSlim (disclaimer I wrote it). The docs are here.

Installation is as simple as downloading and running a Windows installer from here The process is described in the docs and there is a YouTube video https://youtu.be/Zl5QM8rGBC8 It is straightforward, just download and run the installer. The installer is digitally signed and virus scanned.

Then to create the same function as Steve's you would write your function in a Python module:

def myfunc(x,y,z):
    return x + y + z

(Note how no changes were made for the function work with xlSlim - this is a defining feature of xlSlim, no decorators or additional Python packages are required.)

Then in Excel use the RegisterPyModule() function to register the module (assuming you saved the module as mymod.py)

=RegisterPyModule("C:\Users\russe\Documents\mymod.py")

The function is now available for use within Excel as myfunc. Any type hints and doc strings are also processed.

Russel
  • 41
  • 3