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

Question

Newbie - I have a Python script that adjusts the width of different columns of an excel file, according to the values specified:

import openpyxl
from string import ascii_uppercase

newFile = "D:\Excel Files\abc.xlsx"

wb = openpyxl.load_workbook(filename = newFile)        
worksheet = wb.active

for column in ascii_uppercase:
    if (column=='A'):
        worksheet.column_dimensions[column].width = 30
    elif (column=='B'):
        worksheet.column_dimensions[column].width = 40            
    elif (column=='G'):
        worksheet.column_dimensions[column].width = 45            
    else:
        worksheet.column_dimensions[column].width = 15

wb.save(newFile)

Is there any way through which we can adjust the width of every column to its most optimum value, without explicitly specifying it for different columns (means, without using this "if-elif-elif-......-elif-else" structure)? Thanks!

Answer 1

for col in worksheet.columns:
     max_length = 0
     column = col[0].column # Get the column name
# Since Openpyxl 2.6, the column name is  ".column_letter" as .column became the column number (1-based) 
     for cell in col:
         try: # Necessary to avoid error on empty cells
             if len(str(cell.value)) > max_length:
                 max_length = len(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 =)