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)
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.