How can a formatter be applied during an Excel function?

The @xl_func decorator accepts a "formatter" kwarg to apply a formatter to any results that are returned. In this case the formatter has to be declared before the function runs, but what can you do if you need to change what formatter is used depending on some inputs to the function?

One solution is to use pyxll.schedule_call to schedule a call to the Formatter.apply method of a formatter after Excel has finished calculating. You can find the docs for for Formatter.apply method that you need here https://www.pyxll.com/docs/api/formatting.html#formatter. You'll see you need an XLCell instance in order to call that, and you can get that using pyxll.xlfCaller.

One issue is that the calling cell is just a single cell, not the full expanded range. If you are using a version of Excel with dynamic arrays (Excel 365 or >2019) then you can get the full range using the "SpillingToRange" property on the Excel Range object.

Below is an example of how to schedule applying a formatter from inside an Excel worksheet function:

from pyxll import xl_func, schedule_call, xlfCaller, DataFrameFormatter, XLCell
import pandas as pd

def apply_formatter(formatter, cell, value, dtype, *args, **kwargs):
    # Get the full range if dynamic arrays are being used
    r = cell.to_range()
    if r.HasSpill:
        cell = XLCell.from_range(r.SpillingToRange)

    # Apply the formatter
    formatter.apply(cell, value, dtype, *args, **kwargs)


@xl_func(": dataframe")
def df_test():
    df = pd.DataFrame({"A": range(10), "B": range(10)})

    # Schedule a call to format the result
    formatter = DataFrameFormatter()
    cell = xlfCaller()
    schedule_call(apply_formatter, formatter, cell, df, "dataframe")

    return df