openpyxl应用实例——根据导出的源数据自动生成工单看板

用了三百多行代码帮其他部门把他们已有的一套比较费时的报表做了自动化，包括批注、字体和样式都一一还原，原本需要好几个小时的工作用代码两分钟就跑完了，提效效果明显 666

import openpyxl, os, re
import pandas as pd
from datetime import datetime
from win32com.client import DispatchEx
from openpyxl.utils import get_column_letter
from openpyxl.styles import *
from openpyxl.comments import Comment

开始时间 = datetime.now()
print("开始时间:", 开始时间.strftime('%Y-%m-%d %H:%M:%S'))

# 注意：openpyxl 模块不支持.xls格式的表格文件，需要重新保存为.xlsx格式的表格才行。

# 获取文件夹下所有文件，并对文件内容进行拼接
def get_data(file_dir):
    result = pd.DataFrame()
    for root, dirs, files in os.walk(file_dir):  # 获取所有文件
        for file in files:  # 遍历所有文件名
            result = pd.concat([result, pd.read_excel(os.path.join(root,file))], axis = 0, ignore_index = True)  # 拼接绝对路径并放入列表
    return result

def Autowidth(workbook):  # 自动调整列宽
    # 字体格式
    c3_font = Font(name='微软雅黑', size=11)  # 微软雅黑11
    # 设置边框{'medium' 中粗; 'thin'  细;  'thick'  粗;  'dashed'  虚线;  'dotted'  点线}
    border = Border(left=Side(style='thin'), top=Side(style='thin'), right=Side(style='thin'),
                    bottom=Side(style='thin'))  # 给单元格设置细边框
    alignment = Alignment(vertical="center")  # 设置垂直居中
    darkred_fill = PatternFill(fill_type='solid', fgColor='B22222')  # 单元格填充深红色
    c4_font = Font(name='微软雅黑', size=11, bold=True, color='ffffff')  # 微软雅黑11加粗白色，结合深红色单元格使用

    # 使用win32com自动打开文件并保存一下就好了
    xlApp = DispatchEx("Excel.Application")
    xlApp.Visible = False
    xlBook = xlApp.Workbooks.Open(workbook)
    xlBook.Save()
    xlBook.Close()

    bk = openpyxl.load_workbook(workbook, data_only = True)
    for sht in bk:
        # 设置一个字典用于保存列宽数据
        dims = {
   }
        # 遍历表格数据，获取自适应列宽数据
        for row in sht.rows:
            for cell in row:
                if cell.value:
                    # 遍历整个表格，把该列所有的单元格文本进行长度对比，找出最长的单元格
                    # 在对比单元格文本时需要将中文字符识别为1.7个长度，英文字符识别为1个，这里只需要将文本长度直接加上中文字符数量即可.
                    # re.findall('([\u4e00-\u9fa5])', cell.value)能够识别大部分中文字符
                    cell_len = 0.7 * len(re.findall('([\u4e00-\u9fa5])', str(cell.value))) + len(str(cell.value))
                    # print(cell.value, len(str(cell.value)), cell_len)
                    dims[cell.column] = max((dims.get(cell.column, 0), cell_len))
        for col, value in dims.items():
            # 设置列宽，get_column_letter用于获取数字列号对应的字母列号，最后值+2是用来调整最终效果的。
            sht.column_dimensions[get_column_letter(col)].width = min(40, value + 5)
    bk['未完结数据源'].views.sheetView[0].tabSelected = False
    bk.save(workbook)

def colformat(sht, colnum, format):  # 调整列数据格式
    for i in range(2, sht.max_row + 1):
        sht.cell(i, colnum).number_format = format

def colorrgb(r, g, b):  # RGB3组数字转换成6位16进制字符
    colorlist = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F']
    result = ''
    for n in [r, g, b]:
        result = result + colorlist[int(n/16)]
        result = result + colorlist[n % 16]
    return result

# 参数设置
结束日期填充 = '2022-11-20'  # 数据截止日期
结束时间填充 = '18:00:00'  # 统计截止时间
统计周 = [36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47]  # 统计周
统计日批注 = ['0904', '0911', '0918', '0925', '1002', '1009', '1016', '1023', '1030', '1106', '1113', '1120'] # 每周最后一天对应的日期，添加批注用，需与统计周一一对应
统计周批注 = ['0829-0904', '0905-0911', '0912-0918', '0919-0925', '0926-1002', '1003-1009', '1010-1016', '1017-1023', '1024-1030', '1031-1106', '1107-1113', '1114-1120']  # 每周对应的日期范围，添加批注用，需与统计周一一对应
统计月 = [8, 9, 10, 11]  # 统计月
每月周数 = [0, 4, 5, 3] # 统计月对应周数，合并单元格用，需与统计月一一对应
统计月批注 = ['0801-0831', '0901-0930', '1001-1031', '1101-1120']  # 每月对应的日期范围，添加批注用，需与统计月一一对应
批注作者 = '江异'  # 批注作者，添加批注用
批注框高, 批注框宽 = 60, 240  # 批注边框，在这里没有效果，是无效参数

# 源数据路径
file_dir = 'D:/0开思/1履约中心/工单分析/源数据'
data_standard = pd.read_excel('D:/0开思/1履约中心/工单分析/工单问题类型及处理时效.xlsx', sheet_name = '工单问题类型及处理时长', skiprows = (0), usecols = (2, 6, 7, 8))

# 输出文件路径
file_name = 'D:/0开思/1履约中心/工单分析/超时工单看板公示 ' + 结束日期填充.split('-',1)[1].replace('-','.') + '.xlsx'

# 源数据清洗
data = get_data(file_dir)
data = data.drop_duplicates(subset = ['工单号'])
data = data.dropna(subset = ['创建日期'])
data = data[(data.问题类型 != '报错价（一次报价）') & (data.问题类型 != '报错价（二次报价）') & (data.问题类型 != '报价不规范') & (data.处理人职位 != '业务售前客服') & (data.处理人职位 != '测试工程师')]
data['结束日期'].fillna(结束日期填充, inplace = True)
data['结束时间'].fillna(结束时间填充, inplace = True)
data['紧急程度'].fillna('三级', inplace = True)
data['当前处理人'].fillna('', inplace = True)
data['处理人职位'].fillna('', inplace = True)
data['创建时间'] = data.apply(lambda x: datetime.strptime(x['创建日期'] + ' ' + x['创建时间'],'%Y-%m-%d %H:%M:%S'), axis = 1)
data['创建日期'] = data.apply(lambda x: datetime.strptime(x['创建日期'],'%Y-%m-%d'), axis