PyXLL The Python Excel Add-In
  • Product
    • Features
    • Get Started
    • Request Demo
    • Download
  • Pricing
  • Resources
    • Documentation
    • Blog
    • Videos
    • FAQ
    • Learn Python
    • Customer Portal
    • About Us
  • Support
    • Documentation
    • Videos
    • FAQ
    • Contact Us
  • Contact Us
Table of Contents
  • PyXLL Documentation
  • Introduction to PyXLL
  • User Guide
    • Installing PyXLL
    • Configuring PyXLL
    • Worksheet Functions
    • Macro Functions
    • Real Time Data
    • Cell Formatting
      • Formatting Worksheet Functions
      • Pandas DataFrame Formatting
      • Custom Formatters
      • Formatting in Macros Functions
    • Charts and Plotting
    • Custom Task Panes
    • ActiveX Controls
    • Using Pandas in Excel
    • Customizing the Ribbon
    • Context Menu Functions
    • Working with Tables
    • Python as a VBA Replacement
    • Menu Functions
    • Reloading and Rebinding
    • Error Handling
    • Deploying your add-in
    • Workbook Metadata
  • Video Guides and Tutorials
  • API Reference
  • What’s new in PyXLL 5
  • Changelog
Close

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.

Formatting an array with borders

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.

Combining Multiple Formatters¶

Formatters can be combined so you do not have to implement every combination in a single formatter.

Formatters are combined by adding them to each other.

For example, to combine the above formatter with the standard DataFrameFormatter you add them together.

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
« Pandas DataFrame Formatting
Formatting in Macros Functions »
  • Home
  • Product
  • Features
  • Documentation
  • Download
  • Pricing
  • Support
  • Documentation
  • Videos
  • FAQ
  • Learn Python
  • Contact Us
  • About
  • About Us
  • Legal
  • Blog
© Copyright PyXLL Ltd