Degrouping column loses formatting and formatting whole columns cannot be re-applied

[Romain Lhotte]

Initial problem: columns are silently grouped, hiding one degroups them and the formatting is lost.

MRE: example.xlsx

import openpyxl

wb = openpyxl.load_workbook('example.xlsx')
sheet = wb['Sheet1']

# hide column 3
sheet.column_dimensions['C'].hidden = True
# unhide column 3
sheet.column_dimensions['C'].hidden = False

wb.save("example2.xlsx")

Solution : degroup the columns, copy the formatting that got lost by degrouping the columns and now everything works as expected. 

However, it doesn't really work as I would want it to (columns B, C, D had larger width and are highlighted in yellow).  The width is fixable (cf. code) but not the background colour.  I lose the background colour and I can't put it back since openpyxl cannot set a background colour for a whole column, only for already created cells.

import openpyxl

wb = openpyxl.load_workbook('example.xlsx')
sheet = wb['Sheet1']

for v in sheet.column_dimensions.values():
    if v.min != v.max:
       v.max = v.min # COLUMNS B C D GET DEGROUPED (AND I LOSE THE WIDTH/BG COLOUR ON COLUMNS C AND D)

# copy formatting of column 2 to column 3 and 4
sheet.column_dimensions['C'].width = sheet.column_dimensions['B'].width
sheet.column_dimensions['D'].width = sheet.column_dimensions['B'].width

wb.save("example2.xlsx")

wb = openpyxl.load_workbook('example2.xlsx')
sheet = wb['Sheet1']

for v in sheet.column_dimensions.values():
    if v.min != v.max:
       print(v.min, v.max) # NOTHING IS PRINTED, COLUMNS B C D DID NOT REGROUP THEMSELVES

# hide column 3
sheet.column_dimensions['C'].hidden = True  # THIS USED TO DESTROY THE FORMATTING BUT IT DOESN'T ANYMORE
# unhide column 3
sheet.column_dimensions['C'].hidden = False

wb.save("example2.xlsx")

I'm using:

Python 3.11.1, openpyxl == 3.1.2

[Charlie Clark]

On 14 Apr 2023, at 12:28, Romain Lhotte wrote:

It's probably worth noting that this is a continuation of an issue that isn't a bug:

https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1988

Initial problem: columns are silently grouped, hiding one degroups them and
the formatting is lost.

MRE: example.xlsx
https://foss.heptapod.net/openpyxl/openpyxl/uploads/1cdea86950184b83e9fd39ab933743d9/example.xlsx
import openpyxl

wb = openpyxl.load_workbook('example.xlsx')
sheet = wb['Sheet1']

hide column 3

sheet.column_dimensions['C'].hidden = True

unhide column 3

sheet.column_dimensions['C'].hidden = False

wb.save("example2.xlsx")

Solution : degroup the columns, copy the formatting that got lost by
degrouping the columns and now everything works as expected.

This is all the information relating to the columns in the actual worksheet.

<:col min="2" max="4" width="20.6328125" style="1" customWidth="1" />

Openpyxl is a library for working with the file format. As such, it does not necessarily replicate functionality found in applications like Excel and which you're seeing here.

Look at the column dimension in openpyxl, this is best as dictionary.

dict(b)
{'width': '20.6328125',
'customWidth': '1',
'style': '1',
'min': '2',
'max': '4'}

However, it doesn't really work as I would want it to (columns B, C, D had
larger width and are highlighted in yellow). The width is fixable (cf.
code) but not the background colour. I lose the background colour and I
can't put it back since openpyxl cannot set a background colour for a whole
column, only for already created cells.
import openpyxl

As I suggested, you need to copy formatting to new entries in the column dimensions.

The easiest way to do this is probably something like this:

from copy import copy
from openpyxl import load_workbook
wb = load_workbook("Issues/bug1988.xlsx")
ws = wb.active
cd = ws.column_dimensions
b = cd['B']

for idx, key in zip(enumerate(["B", "C", "D"), start=2):
   col = copy(b)
   col.min = col.max = idx
   cd[key] = col

I've spent a lot of time with the OOXML Working Group trying to make sense of the specification, which is frankly awful. And have suggested that the Openpyxl implementation be changed in the future to separate grouping (essentially for outline purposes) from formatting. This would mean you couldn't apply formatting to groups, and cannot group formats. I think this would make things a lot clearer and easier to work with.

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

[Romain Lhotte]

Hi,

Yes, indeed I should have added the information about the issue that wasn't one, sorry for that!

I now understand how to properly copy formatting from one column to another, sorry it took that long to catch up, your solution was expandable to both the MRE and my initial workbook which is fantastic.

If you don't mind, can you confirm I got this right? :

column_dimensions is an attribute of a Worksheet object (https://openpyxl.readthedocs.io/en/latest/api/openpyxl.worksheet.worksheet.html) and returns a ColumnDimension object (https://openpyxl.readthedocs.io/en/latest/api/openpyxl.worksheet.dimensions.html?highlight=worksheet.column_dimensions#openpyxl.worksheet.dimensions.ColumnDimension)? And this ColumnDimension object holds all information about the format characteristics of a given column. Is this some kind of hidden attributes since I don't see it in the worksheet documentation page?

I'm sorry if I was a bit slow to understand I'm not an expert!

Sincerely

[Romain Lhotte]

I would also add that indeed this notion of "columns grouped" is un-intuitive especially if the user hasn't specified that they wanted to group the formatting of those columns. I would believe it is efficient in terms of storage though to only store formatting information about one column?

[Romain Lhotte]

Sorry for the third messages, but yes it's indeed something Excel does if you open the file with Excel. If you modify the file to apply the same formatting after ungrouping, the columns stay ungroup even if you save, then re-open the file with openpyxl. But if in the meantime you open it with Excel, Excel detects the format is the same for those two columns and regroups them automatically. It's easily fixable so no worries, but I think Excel does this automatically to "compress" the file?

[Charlie Clark]

On 14 Apr 2023, at 15:06, Romain Lhotte wrote:

> And this ColumnDimension object holds all information about the format
> characteristics of a given column. Is this some kind of hidden attributes
> since I don't see it in the worksheet documentation page?

All the relevant information is covered in the section on styles:

https://openpyxl.readthedocs.io/en/latest/styles.html#columns-and-rows

[Charlie Clark]

On 14 Apr 2023, at 15:08, Romain Lhotte wrote:

> I would also add that indeed this notion of "columns grouped" is
> un-intuitive especially if the user hasn't specified that they wanted to
> group the formatting of those columns. I would believe it is efficient in
> terms of storage though to only store formatting information about one
> column?

Why speculate like this?

The number of columns almost irrelevant given the number of cells, with a similar set of attributes, so this is not a reason. If storage space was relevant, XML wouldn't be used. And when you see how "wasteful" Excel is when it comes to saving styles, you get an even better idea of how little relevance this has.

No, the conflation is purely down to the history of the file format, and Microsoft's reluctance to take advantage of the opportunity when creating an open specification to move away from such an implementation-specific one. But it was easier for them to keep the internals as they were, and write the specification around them, than to work on something better that would require any adjustment to their codebase.