Python - Excel Column sizing with multiple sheet export to xlsx files

Question

I have a running python script that imports one xlsx file that my co-workers update on a daily basis. My script runs on task scheduler and pulls in this excel file, reads the multiple sheets, sets the header names, and then exports each sheet to an individual folder and excel file that are shared with others in our organization.
The problem I am having is the new xlsx file columns aren't sized at all. Ideally I would like them to be set at a certain width across all columns for ease of use/readability. I have searched high and low looking for a way to read in multiple sheets and export each sheet with column formatting in openpyxl and writer. Anyone have any ideas that may be helpful? Thanks for any help!

Section of code: the rest of the code for the multiple sheets look the same...

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)

Tejas Krishna Reddy Tejas Krishna Reddy · Accepted Answer · 2020-10-29T17:19:57

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)

Python - Excel Column sizing with multiple sheet export to xlsx files

1 Answers