Using Type Hints¶

Type Hints for UDFs¶

The most common place where type information is necessary is when exposing a Python function to Excel via the @xl_func and @xl_macro decorators.

The argument types determine how the function will be registered in Excel, as well as how the Excel values will be converted into Python values. Similarly, the return type dictates how the returned Python value will be converted before being returned to Excel.

To understand why type hinting necessary first consider a function without types:

from pyxll import xl_func

@xl_func
def some_function(a, b, c):
    return a + b + c

This function will do what you expect, if you pass it numbers! PyXLL will convert the Excel types to the closest Python type, and so Excel numbers will be converted to Python float types. So far so good…

Now consider this next example:

from pyxll import xl_func

@xl_func
def pick_a_value(data, row_index, col_index):
    return data[row_index][col_index]

If you pass this function a range of values, a row index, and a column index - for example, =pick_a_value(A1:C3, 1, 1), you might be surprised that it fails!

The range A1:C3 will be converted to a Python list of lists (list of rows, where each row is itself a list), and we can index into that in the way shown – but, the indexing will fail because the numbers passed as row_index and col_index are Excel numbers which will be converted to Python float types. We need Python int types in order to index correctly.

Also, what would happen if we were to pass a single cell as our data argument? Since PyXLL doesn’t know that we always want that argument to be passed as a list, it will pass a scalar value in that case.

The solution is to add some type information to control how the values are converted. Using type hints, we can do that as follows:

from pyxll import xl_func
import typing

@xl_func
def pick_a_value(data: list[list],
                 row_index: int,
                 col_index: int) -> typing.Any:
    return data[row_index][col_index]

Note that we didn’t need to change the actual function, just the argument and return types (although the return type is not strictly necessary here).

The type hints above are equivalent to using a PyXLL signature string as follows:

from pyxll import xl_func

@xl_func("list<ndim=2> data, int row_index, int col_index: var")
def pick_a_value(data, row_index, col_index):
    return data[row_index][col_index]

Either syntax is fine, and both achieve the same thing.

Type Parameters¶

In PyXLL, argument and return types are used for more than just to specify the Python type – they control exactly how the conversion between the Python and Excel types happen. For many types there are options to how this conversion happens, as well as the type information.

For example, DataFrame types can be converted to and from ranges of values in Excel. But, the fact that an argument or return type is a DataFrame is not enough to control how that conversion happens.

In the DataFrame example, we can choose whether we want the index and columns to be included, or if we want to trim blank space from the range before converting, or various other options. These other options are what are referred to as type parameters in the PyXLL documentation.

Specifying type parameters using a type signature string is done as follows:

from pyxll import xl_func

@xl_func("dataframe<index=True> df, str column: float")
def sum_column(df, column):
    return df[column].sum()

In the above, index=True specifies that when converting from the Excel range to the Python DataFrame the first column of the data should be interpreted as the DataFrame’s index.

To do the same using only Python type hints we need to add this information to the type using the typing.Annotated class and PyXLL’s TypeParameters class. The code above can be re-written as:

from pyxll import xl_func
from pyxll import TypeParameters as TP
from typing import Annotated
import pandas as pd

@xl_func
def sum_column(
    df: Annotated[pd.DataFrame, TP(index=True)],
    column: str
) -> float:
    return df[column].sum()

Using typing.Annotated and TypeParameters all of the options for PyXLL’s various type converters can be specified using Python type hints.

Cached Objects¶

Python objects can be returned from Excel functions as cached object handles, to be passed seamlessly to other Python functions. The Python objects are cached by PyXLL and retrieved when passing objects by their handle into another PyXLL function.

To tell PyXLL to pass a Python object as an object handle the object type is used, but when writing Python code with type hints using the object type directly is often too broad and not helpful as a type hint.

For this reason PyXLL provides the generic type alias Object [1], which can be used as Object[T] where T is the actual type. Type checkers will see T, but the PyXLL add-in will understand that an object handle is to be used.

For example, the following returns a pandas.DataFrame to Excel as an object handle. Type checkers will correctly see this function as returning a DataFrame.

from pyxll import xl_func, Object
import pandas as pd

@xl_func
def load_dataframe() -> Object[pd.DataFrame]:
    df = ... # load or build DataFrame object
    return df

See also Cached Objects.

Real Time Data Types¶

When defining a Real Time Data, or RTD, function, it’s necessary to set the return type to rtd. This tells the PyXLL add-in to register the function differently, as it’s an RTD function.

When using type hints, if writing an RTD function that returns an RTD instance that is no problem and you can simply use RTD as your return type. The RTD class is a generic type [2] and you can specify the inner type of the RTD value as well, for example:

from pyxll import xl_func, RTD

# This class handles the RTD updates and self.value is set to 'int' values
class MyRtdType(Rtd):
    ...

@xl_func
def my_rtd_function(...) -> RTD[int]:
    return MyRtdType(...)

When using a generator or async generator, we still need to tell the PyXLL add-in to treat the generator as an RTD function, but the return type hint now needs to be a generator type and not the plain RTD type.

PyXLL provides two generic type aliases for this purpose, RTDGenerator and RTDAsyncGenerator [3].

from pyxll import xl_func, RTDGenerator, AsyncRTDGenerator
from typing import Any
import asyncio
import time

@xl_func
def my_rtd_generator(...) -> RTDGenerator[Any]:
    i = 0
    while True:
        i += 1
        yield i
        time.sleep(5)

@xl_func
async def my_async_rtd_generator(...) -> AsyncRTDGenerator[Any]:
    i = 0
    while True:
        i += 1
        yield i
        await asyncio.sleep(5)

To type checkers, RTDGenerator[T] is an alias for typing.Generator[T, None] and AsyncRTDGenerator[T] is an alias for typing.AsyncGenerator[T, None], but the PyXLL add-in recognizes these as meaning that the function should be registered as an RTD function.

PyXLL’s RTD type has its own type parameters, such as initial_value.We can set those using typing.Annotated and TypeParameters in the same way as shown earlier:

from pyxll import xl_func, RTDGenerator
from pyxll import TypeParameters as TP
from typing import Annotated
import time

@xl_func
def my_rtd_generator(...) -> Annotated[RTDGenerator[int], TP(initial_value=0)]:
    i = 0
    while True:
        i += 1
        yield i
        time.sleep(5)

This is equivalent to the type signature string rtd<int, initial_value=0>.

Footnotes