Python设置excel单元格格式（填充、边框、根据首行内容自动调整列宽）

设置单元格格式

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

def reset_color(filename):
    wb=openpyxl.load_workbook(filename)
    fill=PatternFill(  #设置填充样式
        fill_type='solid',
        start_color='99ccff')
    border=Border(top=Side(border_style='thin',color='000000'), #设置边框样式
            bottom=Side(border_style='thin',color='000000'),
            left=Side(border_style='thin',color='000000'),
            right=Side(border_style='thin',color='000000'))
    for i in wb.sheetnames:
        ws=wb[i]
        ws.sheet_view.showGridLines=False  #隐藏默认网线
        for c in range(1,ws.max_column+1):
            for r in range(1,ws.max_row+1): #添加边框
                bordercell=ws.cell(r,c)
                bordercell.border=border
        for end in range(1,ws.max_column+1):
            fillcell=ws.cell(1,end)
            fillcell.fill=fill #填充首行
    wb.save(filename)
def reset_col(filename):
    wb=load_workbook(filename)
    for sheet in wb.sheetnames:
        ws=wb[sheet]
        df=pd.read_excel(filename,sheet)
        for col in df.columns:
            index=list(df.columns).index(col)
            letter=get_column_letter(index+1)
            collen=len(str(col).encode())  #读取首行（标题行）宽度
            print(collen)
            ws.column_dimensions[letter].width=collen+4  #设置列宽为当前列标题宽度+4
    wb.save(filename)
            
filename=r'{}\2.xlsx'.format(path)


reset_color(filename)            
reset_col(filename)