How to format a cell in Excel using Openpyxl

Styles can be applied to the following aspects:

• Fonts to set font size, color, underline, etc
• Fill to set a pattern or color gradient
• Border to set a border on a cell
• Cell alignment

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 ) 
>>>

Mobile Styles and Named Styles

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

Mobile style

Cell styles are shared between objects, and once they are specified, they cannot be changed. This stops unwanted side effects like style changes for multiple cells when there is only one change.

>>> 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' ] 
>>>  ) 
>>> a1 . font  =  ft 
>>> d4 . font  =  ft 
>>> 
>>> a1 . font . italic  =  True  # is not allowed # doctest: +SKIP 
>>> 
>>> # If you want to change the color of a Font, you need to reassign it:: 
>>> 
>>> a1 . font  =  Font ( color = "FF0000" ,  italic = True )  # the change only affects A1

Copy Styles

Style 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

Color

Colors for fonts, backgrounds, borders, etc. can be set in three ways: index, argb or theme. Indexed colors are a legacy implementation, and the index-dependent colors themselves are provided with the workbook or with the application defaults. Theme colors are useful for complementary shades of color but also depend on the theme present in the workbook. Therefore, it is recommended to use Argb colors.

Argb Colors

RGB colors are set using the hexadecimal values ​​for red, green, and blue.

>>> from  openpyxl.styles  import  Font 
>>> font  =  Font ( color = "FF0000" )

The alpha value refers to the theory of color transparency but this has nothing to do with cell styles. The default 00 will be prepared for any simple RGB value:

>>> from  openpyxl.styles  import  Font 
>>> font  =  Font ( color = "00FF00" ) 
>>> font . color . rgb 
'0000FF00'

There is also support for legacy indexed colors as well as themes and shades.

>>> from  openpyxl.styles.colors  import  Color 
>>> c  =  Color ( indexed = 32 ) 
>>> c  =  Color ( theme = 6 ,  tint = 0.5 )

Indexed colors

Standard colors

0033CCCC

>>> from  openpyxl.workbook  import  Workbook 
>>> from  openpyxl.styles  import  Font ,  Fill 
>>> wb  =  Workbook () 
>>> ws  =  wb . active 
>>> c  =  ws [ 'A1' ] 
>>> c . font  =  Font ( size = 12 )

>>> col  =  ws . column_dimensions [ 'A' ] 
>>> col . font  =  Font ( bold = True ) 
>>> row  =  ws . row_dimensions [ 1 ] 
>>> row . font  =  Font ( underline = "single" )

00FFCC00

>>> from  openpyxl.styles  import  Border ,  Side ,  PatternFill ,  Font ,  GradientFill ,  Alignment 
>>> from  openpyxl  import  Workbook 
>>> 
>>> wb  =  Workbook () 
>>> ws  =  wb . active 
>>> ws . merge_cells ( 'B2:F4' ) 
>>> 
>>> top_left_cell  =  ws [ 'B2' ] 
>>. value  =  "My Cell" 
>>> 
>>> thin  =  Side ( border_style = "thin" ,  color = "000000" ) 
>>> double  =  Side ( border_style = "double" ,  color = "ff0000" ) 
>>> 
>>> top_left_cell . border  =  border ( top = double ,  left = thin ,  bottom = double ) 
>>> top_left_cell . fill  =  PatternFill ( "solid" ,  fgColor = "DDDDDD" ) 
>>> top_left_cell . fill  =  fill  =  GradientFill ( stop = ( "000000" ,  "FFFFFF" )) 
>>> top_left_cell . font   =  Font ( b = True ,  color = "FF0000" ) 
>>>. alignment  =  Alignment ( horizontal = "center" ,  vertical = "center" ) 
>>> 
>>> wb . save ( "styled.xlsx" )

00FF6600

>>> 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 
>>>page_setup . fitToWidth  =  1

55-60

60-63 not affect the cell.

Subscripts 64 and 65 cannot be set and are reserved for foreground and background colors, respectively.

Apply Styles

>>> 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' ] 
>>>  ) 
>>> a1 . font  =  ft 
>>> d4 . font  =  ft 
>>> 
>>> a1 . font . italic  =  True  # is not allowed # doctest: +SKIP 
>>> 
>>> # If you want to change the color of a Font, you need to reassign it:: 
>>> 
>>> a1 . font  =  Font ( color = "FF0000" ,  italic = True )  # the change only affects A1

0

The style is applied directly to the cells

>>> 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' ] 
>>>  ) 
>>> a1 . font  =  ft 
>>> d4 . font  =  ft 
>>> 
>>> a1 . font . italic  =  True  # is not allowed # doctest: +SKIP 
>>> 
>>> # If you want to change the color of a Font, you need to reassign it:: 
>>> 
>>> a1 . font  =  Font ( color = "FF0000" ,  italic = True )  # the change only affects A1

first

Styles can also be applied to columns and rows but note that this only applies to cells created (in excel) after the file is closed. If you want to apply styles to entire rows and columns, you must apply the styles to each cell yourself. Here is a limitation of the file format:

>>> 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' ] 
>>>  ) 
>>> a1 . font  =  ft 
>>> d4 . font  =  ft 
>>> 
>>> a1 . font . italic  =  True  # is not allowed # doctest: +SKIP 
>>> 
>>> # If you want to change the color of a Font, you need to reassign it:: 
>>> 
>>> a1 . font  =  Font ( color = "FF0000" ,  italic = True )  # the change only affects A1

2

>>> 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' ] 
>>>  ) 
>>> a1 . font  =  ft 
>>> d4 . font  =  ft 
>>> 
>>> a1 . font . italic  =  True  # is not allowed # doctest: +SKIP 
>>> 
>>> # If you want to change the color of a Font, you need to reassign it:: 
>>> 
>>> a1 . font  =  Font ( color = "FF0000" ,  italic = True )  # the change only affects A1

3

The merged cells behave similarly to other cell objects. Its value and format are defined in its top left cell. To change the border of the entire merged cell, change the border of its top left cell. The format was created for writing purposes only.

Edit page settings

• Styles are named

In contrast to cell styles, named styles are mutable. They make sense when you want to apply formatting to many different cells at once. NB. Once you’ve 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.
• Create a named style
• Once a named style has been created, it can be registered with the workbook:
• But named styles will also be automatically registered the first time they are assigned to a cell:
• Once registered, assign the style just by name:

Using Stickin Styles

• The specification covers several construction styles that may also be used. Unfortunately, the names for these styles are stored in their local forms. OpenPyXL will only recognize English names and only exactly as written here. This is as follows:
• ‘Normal # # like no style
• Jo number format
• Comma [0]

‘Currency’

• Currency [0]
• ‘Percent’
• Lots of information¶
• ‘Calculation’
• ‘Total’
• Warning text
• Text explanation

Text style

• Hyperlink

Follow hyperlink

• Cells are linked
• Compare lor
• Check box
• ‘Neutral’
• Highlights
• ‘20% – Accent3
• ’40 % – Accent3
• ‘60% – Accent3
• ‘20% – Accent4
• ’40 % – Accent4
• ‘60% – Accent4
• ’20 % – Accent5,
• ’40 % – Accent5
• ‘60% – Accent5
• ‘20% – Accent6
• ’40 % – Accent6
• ‘60% – Accent6

For more information on building types, please refer to

>>> 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' ] 
>>>  ) 
>>> a1 . font  =  ft 
>>> d4 . font  =  ft 
>>> 
>>> a1 . font . italic  =  True  # is not allowed # doctest: +SKIP 
>>> 
>>> # If you want to change the color of a Font, you need to reassign it:: 
>>> 
>>> a1 . font  =  Font ( color = "FF0000" ,  italic = True )  # the change only affects A1

4