Cell formatting (https://www.pyxll.com/docs/userguide/formatting/index.html) is used to apply custom formatting to the result of a PyXLL worksheet function.
For example:
from pyxll import xl_func, Formatter
import datetime as dt
date_formatter = Formatter(number_format="yyyy-mm-dd")
@xl_func(formatter=date_formatter)
def get_date():
return dt.date.today()
We can write custom formatters that apply whatever formatting we like, based on the value returned from the function:
from pyxll import Formatter, xl_func
class CustomFormatter(Formatter):
def apply(self, cell, value, *args, **kwargs):
# apply some formatting to 'cell'
# 'value' is the value returned from our worksheet function
self.apply_style(cell, {"bold": True})
formatter = CustomFormatter()
@xl_func(formatter=formatter)
def my_worksheet_function():
return "Hello!"
But what if we want to pass some formatting information from the worksheet function to the formatting method?
@xl_func(formatter=formatter)
def my_worksheet_function(is_bold=True):
# How to pass 'bold' to our formatter??
return "Hello!"
Add metadata to the return value
The solution is to add this extra information ('bold', in our example) to the return value so that when that value is passed to our formatter we can read it.
from pyxll import Formatter, xl_func
class CustomFormatter(Formatter):
def apply(self, cell, value, *args, **kwargs):
# We can get the 'is_bold' property from our value
is_bold = value.is_bold
self.apply_style(cell, {"bold": is_bold})
formatter = CustomFormatter()
@xl_func(formatter=formatter)
def my_worksheet_function(is_bold=True):
# But what to set our 'return_value' to?
# If we use a string as in our previous example we can't set 'is_bold'???
return_value = ???
return_value.is_bold = bold
return return_value
Wrapping the return value in another type
If we are returning an instance of Python class where either was can alter the class to add more properties, or we can set additional properties without any issues (such as on a pandas DataFrame) then there is no problem. In those cases we can set the formatting information we need on the returned value and pick it up in our apply method.
For other types, or if we don't want to pollute our value type with formatting information, we need to wrap the return value in a wrapper type to get the formatting information to the apply method.
For example:
class FormattedValue:
def __init__(self, value, is_bold): # + other attributes as needed
self.value = value
self.is_bold = is_bold
@xl_func(formatter=formatter)
def my_worksheet_function(is_bold=True):
# We return both the value and the formatting information
return FormattedValue(value="Hello!", is_bold=is_bold)
But now we have another issue! When we return our "FormattedValue" to Excel is is returned as a cached object and we see the return value as "FormattedValue@0" instead of the value, "Hello!".
In order to solve that, we need to register a custom return type for our FormattedValue class using the xl_return_value decorator (see https://www.pyxll.com/docs/userguide/udfs/argtypes.html#custom-types).
from pyxll import xl_func, xl_return_type
@xl_return_type("FormattedValue", "var", typing_type=FormattedValue)
def unwrap_formatted_value(x):
return x.value
@xl_func(formatter=formatter)
def my_worksheet_function(is_bold=True) -> FormattedValue:
# Note the type annotation above!
return FormattedValue(value="Hello!", is_bold=is_bold)
We have to add a type annotation or supply a function signature to xl_func to tell PyXLL that we want to use the FormatterValue type converter when calling this function from Excel. Then, PyXLL will automatically convert the FormattedValue when calling our function, and the underlying wrapped value will be returned to Excel.
The FormattedValue with the formatting information is passed to our custom formatter's apply method and so we can get any formatting information we need that way, and the wrapped value is unwrapped automatically when returning to Excel!
Extra credit: What about more complex types?
The above 'unwrap_formatted_value' type converter assumes that the 'value' of the formatted value can always easily be mapped to one of Excel's basic types using the 'var' type.
But what about more complex types where we need to explicitly set the type conversion, for example, if returning a DataFrame?
To do that, we can add the data type to the FormattedValue class and use that as part of our unwrapping function. We can use PyXLL's "get_type_converter" function to do the type conversion (https://www.pyxll.com/docs/api/types.html#get-type-converter)
from pyxll import xl_func, xl_return_type, get_type_converter
class FormattedValue:
def __init__(self, value, dtype, is_bold): # added 'dtype'
self.value = value
self.dtype = dtype
self.is_bold = is_bold
@xl_return_type("FormattedValue", "var", typing_type=FormattedValue)
def unwrap_formatted_value(x):
# Get a function to convert from our 'dtype'
to_var = get_type_converter(x.dtype, "var")
# Return the converted, unwrapped, value
return to_var(x.value)
@xl_func(formatter=formatter)
def my_worksheet_function(is_bold=True) -> FormattedValue:
# Construct our Formatted value with the added 'dtype'
# so it gets converted correctly
return FormattedValue(value="Hello!",
dtype="str",
is_bold=is_bold)