Autosize column width in openpyxl – Python

In this tutorial, you will learn how to size the column width of an Excel file so that one can easily see and read the data containing it. We will use the openpyxl library as it is built to perform all the tasks related to Excel files easily and with no complexity.

You can check: How to read xlsx file with openpyxl in Python

Since our job is related to columns, we will read the file in the column fashion. I will be using the below Excel file:

You can see from the image that the column Country needs modification in width.

Autosizing the columns

To read the file column-wise, we use the worksheet.columns function. Now, we have to calculate the maximum width that the particular cell contains in the respective column. Then we will assign that width to column width using worksheet.column_dimensions[column_letter].width function.
You can further add more precision in the width by adjusting the multiplier value (here its 1.2).

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

workbook = load_workbook(filename='example.xlsx')
# Put the file path


worksheet = workbook['Sheet1'] 

# Iterate through each column and adjust the width
for column in worksheet.columns:
    max_length = 0
    column_letter = get_column_letter(column[0].column)
    for cell in column:
        try:
            # Measure the length of the cell value
            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_letter].width = adjusted_width


workbook.save('auto_width.xlsx')

Output

You can see in the above image that the width of the Country Column has been modified.