Python-Excel列大小调整和多页导出到xlsx文件

Question

我有一个运行的python脚本，它导入了一个xlsx文件，我的同事每天都会更新这个文件。“我的脚本”在任务计划程序上运行，并拉入此excel文件，读取多个工作表，设置标题名称，然后将每个工作表导出到单独的文件夹和excel文件，以便与组织中的其他人共享。
我遇到的问题是新的xlsx文件列根本没有大小。理想情况下，我希望在所有列中将它们设置为一定的宽度，以便于使用/可读性。我到处寻找一种方法，在openpyxl和writer中读取多个工作表并导出每个工作表的列格式。有人有任何可能有用的想法吗？谢谢你的帮助

代码部分：多个工作表的其余代码看起来相同

import pandas as pd
from pandas import option_context

## List column names for 15 and 16 column sheets
gt_cols16 = ['Patient Name', 'Move In Date', 'Addendum Paperwork', 'Consent Paperwork', 'Authorization Paperwork', 'Paperwork Mailed', 'SOC Pioneer IHP', 'SOC HH', 'SOC Hospice', 'SOC OP', 'SOC Pioneer PCP', 'Plan of Care Sent', 'Covid 19 Positive', 'Covid 19 Last Test Date', 'Would have moved out without services?', 'Notes/Story']
gt_cols15 = ['Patient Name', 'Move In Date', 'Addendum Paperwork', 'Consent Paperwork', 'Authorization Paperwork', 'SOC Pioneer IHP', 'SOC HH', 'SOC Hospice', 'SOC OP', 'SOC Pioneer PCP', 'Plan of Care Sent', 'Covid 19 Positive', 'Covid 19 Last Test Date', 'Would have moved out without services?', 'Notes/Story']

##Define Excel to use
xls = pd.ExcelFile('Grace Tracking.xlsx')

##Generate Data frames from Excel File xls, split each sheet and write to excel file
df1 = pd.read_excel(xls, sheet_name='State St Memory Care')
df1.columns = gt_cols16
df1 = df1.iloc[1:]
df1.to_excel('State_St_Memory_Care\State_St_Memory_Care.xlsx', index=False)

df2 = pd.read_excel(xls, sheet_name='State St IL')
df2.columns = gt_cols15
df2 = df2.iloc[1:]
df2.to_excel('State_St_IL\State_St_IL.xlsx', index=False)

df3 = pd.read_excel(xls, sheet_name='State St AL')
df3.columns = gt_cols16
df3 = df3.iloc[1:]
df3.to_excel('State_St_AL\State_St_AL.xlsx', index=False)

Answer 1

import pandas as pd
from pandas import option_context

## List column names for 15 and 16 column sheets
gt_cols16 = ['Patient Name', 'Move In Date', 'Addendum Paperwork', 'Consent Paperwork', 'Authorization Paperwork', 'Paperwork Mailed', 'SOC Pioneer IHP', 'SOC HH', 'SOC Hospice', 'SOC OP', 'SOC Pioneer PCP', 'Plan of Care Sent', 'Covid 19 Positive', 'Covid 19 Last Test Date', 'Would have moved out without services?', 'Notes/Story']
gt_cols15 = ['Patient Name', 'Move In Date', 'Addendum Paperwork', 'Consent Paperwork', 'Authorization Paperwork', 'SOC Pioneer IHP', 'SOC HH', 'SOC Hospice', 'SOC OP', 'SOC Pioneer PCP', 'Plan of Care Sent', 'Covid 19 Positive', 'Covid 19 Last Test Date', 'Would have moved out without services?', 'Notes/Story']

##Define Excel to use
xls = pd.ExcelFile('Grace Tracking.xlsx')
sheet_names = xls.sheet_names

for names_ in sheet_names:
    df1 = pd.read_excel(xls, sheet_name=names_)
    df1.columns = gt_cols16
    df1 = df1.iloc[1:]
    df1.to_excel('{}/{}.xlsx'.format(names_), index=False)