Autofit dataframe in python

Question

I tried using this function that someone answered here. To do autofit for a dataframe when I output it to an xlsx spread sheet. Here is the function:

def get_col_widths(dataframe):
    # First we find the maximum length of the index column   
    idx_max = max([len(str(s)) for s in dataframe.index.values] + [len(str(dataframe.index.name))])
    # Then, we concatenate this to the max of the lengths of column name and its values for each column, left to right
    return [idx_max] + [max([len(str(s)) for s in dataframe[col].values] + [len(col)]) for col in dataframe.columns]

for i, width in enumerate(get_col_widths(df)):
worksheet.set_column(i, i, width)

This works for some but not all the columns. I am wondering if anyone knew of a way to autofit a pandas dataframe.

Pandas does not support autofit. You will need to use openpyxl or xlwings. — James, Aug 26 '19 at 21:50
@James Okay, the function above uses xlswriter. But I have not found a source for xlwings — Wolfy, Aug 26 '19 at 21:52

mouwsy · Answer 1 · 2020-10-26T10:21:42.240

You can use xlwings to autofit your columns after saving the dataframe to excel.

import pandas as pd
import xlwings as xw

report_file = "test.xlsx"

df1 = pd.DataFrame([
    ('this is a long term1', 1, 1, 3),
    ('this is a long term2', 1, 2, 5),
    ('this is a long term3', 1, 1, 6),
    ('this is a long term2', 1, 1, 9),
    ], columns=['term', 'aaaa', 'bbbbbbb', "cccccccccccccccccccccccccccccccccccccccccccccc"])

writer = pd.ExcelWriter(report_file, engine="xlsxwriter")
df1.to_excel(writer, sheet_name="Sheet1", index=False)

workbook = writer.book
worksheet1 = writer.sheets["Sheet1"]
num_format = workbook.add_format({"num_format": '#,##0.00'})

worksheet1.set_column("B:D", cell_format=num_format)
writer.save()

# Autofit all columns with xlwings.
app = xw.App(visible=False)
wb = xw.Book(report_file)

for ws in wb.sheets:
    ws.autofit(axis="columns")

wb.save(report_file)
app.quit()

Autofit dataframe in python

1 Answers1