3

I'm desperately trying to use Pyxll in order to write some excel function that gets a bunch of arrays, loads them in Python, converts them to pandas DataFrames, plays with the data a bit and then returns the final DataFrame. Now, for returning the DataFrame I found pyxll examples, but no matter how I try, I can't seem to be able to convert the excel arrays I load into pandas DataFrames I can work with.

For instance, I tried using the code below, but no luck. Maybe if I had some way of knowing what's loading in Python and how it looks, perhaps I had better chances to understand how to manipulate the data, but I don't know how to view the output on my Canopy output area.

Does anyone know an easy way to import data from excel to python, process it and then return it to excel, without having to save the file, load it in python, process the data and overwrite the existing file?

@xl_func("string[] name, var[] day, string[] method, string[] currency, numpy_array amounts, date[] dates: dataframe")
def test(name, day, method, currency, amounts, dates):

df_name = DataFrame(name, columns = ['Name'])
    df_method = DataFrame(method, columns = ['Method']).ix[1:]
    df_currency = DataFrame(currency, columns = ['Currency']).ix[1:]


    df = df_name.join(df_method).join(df_currency)

    cols = ['Name', 'Currency', 'Method']
    df = df[cols]


return DataFrame(dates)
erantdo
  • 685
  • 2
  • 9
  • 19

3 Answers3

4

PyXLL can accept and return pandas dataframes using custom types.

Take a look at this example: https://github.com/pyxll/pyxll-examples/blob/master/pandas

To see the output look at the log file. To play around interactively in Python with an IPython prompt use this example: https://github.com/pyxll/pyxll-examples/tree/master/ipython

Much better to stick with functions rather than resorting to named ranges.

You can use PyXLL to register keyboard shortcuts too. See this example for a shortcut that automatically resizes the output of an array formula: https://github.com/pyxll/pyxll-examples/blob/master/shortcuts/resize_array_formula.py

If you need more help contact support for a quick response.

Tony Roberts
  • 387
  • 1
  • 5
3

Have a look at (my) library xlwings. It makes sending DataFrames back and forth as easy as:

>>> from xlwings import Workbook, Range
>>> import pandas as pd
>>> wb = Workbook()  # Pass in the path of a file to work with an existing Workbook
>>> df = pd.DataFrame([[1., 2.], [3., 4.]], columns=['one', 'two'])
>>> Range('A1', index=False).value = df # send it over to Excel
>>> data = Range('A1').table.value  # read it back
>>> pd.DataFrame(data[1:], columns=data[0])
   one  two
0    1    2
1    3    4

See especially the docs about DataFrames and how to call it from VBA.

Felix Zumstein
  • 6,737
  • 1
  • 30
  • 62
  • Hi! This looks amazing! Two questions, please: 1. Does it work with 64bit systems? because I got an error when running the Fibonacci example. 2. Is it possible to transfer dynamic values to the function I wrote in Python? because in your example you take it for granted that the input is on B1, but what if I want to store the data in cell C1 without having to change the Python code? Thanks a lot!! – erantdo Sep 26 '14 at 00:15
  • Hi! 1. Yes it works with 64bit Python, even in the combination with 32bit Excel, for example. But on Windows, you need to install the dependency pywin32 as explained in the docs. 2. You can work with named ranges. If the top left cell is a named range, you can have a flexible and dynamic setup with e.g.: `Range('NamedRange').table.value`. Let me know if you need more help. – Felix Zumstein Sep 26 '14 at 05:50
  • Thanks, but I'm running Canopy, so I have pywin32 219.0.0-1 already installed. Do you want me to send you a screenshot of the error I'm getting? – erantdo Sep 26 '14 at 09:32
  • 1
    It's indeed an Excel 64bit issue: Comment out the `Private Declare Function system Lib ...` line in the xlwings VBA module for now as a workaround. It will be fixed in the next version, issue is logged here: https://github.com/ZoomerAnalytics/xlwings/issues/72 – Felix Zumstein Sep 26 '14 at 10:33
  • This has been fixed in v0.2.3 – Felix Zumstein Oct 20 '14 at 19:02
  • It was hard to find how to do this using xlwings, luckily google brought me here. Thanks for your answer. – Leo Dec 08 '14 at 08:40
2

Check out the xlrd (Python 2 and 3), xlwt (Python 2 only), and xlsxwriter (Python 2 and 3) modules for use with pandas. Pandas has hooks for them in its code; you can read all about the read_excel and to_excel functions here.

MattDMo
  • 100,794
  • 21
  • 241
  • 231
  • Thank you, but I'd like to leave Canopy out of it, i.e., I'd like to write the function in Python and then run everything from excel, as if I'm running a VBA code – erantdo Sep 25 '14 at 22:37