Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Openpyxl style loader from YAML file
#!/usr/bin/env python
import yaml
from openpyxl.styles import (
Alignment,
Color,
PatternFill,
Font,
Border,
Side,
Style)
from openpyxl.cell import get_column_letter
from openpyxl import Workbook
yaml_config = """
# Configuration file for a report
report_styles:
header:
fill:
fill_type: 'solid'
color:
start_color: 'FF92D050'
font:
name: 'Callibri'
size: '10'
bold: True
color:
color: 'FF000000'
alignment:
horizontal: 'center'
vertical: 'center'
border:
left:
side:
border_style: 'thin'
right:
side:
border_style: 'thin'
top:
side:
border_style: 'thin'
bottom:
side:
border_style: 'double'
# Percent formatting
'percent':
font:
name: 'Callibri'
size: '10'
number_format: '0.0%'
border:
left:
side:
border_style: 'thin'
right:
side:
border_style: 'thin'
top:
side:
border_style: 'thin'
bottom:
side:
border_style: 'thin'
# Formatting with a negative number
'bogomips':
font:
name: 'Callibri'
size: '10'
number_format: '#,##0 "bogomips";[Red]-#,##0 "bogomips"'
border:
left:
side:
border_style: 'thin'
right:
side:
border_style: 'thin'
top:
side:
border_style: 'thin'
bottom:
side:
border_style: 'thin'
"""
def coord2excel(col, row):
""" Return an Excel-formatted coordinate given an row / column """
excel_col = get_column_letter(col)
excel_position = ('{col}{row}').format(
col=excel_col,
row=row)
return excel_position
CFG = yaml.load(yaml_config)
class Report():
def __init__(self):
self.styles = {}
self.load_styles()
self.wb = Workbook()
self.ws = self.wb.worksheets[0]
def load_styles(self):
for style in CFG['report_styles']:
xl_font_style = Font()
xl_fill_style = PatternFill()
xl_alignment_style = Alignment()
xl_border_style = Border()
xl_number_format = 'General'
if 'font' in CFG['report_styles'][style]:
xl_color_style = None
font_dict = CFG['report_styles'][style]['font']
if 'color' in font_dict:
if 'color' in font_dict['color']:
xl_color_style = Color(rgb=font_dict['color']['color'])
font_dict.pop('color')
xl_font_style = Font(**CFG['report_styles'][style]['font'])
if xl_color_style:
tmp_font_style = xl_font_style.copy(color=xl_color_style)
xl_font_style = tmp_font_style
if 'alignment' in CFG['report_styles'][style]:
xl_alignment_style = \
Alignment(**CFG['report_styles'][style]['alignment'])
if 'fill' in CFG['report_styles'][style]:
xl_start_color_style = None
xl_end_color_style = None
fill_dict = CFG['report_styles'][style]['fill']
if 'color' in fill_dict:
if 'start_color' in fill_dict['color']:
xl_start_color_style = \
Color(rgb=fill_dict['color']['start_color'])
if 'end_color' in fill_dict['color']:
xl_end_color_style = \
Color(rgb=fill_dict['color']['end_color'])
fill_dict.pop('color')
xl_fill_style = PatternFill(**fill_dict)
if xl_start_color_style:
tmp_fill_style = xl_fill_style.copy(
start_color=xl_start_color_style)
xl_fill_style = tmp_fill_style
if xl_end_color_style:
tmp_fill_style = xl_fill_style.copy(
end_color=xl_end_color_style)
xl_fill_style = tmp_fill_style
if 'border' in CFG['report_styles'][style]:
xl_border_style = None
border_dict = CFG['report_styles'][style]['border']
side_dict = {}
for side in border_dict.keys():
xl_side_style = Side(**border_dict[side]['side'])
side_dict[side] = xl_side_style
xl_border_style = Border(**side_dict)
if 'number_format' in CFG['report_styles'][style]:
xl_number_format = CFG['report_styles'][style]['number_format']
self.styles[style] = Style(
font=xl_font_style,
fill=xl_fill_style,
alignment=xl_alignment_style,
border=xl_border_style,
number_format=xl_number_format)
def prep_format_row(self, format_row, row):
for i in range(0, self.ws.get_highest_column()):
excel_coordinate = coord2excel(i + 1, row)
self.ws.cell(excel_coordinate).style = \
self.styles[format_row]
def generate_report(self):
data_row = []
for i in range(1, 10):
data_row.append('header ' + str(i))
self.ws.append(data_row)
self.prep_format_row('header', self.ws.get_highest_row())
data_row = []
for i in range(1, 10):
data_row.append(i * .1)
self.ws.append(data_row)
self.prep_format_row('percent', self.ws.get_highest_row())
data_row = []
for i in range(-5, 4):
data_row.append(i)
self.ws.append(data_row)
self.prep_format_row('bogomips', self.ws.get_highest_row())
def save(self):
filename = 'report.xlsx'
self.wb.save(filename)
def main():
report = Report()
report.generate_report()
report.save()
if __name__ == "__main__":
main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment