Excel_Apply_Custom_Styles.ipynb

Kernel: Python 3

Excel - Apply Custom Styles

Give Feedback | Bug report

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)