设置单元格格式
import openpyxlfrom openpyxl.styles import Font,PatternFill,Side,Alignment,Borderdef 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=borderfor 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 #设置列宽为当前列标题宽度+4wb.save(filename)filename=r'{}\2.xlsx'.format(path)reset_color(filename) reset_col(filename)
