excelgroupingopenpyxl

openpyxl: (column) grouping - outline level not working & retaining only last one


I am trying to group columns at multiple levels and in this process only the last outline level is retained, though the outline number area in spreadhseet shows all outlines. I referred to this past post and it worked initially and it stopped working for the below code:

Here is my code:

from openpyxl.utils.cell import get_column_letter

def group_xlsx(output_worksheet):

    start_row=1 #start row
    start_column=2 #start column
    total_columns=48 #total columns
    columns_number=3 #columns in each group
    
    #outer group
    first_column=get_column_letter(start_column)
    last_column=get_column_letter(total_columns+2)
    output_worksheet.column_dimensions.group(first_column,last_column,hidden=True,outline_level=1)
    
    print('Outer group columns are {} & {}'.format(first_column,last_column))

    #inner group
    for index in range(start_column,total_columns+1,columns_number+1):
        first_column=get_column_letter(index)
        last_column=get_column_letter(index+2)
        output_worksheet.column_dimensions.group(first_column,last_column,hidden=True,outline_level=2)

    print('Last inner group columns are {} & {}'.format(first_column,last_column))

    return

This is how it looks in spreadheet

enter image description here

Not sure where it is going wrong. Any suggestions are greatly appreciated.


Solution

  • With the way it is formatted in Excel, you'd want to do something like the following to achieve what I believe you are attempting;

    from openpyxl.utils.cell import get_column_letter
    import openpyxl
    
    
    def group_xlsx(output_worksheet):
        
        # outline_level 1
        for i in range(5, 50, 4):
            output_worksheet.column_dimensions.group(get_column_letter(i), hidden=True, outline_level=1)
            print(f"Setting group for separation column: {get_column_letter(i)}")
    
        # outline_level 2
        for j in range(2, 50, 4):
            output_worksheet.column_dimensions.group(get_column_letter(j), get_column_letter(j+2), hidden=True, outline_level=2)
            print(f"Setting group for {get_column_letter(j)}:{get_column_letter(j+2)}")
    
        return
    
    filename = 'grouping.xlsx'
    
    # Create Excel
    wb = openpyxl.Workbook()
    ws = wb.active
    
    # Call function
    group_xlsx(ws)
    
    # Save workbook
    wb.save(filename)
    

    The Sheet should look like the following if I read what your trying to do in your code correctly.
    Obviously with the groups expanded

    Expected Output