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!

Excel

Get dynamic active range

Last modified 2mo ago

Copy link

Edit on GitHub

Contents

Input

Model

Output