Silently grouped columns openpyxl
I might have missed something in #1988 (closed) that I tried to re-read (and the documentation that got updated) since I'm not too familiar with how openpyxl works "in the backend".
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 problem is 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. The width is fixable (cf. code) but not the background colour.
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")
Python 3.11.1
, openpyxl == 3.1.2