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

Issue

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

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

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.

Answered By - mouwsy