Working with styles

[jackjindtnt]

Working with styles Introduction Styles are used to change the look of your data while displayed on screen. They are also used to determine the formatting for numbers.

Styles can be applied to the following aspects:

font to set font size, color, underlining, etc. fill to set a pattern or color gradient border to set borders on a cell cell alignment protection The following are the default values

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font font = Font(name='Calibri', ... size=11, ... bold=False, ... italic=False, ... vertAlign=None, ... underline='none', ... strike=False, ... color='FF000000') fill = PatternFill(fill_type=None, ... start_color='FFFFFFFF', ... end_color='FF000000') border = Border(left=Side(border_style=None, ... color='FF000000'), ... right=Side(border_style=None, ... color='FF000000'), ... top=Side(border_style=None, ... color='FF000000'), ... bottom=Side(border_style=None, ... color='FF000000'), ... diagonal=Side(border_style=None, ... color='FF000000'), ... diagonal_direction=0, ... outline=Side(border_style=None, ... color='FF000000'), ... vertical=Side(border_style=None, ... color='FF000000'), ... horizontal=Side(border_style=None, ... color='FF000000') ... ) alignment=Alignment(horizontal='general', ... vertical='bottom', ... text_rotation=0, ... wrap_text=False, ... shrink_to_fit=False, ... indent=0) number_format = 'General' protection = Protection(locked=True, ... hidden=False)

Cell Styles and Named Styles There are two types of styles: cell styles and named styles, also known as style templates.

Cell Styles Cell styles are shared between objects and once they have been assigned they cannot be changed. This stops unwanted side-effects such as changing the style for lots of cells when instead of only one.

from openpyxl.styles import colors from openpyxl.styles import Font, Color from openpyxl import Workbook wb = Workbook() ws = wb.active

a1 = ws['A1'] d4 = ws['D4'] ft = Font(color=colors.RED) a1.font = ft d4.font = ft

a1.font.italic = True # is not allowed

If you want to change the color of a Font, you need to reassign it::

a1.font = Font(color=colors.RED, italic=True) # the change only affects A1 Copying styles Styles can also be copied

from openpyxl.styles import Font from copy import copy

ft1 = Font(name='Arial', size=14) ft2 = copy(ft1) ft2.name = "Tahoma" ft1.name 'Arial' ft2.name 'Tahoma' ft2.size # copied from the 14.0 Basic Font Colors Colors are usually RGB or aRGB hexvalues. The colors module contains some handy constants

from openpyxl.styles import Font from openpyxl.styles.colors import RED font = Font(color=RED) font = Font(color="FFBB00") There is also support for legacy indexed colors as well as themes and tints

from openpyxl.styles.colors import Color c = Color(indexed=32) c = Color(theme=6, tint=0.5) Applying Styles Styles are applied directly to cells

from openpyxl.workbook import Workbook from openpyxl.styles import Font, Fill wb = Workbook() ws = wb.active c = ws['A1'] c.font = Font(size=12) Styles can also applied to columns and rows but note that this applies only to cells created (in Excel) after the file is closed. If you want to apply styles to entire rows and columns then you must apply the style to each cell yourself. This is a restriction of the file format:

col = ws.column_dimensions['A'] col.font = Font(bold=True) row = ws.row_dimensions[1] row.font = Font(underline="single") Styling Merged Cells Sometimes you want to format a range of cells as if they were a single object. Excel pretends that this is possible by merging cells (deleting all but the top-left cell) and then recreating them in order to apply pseudo-styles.

from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment from openpyxl import Workbook

def style_range(ws, cell_range, border=Border(), fill=None, font=None, alignment=None): """ Apply styles to a range of cells as if they were a single cell.

:param ws:  Excel worksheet instance
:param range: An excel range to style (e.g. A1:F20)
:param border: An openpyxl Border
:param fill: An openpyxl PatternFill or GradientFill
:param font: An openpyxl Font object
"""

top = Border(top=border.top)
left = Border(left=border.left)
right = Border(right=border.right)
bottom = Border(bottom=border.bottom)

first_cell = ws[cell_range.split(":")[0]]
if alignment:
    ws.merge_cells(cell_range)
    first_cell.alignment = alignment

rows = ws[cell_range]
if font:
    first_cell.font = font

for cell in rows[0]:
    cell.border = cell.border + top
for cell in rows[-1]:
    cell.border = cell.border + bottom

for row in rows:
    l = row[0]
    r = row[-1]
    l.border = l.border + left
    r.border = r.border + right
    if fill:
        for c in row:
            c.fill = fill

wb = Workbook() ws = wb.active my_cell = ws['B2'] my_cell.value = "My Cell" thin = Side(border_style="thin", color="000000") double = Side(border_style="double", color="ff0000")

border = Border(top=double, left=thin, right=thin, bottom=double) fill = PatternFill("solid", fgColor="DDDDDD") fill = GradientFill(stop=("000000", "FFFFFF")) font = Font(b=True, color="FF0000") al = Alignment(horizontal="center", vertical="center")

style_range(ws, 'B2:F4', border=border, fill=fill, font=font, alignment=al) wb.save("styled.xlsx") Edit Page Setup

from openpyxl.workbook import Workbook

wb = Workbook() ws = wb.active

ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID ws.page_setup.fitToHeight = 0 ws.page_setup.fitToWidth = 1 Named Styles In contrast to Cell Styles, Named Styles are mutable. They make sense when you want to apply formatting to lots of different cells at once. NB. once you have assigned a named style to a cell, additional changes to the style will not affect the cell.

Once a named style has been registered with a workbook, it can be referred to simply by name.

Creating a Named Style

from openpyxl.styles import NamedStyle, Font, Border, Side highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=20) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) Once a named style has been created, it can be registered with the workbook:

wb.add_named_style(highlight) But named styles will also be registered automatically the first time they are assigned to a cell:

ws['A1'].style = highlight Once registered assign the style using just the name:

ws['D5'].style = 'highlight' Using builtin styles The specification includes some builtin styles which can also be used. Unfortunately, the names for these styles are stored in their localised forms. openpyxl will only recognise the English names and only exactly as written here. These are as follows:

‘Normal’ # same as no style Number formats ‘Comma’ ‘Comma [0]’ ‘Currency’ ‘Currency [0]’ ‘Percent’ Informative ‘Calculation’ ‘Total’ ‘Note’ ‘Warning Text’ ‘Explanatory Text’ Text styles ‘Title’ ‘Headline 1’ ‘Headline 2’ ‘Headline 3’ ‘Headline 4’ ‘Hyperlink’ ‘Followed Hyperlink’ ‘Linked Cell’ Comparisons ‘Input’ ‘Output’ ‘Check Cell’ ‘Good’ ‘Bad’ ‘Neutral’ Highlights ‘Accent1’ ‘20 % - Accent1’ ‘40 % - Accent1’ ‘60 % - Accent1’ ‘Accent2’ ‘20 % - Accent2’ ‘40 % - Accent2’ ‘60 % - Accent2’ ‘Accent3’ ‘20 % - Accent3’ ‘40 % - Accent3’ ‘60 % - Accent3’ ‘Accent4’ ‘20 % - Accent4’ ‘40 % - Accent4’ ‘60 % - Accent4’ ‘Accent5’ ‘20 % - Accent5’ ‘40 % - Accent5’ ‘60 % - Accent5’ ‘Accent6’ ‘20 % - Accent6’ ‘40 % - Accent6’ ‘60 % - Accent6’ ‘Pandas’ For more information about the builtin styles please refer to the openpyxl.styles.builtins

[jackjindtnt]

from openpyxl.workbook import Workbook from openpyxl.worksheet.properties import WorksheetProperties, PageSetupProperties

wb = Workbook() ws = wb.active

wsprops = ws.sheet_properties wsprops.tabColor = "1072BA" wsprops.filterMode = False wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True, autoPageBreaks=False) wsprops.outlinePr.summaryBelow = False wsprops.outlinePr.applyStyles = True wsprops.pageSetUpPr.autoPageBreaks = True

[jackjindtnt]

Conditional Formatting Excel supports three different types of conditional formatting: builtins, standard and custom. Builtins combine specific rules with predefined styles. Standard conditional formats combine specific rules with custom formatting. In additional it is possible to define custom formulae for applying custom formats using differential styles.

Note

The syntax for the different rules varies so much that it is not possible for openpyxl to know whether a rule makes sense or not. The basic syntax for creating a formatting rule is:

from openpyxl.formatting import Rule from openpyxl.styles import Font, PatternFill, Border from openpyxl.styles.differential import DifferentialStyle dxf = DifferentialStyle(font=Font(bold=True), fill=PatternFill(start_color='EE1111', end_color='EE1111')) rule = Rule(type='cellIs', dxf=dxf, formula=["10"]) Because the signatures for some rules can be quite verbose there are also some convenience factories for creating them.

Builtin formats The builtins conditional formats are:

ColorScale IconSet DataBar Builtin formats contain a sequence of formatting settings which combine a type with an integer for comparison. Possible types are: ‘num’, ‘percent’, ‘max’, ‘min’, ‘formula’, ‘percentile’.

ColorScale You can have color scales with 2 or 3 colors. 2 color scales produce a gradient from one color to another; 3 color scales use an additional color for 2 gradients.

The full syntax for creating a ColorScale rule is:

from openpyxl.formatting.rule import ColorScale, FormatObject from openpyxl.styles import Color first = FormatObject(type='min') last = FormatObject(type='max')

colors match the format objects:

colors = [Color('AA0000'), Color('00AA00')] cs2 = ColorScale(cfvo=[first, last], color=colors)

a three color scale would extend the sequences

mid = FormatObject(type='num', val=40) colors.insert(1, Color('00AA00')) cs3 = ColorScale(cfvo=[first, mid, last], color=colors)

create a rule with the color scale

from openpyxl.formatting.rule import Rule rule = Rule(type='colorScale', colorScale=cs3) There is a convenience function for creating ColorScale rules

from openpyxl.formatting.rule import ColorScaleRule rule = ColorScaleRule(start_type='percentile', start_value=10, start_color='FFAA0000', ... mid_type='percentile', mid_value=50, mid_color='FF0000AA', ... end_type='percentile', end_value=90, end_color='FF00AA00') IconSet Choose from the following set of icons: ‘3Arrows’, ‘3ArrowsGray’, ‘3Flags’, ‘3TrafficLights1’, ‘3TrafficLights2’, ‘3Signs’, ‘3Symbols’, ‘3Symbols2’, ‘4Arrows’, ‘4ArrowsGray’, ‘4RedToBlack’, ‘4Rating’, ‘4TrafficLights’, ‘5Arrows’, ‘5ArrowsGray’, ‘5Rating’, ‘5Quarters’

The full syntax for creating an IconSet rule is:

from openpyxl.formatting.rule import IconSet, FormatObject first = FormatObject(type='percent', val=0) second = FormatObject(type='percent', val=33) third = FormatObject(type='percent', val=67) iconset = IconSet(iconSet='3TrafficLights1', cfvo=[first, second, third], showValue=None, percent=None, reverse=None)

assign the icon set to a rule

from openpyxl.formatting.rule import Rule rule = Rule(type='iconSet', iconSet=iconset) There is a convenience function for creating IconSet rules:

from openpyxl.formatting.rule import IconSetRule rule = IconSetRule('5Arrows', 'percent', [10, 20, 30, 40, 50], showValue=None, percent=None, reverse=None) DataBar Currently, openpyxl supports the DataBars as defined in the original specification. Borders and directions were added in a later extension.

The full syntax for creating a DataBar rule is:

from openpyxl.formatting.rule import DataBar, FormatObject first = FormatObject(type='min') second = FormatObject(type='max') data_bar = DataBar(cfvo=[first, second], color="638EC6", showValue=None, minLength=None, maxLength=None)

assign the data bar to a rule

from openpyxl.formatting.rule import Rule rule = Rule(type='dataBar', dataBar=data_bar) There is a convenience function for creating DataBar rules:

from openpyxl.formatting.rule import DataBarRule rule = DataBarRule(start_type='percentile', start_value=10, end_type='percentile', end_value='90', ... color="FF638EC6", showValue="None", minLength=None, maxLength=None) Standard conditional formats The standard conditional formats are:

Average Percent Unique or duplicate Value Rank

from openpyxl import Workbook from openpyxl.styles import Color, PatternFill, Font, Border from openpyxl.styles.differential import DifferentialStyle from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule

wb = Workbook() ws = wb.active

Create fill

redFill = PatternFill(start_color='EE1111', ... end_color='EE1111', ... fill_type='solid')

Add a two-color scale

Takes colors in excel 'RRGGBB' style.

ws.conditional_formatting.add('A1:A10', ... ColorScaleRule(start_type='min', start_color='AA0000', ... end_type='max', end_color='00AA00') ... )

Add a three-color scale

ws.conditional_formatting.add('B1:B10', ... ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000', ... mid_type='percentile', mid_value=50, mid_color='0000AA', ... end_type='percentile', end_value=90, end_color='00AA00') ... )

Add a conditional formatting based on a cell comparison

addCellIs(range_string, operator, formula, stopIfTrue, wb, font, border, fill)

Format if cell is less than 'formula'

ws.conditional_formatting.add('C2:C10', ... CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill))

Format if cell is between 'formula'

ws.conditional_formatting.add('D2:D10', ... CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill))

Format using a formula

ws.conditional_formatting.add('E1:E10', ... FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill))

Aside from the 2-color and 3-color scales, format rules take fonts, borders and fills for styling:

myFont = Font() myBorder = Border() ws.conditional_formatting.add('E1:E10', ... FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill))

Highlight cells that contain particular text by using a special formula

red_text = Font(color="9C0006") red_fill = PatternFill(bgColor="FFC7CE") dxf = DifferentialStyle(font=red_text, fill=red_fill) rule = Rule(type="containsText", operator="containsText", text="highlight", dxf=dxf) rule.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))'] ws.conditional_formatting.add('A1:F40', rule) wb.save("test.xlsx")