How can I specify the data types of columns when passing pandas DataFrames to and from Excel?

Pandas DataFrames can be passed to your worksheet functions, or accessed in Excel macros using the pyxll.XLCell type, using the "dataframe" type.

See https://www.pyxll.com/docs/userguide/pandas.html for detailed instructions of how to access Excel data as pandas DataFrame objects using PyXLL.

For example, if you wanted to read a range in Excel as a pandas DataFrame as part of an Excel macro, this is one way you can do that:

from pyxll import xl_app, XLCell

@xl_macro
def get_dataframe():
    # Get the data in range A1:D10 as a pandas DataFrame
    cells = XLCell.from_range("A1:D10")

    # Get the value as a DataFrame
    df = cells.options(type="dataframe").value

Now suppose that column "A" in the DataFrame should be a date. How do we tell PyXLL to convert the Excel value into a Python date object?

The answer is to use the "dtypes" parameter to the "dataframe" type. PyXLL types can be parameterized using the syntax "<options>". For example, "dataframe<index=True>" parameterizes the dataframe type and tells PyXLL that we want to include the index when converting the DataFrame.

The "dtypes" parameter to the "dataframe" type is a dictionary of column names to types. So, to tell PyXLL that column "A" is a date we would do the following:

# Get the value as a DataFrame
df = cells.options(type="dataframe<dtypes={'A': 'date'}>").value

Or, if we were writing a worksheet function we could do this:

from pyxll import xl_func

@xl_func("dataframe<dtypes={'A': 'date'}> df: var")
def your_function(df):
    ....

A less cumbersome syntax

Specifying everything in the type signature can become cumbersome when dealing with complex options. PyXLL provides a way to pass the type parameters as a dictionary instead. For XLCell.options there is the "type_kwargs" argument, and for @xl_func there is the @xl_arg decorator.

For example, we can re-write the first example as follows:

from pyxll import xl_app, XLCell

@xl_macro
def get_dataframe():
    # Get the data in range A1:D10 as a pandas DataFrame
    cells = XLCell.from_range("A1:D10")

    # Get the value as a DataFrame
    df = cells.options(
        type="dataframe",
        type_kwargs={
            "dtypes": {
                "A": "date"
            }
        }
    ).value

And for the @xl_func example we can write it as:

from pyxll import xl_func

@xl_func
@xl_arg("df", "dataframe", dtypes={"A": "date"})
def your_function(df):
 ....

For returning DataFrames the same can be done using the @xl_return decorator.