[FIXED] adjust column width size using openpyxl

Issue

I'm facing trouble in adjusting column width of the below excel file. I'm using this code.

from openpyxl.utils import get_column_letter

ws.delete_cols(1)   #remove col 'A' which has pandas index no.

for column in ws.iter_cols():
    name = get_column_letter(column[0].column)
    new_col_length = max(len(str(cell.value)) for cell in column)
    #ws.column_dimensions[name].bestFit = True    #I tried this but the result is same
    ws.column_dimensions[name].width = new_col_length

excel sheet:

the output im getting.:

Solution

Something like this should manage the deletion of column A using Openpyxl

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

path = 'col_width.xlsx'
wb = load_workbook(path)
ws = wb['Sheet1']

remerge_cells_list = []

# Remove existing merged cells, and
# Add the calculated new cell coords to remerge_cells_list to re-add after
# the deletion of column A.
for unmerge in range(len(ws.merged_cells.ranges)):
    current_merge = ws.merged_cells.ranges[0]
    new_min_col = get_column_letter(current_merge.min_col-1)
    new_max_col = get_column_letter(current_merge.max_col-1)
    remerge_cells_list.append(new_min_col + str(current_merge.min_row) + ':'
                              + new_max_col + str(current_merge.max_row))
    print("Removing merge: " + current_merge.coord)
    ws.unmerge_cells(current_merge.coord)

print("\nDeleting column A\n")
ws.delete_cols(1)   #remove col 'A' which has pandas index no.

# Set the column width dimenions
for column in ws.iter_cols():
    name = get_column_letter(column[0].column)
    new_col_length = max(len(str(cell.value)) for cell in column)
    # ws.column_dimensions[name].bestFit = True    #I tried this but the result is same
    ws.column_dimensions[name].width = new_col_length+2 # Added a extra bit for padding

# Re-merge the cells from the remerge_cells_list
# Don't think it matters if this is done before or after resizing the columns
for merge in remerge_cells_list:
    print("Add adjusted cell merge: " + merge)
    ws.merge_cells(merge)

wb.save('col_width_out.xlsx')

Answered By - moken