Auto-Adjust Excel Column Width by Python openpyxl

Adjusting the column’s width according to the text or headings of the column is particularly important when you copy data from one sheet to another while using Python openpyxl, Pandas, etc.

So, the user has to double-click the handle or drag & drop to adjust the widths to read the headings/text in cells thoroughly.

Auto-adjust columns of a newly created sheet

First, have a look at an example below where we will copy data from one Workbook sheet to another.

For comparison, the source sheet and destination sheet are displayed after executing this code:

import openpyxl as copy_ex;

# Loading source File - Where you want to copy from and specify sheet name

wb_src = copy_ex.load_workbook("test_Excel.xlsx")

sheet_src = wb_src["Product Information"]


# Loading Destination Workbook and specify sheet

wb_dest = copy_ex.load_workbook("test_copy.xlsx")

sheet_dest = wb_dest.active


for i in range (1, sheet_src.max_row + 1):

    for j in range (1, sheet_src.max_column + 1):

        # Assign source cell value

        cell_src = sheet_src.cell(row = i, column = j)

        # paste to destination cell

        sheet_dest.cell(row = i, column = j).value = cell_src.value

# Save the file after copying data

wb_dest.save("test_copy.xlsx")

Result:

You can see in the above graphic, the destination sheet’s columns are narrower than the text width.

The example of auto-adjusting the column widths

We added a few lines of code to expand the column widths according to the text in headings so that it’s all visible.

Program:

import openpyxl as copy_colauto;

# Loading source File - Where you want to copy from and specify sheet name

wb_src_cols = copy_colauto.load_workbook("test_Excel.xlsx")

sheet_src = wb_src_cols["Product Information"]


# Loading Destination Workbook and specify sheet

wb_dest_cols = copy_colauto.load_workbook("test_copy.xlsx")

sheet_dest = wb_dest_cols.active

for i in range (1, sheet_src.max_row + 1):

    for j in range (1, sheet_src.max_column + 1):

        # Assign source cell value

        cell_src = sheet_src.cell(row = i, column = j)


        # paste to destination cell

        sheet_dest.cell(row = i, column = j).value = cell_src.value


#Logic for making column width

for col in sheet_dest.columns:

     SetLen = 0

     column = col[0].column_letter # Get the column name

     for cell in col:

             if len(str(cell.value)) > SetLen:

                 SetLen = len(str(cell.value))

     set_col_width = SetLen + 5

     #Setting the column width

     sheet_dest.column_dimensions[column].width = set_col_width      

# Save the file after copying data

wb_dest_cols.save("col_adjust.xlsx")

Output:

Adjusting column-width of an existing sheet

Similarly, if you want to adjust the column width of an existing sheet without performing any other action – you can do this as well.

The program below uses a “test_sheet.xlsx” where you can see the column width is less than the text it contains:

By running the program, we will make its column width per the text:

from openpyxl import load_workbook

#Load an existing workbook

wb_col = load_workbook("test_sheet.xlsx")


#Using active sheet

sheet = wb_col.active

#Logic for making column width

for col in sheet.columns:

     SetLen = 0

     column = col[0].column_letter # Get the column name

     for cell in col:

             if len(str(cell.value)) > SetLen:

                 SetLen = len(str(cell.value))

     set_col_width = SetLen + 5

     #Setting the column width

     sheet.column_dimensions[column].width = set_col_width       


# Save the workbook

wb_col.save("test_sheet.xlsx")

Result: