Python - Automatically Adjust Width of an Excel File'S Columns

Python - Automatically adjust width of an excel file's columns

for col in worksheet.columns:
max_length = 0
column = col[0].column_letter # Get the column name
for cell in col:
try: # Necessary to avoid error on empty cells
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
adjusted_width = (max_length + 2) * 1.2
worksheet.column_dimensions[column].width = adjusted_width

This could probably be made neater but it does the job. You will want to play around with the adjusted_width value according to what is good for the font you are using when viewing it. If you use a monotype you can get it exact but its not a one-to-one correlation so you will still need to adjust it a bit.

If you want to get fancy and exact without monotype you could sort letters by width and assign each width a float value which you then add up. This would require a third loop parsing each character in the cell value and summing up the result for each column and probably a dictionary sorting characters by width, perhaps overkill but cool if you do it.

Edit: Actually there seems to be a better way of measuring visual size of text: link personally I would prefer the matplotlib technique.

Hope I could be of help, my very first stackoverflow answer =)

Auto adjust column width for multisheet Excel

I know this isn't pretty, but found a solution to this problem where I parse worksheets of xlsx file and find the max length of each column.

import os
import openpyxl
from openpyxl.worksheet.dimensions import ColumnDimension, DimensionHolder
from openpyxl.utils import get_column_letter

def excel_autoadjust_col(path, target_excel, padding):
target_file = os.path.join(path, target_excel)
wb = openpyxl.load_workbook(target_file)
sheets = [sheet for sheet in wb.get_sheet_names()]

for sheet in sheets:
ws = wb[sheet]
dim_holder = DimensionHolder(worksheet=ws)

for col in range(ws.min_column, ws.max_column + 1):
width = 0
for row in range(ws.min_row, ws.max_row + 1):
cell_value = ws.cell(column=col, row=row).value
if cell_value:
cell_len = len(str(cell_value))
if cell_len > width:
width = cell_len + padding

dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=width)

ws.column_dimensions = dim_holder
print("Completed adjustments for {}".format(target_excel))

this is a Frankenstein of everything I found on stackoverflow (ty community).

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


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_letter

column_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)
column_widths += [len(cell)]

for i, column_width in enumerate(column_widths,1): # ,1 to start at 1
worksheet.column_dimensions[get_column_letter(i)].width = column_width

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

Related Topics

Leave a reply