- openpyxl.cell.cell module¶
- Working with styles¶
- Cell Styles and Named Styles¶
- Cell Styles¶
- Copying styles¶
- Colours¶
- aRGB colours¶
- Indexed Colours¶
- Working with styles¶
- Cell Styles and Named Styles¶
- Cell Styles¶
- Copying styles¶
- Colours¶
- aRGB colours¶
- Indexed Colours¶
- Python openpyxl – Excel Formatting Cells
- Library
- Python & Excel – Cell Formatting
- Color – Pattern (solid) Fill
- Color – Gradient Fill
- Borderlines
- Text Alignment
openpyxl.cell.cell module¶
The Cell class is required to know its value and type, display options, and any other features of an Excel cell. Utilities for referencing cells using Excel’s ‘A1’ column/row nomenclature are also provided.
class openpyxl.cell.cell. Cell ( worksheet, row=None, column=None, value=None, style_array=None ) [source] ¶
Describes cell associated properties.
Properties of interest include style, type, value, and address.
base_date ¶ check_error ( value ) [source] ¶
Tries to convert Error” else N/A
Check string coding, length, and line break character
The numerical index of the column
Column number of this cell (1-based)
Returns the comment associated with this cell
| Type: | openpyxl.comments.Comment |
|---|
coordinate ¶
This cell’s coordinate (ex. ‘A5’)
data_type ¶ encoding ¶ hyperlink ¶
Return the hyperlink target or an empty string
Always returns the value for excel.
True if the value is formatted as a date
| Type: | bool |
|---|
offset ( row=0, column=0 ) [source] ¶
Returns a cell location relative to this cell.
Row number of this cell (1-based)
Get or set the value held in the cell.
| Type: | depends on the value (string, float, int or datetime.datetime ) |
|---|
class openpyxl.cell.cell. MergedCell ( worksheet, row=None, column=None ) [source] ¶
Describes the properties of a cell in a merged cell and helps to display the borders of the merged cell.
The value of a MergedCell is always None.
column ¶ comment = None¶ coordinate ¶
This cell’s coordinate (ex. ‘A5’)
data_type = ‘n’¶ hyperlink = None¶ row ¶ value = None¶ openpyxl.cell.cell. WriteOnlyCell ( ws=None, value=None ) [source] ¶ openpyxl.cell.cell. get_time_format ( t ) [source] ¶ openpyxl.cell.cell. get_type ( t, value ) [source] ¶
© Copyright 2010 — 2023, See AUTHORS Revision 4212e3e95a42 .
Versions latest stable 3.1.2 3.1.1 3.1.0 3.1 3.0 2.6 2.5.14 2.5 2.4 Downloads html On Read the Docs Project Home Builds Free document hosting provided by Read the Docs.
Working with styles¶
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 only one changes.
>>> 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="FF0000") >>> 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
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
Colours¶
Colours for fonts, backgrounds, borders, etc. can be set in three ways: indexed, aRGB or theme. Indexed colours are the legacy implementation and the colours themselves depend upon the index provided with the workbook or with the application default. Theme colours are useful for complementary shades of colours but also depend upon the theme being present in the workbook. It is, therefore, advisable to use aRGB colours.
aRGB colours¶
RGB colours are set using hexadecimal values for red, green and blue.
>>> from openpyxl.styles import Font >>> font = Font(color="FF0000")
The alpha value refers in theory to the transparency of the colour but this is not relevant for cell styles. The default of 00 will prepended to any simple RGB value:
>>> from openpyxl.styles import Font >>> font = Font(color="00FF00") >>> font.color.rgb '0000FF00'
There is also support for legacy indexed colours as well as themes and tints.
>>> from openpyxl.styles.colors import Color >>> c = Color(indexed=32) >>> c = Color(theme=6, tint=0.5)
Indexed Colours¶
| Index | |||||
|---|---|---|---|---|---|
| 0-4 | 00000000 | 00FFFFFF | 00FF0000 | 0000FF00 | 000000FF |
| 5-9 | 00FFFF00 | 00FF00FF | 0000FFFF | 00000000 | 00FFFFFF |
| 10-14 | 00FF0000 | 0000FF00 | 000000FF | 00FFFF00 | 00FF00FF |
| 15-19 | 0000FFFF | 00800000 | 00008000 | 00000080 | 00808000 |
| 20-24 | 00800080 | 00008080 | 00C0C0C0 | 00808080 | 009999FF |
| 25-29 | 00993366 | 00FFFFCC | 00CCFFFF | 00660066 | 00FF8080 |
| 30-34 | 000066CC | 00CCCCFF | 00000080 | 00FF00FF | 00FFFF00 |
| 35-39 | 0000FFFF | 00800080 | 00800000 | 00008080 | 000000FF |
| 40-44 | 0000CCFF | 00CCFFFF | 00CCFFCC | 00FFFF99 | 0099CCFF |
| 45-49 | 00FF99CC | 00CC99FF | 00FFCC99 | 003366FF | 0033CCCC |
| 50-54 | 0099CC00 | 00FFCC00 | 00FF9900 | 00FF6600 | 00666699 |
| 55-60 | 00969696 | 00003366 | 00339966 | 00003300 | 00333300 |
| 60-63 | 00993300 | 00993366 | 00333399 | 00333333 |
Working with styles¶
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 only one changes.
>>> 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="FF0000") >>> 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
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
Colours¶
Colours for fonts, backgrounds, borders, etc. can be set in three ways: indexed, aRGB or theme. Indexed colours are the legacy implementation and the colours themselves depend upon the index provided with the workbook or with the application default. Theme colours are useful for complementary shades of colours but also depend upon the theme being present in the workbook. It is, therefore, advisable to use aRGB colours.
aRGB colours¶
RGB colours are set using hexadecimal values for red, green and blue.
>>> from openpyxl.styles import Font >>> font = Font(color="FF0000")
The alpha value refers in theory to the transparency of the colour but this is not relevant for cell styles. The default of 00 will prepended to any simple RGB value:
>>> from openpyxl.styles import Font >>> font = Font(color="00FF00") >>> font.color.rgb '0000FF00'
There is also support for legacy indexed colours as well as themes and tints.
>>> from openpyxl.styles.colors import Color >>> c = Color(indexed=32) >>> c = Color(theme=6, tint=0.5)
Indexed Colours¶
| Index | |||||
|---|---|---|---|---|---|
| 0-4 | 00000000 | 00FFFFFF | 00FF0000 | 0000FF00 | 000000FF |
| 5-9 | 00FFFF00 | 00FF00FF | 0000FFFF | 00000000 | 00FFFFFF |
| 10-14 | 00FF0000 | 0000FF00 | 000000FF | 00FFFF00 | 00FF00FF |
| 15-19 | 0000FFFF | 00800000 | 00008000 | 00000080 | 00808000 |
| 20-24 | 00800080 | 00008080 | 00C0C0C0 | 00808080 | 009999FF |
| 25-29 | 00993366 | 00FFFFCC | 00CCFFFF | 00660066 | 00FF8080 |
| 30-34 | 000066CC | 00CCCCFF | 00000080 | 00FF00FF | 00FFFF00 |
| 35-39 | 0000FFFF | 00800080 | 00800000 | 00008080 | 000000FF |
| 40-44 | 0000CCFF | 00CCFFFF | 00CCFFCC | 00FFFF99 | 0099CCFF |
| 45-49 | 00FF99CC | 00CC99FF | 00FFCC99 | 003366FF | 0033CCCC |
| 50-54 | 0099CC00 | 00FFCC00 | 00FF9900 | 00FF6600 | 00666699 |
| 55-60 | 00969696 | 00003366 | 00339966 | 00003300 | 00333300 |
| 60-63 | 00993300 | 00993366 | 00333399 | 00333333 |
Python openpyxl – Excel Formatting Cells
This tutorial will show you how to use the Python openpyxl library to customize Excel formatting such as cell color, alignment, borderlines, etc. We’ll continue with the previous example to make our monthly budget spreadsheet look prettier.
Library
The only library required is openpyxl, type the following in the command prompt window to install the library:
Python & Excel – Cell Formatting
Collectively, these attributes are called styles but I still prefer calling them just formats. One thing to note is that we can apply styles to only one cell at a time. So we’ll need to do a loop in order to format a range of cells.
Color – Pattern (solid) Fill
There are two types of cell color formatting: Pattern Fill and Gradient Fill.
We use Pattern Fill to make a solid color or a pattern for the cell. A Pattern Fill refers to the following formatting in Excel:
The below code changes cells B11:C11 to a light gray background and changes cells B19:C19 to a dotted background known as 6.25% Gray (found in the Pattern Style dropbox). To make the formatting easier to see, we’ll hide all gridlines in Excel by setting ws.sheet_view.showGridLines to False.
Color – Gradient Fill
A Gradient Fill usually takes two colors and interpolates colors between them and fills the cell background. The following code takes a cyan color (’85E4F7′) and a light blue color (‘4617F1’) for the gradient fill. You can change the two RGB hex code inside the stop argument to any color to create your own gradient fill.
Borderlines
Borderlines refer to the four sides of a cell. With openpyxl, we can control the style (solid vs dash, etc), thickness, color, and position of the borderlines. We need two objects to model a cell’s borderline: Border and Side.
A Border can contain one or multiple Sides. Then we assign the cell.border attribute to a Border object to create the borderlines.
Text Alignment
We use the Alignment object to model text/number alignment within a cell, then assign the cell.alignment attribute to the object.