Cell Formatting ¶

Formatter for formatting values returned via xl_func, or using XLCell.options and XLCell.value.

Use Formatter.rgb for constructing color values.

Formatters may be combined by adding them together.

Custom formatters should use this class as their base class.

See Cell Formatting for more details.

apply(self, cell, value=None, datatype=None, datatype_ndim=0, datatype_kwargs={}, transpose=False)

The apply method is called to apply a formatter to a cell or range of cells.

It is called after a worksheet function decorated with xl_func has returned if using the formatter kwarg. It can also be used directly with an XLCell instance from a macro function.

This method may be implemented by a sub-class for custom formatting. For array functions, if the formatter should be applied cell by cell for each cell in the range, use apply_cell instead.

Parameters:

cell – Instance of an XLCell the formatting is to be applied to. value – The value returned from the xl_func or XLCell.value. datatype – The datatype of the value being formatted. datatye_ndim – The number of dimensions (0, 1 or 2) of the value being formatted. datatype_kwargs – The parameters of the datatype of the value being formatted. transpose – The transpose option from the xl_func decorator.

When a value is returned from an xl_func the formatter is applied after Excel has finished calculating.

The apply method is called with the value returned, and any details about the datatype of the returned value. This allows the formatter to apply formatting relevant to the returned datatype, and can be conditional on the returned value.

apply_cell(self, cell, value=None, datatype=None, datatype_kwargs={})

For use by custom formatters.

If you need the formatter to be called for each individual cell when formatting an array formula, override this method instead of Formatter.apply.

Unlike Formatter.apply this method is called for each item in the returned value. If you need to apply formatting at the array level and the item level you may override both, but ensure you call the super-class method Formatter.apply from your override apply method.

Parameters:	cell – Instance of an XLCell the formatting is to be applied to. value – The value returned from the xl_func or XLCell.value. datatype – The datatype of the value being formatted. datatype_kwargs – The parameters of the datatype of the value being formatted.

clear(self, cell)

Clear any formatting from a cell, or range of cells.

This is called before applying the formatter.

For a resizing array function, the cell passed to this clear method is the previous range that was formatted, allowing arrays to contract without leaving formatting of empty cells behind.

The default implementation clears all formatting, but this may be overridden in a sub-class if more selective clearing is required.

Parameters:	cell – Instance of XLCell that should have its formatting cleared.

apply_style(cell, style)

Apply a style dictionary to an instance of an XLCell.

This can be used to apply basic styling to a cell without having to use XLCell.to_range and win32com.

The style dictionary may have the following entries:

• interior_color: Interior color of the cell (see Formatter.rgb).
• text_color: Text color (see Formatter.rgb).
• bold: Set to True for bold text, False otherwise.
• italic: Set to True for italic text, False otherwise.
• font_size: Font size in points (int).
• number_format: Excel number format to apply to the cell.
• auto_fit: Auto-fit to the content of the cells. May be True (fit column width), False (don’t fit), ‘columns’ (fit column width), ‘rows’ (fit row width), ‘both’ (fit column and row width).

Parameters:	cell – Instance of XLCell to apply the style to. style – Dict specifying the style to be applied.

rgb(red, green, blue)

Return a color value understood by Excel.

Excel colors are in the form ‘BGR’ instead of the usual ‘RGB’ and this utility method constructs color values from their RGB components.

Parameters:	red (int) – Red component between 0 and 255. green (int) – Green component between 0 and 255. blue (int) – Blue component between 0 and 255.

Formatter for DataFrames.

For each argument expecting a Formatter, a dict may also be provided.

When a list of formatters is used (e.g. for the row or index formatters) the formatters will cycle through the list and repeat. For example, to format a table with striped rows only two row formatters are needed.

__init__(rows=default_row_formatters, header=default_header_formatter, index=default_index_formatter, columns=none, conditional_formatters=None, **kwargs)

Parameters:

rows – Formatter or list of formatters to be applied to the rows. header – Formatter to use for the header column names. index – Formatter or list of formatters to be applied to the index. columns – Dict of column name to formatter or list of formatters to be applied for specific columns (in addition to the any row formatters). conditional_formatters – A list of :py:class`ConditionalFormatters` to be applied in order after any other formatting has been applied. kwargs – Additional Formatter kwargs that will affect the entire formatted range.

default_row_formatters

List of two default formatters for alternating row formats.

default_header_formatter

Default formatter used for any column headers.

default_index_formatter

Default formatter used for any index columns.

Formatter for dates, times and datetimes.

All formats are in the standard Python datetime format.

This formatter tests the values and applies the relevant number format according to the type.

__init__(date_format='%Y-%m-%d', time_format='%H:%M:%S', datetime_format=None)

Parameters:	date_format – Format used for date values. time_format – Format used for time values. datetime_format – Format used for datetime values.

If datetime_format is not specified then it is constructed by combining date_format and time_format.

Conditional formatter for use with DataFrameFormatter.

This can be used to apply formatting to a DataFrame that is conditional on the values in the DataFrame.

The ConditionalFormatter works by evaluating an expression on the DataFrame using DataFrame.eval. Rows where the expression returns True have a formatter applied to them. The formatting can be further restricted to one or more columns.

To apply different formats to different values use multiple ConditionalFormatters.

__init__(expr, formatter, columns=None, **kwargs)

Parameters:

expr – Boolean expression for selecting rows to which the formatter will apply. formatter – Formatter that will be applied to the selected cells. columns – Column name or list of columns that the formatter will be applied to. May also be a callable, in which case it should accept a DataFrame and return a column or list of columns. kwargs – Additional arguments passed to DataFrame.eval when selecting the rows to apply the formatter to.

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

green_formatter = Formatter(interior_color=Formatter.rgb(0, 0xff, 0))
red_formatter = Formatter(interior_color=Formatter.rgb(0xff, 0, 0))

a_gt_zero = ConditionalFormatter("A > 0", formatter=green_formatter)
a_lt_zero = ConditionalFormatter("A < 0", formatter=red_formatter)

df_formatter = DataFrameFormatter(conditional_formatters=[
                                    a_gt_zero,
                                    a_lt_zero])

@xl_func("var x: dataframe", formatter=df_formatter, auto_resize=True)
def load_dataframe(x):
    # load a dataframe with column 'A'
    return df

Base class for conditional formatters.

Subclass this class to create your own custom conditional formatters for use with DataFrameFormatter.

get_formatters(self, df)

Parameters:	df – DataFrame to be formatted.
Returns:	A new DataFrame with the same index and columns as ‘df’ with values being instances of the Formatter class. Where no formatting is to be applied the returned DataFrame value should be None.