CODEWITHSUNDEEP
pythonmacosxlwings
YGA
YGA

Reputation: 10056

Using xlwings, is there a way to collapse all the groups on a sheet?

I'm using xlwings with python to copy one excel sheet to another. As part of the copying, however, I'd like to collapse all the grouped columns.

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; I just want all the existing ones to be collapsed.

Is there a simple command to do that? Cannot find it via google. (if the answer is to group specific ranges - the answer also would need to list how to find the ranges that are available for grouping!).

Edited to add: The solution must work on MacOS. The answer proposed by @grismar below fails on a Mac:

   (Pdb) sheet.api.Outline  
    *** AttributeError: Unknown property, element or command: 'Outline'

Upvotes: 2

Views: 90

Answers (2)

redoc
redoc

Reputation: 2459

I have avoided using sheet.api.Outline, but have not tested on mac which I don't have.
This code cycles through every column and if possible collapses with sheet.api.Columns(col).ShowDetail=False.

import xlwings as xw

file_path = "inp.xlsx"
output_file_path = "out.xlsx"

# Open the workbook
app = xw.App(visible=False)  # Open in the background
try:
    wb = app.books.open(file_path)
    sheet = wb.sheets[0]  # Get the first sheet



    # Collapse all grouped columns
    for col in range(1, sheet.api.UsedRange.Columns.Count + 1):
        # Check if the column has an outline level > 1
        if sheet.api.Columns(col).OutlineLevel > 1:
            try:
                sheet.api.Columns(col).ShowDetail = False  # Collapse the group
            except Exception as e:
                pass

    # Save the workbook
    wb.save(output_file_path)
    print(f"File saved as {output_file_path}")
finally:
    # Ensure Excel is closed
    wb.close()
    app.quit()

Upvotes: 0

Grismar
Grismar

Reputation: 31354

If you just want all groups collapsed, you can used this:

import xlwings as xw

book = xw.Book()
sheet = book.sheets[0]

for i in range(1, 6):
    sheet.cells(i+1, 1).value = chr(64 + i)
    for j in range(2, 6):
        sheet.cells(i+1, j).value = i * (j - 1)
    sheet.cells(1, i+1).value = chr(64 + i)

sheet.api.Columns("B:C").Group()
sheet.api.Columns("E:F").Group()

sheet.api.Outline.ShowLevels(ColumnLevels=1)

This creates a new sheet, fills it with 5 columns of simple data, creates two groups of columns, and then collapses them both.

Calling this next would expand them again:

sheet.api.Outline.ShowLevels(ColumnLevels=2)

If you're trying to collapse a specific grouped set of columns, you can do something like this:

for col in range(2, 4):  # Excel columns 2 (B) to 3 (C)
    sheet.api.Columns(col).Hidden = True

I know you said "I want them to be non-hidden, but just have the outlines collapsed to level 0!", but collapsing them is the same as setting them to hidden. You will see that the behaviour is exactly the same when you run the above.

Pressing the "+" button above the group has the same effect as:

for col in range(2, 4):
    sheet.api.Columns(col).Hidden = False

Edit: you asked about doing this on a Mac (which should have probably been included as a tag in the original question). I don't have a Mac, but this is what I can come up with, which might work. If it does not, you'll need someone with access to xlwings on a Mac to help out, or look at the relevant documentation.

# same up to this point:

sheet.range("B:C").api.Group()
sheet.range("E:F").api.Group()

Upvotes: 1

Related Questions