How to format a cell in Excel using Openpyxl

How to format a cell in Excel using Openpyxl. In this post, Ngolongtech will guide you How to format a cell in Excel using Openpyxl.

How to format a cell in Excel using Openpyxl
How to format a cell in Excel using Openpyxl

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

Table of contents
0-4 00000000 00ffffff 00FF0000 0000FF00 000000ff
         
5-9 00FFFF00 00FF00FF 0000ffff 00000000 00ffffff
         
00FF0000 00FF0000 0000FF00 000000ff 00FFFF00 00FF00FF
         
0000ffff 0000ffff 008000000 00008000 00000080 00808000
         
10-14 00800080 00008080 15-19 00808080 20-24
         
00C0C0C0 00993366 009999ff 25-29 00660066 00ffffcc
         
00ccffff 00FF8080 30-34 00000080 00FF00FF 00FFFF00
         
00FF00FF 0000ffff 00800080 008000000 00008080 000000ff
         
5-9 00FFFF00 25-29 00ffffcc 00ccffff 00FF8080
         
30-34 000066cc 00ccccff 35-39 40-44 0000ccff
         
00ccffcc 00ffff99 0099ccff 45-49 00ff99cc 00666699
         
00cc99ff 00969696 00003366 00339966 00003300 00333300
         
00FFCC99 00993300 00993366 00333399 00333333
       

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

How to style a cell in openpyxl?

To change the style property of a cell, you must first copy the existing style object from the cell and change the value of the property, or you must create a new style object with the desired settings. Then assign the new type object to the cell.Save this answer. copy the existing style object from the cell and change the value of the property or you have to create a new style object with the desired settings. Then, assign the new style object to the cell . Save this answer.

How to change color of a cell in excel using openpyxl?

OpenPyXL gives you a class called mematurefill that you can use to change the background color of cells …
patternType=None..
fgColor=Color().
bgColor=Color().
fill_type=None..
start_color=None..
end_color=None..

How to color a cell in Excel using Python?

How to Add Color to Excel Cells in Python. .
Example:.
Step 1: Import OpenPyXL library and module into Python program to add color to cell…..
Note: OpenPyxl…..
Step2: Connect/Link your Excel workbook file and corresponding workbook work sheet with Python program..

End

Above are some suggestions for solutions to format a cell in Excel using Openpyxl Ngolongtech hopes this will be useful information you can refer to in the process of using. Thanks for reading!

Visit Macwintips.com to update the most practical and useful tips for Mac and Windows!

 

 

Sponsored Links: