1

I want to set the column width equal to the max string length in that column, actually I want to adjust the width of the column.

Here is my code:

styler = df.style 
for column in df:
    column_length = df[column].astype(str).str.len().max()
    #styler.set_properties(subset=[column], **{'width': '200px'})
    styler.set_properties(subset=[column], **{'width': str(column_length)+'px'})
 

styler.to_excel('C:/Users/test.xlsx', index=False)

The column width is not set in the exported excel file, what am I doing wrong?

ncica
  • 7,015
  • 1
  • 15
  • 37

2 Answers2

1

If anyone has the same problem here is the solution:

writer = pd.ExcelWriter('C:/Users/test.xlsx')

styler.to_excel(writer, sheet_name='report', index=False)

for column in df:
   column_length = max((
        df[column].astype(str).str.len().max(),  # len of largest item
        len(str(df[column].name))  # len of column name/header
        )) + 1  # adding a little extra space
    col_idx = df.columns.get_loc(column)
    writer.sheets['report'].set_column(col_idx, col_idx, column_length)

writer.save()
ncica
  • 7,015
  • 1
  • 15
  • 37
0

You can use pandas.options.display.max_colwidth = ... and set the max length.

To set the exported excel column width, see this stackoverflow page here.

Adrien Riaux
  • 266
  • 9