python pandas设置excel列宽、缩放比例

pandas设置excel列宽、缩放比例

文件存在

file_path = '保存的文件路径'
sheet_name = '数据在Excel的sheet名'

result = pd.DataFrame()
with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
    #数据传给Excel的writer
    result.to_excel(writer, index=False, sheet_name=sheet_name)
    #再从writer加载回该sheet
    worksheet = writer.sheets[sheet_name]
    # 指定列宽设置的范围
    worksheet.set_column("A:G", 20)  
    #循环每一列的列序号，设置列宽为20个单位（指Excel中的列宽单位）  这种方法也行
    for idx in range(result.shape[1]):
        worksheet.set_column(idx, idx, 20)
    # 设置缩放比例
    worksheet.set_zoom(zoom=125)
    #writer保存数据到本地
    writer.save()

文件不存在(在内存中)

df = pd.DataFrame(excel_data)
with BytesIO() as output:
  with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)
    worksheet = writer.sheets["Sheet1"]
    worksheet.set_column("A:G", 20)
    worksheet.set_zoom(zoom=125)
    writer.save()
output.seek(0)
return FileResponse(output, filename="xxx.xslx", as_attachment=True)