openpyxl format cells 格式化单元格

日常 triage API automation 时，都手动把 failed 的 feature 汇总到excel里，还要手动格式化整得漂亮点出报告，每天如此，是花了不少时间的，总想着把一切手动的工作量都自动化了，一劳永逸。关于自动化定制 report，将分 2 篇介绍，这篇主要来说说 openpyxl 格式化问题，最常用的就是设置单元格的字体，边框，hyperlink，对齐，自适应宽度等，更多细节可以参考 openpyxl

首先将用到的模块导入：

import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, colors, Alignment,Border, Side, PatternFill

定制化 border 边框，边框线的类型，颜色等，通常我们用到黑色细线。具体可以参考 openpyxl.styles.borders

def my_border(side_style = 'thin'):
    border_set = Border(left=Side(style=side_style, color=colors.BLACK),
                    right=Side(style=side_style, color=colors.BLACK),
                    top=Side(style=side_style, color=colors.BLACK),
                    bottom=Side(style=side_style, color=colors.BLACK))
    return border_set

定制化 font 字体方面的属性：这里默认了字体和大小，定制了颜色和是否加粗，更多细节参考 openpyxl.styles.fonts module

def my_font(font_color, font_bold = True):
    font_set = Font(name='Arial', size=10, color=font_color, bold=font_bold)
    return font_set

定制化填充色：默认是不填充的，通常只用到 solid fill type, 有关细节参考 penpyxl.styles.fills.PatternFill

def my_pattern_fill(fill_type=None, fill_color=colors.WHITE):
    fille_set = PatternFill(patternType=fill_type, fgColor=fill_color) 
    return fille_set

定制化对齐，通常垂直居中，水平可 left，center，具体参考 openpyxl.styles.alignment module

def my_alignment(horizontal_type):
    alignment_set = Alignment(horizontal=horizontal_type, vertical='center')
    return alignment_set

定制自适应列宽， 这里有几个小处理：

1. 合并的单元格不做处理，
2. 如果传入 width 可以固定设置宽度，否则根据单元格内容自动设置， 计算每列单元格的字符长度，取最长的设置为了宽度，具体自己也可以调整，这里忽略了中文字体。
3. 还有对 hyperlink 的单元格也做了特殊处理，取 value 的时候过滤掉 link 部分。

def format_collumn_width(worksheet, col_index, width=None):
    col = worksheet[col_index]
    max_length = 0
    cell_value = ''
    for cell in col:
        if width is not None:
            break
        if cell.coordinate in worksheet.merged_cells: # not check merge_cells
            continue
        try: # Necessary to avoid error on empty cells
            # to handle the hyperlink cells
            if cell.value.find('=HYPERLINK') >= 0:
                start_index = cell.value.find(',')
                cell_value = cell.value[start_index - 5:]
            else:
                cell_value = cell.value
            if len(str(cell_value)) > max_length:
                max_length = len(cell_value)
        except:
            pass
    # adjusted_width = (max_length + 2) * 1.2
    if width is None:
        adjusted_width = max_length * 0.9
    else:
        adjusted_width = width
    worksheet.column_dimensions[col_index].width = adjusted_width

hyperlink：设置超链接

cell.value = ‘=HYPERLINK(“{}”, “{}”)’.format(‘hyperlink’, ‘cell content’))

e.g.

  cell.value = '=HYPERLINK("{}", "{}")'.format('http://www.baidu.com', 'bai du'))

或则：

cell.value = 'baidu'
cell.hyperlink = 'http://www.baidu.com'

格式化单元格

def format_cells(sheet, start_collumn, start_index, end_collumn, end_index, 
                font_color = colors.BLACK, font_bold = True,
                fill_type = None, fill_color = colors.WHITE, 
                alignment_horizontal = 'center'):

    boder_set = my_border()
    font_set = my_font(font_color, font_bold)
    fille_set = my_pattern_fill(fill_type, fill_color)
    alignment_set = my_alignment(alignment_horizontal)

    for row in tuple(sheet[start_collumn + str(start_index):end_collumn + str(end_index)]):
        for cell in row:
            cell.border = boder_set
            cell.font = font_set
            cell.alignment = alignment_set
            cell.fill = fille_set

应用：请忽略 all_feature_summary_list，这个都是从 log 里提取出来的，每一个item 都是一个数组，表示 excel 里一行的内容。

def generate_triage_report(excel_file, all_feature_summary_list):
    wb = openpyxl.load_workbook(excel_file)
    report_sheet = wb.create_sheet("Summary Report")
    report_sheet.append(('Smoke',))
    report_sheet.merge_cells('A1:F1')
    report_sheet.append(('Feature', 'Passed', 'Failed', 'Total', 'Failed Reason', 'Comments'))
    for feature in all_feature_summary_list:
        report_sheet.append(feature)

    data_row_count = len(all_feature_summary_list)
    # format 'Smoke' 'Feature' head cells
    format_cells(report_sheet, 'A', 1, 'A', 2, alignment_horizontal = 'left')
    # format 'Failed Reason', 'Comments' head cells
    format_cells(report_sheet, 'E', 2, 'F', 2, alignment_horizontal = 'left')
    # format 'Passed', 'Failed', 'Total' head cells
    format_cells(report_sheet, 'B', 2, 'D', 2)

    # format 'Feature' data cells
    format_cells(report_sheet, 'A', 3, 'A', 2 + data_row_count, font_color='0097da', font_bold=False, alignment_horizontal='left')
    # format 'Passed' data cells
    format_cells(report_sheet, 'B', 3, 'B', 2 + data_row_count, font_bold=False, fill_type='solid', fill_color='92dd96')
    # format 'Failed' data cells
    format_cells(report_sheet, 'C', 3, 'C', 2 + data_row_count, font_bold=False, fill_type='solid', fill_color='f2928c')
    # format 'Total' data cells
    format_cells(report_sheet, 'D', 3, 'D', 2 + data_row_count, font_bold=False)
    # format 'Failed Reason', 'Comments' data cells
    format_cells(report_sheet, 'E', 3, 'F', 2 + data_row_count, font_bold=False, alignment_horizontal='left')

    format_collumn_width(report_sheet, 'A')
    format_collumn_width(report_sheet, 'E')
    format_collumn_width(report_sheet, 'F', 30)
   
    wb.save(excel_file)

效果图如下：