openpyxl Part 12 – Styles

In the previous part of the openpyxl series we learned how to add formulae to your workbook. In this and the following part we’ll be talking about styles. In this part we’ll talk about styling in general and in the next part we’ll talk about conditional styling.

As in the preceding couple articles, we’ll be working on the wb2.xlsx workbook. Here’s what it currently contains:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	911500.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pets for Everyone	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12
Bookworms Rule	Rome	publisher	124474.00	40
SUM				376
MIN				12
MAX				84
AVERAGE				37.6
BIG COMPANIES				4

As usual, let’s start by loading the workbook:

 >>> from openpyxl import load_workbook
 >>> workbook = load_workbook(filename="wb2.xlsx")
 >>> sheet = workbook.active 

Let’s start by adding styles to single cells. There are lots of classes in the openpyxl.styles module that you can use to style your workbook. In the example below we’ll use just a couple of them.

So, first we must import the classes we need:

 >>> from openpyxl.styles import colors, Font, Alignment, Side, Border

Now we can create some styles and then apply them to some cells. Let’s start with some font styles:

 >>> bold_font = Font(bold=True)
 >>> italic_font = Font(italic=True) 
 >>> underlined_font = Font(underline='double') 

Now some styles used for alignment:

 >>> left_aligned = Alignment(horizontal='left')
 >>> right_aligned = Alignment(horizontal='right')
 >>> center_aligned = Alignment(horizontal='center') 

Let’s create some line styles:

 >>> double_border = Side(color=colors.BLUE, border_style='double')
 >>> dashed_border = Side(style='dashed') 

And let’s use them to add a border to a cell:

 >>> cell_border = Border(top=double_border,
 ...                      bottom=double_border,
 ...                      right=dashed_border,
 ...                      left=dashed_border) 

Now that we have the styles defined, let’s ad them to some of the cells:

 >>> sheet["A21"].font = bold_font
 >>> sheet["A22"].font = italic_font
 >>> sheet["A23"].font = underlined_font
 >>> sheet["E21"].alignment = left_aligned
 >>> sheet["A24"].alignment = right_aligned
 >>> sheet["E25"].alignment = center_aligned
 >>> sheet["E24"].border = cell_border 

Let’s save the changes:

 >>> workbook.save(filename="wb2.xlsx") 

Now if you open the workbook, you will see all the styles applied:

Named Styles

You can also create named styles, which are templates that you can apply to multiple cells. Let’s create two simple templates, one for the company column and the other for the header row. First, we have to import the NamedStyle class:

 >>> from openpyxl.styles import NamedStyle 

Now let’s define the template for the first column:

 >>> company_column = NamedStyle(name='company')
 >>> company_column.font = Font(bold=True, size=30, color=colors.GREEN)
 >>> company_column.alignment = Alignment(horizontal='right')
 >>> company_column.border = Border(right=Side(border_style='dotted', color=colors.RED)) 

And the template for the header row:

 >>> header_row = NamedStyle(name='header')
 >>> header_row.font = Font(italic=True, bold=True)
 >>> header_row.alignment = Alignment(horizontal='center', vertical='center')
 >>> header_row.border = Border(bottom=Side(border_style='thick')) 

Finally, let’s apply the two styles to the first column and to the first row:

 >>> for cell in sheet['A']:
 ...     cell.style = company_column
 ... 
 >>> for cell in sheet[1]:
 ...     cell.style = header_row 

Let’s save the workbook:

 >>> workbook.save(filename="wb2.xlsx") 

If you now open the workbook, you should see the new styles:

To see the names of the companies in the first column, let’s just make the first column wider:

In the next part of this series we’ll have a look at conditional styling.