5

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
  • 3,725
  • 4
  • 21
  • 32
  • 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

1 Answers1

3

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
  • 166
  • 1
  • 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