Custom Functions¶

This tutorial teaches you everything about custom functions.

Basic syntax¶

The simplest custom function only requires the @func decorator:

from xlwings import func

@func
def hello(name):
    return f"Hello {name}!"

In Excel, you can call this function by typing =HELLO("World") into a cell. You could also type World into cell A1, then reference that cell from the formula like so: =HELLO(A1).

By default, function arguments that are single cells (like the name argument in the above example) arrive as a simple float, integer, boolean, or string. One-dimensional Excel ranges arrive as a list of values (e.g., [1, 2]), and two-dimensional ranges arrive as a list of lists (e.g., [[1, 2], [3, 4]]). For more details, see Dimension of arguments. The next section describes how to have the values arrive as pandas DataFrame instead.

pandas DataFrames¶

By using the @arg and @ret decorators, you can apply converters and options to arguments and the return value, respectively.

For example, to read in the values of a range as pandas DataFrame and return the correlations without writing out the header and the index, you would write:

import pandas as pd
from xlwings import func, arg, ret

@func
@arg("df", pd.DataFrame)
@ret(index=False, header=False)
def correl2(df):
    return df.corr()

For an overview of the available converters and options, have a look at Converters and Options.

Using type hints instead of decorators¶

You can use type hints instead of or in combination with decorators:

from xlwings import func
import pandas as pd

@func
def myfunction(df: pd.DataFrame) -> pd.DataFrame:
    # df is a DataFrame, do something with it
    return df

In this example, the return type (-> pd.DataFrame) is optional, as xlwings automatically checks the type of the returned object.

If you need to provide additional conversion arguments, you can either provide them via an annotated type hint or via a decorator. Note that when you use type hints and decorators together, decorators override type hints for conversion.

To set index=False for both the argument and the return value, you can annotate the type hint like this:

from typing import Annotated
from xlwings import func
import pandas as pd

@func
def myfunction(
    df: Annotated[pd.DataFrame, {"index": False}]
) -> Annotated[pd.DataFrame, {"index": False}]:
    # df is a DataFrame, do something with it
    return df

As this might be a little harder to read, you can extract the type definition, which also allows you to reuse it like so:

from typing import Annotated
from xlwings import func
import pandas as pd

Df = Annotated[pd.DataFrame, {"index": False}]

@func
def myfunction(df: Df) -> Df:
    # df is a DataFrame, do something with it
    return df

Alternatively, you could also combine type hints with decorators:

from typing import Annotated
from xlwings import func, arg, ret
import pandas as pd

@func
@arg("df", index=False)
@ret(index=False)
def myfunction(df: pd.DataFrame) -> pd.DataFrame:
    # df is a DataFrame, do something with it
    return df

Variable number of arguments (`*args`)¶

Varargs are supported. You can also use a converter, which will be applied to all arguments provided by *args:

from xlwings import func, arg

@func
@arg("*args", pd.DataFrame, index=False)
def concat(*args):
    return pd.concat(args)

and the same with type hints:

from typing import Annotated
from xlwings import func

@func
def concat(*args: Annotated[pd.DataFrame, {"index": False}]):
    return pd.concat(args)

Doc strings¶

To describe your function and its arguments, you can use a function docstring or the arg decorator, respectively:

from xlwings import func, arg

@func
@arg("name", doc='A name such as "World"')
def hello(name):
    """This is a classic Hello World example"""
    return f"Hello {name}!"

And again with type hints:

from typing import Annotated
from xlwings import func

@func
def hello(name: Annotated[str, {"doc": 'A name such as "World"'}]):
    """This is a classic Hello World example"""
    return f"Hello {name}!"

These doc strings will appear in Excel’s function wizard/formula builder. Note that the name of the arguments will automatically be shown when typing the formula into a cell without having to do anything (intellisense).

Date and time¶

Depending on whether you’re reading from Excel or writing to Excel, there are different tools available to work with date and time.

Reading date and time¶

In the context of custom functions, xlwings will detect numbers, strings, and booleans but not cells with a date/time format. Hence, you need to use converters. For single datetime arguments do this:

import datetime as dt
from xlwings import func

@func
@arg("date", dt.datetime)
def myfunc(date):
    return date

And again with type hints:

import datetime as dt
from xlwings import func

@func
def myfunc(date: dt.datetime):
    return date

Instead of dt.datetime, you can also use dt.date to get a date object instead.

If you have multiple values that you need to convert, you can use the xlwings.to_datetime() function:

import datetime as dt
import xlwings as xw
from xlwings import func

@func
def myfunc(dates):
    dates = [xw.to_datetime(d) for d in dates]
    return dates

And if you are dealing with pandas DataFrames, you can simply use the parse_dates option. It behaves the same as with pandas.read_csv():

import pandas as pd
from xlwings import func, arg

@func
@arg("df", pd.DataFrame, parse_dates=[0])
def timeseries_start(df):
    return df.index.min()

and again with type hints:

from typing import Annotated
import pandas as pd
from xlwings import func

@func
def timeseries_start(df: Annotated[pd.DataFrame, {"parse_dates": [0]}]):
    return df.index.min()

Like pandas.read_csv(), you could also provide parse_dates with a list of columns names instead of indices.

Writing date and time¶

When writing datetime object to Excel, xlwings automatically formats the cells as date if your version of Excel supports data types, so no special handling is required:

import datetime as dt
import xlwings as xw
from xlwings import func

@func
def pytoday():
    return dt.date.today()

By default, it will format the date according to default format of Excel, but you can also override this by providing the date_format return option:

import datetime as dt
import xlwings as xw
from xlwings import func

@func
@ret(date_format="yyyy-m-d")
def pytoday():
    return dt.date.today()

and again with type hints:

import datetime as dt
import xlwings as xw
from xlwings import func

@func
def pytoday() -> Annotated[dt.date, {"date_format": "yyyy-m-d"}]:
    return dt.date.today()

For the accepted date_format string, consult the official Excel documentation.

Note

Some older builds of Excel don’t support date formatting and will display the date as date serial instead, requiring you format it manually.

Namespace¶

A namespace groups related custom functions together by prepending the namespace to the function name, separated with a dot. For example, to have NumPy-related functions show up under the numpy namespace, you could do:

import numpy as np
from xlwings import func

@func(namespace="numpy")
def standard_normal(rows, columns):
    rng = np.random.default_rng()
    return rng.standard_normal(size=(rows, columns))

This function will be shown as NUMPY.STANDARD_NORMAL in Excel.

Sub-namespace¶

You can create sub-namespaces by including a dot like so:

@func(namespace="numpy.random")

This function will be shown as NUMPY.RANDOM.STANDARD_NORMAL in Excel.

Help URL¶

You can include a link to a web page with more information about your function by using the help_url option. The function wizard/formula builder will show that link under “More help on this function”.

from xlwings import func

@func(help_url="https://www.xlwings.org")
def hello(name):
    return f"Hello {name}!"

Array Dimensions¶

If you want your function to accept arguments of any dimensions (as single cell or one- or two-dimensional ranges), you may need to use the ndim option to make your code work in every case. Likewise, you can return a simple list in a vertical orientation by using the transpose option.

Dimension of arguments¶

Depending on the dimensionality of the function parameters, xlwings either delivers a scalar, a list, or a nested list:

Single cells (e.g., A1) arrive as scalar, i.e., number, string, or boolean: 1 or "text", or True
A one-dimensional (vertical or horizontal!) range (e.g. A1:B1 or A1:A2) arrives as list: [1, 2]
A two-dimensional range (e.g., A1:B2) arrives as nested list: [[1, 2], [3, 4]]

This behavior is not only consistent in itself, it’s also in line with how NumPy works and is often what you want: for example, you can directly loop over a vertical 1-dimensional range of cells.

However, if the argument can be anything from a single cell to a one- or two-dimensional range, you’ll want to use the ndim option: this allows you to always get the inputs as a one- or two-dimensional list, no matter what the input dimension is:

from xlwings import func, arg

@func
@arg("x", ndim=2)
def add_one(x):
    return [[cell + 1 for cell in row] for row in data]

and again with type hints:

from typing import Annotated
from xlwings import func

@func
def add_one(x: Annotated[float, {"ndim": 2}]):
    return [[cell + 1 for cell in row] for row in data]

The above sample would raise an error if you’d leave away the ndim=2 and use a single cell as argument x.

Dimension of return value¶

If you need to write out a list in vertical orientation, the transpose option comes in handy:

from xlwings import func, ret

@func
@ret(transpose=True)
def vertical_list():
    return [1, 2, 3, 4]

and again with type hints:

from typing import Annotated
from xlwings import func

@func
def vertical_list() -> Annotated[list, {"transpose": True}]:
    return [1, 2, 3, 4]

Error handling and error cells¶

When writing to Excel, error cells in Excel such as #VALUE! are used to display the Python error. When reading, xlwings turns error cells into None by default but optionally allows you to read them as strings. Let’s get into the details!

Error handling¶

Whenever there’s an error in Python, the cell value will show #VALUE!. To understand what’s going on, click on the cell with the error, then hover (don’t click!) on the exclamation mark that appears: you’ll see the error message.

Writing NaN values¶

np.nan and pd.NA will be converted to Excel’s #NUM! error type.

Error cells¶

Reading error cells¶

By default, error cells such as #VALUE! are converted to None (scalars and lists) or np.nan (NumPy arrays and pandas DataFrames). If you’d like to get them in their string representation, use err_to_str option:

from xlwings import func, arg

@func
@arg("x", err_to_str=True)
def myfunc(x):
    ...

and again with type hints:

from typing import Annotated, Any
from xlwings import func

@func
def myfunc(x: Annotated[list[list[Any]], {"err_to_str"=True}):
    ...

Writing error cells¶

To format cells as proper error cells in Excel, simply use their string representation (#DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!):

from xlwings import func

@func
def myfunc(x):
    return ["#N/A", "#VALUE!"]

Note

Some older versions of Excel don’t support proper error types and will display the error as string instead.

Dynamic arrays¶

If your return value is a one- or two-dimensional array such as a list, NumPy array, or pandas DataFrame, Excel will automatically spill the values into the surrounding cells by using the native dynamic arrays. There are no code changes required:

Returning a simple list:

from xlwings import func

@func
def programming_languages():
    return ["Python", "JavaScript"]

Returning a NumPy array with standard normally distributed random numbers:

import numpy as np
from xlwings import func

@func
def standard_normal(rows, columns):
    rng = np.random.default_rng()
    return rng.standard_normal(size=(rows, columns))

Returning a pandas DataFrame:

import pandas as pd
from xlwings import func

@func
def get_dataframe():
    df = pd.DataFrame({"Language": ["Python", "JavaScript"], "Year": [1991, 1995]})
    return df

Volatile functions¶

Volatile functions are recalculated whenever Excel calculates something, even if none of the function arguments have changed. To mark a function as volatile, use the volatile argument in the func decorator:

import datetime as dt
from xlwings import func

@func(volatile=True)
def last_calculated():
    return f"Last calculated: {dt.datetime.now()}"

Asynchronous functions¶

Custom functions are always asynchronous, meaning that the cell will show #BUSY! during calculation, allowing you to continue using Excel: custom functions don’t block Excel’s user interface.