Custom sheet
Here below we will apply different formatting on an Excel sheet
Tags: #excel #openpyxl #font #border #background #naas
Author: Sébastien Grech

Input

Import libraries

1
import naas
2
from openpyxl import load_workbook
3
from openpyxl.cell import Cell
4
from openpyxl.styles import Color, PatternFill, Font, Border
5
from openpyxl.styles.borders import Border, Side
Copied!

Setup your variables

1
# Inputs
2
excel_init_path = "Excel_Template.xlsx"
3
4
# Outputs
5
excel_out_path = "Excel_Custom.xlsx"
Copied!

Setup your custom style

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

Model

Load Excel file and get active worksheet

1
wb = load_workbook(excel_init_path)
2
ws = wb.active
3
ws
Copied!

Apply sheet style : Font and border

1
cell_range = ws[sheet_range]
2
for row in cell_range:
3
for cell in row:
4
cell.font = sheet_font
5
cell.border = sheet_border
Copied!

Apply number format

1
cell_range = ws[number_range]
2
for row in cell_range:
3
for cell in row:
4
cell.number_format = number_format
Copied!

Apply header format

1
for cell in ws[header_range]:
2
cell.fill = header_bg
3
cell.font = header_font
Copied!

Apply total format

1
for cell in ws[total_range]:
2
cell.fill = total_bg
Copied!

Output

Save new excel

1
wb.save(excel_out_path)
Copied!

Share your excel

1
naas.asset.add(excel_out_path)
Copied!
Copy link
Edit on GitHub