CODEWITHSUNDEEP
pythonexcelopenpyxl
YGA
YGA

Reputation: 10056

With openpyxl, how do I collapse all the existing groups in the resultant excel?

I am using openpyxl with a worksheet that has a lot of grouped columns at different levels. I would like the resultant output to simply collapse all the groups.

This is different than hiding the relevant columns and rows; I want them to be non-hidden, but just have the outlines collapsed to level 0!

And to be clear, I also don't want to create new outlines (as in @moken's answer below); I just want all the existing ones to be collapsed.

Is this possible?

Upvotes: 0

Views: 117

Answers (1)

redoc
redoc

Reputation: 2459

I think you are expecting this:

from openpyxl import load_workbook

file_path = "inp.xlsx"
workbook = load_workbook(file_path)
sheet = workbook.active  

# Collapse all existing row groups
for group in sheet.row_dimensions:
    if hasattr(sheet.row_dimensions[group], "outlineLevel") and sheet.row_dimensions[group].outlineLevel > 0:
        sheet.row_dimensions[group].hidden = True  # Collapse the group

# Collapse all existing column groups
for group in sheet.column_dimensions:
    if hasattr(sheet.column_dimensions[group], "outlineLevel") and sheet.column_dimensions[group].outlineLevel > 0:
        sheet.column_dimensions[group].hidden = True  # Collapse the group


output_file_path = "out.xlsx"  
workbook.save(output_file_path)
print(f"File saved as {output_file_path}")

This searches an collapses all existing groups than creating new

Upvotes: 1

Related Questions