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:

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

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

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

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) 

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

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:

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

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

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.

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.