Custom Formatters ¶

Although the standard formatters provide basic functionality to handle many common cases, you may want to apply your own formatting. This can be achieved using a custom formatter derived from Formatter.

For applying basic styles in your own formatter you can use Formatter.apply_style, but for everything else you can use the Excel Object Model.

With VBA it’s possible to style cells and ranges by changing the background color, adding borders, and changing the font among other things. In Python it’s no different as the entire Excel Object Model is available to you in Python, just as it is in VBA.

To write a custom formatter create a class that inherits from Formatter. The methods Formatter.apply, Formatter.apply_cell and Formatter.clear can be overridden to apply any formatting you require.

For example, if you wanted to apply borders using a formatter you would do the following:

from pyxll import Formatter, xl_func

# Needed to get VBA constants
from win32com.client import constants

class BorderFormatter(Formatter):

    def apply(self, cell, *args, **kwargs):
        # get the Excel.Range COM object from the XLCell
        xl_range = cell.to_range()

        # add a border to each edge
        for edge in (constants.xlEdgeLeft,
                     constants.xlEdgeRight,
                     constants.xlEdgeTop,
                     constants.xlEdgeBottom):
            border = xl_range.Borders[edge]
            border.LineStyle = constants.xlContinuous
            border.ColorIndex = 0
            border.TintAndShade = 0
            border.Weight = constants.xlThin

        # call the super class to apply any other styles
        super().apply(cell, *args, **kwargs)

border_formatter = BorderFormatter()

@xl_func(formatter=border_formatter, auto_resize=True)
def func_with_borders():
    return [
        [1, 2, 3],
        [4, 5, 6],
        [7, 8, 9]
    ]

You can use the VBA Macro Recorder to record a VBA Macro to apply any style you want, and then examine the recorded VBA code to see what you need to do. The recorded VBA code can be transformed into Python code.

For example, the following VBA code was recorded setting the left edge border. From the recorded code we can see what needs to be done and translate that into the required Python code as demonstrated above.

Sub Macro1()
    Range("D4:G8").Select

    With Selection.Borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .Weight = xlThin
    End With

End Sub

See Python as a VBA Replacement for more information on how to translate VBA code to Python.

from pyxll import xl_func, DataFrameFormatter

df_formatter = DataFrameFormatter()
add_borders = BorderFormatter()

df_formatter_with_borders = df_formatter + add_borders

@xl_func(formatter=df_formatter_with_borders, 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