import pandas as pdfrom openpyxl import load_workbookfrom openpyxl.utils import get_column_letterfrom openpyxl.styles import Font, Alignment# 自动设置excel列宽def auto_width(filename='合并.xlsx'):# 打开xlsx文件wb = load_workbook(filename)# 遍历sheetfor sheet in wb.sheetnames:ws = wb[sheet]df = pd.read_excel(filename,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)ws['A'+str(i+1)].alignment = alignmentws['B'+str(i+1)].alignment = alignmentws.row_dimensions[i].height = 20for 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.9wb.save(filename)
