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!
for col in worksheet.columns:
max_length = 0
column = col[0].column # 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(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 =)
I have a problem with merged_cells and autosize not work correctly, if you have the same problem, you can solve adding the next lines inside code of oldsea
for col in worksheet.columns:
max_length = 0
column = col[0].column # Get the column name
for cell in col:
if cell.coordinate in worksheet.merged_cells: # not check merge_cells
continue
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
If possible you should determine the length of the longest entry in the column and use that to set the width.
I'm assuming you can make use of for entry in ascii_uppercase.
I'm on mobile atm so can't give a concrete code example but what I said before should help you get closer to what you want to achieve.