Python - Automatically adjust width of an excel file's columns
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 =)
Updated version as of openpyxl 3.0.0 (using .columns fails with TypeError: expected <class 'str'>
:
for column_cells in ws.columns:
length = max(len(as_text(cell.value)) for cell in column_cells)
ws.column_dimensions[column_cells[0].column_letter].width = length
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