openpyxl修改格式

 

def Modify_format(table_name):
    wb = openpyxl.load_workbook(table_name)
    # ws = wb.active
    # 处理多个sheet
    for ws in wb:


        # ws.alignment = openpyxl.styles.Alignment(horizontal='left', vertical='center')  # 设置所有行列 左对齐
        # ws.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center') # 设置所有行列居中
        # 单元格cell自动换行  wrapText=True
        for r in ws:
            for c in r:
                c.alignment = openpyxl.styles.Alignment(horizontal='left', vertical='center', wrapText=True)

        lks = []  # 英文变量太费劲，用汉语首字拼音代替
        for i in range(1, ws.max_column + 1):  # 每列循环
            lk = 1  # 定义初始列宽，并在每个行循环完成后重置
            for j in range(1, ws.max_row + 1):  # 每行循环
                sz = ws.cell(row=j, column=i).value  # 每个单元格内容
                if isinstance(sz, str):  # 中文占用多个字节，需要分开处理
                    lk1 = len(sz.encode('gbk'))  # gbk解码一个中文两字节，utf-8一个中文三字节，gbk合适
                else:
                    lk1 = len(str(sz))
                if lk < lk1:
                    lk = lk1  # 借助每行循环将最大值存入lk中
                # print(lk)
            lks.append(lk)  # 将每列最大宽度加入列表。（犯了一个错，用lks = lks.append(lk)报错，append会修改列表变量，返回值none，而none不能继续用append方法）

        # 第二步：设置列宽
        for i in range(1, ws.max_column + 1):
            # 排除O列
            if i == 15:
                continue
            k = get_column_letter(i)  # 将数字转化为列名,26个字母以内也可以用[chr(i).upper() for i in range(97, 123)]，不用导入模块
            ws.column_dimensions[k].width = lks[i - 1] + 2  # 设置列宽，一般加两个字节宽度，可以根据实际情况灵活调整
        # 设置边框
        for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
            for cell in row:
                cell.border = Border(left=Side(border_style="thin", color='000000'),
                                     right=Side(border_style="thin", color='000000'),
                                     top=Side(border_style="thin", color='000000'),
                                     bottom=Side(border_style="thin", color='000000'))

        # 设置冻结首行
        ws.freeze_panes = 'A2'
        # 设置数据筛选
        ws.auto_filter.ref = ws.dimensions
        # 指定列宽
        # ws.column_dimensions['A'].width = 56
        # ws.column_dimensions['B'].width = 22
        # ws.column_dimensions['C'].width = 33
        ws.column_dimensions['D'].width = 60
        # ws.column_dimensions['H'].width = 40
        ws.row_dimensions[1].height = 22  # 设置第1行的行高 33
        # 设置2-100行的行高 99
        for i in range(2, 615):
            ws.row_dimensions[i].height = 33
        wb.save(table_name)


    Modify_format(table_name)   # 修改格式