Search code examples
pythonpandasopenpyxlxlsxwriter

Auto-adjust column width to fit the content in panda generated Excel files

I am using pandas.to_excel() to generate xlsx files.

On my Win10 openpyxl is used by default because xlsxwriter is not installed. My question goes to both options.

The columns of resulting xlsx files are to small for the content. I have to adjust them via Excel.

This is how a generated sheet looks like

enter image description here

And this is how it looks like after auto-adjust the width in Excel itself.

enter image description here

I want that pandas/openpyxl/xlsxwriter does this auto-adjusting by itself. Is there an option or a way for this?

Solution

  • The only reliable way I know to do this is to use .autofit() in xlwings:

    import pandas as pd
import xlwings as xw

filename = r"test.xlsx"
df = pd._testing.makeDataFrame()
df.to_excel(filename)

with xw.App(visible=False):
    wb = xw.Book(filename)
    for ws in wb.sheets:
        ws.autofit(axis="columns")
    wb.save(filename)
    wb.close()

    Be aware that using xlwings requires Microsoft Excel to be installed.