Problem with some Column Widths.

[Fabian]

Hi everyone,

I'm encountering an issue while working with openpyxl on my Mac (Python 3.12.4, openpyxl 3.1.4). When I loop over the cells in a worksheet to get the column widths, some columns return a width of 13.0, even though their actual widths in the original Excel sheet are different. However, for other columns, I get the correct widths.

Here is the loop I am using:

for row in sheet.iter_rows():

for cell in row:

col_letter = cell.column_letter:

col_width = sheet.column_dimensions[col_letter].width

The issue is that some columns always return 13.0, which is not their actual width in the Excel sheet. I can't figure out why this happens only to some columns while others return the correct width.

Does anyone have any idea why this inconsistency occurs and how I can fix it?

Thank you in advance for your help!

[Fabian]

Hi again, 

I still have the same problem but I figured out why it happens. Excel wont safe some columns into the .xml file so openpyxl has no values to extract and thus uses the 13.0 as width. 

My Problem still is I cant quite figure out why and where openpyxl gets the 13.0 from I am debugging and maybe I will find the code where it gets the 13.0 then I will keep you updated. Otherwise if someone has a solution for this Problem I would be very happy because it would be a huge time save on my part. 

Thank you for reeding this have a nice day! 

[Charlie Clark]

On 17 Jul 2024, at 9:59, Fabian wrote:

> Hi again,
>
> I still have the same problem but I figured out why it happens. Excel wont
> safe some columns into the .xml file so openpyxl has no values to extract
> and thus uses the 13.0 as width.
> My Problem still is I cant quite figure out why and where openpyxl gets the
> 13.0 from I am debugging and maybe I will find the code where it gets the
> 13.0 then I will keep you updated. Otherwise if someone has a solution for
> this Problem I would be very happy because it would be a huge time save on
> my part.

This has come up in a few bug reports. The underlying problem is that if a column element does not have a width attribute, Excel assumes this to be 0 and hides the column. There's nothing in the specification to indicate this but this is the world we live in.

Therefore, when you create a new column element, it has a default value. I chose 13 because that's what my system comes up with. In theory, it might be possible to use the "defaultcolumnwidth" attribute from worksheet properties but, seeing as this does not seem to be the actual default width, and is a little more complicated to implement, I went for the simpler route, hoping that any code that uses this a lot would set widths as required.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Fabian]

I have found the solution at least for my problem I do not know if it can be generalised but for me it worked this way:

If you take a look at the .xml of a sheet you can find the cols like this:

<cols>

<col min="1" max="1" width="4.5" customWidth="1"/>

<col min="2" max="2" width="18.5" bestFit="1" customWidth="1"/>

<col min="3" max="4" width="8.1640625" bestFit="1" customWidth="1"/>

<col min="5" max="23" width="11.5" bestFit="1" customWidth="1"/>

<col min="24" max="28" width="12.5" bestFit="1" customWidth="1"/>

</cols> 

And I found out that the min or max is corresponding to the letter in the alphabet so if you have a min of 3 and a max of 4 the columns C and D have the same width so I just used a loop if a column gets assigned your 13.0 as  it will get the same width as the previous one but as it can happen that even the column A gets a width of 13.0 for this case or the first loop I just assigned the defaultColumnWidth to this column. 

For me this worked maybe it helps someone else too. 

Best,
Fabian

[Charlie Clark]

On 23 Jul 2024, at 10:18, Fabian wrote:

> And I found out that the min or max is corresponding to the letter in the
> alphabet so if you have a min of 3 and a max of 4 the columns C and D have
> the same width so I just used a loop if a column gets assigned your 13.0
> as it will get the same width as the previous one but as it can happen
> that even the column A gets a width of 13.0 for this case or the first loop
> I just assigned the defaultColumnWidth to this column.

I'm glad you got this working for you but most of it is covered in the documentation:

https://openpyxl.pages.heptapod.net/openpyxl/styles.html#columns-and-rows

And the grouping code shows how things works.

Excel is typically inconsistent in the way columns and rows are handled in this area: columns can be grouped explicitly with groups used not only for outlining; but rows can only be grouped for outlining and even then they're not properly grouped.