Python Excel – Styling Your Worksheets

by

Part 6 in Working with Excel focuses on styling. Impress your customers by styling the Worksheet to match their brand by:

  • adding in their unique logo,
  • using their color scheme,
  • using their preferred font style,
  • formatting the Worksheet using their preferred report style.

Taking the time to do this makes you stand above the crowd.

While you work through this article, you may notice that a number of the styles can be grouped and applied in one iterator.

For learning purposes, we wanted to focus on simple style changes. Feel free to re-write the code.

Background and Preparation

The j-greats.xlsx file should exist on your system. If you do not have this particular file, click here for instructions.

Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

import openpyxl
import openpyxl.styles
import string

file_name = 'j-greats.xlsx'
w_book = load_workbook(filename=file_name)
w_sheet = w_book.active

How to Add a Heading and Style It?

This style example inserts a Heading at the top of the Active Worksheet and applies various styles.

from openpyxl.styles import Alignment
from openpyxl.styles import Font

w_sheet.insert_rows(idx=0, amount=3)
w_sheet['A2'] = 'Jazz Greats'
w_sheet.merge_cells('A2:H2')
w_sheet['A2'].alignment = Alignment(horizontal='center', vertical='center')  
w_sheet['A2'].font = Font(size=22, italic=True, color='1A86A6')
w_book.save(filename=file_name)
w_book.close()
  • Line [1-2] imports openpyxl properties we need to access.
  • Line [3] inserts three blank rows at the top left of the Active Worksheet.
  • Line [4] assigns the heading to cell A2.
  • Line [5] merges columns A1:H2 into one column.
  • Line [6] sets the alignment to center for horizontal and vertical.
  • Line [7] sets the font size and color (a hex value).
  • Line [8] saves the changes.
  • Line [9] closes the Workbook.

Output

How to Add a Logo?

Building on customer branding, this example inserts a logo for the fictitious company Musica at the top left corner of the Active Worksheet.

Save the image below to the same directory as your Python script (current working directory).

from openpyxl.drawing.image import Image  

logo = Image(r"logo-music.png")  
logo.width = 160
logo.height = 60  
w_sheet.add_image(logo, "A2")  
w_book.save(filename=filename)
w_book.close()
  • Line [1] imports openpyxl properties we need to access.
  • Line [2] assigns the logo graphic file to the variable logo.
  • Line [3-4] assigns the width and height of the image.
  • Line [5] adds the image to cell A2.
  • Line [6] saves the changes.
  • Line [7] closes the Workbook.

Output

How to Style a Header Row?

This example styles the header row by changing the font color, background, and alignment properties.

from openpyxl.styles import Alignment
from openpyxl.styles import Font
from openpyxl.styles import PatternFill

new_font  = Font(color='105B71', italic=True, bold=True)
new_align = Alignment(horizontal='center', vertical='center')  
new_fill  = PatternFill(start_color='bde9f7', end_color='bde9f7', fill_type='solid')

for cell in w_sheet["4:4"]:
    cell.font = new_font
    cell.alignment = new_align
    cell.fill = new_fill
w_book.save(file_name)
w_book.close()
  • Line [1-3] imports openpyxl properties we need to access.
  • Line [4-6] set up the cell font, alignment, and fill properties.
  • Line [7] initializes the loop for each cell containing data in the 4th row.
    • Line [8-10] sets the new font, alignment, and background.
  • Line [11] saves the changes.
  • Line [12] closes the open Workbook.

How to Style Borders?

In Part 3, we ran code to insert the Middle column. This column was borderless (by default). Running the code below fixes this issue. To delve further into the border property, click here.

from openpyxl.styles.borders import Border, Side

thin_border = Border(left=Side(style='thin'), 
                     right=Side(style='thin'), 
                     top=Side(style='thin'), 
                     bottom=Side(style='thin'))
w_sheet.cell(row=4, column=2).border = thin_border
w_book.save(file_name)
w_book.close()
  • Line [1] imports an openpyxl property we need to access.
  • Line [2] sets a border style to B4 (Middle). This style matches the existing border style in the header.
  • Line [6] applies this style to B4 (Middle).
  • Line [7] saves the changes.
  • Line [8] closes the Workbook.

Output

How to Change Column Alignment?

For this example, the data in the Birth Year and the Birth Country columns alignment will be a

new_align = Alignment(horizontal='center', vertical='center')  

def align_cell(w_sheet, col, align):
    for cell in w_sheet[col]:
      cell.alignment = align

align_cell(w_sheet, 'D', new_align)
align_cell(w_sheet, 'F', new_align)
w_book.save(file_name)
w_book.close()
  • Line [1] uses the same alignment code as above.
  • Line [2] creates a function with three parameters to set the alignment of a specific column data.
    • Line [3] and [4] set the new alignment for each row in the relevant column parameter.
  • Line [5] and [6] call the align_cell function with three parameters.
  • Line [7] saves the changes.
  • Line [8] closes the open Workbook.

Output

How to Change Column Widths?

If you search this heading in Google, you will come up with endless examples. These examples range from error-ridden to complex solutions. However, with slicing, we can make this code efficient and Pythonic.

max_cols = string.ascii_uppercase[:w_sheet.max_column]
for c in max_cols:
    w_sheet.column_dimensions[c].width = 17
w_book.save(file_name)
w_boook.close()
  • Line [1] uses slicing to select all the column characters (ABCDEFGH) in our Worksheet.
  • Line [2] initializes an iterator to loop through every element (character) of max_cols (ABCDEFGH).
    • Line [3] sets the width of the column. This loop repeats until the end of max_cols.
  • Line [4] saves the changes.
  • Line [5] closes the open Workbook.

How to Format Numbers?

This examples formats column H. This code will format the Est. Worth column.

for cell in w_sheet['H']:
    cell.number_format = '$#,##0.00'
w_book.save(file_name)
w_book.close()
  • Line [1] initializes an iterator to loop through column H.
    • Line [2] applies a format to each cell in this column.
  • Line [3] saves the changes.
  • Line [4] closes the Workbook.

Output

Summary

This article concludes our Working with Excel series.

With the knowledge you have gained, you should feel confident enough to apply different styles to the Worksheet.