Grouping columns seems to reset column widths

Issue #412 invalid
Tom Wild created an issue

When I group columns using:

worksheet.column_dimensions.group(openpyxl.cell.get_column_letter(groupColumnsItem[0]), openpyxl.cell.get_column_letter(groupColumnsItem[1]), hidden=True)

It seems to reset the column widths that I set using:

worksheet.column_dimensions[openpyxl.cell.get_column_letter(colNum)].width = colWidth/7.0

It doesn't matter what order I do the steps in, the column widths are not set but If I don't group the columns then the column widths do get set

It doesn't matter whether the hidden parameter in the group function is True or False

Any thoughts would be appreciated

Tom

Comments (15)

  1. Tom Wild reporter

    I notice that I am also getting the error message (which doesn't stop the program): C:\Python27\lib\site-packages\openpyxl-2.1.3-py2.7.egg\openpyxl\workbook\names\named_range.py:121: UserWarning: Discarded range with reserved name This may or may not be relevant

  2. CharlieC

    The warning is just letting you know that some parts of the original file cannot be imported.

    If you group columns, you should expect previously assigned values to be lost. Please provide a more detail about what you're doing and what you expect to happen.

  3. Tom Wild reporter

    I am grouping columns so they can be hidden and then only displayed if the user clicks the little plus sign Before I group them I set the column widths to what I want them to be but when I group the columns to hide them the column width data seems to be getting lost

    I want this: This.jpg

    Not this: NotThis.jpg

  4. CharlieC

    When you group columns you create a single object to represent them all with a span (min, max) of the numerical indices. The existing objects will be destroyed and replaced by the new one so all attributes will be shared. The column width will need reassigning.

  5. Patrick Bergeron

    I am also seeing this, for the same reason. I even try to reassign the column widths, but no dice.

    This bug is marked as invalid, but I don't think this is an invalid bug.

    When MS Excel creates a group, it retains and remembers the column widths. They remain the same when you expand/close the group. I don't understand why openpyxl would have trouble dealing with this.

    For what it's worth, my use case is that I want to collapse many data columns that serves as the input to another column (the result column). I would like the user to be able to expand/collapse the data columns.

    Unfortunately at this time, I can't seem to be able to do this: - Grouping columns in openpyxl resets the previously-set columns widths - Re-assigning the column widths AFTER they have been grouped by openpyxl doesn't seem to work.

    In other words, I have the same problem as the original bug reporter.

  6. Patrick Bergeron

    Hi Charlie. Sorry -- what's a PR? Pull Request?

    I am afraid my Python skills really aren't up to the task for this.

    However, if I may ask, why is is it difficult to load up Excel, create a blank workbook set the first row with "Apples" "Oranges" "Bananas" "Mangos" "Pineapples" "Prunes" "Plums" etc, adjust the column width for column, and then group a few then save the file.

    Then, reproduce the same xlsx using openpyxl (eg: set the first row, then group the columns and save).

    Finally, compare the XML versions of the xlsx and determine how the differences are represented?

    On another note, I know grouping isn't supported for rows. Is there a reason why?

  7. Daniel OHara

    I have had the same issue and have a potential solution. If we add a width parameter to the group method it can be used to set the width in the <col> tag at the same time that we set the outlineLevel.

    So in dimension.py

        def group(self, start, end=None, outline_level=1, hidden=False, width=None):
            """allow grouping a range of consecutive rows or columns together
    
            :param start: first row or column to be grouped (mandatory)
            :param end: last row or column to be grouped (optional, default to start)
            :param outline_level: outline level
            :param hidden: should the group be hidden on workbook open or not
            :param width: width for column
            """
            if end is None:
                end = start
    
            if isinstance(self.default_factory(), ColumnDimension):
                new_dim = self[start]
                new_dim.outline_level = outline_level
                new_dim.hidden = hidden
                work_sequence = get_column_interval(start, end)[1:]
                for column_letter in work_sequence:
                    if column_letter in self:
                        del self[column_letter]
                new_dim.min, new_dim.max = map(column_index_from_string, (start, end))
                new_dim.width = width
            elif isinstance(self.default_factory(), RowDimension):
                for el in range(start, end + 1):
                    new_dim = self.worksheet.row_dimensions[el]
                    new_dim.outline_level = outline_level
                    new_dim.hidden = hidden
    

    This code illustrates how we could then set the width of grouped/folded columns:

    import openpyxl
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.column_dimensions.group('A', 'A')
    ws.column_dimensions.group('B', 'C', outline_level=2, width=30)
    ws.column_dimensions.group('D', 'D', outline_level=2, width=20)
    ws.column_dimensions.group('E', 'E')
    wb.save('openpyxlGroupTest.xlsx')
    
  8. CharlieC

    The specification is unfortunately completely unclear on how grouping and widths should be handled. I'm waiting for clarification from the OOXML working group and Microsoft on this before writing any code.

  9. Log in to comment