How to auto-fit Pandas pd.to_excel() XLSX column width

If you export XLSX data using df.to_excel(), the column widths in the spreadsheet are left as default and are not adjusted automatically:

# Load example dataset
df = pd.read_csv("https://datasets.techoverflow.net/timeseries-example.csv", parse_dates=["Timestamp"])
df.set_index("Timestamp", inplace=True)

# Export dataset to XLSX
df.to_excel("example.xlsx")

Solution

You can use UliPlot’s auto_adjust_xlsx_column_width in order to automatically adjust the column width.

pip install UliPlot

Then use it like this in order to export the XLSX:

from UliPlot.XLSX import auto_adjust_xlsx_column_width

# Load example dataset
df = pd.read_csv("https://datasets.techoverflow.net/timeseries-example.csv", parse_dates=["Timestamp"])
df.set_index("Timestamp", inplace=True)

# Export dataset to XLSX
with pd.ExcelWriter("example.xlsx") as writer:
    df.to_excel(writer, sheet_name="MySheet")
    auto_adjust_xlsx_column_width(df, writer, sheet_name="MySheet", margin=0)

Note that the algorithm currently tends to oversize the columns a bit, but in most cases, every type of column will fit.