Returning Data Frame to excel through xlwings UDF

Question

I'm trying to create a User-Defined Function using Python, Xlwings library and Excel.

The objective is to use a function, let's call ret_data_frames to fill not only the the A1 cell, but all the need cells to return the entire data frame.

Like the images above:

The data frame that I used is obtained through web-scraping and have large dimensions, so I let's use a simple data frame and, if this works, I replace the idea to my original code

import numpy as np
import pandas as pd

def get_data_frame(data):
    dates = pd.date_range(data,periods=6)
    df = pd.DataFrame(np.random.randn(6,4),index=dates,columns=list('ABCD'))

After that, it's necessary to create the UDF function

import xlwings as xw
from test import get_data_frame

@xw.func
def ret_data_frame(data):
    return get_data_frame(data)

This idea isn't working, so, the question is:

It's possible to use UDF's to reproduce dataframes?

Thanks

Answer 1

This what worked for me:

@xw.func
@xw.arg('x', pd.DataFrame, index=False, header=True)
@xw.ret(header=True)
def get_my_df(x):
    return x

And there are various ways you can return dataFrame, with this

@xw.ret(header=True, index=True, expand='table')

you can return dataFrame immediately into Excel, without a necessity to wrap it into Excel array formulas.

Answer 2

You need to have the correct xlwings reutrn

@xw.ret(index=False, header=True, expand='table')

decorator at the top of your function

@xw.func
@xw.ret(index=False, header=True, expand='table')
def ret_data_frame(data):
    return get_data_frame(data)