Office.js Custom Functions#

Requirements

  • xlwings edition: PRO

  • Server OS: Windows, macOS, Linux

  • Excel platform: Windows, macOS, Web

  • Google Sheets: not supported (planned)

  • Minimum xlwings version: 0.30.0

  • Minimum Excel version: 2021 or 365

Quickstart#

Custom functions are based on Office.js add-ins. It’s therefore a good idea to revisit the Office.js Add-in docs.

  1. Follow the full Office.js Add-in Quickstart. At the end of it, you should have the backend server running and the manifest sideloaded.

  2. That’s it! You can now use the custom functions that are defined in the quickstart project under app/custom_functions.py: e.g., type =HELLO("xlwings") into a cell and hit Enter—you’ll be greeted by Hello xlwings!.

As long as you don’t change the name or arguments of the function, you can edit the code in the app/custom_functions.py file and see the effect immediately by recalculating your formula. You can recalculate by either editing the cell and hitting Enter again, or by hitting Ctrl-Alt-F9 (Windows) or Ctrl-Option-F9 (macOS). If you add new functions or make changes to function names or arguments of existing functions, you’ll need to sideload the add-in again.

Basic syntax#

As you could see in the quickstart sample, the simplest custom function only requires the @pro.func decorator:

from xlwings import pro

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

Note

The decorators for Office.js are imported from xlwings.pro instead of xlwings and therefore read pro.func instead of xw.func. See also Custom functions vs. legacy UDFs.

Python modules#

By default, xlwings expects the functions to live in a module called custom_functions.py.

  • If you want to call your module differently, import it like so: import your_module as custom_functions

  • If you want to store your custom functions across different modules/packages, import them into custom_functions.py:

    # custom_functions.py
    from mypackage.subpackage import func1, func2
    from mymodule import func3, func4
    

Note that custom_functions needs to be imported where you define the required endpoints in your web framework, see Backend and Manifest.

pandas DataFrames#

By using the @pro.arg and @pro.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:

from xlwings import pro

@pro.func
@pro.arg("df", pd.DataFrame, index=False, header=False)
@pro.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.

Doc strings#

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

from xlwings import pro

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

These doc strings will appear in Excel’s function wizard. Note that the name of the arguments will automatically be shown when typing the formula into a cell (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

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 pro

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

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 pro

@pro.func
def isoformat(dates):
    dates = [xw.to_datetime(d) for d in dates]
    return [d.isoformat() for d in 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 pro

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

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

Writing

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 xlwings as xw
import datetime as dt

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

By default, it will format the date according to the content language of your Excel instance, but you can also override this by explicitly providing the date_format option:

import xlwings as xw
import datetime as dt

@pro.func
@pro.ret(date_format="yyyy-m-d")
def pytoday():
    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. See also Limitations.

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 would do:

import numpy as np
from xlwings import pro

@pro.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:

@pro.func(namespace="numpy.random")

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

Default namespace

If you want all your functions appear under a common namespace, you can include the following line under the ShortStrings sections in the manifest XML:

<bt:String id="Functions.Namespace" DefaultValue="XLWINGS"/>

Have a look at manifest-xlwings-officejs-quickstart where the respective line is commented out.

If you define a namespace as part of the function decorator while also having a default namespace defined, the namespace from the function decorator will define the sub-namespace.

Array Dimensions#

If arguments can be anything from a single cell to one- or two-dimensional ranges, you may need to use the ndim option to make your code work in every case. Likewise, there’s an easy trick to return a simple list in a vertical orientation by using the transpose option.

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 two-dimensional list, no matter what the input dimension is:

@pro.func
@pro.arg("x", ndim=2)
def add_one(x):
    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.

Return value

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

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

Error handling and error cells#

Error cells in Excel such as #VALUE! are used to display an error from Python. xlwings also treats error cells in a specific way and allows you to return them explicitly. 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.

If you see Internal Server Error, you need to consult the Python server logs or you can add an exception handler for the type of Exception that you’d like to see in more detail on the frontend, see xlwings_exception_handler in the quickstart project under app/server_fastapi.py.

Writing NaN values#

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

Error cells#

Reading

By default, error cells 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:

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

Writing

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

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

Note

Some older builds of Excel don’t support proper error types and will display the error as string instead, see also Limitations.

Dynamic arrays#

If your return value is not just a sinle value but a one- or two-dimensional list, 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 pro

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

Returning a NumPy array with standard normally distributed random numbers:

import numpy as np
from xlwings import pro

@pro.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 pro

@pro.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 pro

@pro.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 function don’t block Excel’s user interface.

Backend and Manifest#

This section highlights which part of the code in app/server_fastapi.py, app/taskpane.html and manifest-xlwings-officejs-quickstart.xml are responsible for handling custom functions. They are already implemented in the quickstart project.

Backend#

The backend needs to implement the following three endpoints to support custom functions. You can check them out under app/server_fastapi.py or in one of the other framework implementations.

import xlwings as xw
import custom_functions

@app.get("/xlwings/custom-functions-meta")
async def custom_functions_meta():
    return xw.pro.custom_functions_meta(custom_functions)


@app.get("/xlwings/custom-functions-code")
async def custom_functions_code():
    return PlainTextResponse(xw.pro.custom_functions_code(custom_functions))


@app.post("/xlwings/custom-functions-call")
async def custom_functions_call(data: dict = Body):
    rv = await xw.pro.custom_functions_call(data, custom_functions)
    return {"result": rv}
import xlwings as xw
import custom_functions

async def custom_functions_meta(request):
    return JSONResponse(xw.pro.custom_functions_meta(custom_functions))


async def custom_functions_code(request):
    return PlainTextResponse(xw.pro.custom_functions_code(custom_functions))


async def custom_functions_call(request):
    data = await request.json()
    rv = await xw.pro.custom_functions_call(data, custom_functions)
    return JSONResponse({"result": rv})

You’ll also need to load the custom functions by adding the following line at the end of the head element in your HTML file, see app/taskpane.html in the quickstart project:

<head>
  <!-- ... -->
  <script type="text/javascript" src="/xlwings/custom-functions-code"></script>
</head>

Manifest#

The relevant parts in the manifest XML are:

<Requirements>
    <Sets DefaultMinVersion="1.1">
    <Set Name="SharedRuntime" MinVersion="1.1"/>
    </Sets>
</Requirements>

And:

<Runtimes>
    <Runtime resid="Taskpane.Url" lifetime="long"/>
</Runtimes>
<AllFormFactors>
    <ExtensionPoint xsi:type="CustomFunctions">
    <Script>
        <SourceLocation resid="Functions.Script.Url"/>
    </Script>
    <Page>
        <SourceLocation resid="Taskpane.Url"/>
    </Page>
    <Metadata>
        <SourceLocation resid="Functions.Metadata.Url"/>
    </Metadata>
    <Namespace resid="Functions.Namespace"/>
    </ExtensionPoint>
</AllFormFactors>

As mentioned under Namespace: if you want to set a default namespace for your functions, you’d do that with this line:

<bt:String id="Functions.Namespace" DefaultValue="XLWINGS"/>

As usual, for the full context, have a look at manifest-xlwings-officejs-quickstart.xml in the quickstart sample.

Authentication#

To authenticate (and possibly authorize) the users of your custom functions, you’ll need to implement a global getAuth() function under app/taskpane.html. In the quickstart project, it’s set up to give back an empty string:

globalThis.getAuth = async function () {
  return ""
};

The string that this functions returns will be provided as Authorization header whenever a custom function executes so the backend can authenticate the user. Hence, to activate authentication, you’ll need to change this function to give back the desired token/credentials.

Note

The getAuth function is required for custom functions to work, even if you don’t want to authenticate users, so don’t delete it.

SSO / AzureAD authentication

The most convenient way to authenticate users is to use single-sign on (SSO) based on Azure AD, which will use the identity of the signed-in Office user (requires Microsoft 365):

globalThis.getAuth = async function () {
  try {
    let accessToken = await Office.auth.getAccessToken({
      allowSignInPrompt: true,
    });
    return "Bearer " + accessToken;
  } catch (error) {
    return "Error: " + error.message;
  }
};

Deployment#

To deploy your custom functions, please refer to Production deployment in the Office.js Add-ins docs.

Custom functions vs. legacy UDFs#

While Office.js-based custom functions are mostly compatible with the VBA-based UDFs, there are a few differences, which you should be aware of when switching from UDFs to custom functions or vice versa:

Custom functions (Office.js-based)

User-defined functions UDFs (VBA-based)

Supported platforms

  • Windows

  • macOS

  • Excel on the web

  • Windows

Empty cells are converted to

0 => If you want None, you have to set the following formula in Excel: =""

None

Cells with integers are converted to

Integers

Floats

Reading Date/Time-formatted cells

Requires the use of dt.datetime or parse_dates in the arg decorators

Automatic conversion

Writing datetime objects

Automatic cell formatting

No cell formatting

Can write proper Excel cell error

Yes

No

Writing NaN (np.nan or pd.NA) arrives in Excel as

#NUM!

Empty cell

Functions are bound to

Add-in

Workbook

Asynchronous functions

Always and automatically

Requires @xw.func(async_mode="threading")

Decorators

from xlwings import pro, then pro.func etc.

import xlwings as xw, then xw.func etc.

Formula Intellisense

Yes

No

Supports namespaces e.g., NAMESPACE.FUNCTION

Yes

No

Capitalization of function name

Excel formula gets automatically capitalized

Excel formula has same capitalization as Python function

Supports (SSO) Authentication

Yes

No

caller function argument

N/A

Returns Range object of calling cell

@xw.arg(vba=...)

N/A

Allows to access Excel VBA objects

Supports pictures

No

Yes

Requires a local installation of Python

No

Yes

Python code must be shared with end-user

No

Yes

Requires License Key

Yes

No

License

PolyForm Noncommercial License 1.0.0 or xlwings PRO License

BSD 3-clause Open Source License

Limitations#

  • The Office.js Custom Functions API was introduced in 2018 and therefore requires at least Excel 2021 or Excel 365.

  • Note that some functionality requires specific build versions, such as error cells and date formatting, but if your version of Excel doesn’t support these features, xlwings will fall back to either string-formatted error messages or unformatted date serials. For more details on which builds support which function, see Custom Functions requirement sets.

  • xlwings custom functions must be run with the shared runtime, i.e., the runtime that comes with a task pane add-in. The JavaScript-only runtime is not supported.

Roadmap#

  • Streaming functions

  • Object handlers

  • Client-side caching

  • Add support for Google Sheets