Column Autosizing

[Fran Fitzpatrick]

I know this had been brought up before, but since it's five years old, I wanted to bring it back up again...

I'm looking to autosize my column width's around the largest text in the column.  I figured the `.auto_size` property would be the right thing to use, but setting that to True simply hides the column (and setting .hidden to False does not unhide it).

So, my question, what's the best way to do this?  Surely manually setting the .width attribute to a constant, or to the length of the largest string, is not the right way to go about this...

Thanks,

Fran

[Charlie Clark]

Am .03.2016, 17:37 Uhr, schrieb Fran Fitzpatrick
<francis.x....@gmail.com>:

> I know this had been brought up before

> <https://groups.google.com/forum/#!searchin/openpyxl-users/auto_size/openpyxl-users/r5SLKTnkH78/WHL0DAtTU8YJ>,

> but since it's five years old, I wanted to bring it back up again...
>
> I'm looking to autosize my column width's around the largest text in the
> column. I figured the `.auto_size` property would be the right thing to
> use, but setting that to True simply hides the column (and setting
> .hidden to False does not unhide it).

Yes, this is a problem. This is what the specification says about the
"bestFit" attribute, aliases as "auto-size" in openpyxl:
"""
Flag indicating if the specified column(s) is set to 'best fit'. 'Best
fit' is set to true under these conditions:
The column width has never been manually set by the user, AND
The column width is not the default width
'Best fit' means that when numbers are typed into a cell contained in a
'best fit'
column, the column width should automatically resize to display the
number. [Note: In best fit cases, column width must not be made smaller,
only larger. end note]
"""

This is an instruction for the application to recalculate the column width
where the column width has neither been set nor is it the default. Spot
the contradiction? We don't consider openpyxl to be an application in this
sense, so it does not calculate the width of the column for you.

> So, my question, what's the best way to do this? Surely manually setting
> the .width attribute to a constant, or to the length of the largest
> string, is not the right way to go about this...

This is indeed the thing to do. Note, that column widths are effectively
platform-specific. There are various examples of how you can calculate
them for Windows using the Windows API for font metrics.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[robr...@gmail.com]

Hi there

I had the same problem and have the following workaround which isn't perfect, but a good approximation:

factor_charactersize = 1.05
for column in range(1,max_col+1):
    max = 0
    for row in range(1,max_row+1):
        value = str(worksheet.cell(row=row, column=column).value)
        if len(value) > max:
            max = len(value)
    worksheet.column_dimensions[get_column_letter(column)].width = max * factor_charactersize

depending on the font I multiply the maximal length of a string in a column with a factor.

[Charlie Clark]

Am .08.2018, 19:43 Uhr, schrieb <robr...@gmail.com>:

> Hi there
>
> I had the same problem and have the following workaround which isn't
> perfect, but a good approximation:
>
> factor_charactersize = 1.05
> for column in range(1,max_col+1):
> max = 0
> for row in range(1,max_row+1):
> value = str(worksheet.cell(row=row, column=column).value)
> if len(value) > max:
> max = len(value)
> worksheet.column_dimensions[get_column_letter(column)].width = max *
> factor_charactersize

A bit more robust:

for idx, col in enumerate(ws.columns, 1):
vals = (len(u"{0}".format(c.value)) for c in col if c.value is not None)
max_width = max(vals) * factor
ws.column_dimensions[get_column_letter(column)].width = max_width

> depending on the font I multiply the maximal length of a string in a
> column with a factor.

The width is dependent upon the widest character in the typeface,
generally "m" or "w", but is also OS-specific so what might work on your
machine might not work on someone else's.