openpyxl - adjust column width size

You could estimate (or use a mono width font) to achieve this. Let's assume data is a nested array like [['a1','a2'],['b1','b2']]

We can get the max characters in each column. Then set the width to that. Width is exactly the width of a monospace font (if not changing other styles at least). Even if you use a variable width font it is a decent estimation. This will not work with formulas.

from openpyxl.utils import get_column_lettercolumn_widths = []for row in data:    for i, cell in enumerate(row):        if len(column_widths) > i:            if len(cell) > column_widths[i]:                column_widths[i] = len(cell)        else:            column_widths += [len(cell)]for i, column_width in enumerate(column_widths):    worksheet.column_dimensions[get_column_letter(i+1)].width = column_width

A bit of a hack but your reports will be more readable.

My variation of Bufke's answer. Avoids a bit of branching with the array and ignores empty cells / columns.

Now fixed for non-string cell values.

ws = your current worksheetdims = {}for row in ws.rows:    for cell in row:        if cell.value:            dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value))))    for col, value in dims.items():    ws.column_dimensions[col].width = value

As of openpyxl version 3.0.3 you need to use

 dims[cell.column_letter] = max((dims.get(cell.column_letter, 0), len(str(cell.value))))

as the openpyxl library will raise a TypeError if you pass column_dimensions a number instead of a column letter, everything else can stay the same.

Even more pythonic way to set the width of all columns that works at least in openpyxl version 2.4.0:

for column_cells in worksheet.columns:    length = max(len(as_text(cell.value)) for cell in column_cells)    worksheet.column_dimensions[column_cells[0].column].width = length

The as_text function should be something that converts the value to a proper length string, like for Python 3:

def as_text(value):    if value is None:        return ""    return str(value)