Python调整Excel格式

功能：设置excel数据加外框、适应最适合列宽、数据居中等

原样式：                                                                      输出结果：

                     

# excel 数据处理库
import openpyxl
 
# excel 数据样式设置类
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment 
from openpyxl.styles import Border, Side, colors
import pandas as pd
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, Alignment

import os


def my_border(t_border, b_border, l_border, r_border):
    border = Border(top=Side(border_style=t_border, color=colors.BLACK),
                    bottom=Side(border_style=b_border, color=colors.BLACK),
                    left=Side(border_style=l_border, color=colors.BLACK),
                    right=Side(border_style=r_border, color=colors.BLACK))
    return border

#初始化制定区域边框为所有框线
def format_border(s_column, s_index, e_column , e_index):
    for row in tuple(ws[s_column + str(s_index):e_column + str(e_index)]):
        for cell in row:
            cell.border = my_border('thin', 'thin', 'thin', 'thin')



wb = openpyxl.load_workbook("广东省城市.xlsx")
for sheet in wb.sheetnames:
    ws = wb[sheet]
    df = pd.read_excel("广东省城市.xlsx",sheet_name=sheet, engine='openpyxl')
    # 把表头改到最后一行
    df.loc[len(df)]=list(df.columns)
    for i in df.index:
        # 设置单元格对齐方式 Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
        alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True)

        format_border('A', 0, 'D', len(df)) # 根据实际列数量修改
        for j in ["A","B","C","D",]:
            ws[j+str(i+1)].alignment = alignment
        ws.row_dimensions[i].height = 20
    for col in df.columns:
        # 获取列序号
        index = list(df.columns).index(col)
        # 获取行字母表头
        letter = get_column_letter(index+1)
        # 获取当前列最大宽度
        collen = df[col].apply(lambda x :len(str(x).encode())).max()
        # 设置列宽为最大长度比例
        ws.column_dimensions[letter].width = collen*0.9
    ws.column_dimensions["A"].width = 15 

wb.save("输出结果.xlsx")