Array formulas can also be formatted, and PyXLL provides the DataFrameFormatter
class specifically for functions that return pandas DataFrames.
from pyxll import xl_func, xl_return, Formatter, DataFrameFormatter
import pandas as pd
df_formatter = DataFrameFormatter(
index=Formatter(bold=True, interior_color=Formatter.rgb(0xA9, 0xD0, 0x8E)),
header=Formatter(bold=True, interior_color=Formatter.rgb(0xA9,0xD0,0x8E)),
rows=[
Formatter(interior_color=Formatter.rgb(0xE4, 0xF1, 0xDB)),
Formatter(interior_color=Formatter.rgb(0xF4, 0xF9, 0xF1)),
],
columns={
"C": Formatter(number_format="0.00%")
}
)
@xl_func(formatter=df_formatter, auto_resize=True)
@xl_return("dataframe<index=True>")
def get_dataframe():
df = pd.DataFrame({
"A": [1, 2, 3],
"B": [4, 5, 6],
"C": [0.3, 0.6, 0.9]
})
return df
When the function is called from Excel, any previous formatting is cleared and the formatter is applied to the range for the DataFrame.
The DataFrameFormatter class handles many common formatting requirements, but
more complex formatting can be done by a custom formatter.
If the size of the DataFrame changes when inputs change, as long as the formula stays the same the previous range will be cleared before formatting the new range. This allows the returned range to contract without the formatting being left behind.
As well as formatting specific rows and columns based on their position in the DataFrame as shown above, it is also possible to apply formatting that is conditional on the values in the DataFrame.
This is done using the ConditionalFormatter class.
The ConditionalFormatter class is constructed with an expression string and a
formatter object. The expression string is passed to the DataFrame.eval method which
returns a Series where that expression evaluates to True. The formatter will be applied to
the rows where that expression is True. The formatting can be further restricted to only
apply to specific columns.
A list of ConditionalFormatter objects can be passed as the conditional_formatters
argument to DataFrameFormatter. The conditional formatters are applied in order after
any other formatting has been applied.
The following example shows how to color rows green where column A is greater than 0 and red where column A is less than 0.
from pyxll import DataFrameFormatter, ConditionalFormatter, Formatter, xl_func
import pandas as pd
green_formatter = Formatter(interior_color=Formatter.rgb(0x00, 0xff, 0x00))
red_formatter = Formatter(interior_color=Formatter.rgb(0xff, 0x00, 0x00))
a_gt_zero = ConditionalFormatter("A > 0", formatter=green_formatter)
b_lt_zero = ConditionalFormatter("A < 0", formatter=red_formatter)
df_formatter = DataFrameFormatter(conditional_formatters=[
a_gt_zero,
b_lt_zero])
@xl_func(": dataframe<index=False>", formatter=df_formatter, auto_resize=True)
def get_dataframe():
df = pd.DataFrame({
"A": [-1, 0, 1],
"B": [1, 2, 3],
"C": [4, 5, 6]
})
return df
To restrict the formatting to certain columns the columns argument to ConditionalFormatter
can be used. This can be a list of column names or a function that takes a DataFrame and
returns a list of columns.
For more complex conditional formatting a custom conditional formatter class can be derived from
ConditionalFormatterBase.
The method ConditionalFormatterBase.get_formatters should
be implemented to return a DataFrame of Formatter objects where any formatting
is to be applied.
The returned DataFrame must have the same index and columns as the DataFrame
being formatted.
The following example shows how a custom ConditionalFormatter can be written that changes the background color of cells in a DataFrame based on their value.
from pyxll import xl_func, DataFrameFormatter, ConditionalFormatterBase, Formatter
from matplotlib import colors, cm
import pandas as pd
class RainbowFormatter(ConditionalFormatterBase):
def __init__(self, column, min=0, max=100, cmap="rainbow"):
self.column = column
self.min = min
self.max = max
self.cmap = cmap
def get_formatters(self, df):
# Create an empty DataFrame with the same index and columns as df.
formatters = pd.DataFrame(None, index=df.index, columns=df.columns)
# Normalize the column values into the range [0, 1]
normalizer = colors.Normalize(self.min, self.max)
values = normalizer(df[self.column])
# Get a list of (r,g,b,a) colors from a colormap.
colormap = cm.get_cmap(self.cmap)
color_values = colormap(values)
# Create the Formatter objects, remembering Formatter.rgb takes integers from 0 to 255.
# This could use any Formatter class, including your own custom formatters.
formatters[self.column] = [
Formatter(interior_color=Formatter.rgb(int(r * 255), int(g * 255), int(b * 255)))
for r, g, b, a in color_values
]
# Return the DataFrame containing Formatter objects for the cells we want to format
return formatters
# Construct a DataFrameFormatter using our custom RainbowFormatter class.
# Multiple formatters can be combined by adding them together.
df_formatter = DataFrameFormatter(
header=Formatter(interior_color=Formatter.rgb(255, 255, 0)),
conditional_formatters=[
RainbowFormatter("A"),
RainbowFormatter("B")
]
)
# This worksheet function uses our DataFrameFormatter and RainbowFormatters
@xl_func("int min, int max, int step: dataframe", formatter=df_formatter)
def custom_formatter_test(min=0, max=100, step=5):
df = pd.DataFrame({"A": range(min, max, step), "B": range(max, min, -step)})
return df
