Openpyxl style loader from YAML file

load_styles.py

#!/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()