I am searching for a while and I found no straight forward answer to do so except using xlwings, which needs to use microsoft excel which is not desired in my case...

Only way to autofit column width to text is below code

for col in sheet.columns:
    max_length = 0
    column = col[0].column_letter  # Get the column name
    for cell in col:
        if cell.coordinate in sheet.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(str(cell.value))
        except:
            pass
    adjusted_width = max_length   2
    sheet.column_dimensions[column].width = adjusted_width

which is off course character count dependent...

CodePudding user response：

Below code doesn't impact the performance significantly, but you need to have your font file and it's location for this code.

But still xlwings' way is more reliable

It is based on this answer on stack overflow

function for getting the width

def get_text_width(text_input, point_size):
    font = TTFont('../fonts/calibri_regular.ttf')
    cmap = font['cmap']
    t = cmap.getcmap(3, 1).cmap
    s = font.getGlyphSet()
    units_per_em = font['head'].unitsPerEm
    total = 0
    for c in text_input:
        if ord(c) in t and t[ord(c)] in s:
            total  = s[t[ord(c)]].width
        else:
            total  = s['.notdef'].width
    total = total * float(point_size) / units_per_em
    # getFont size in pixels
    inPix = (16 * total) / 12
    inOpenPyXlUnit = inPix / 7

    return inOpenPyXlUnit

And then Iterate and adjust column width...

for col in sheet.columns:
    max_length = 0
    column = col[0].column_letter  # Get the column name
    longestTextCell = None
    for cell in col:
        if cell.coordinate in sheet.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(str(cell.value))
                longestTextCell = cell
                # cell.font = mStyle
                # cell.font = mStyle
        except:
            pass
    adjusted_width = ((get_text_width(str(longestTextCell.value), 12))   2)
    print(column   " max width is "   str(adjusted_width))
    sheet.column_dimensions[column].width = adjusted_width