Trying to change column width of dataframe with df.style.set_properties, but does not work

Question

I am trying to change the column width of my dataframe through the use of pandas' style and set_properties, but the width parameter is the only thing that doesn't seem to work while the rest of the parameters work and apply the appropriate changes.

with pd.ExcelWriter(results_path, mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
     df.style.set_properties(
     **{'width': '600px', 'text-align': 'right', 'background-color': '#B4C6E7', 'color': 'black',     
     'border': '1.3px solid black'}).to_excel \
     (writer, sheet_name="Sheet", header=True, startrow=0, startcol=0,index=False)

I also tried using pd.set_option('display.max_colwidth', None), but that did not have an effect either.

Answer 1

The width parameter in set_properties() is used to set the width of a column's content, not the entire column itself. Also set_properties() is more focused on cell-leveling styling and formatting, while set_column() is used to control the column width. Along with that it's important to know that pd.set_option('display.max_colwidth',None) affects how text is displayed when printed to the console, not the actual column width in the output. To change the width of the entire column, you would need to use other methods shown below:

import pandas as pd

# Your DataFrame
data = {'Column1': ['aaa', 'bbb', 'ccc'],
        'Column2': ['111', '222', '333']}
df = pd.DataFrame(data)

# Change the column width using ExcelWriter
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

    # Get the xlsxwriter workbook and worksheet objects
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']

    # Set the column width and format for your specific column
    worksheet.set_column('B:B', 20)  # For example, sets the width of column B

    # Add styling using pandas' style
    df.style.applymap(lambda x: 'background-color: #B4C6E7; color: black; border: 1.3px solid black')

    writer.save()