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))
         except:
             pass
     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

    wb.save(target_file)
    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

[['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_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)
        else:
            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