How to define dynamic column range in Excel using Python

Question

I am using Python 3 to generate an Excel file. In each run a number of columns is different.

With static number of columns, I would use e.g.:

writer = pd.ExcelWriter("File.xlsx", engine = "xlsxwriter")
writer.sheets["Sheet"].set_column("A:G", 10)

What is a code expression to select all Excel columns (replacing "A:G" above)?

score 0 · Answer 1 · answered Jan 03 '23 at 11:03

0

If you can determine the shape of your data, using the integer based notation can be helpful.

writer.sheets["Sheet"].set_column(0,data.shape[1], 10)

answered Jan 03 '23 at 11:03

boi-doingthings

67
4

Smart, thanks! I was about to go the way of auto-adjust as commented below: – Mr.Slow Jan 03 '23 at 11:08

score 0 · Accepted Answer · answered Jan 03 '23 at 11:09

Another way of actually auto-adjusted width found [here].

writer = pd.ExcelWriter('/path/to/output/file.xlsx') 
df.to_excel(writer, sheet_name='sheetName', index=False, na_rep='NaN')

for column in df:
    column_length = max(df[column].astype(str).map(len).max(), len(column))
    col_idx = df.columns.get_loc(column)
    writer.sheets['sheetName'].set_column(col_idx, col_idx, column_length)

writer.close()

How to define dynamic column range in Excel using Python

2 Answers2