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

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

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

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

• excel
• grouping
• openpyxl

Share Improve this question Follow asked Mar 13 at 15:16 Rajagopal PolisettiRajagopal Polisetti 3544 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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}