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_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 =)