Links

Apply Custom Styles

Tags: #excel #openpyxl #font #border #background #naas #finance #snippet
Author: Sébastien Grech​
Last update: 2023-04-12 (Created: 2023-02-07)
Description: This notebook provides instructions on how to apply custom styles to an Excel spreadsheet.

Input

Import libraries

import naas
from openpyxl import load_workbook
from openpyxl.cell import Cell
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles.borders import Border, Side

Setup your variables

# Inputs
excel_init_path = "Excel_Template.xlsx"
​
# Outputs
excel_out_path = "Excel_Custom.xlsx"

Setup your custom style

NB: Colors must be aRGB hex values : 'black' = '000000'
# Sheet Range
sheet_range = "A1:M54"
​
# Sheet Font
sheet_font = Font(name="Arial", bold=False, color="000000", size="11")
​
# Border style
sheet_border = Border(
left=Side(border_style="thin", color="000000"),
right=Side(border_style="thin", color="000000"),
top=Side(border_style="thin", color="000000"),
bottom=Side(border_style="thin", color="000000"),
)
# Number range
number_range = "B2:M54"
​
# Number format
number_format = "#,##0"
# Header range
header_range = "1:1"
​
# Header background
header_bg = PatternFill(start_color="24292e", end_color="24292e", fill_type="solid")
​
# Header font
header_font = Font(name="Arial", bold=True, color="FFFFFF", size="11")
# Total range
total_range = "54:54"
​
# Total background
total_bg = PatternFill(start_color="47DD82", end_color="47DD82", fill_type="solid")

Model

Load Excel file and get active worksheet

wb = load_workbook(excel_init_path)
ws = wb.active
ws

Apply sheet style : Font and border

cell_range = ws[sheet_range]
for row in cell_range:
for cell in row:
cell.font = sheet_font
cell.border = sheet_border

Apply number format

cell_range = ws[number_range]
for row in cell_range:
for cell in row:
cell.number_format = number_format

Apply header format

for cell in ws[header_range]:
cell.fill = header_bg
cell.font = header_font

Apply total format

for cell in ws[total_range]:
cell.fill = total_bg

Output

Save new excel

wb.save(excel_out_path)

Share your excel

naas.asset.add(excel_out_path)