Styling Merged Cells isn't working

Issue #365 resolved
Brent O'Connor
created an issue

I'm having an issue when trying to style cells that have been merged. I'm trying to add a thin border to a merge cell with the following code.

workbook = Workbook()
ws = workbook.active
ws.merge_cells('B2:F2')
my_cell = ws.cell('B2')
my_cell.value = "My Cell"
my_cell.style = Style(font=Font(bold=True),
                      border=Border(top=Side(border_style='thin', color=colors.BLACK),
                                    left=Side(border_style='thin', color=colors.BLACK),
                                    bottom=Side(border_style='thin', color=colors.BLACK),
                                    right=Side(border_style='thin', color=colors.BLACK),),
                      alignment=Alignment(horizontal='center'))

However it ends up looking like this ...

merge_cell_example.png

When it should look like this ...

merge_cell_example2.png

Comments (85)

  1. CharlieC

    Looking at the way Excel does this is not very promising. It uses three styles: for B2 (open on the right); for C2:E2 (open on both sides); and for F2 (open on the left).

    The code for this would look something like this:

    left = border.copy(right=None)
    right = border.copy(left=Side(border_style=None))
    middle = border.copy(left=Side(border_style=None), right=Side(border_style=None))
    st = ws['b2'].style
    ws['b2'].style = st.copy(border=left)
    ws['c2'].style = st.copy(border=middle)
    ws['d2'].style = st.copy(border=middle)
    ws['e2'].style = st.copy(border=middle)
    ws['f2'].style = st.copy(border=right)
    

    But as things stand we don't create cells within a merged area because they have no content.

    I would accept a pull request for this but it would have to manage keeping the cells empty.

  2. Brent O'Connor reporter

    Thanks for the feedback. So my best bet is to make a helper function for styling a range of cells? A helper function like that might be a good thing to put into openpyxl.

  3. CharlieC

    Not sure if a single helper function is possible but if you think you can come up with one feel free to make a suggestion,

    I think the bigger problem is that Excel relies on creating dummy cells for this to work. The problem with this is that merged cells cannot have any content so they are usually deleted. This needs to be handled carefully otherwise any code working with the sheet could get confused: you could merge some cells, implicitly recreate them by applying some formatting, assign values to them by mistake and think the values will survive.

  4. Brent O'Connor reporter

    With the following ...

    def style_range(ws, cell_range, style=None):
        """
        :param ws:  Excel worksheet instance
        :param range: An excel range to style (e.g. A1:F20)
        :param style: An openpyxl Style object
        """
    
        start_cell, end_cell = cell_range.split(':')
        start_coord = coordinate_from_string(start_cell)
        start_row = start_coord[1]
        start_col = column_index_from_string(start_coord[0])
        end_coord = coordinate_from_string(end_cell)
        end_row = end_coord[1]
        end_col = column_index_from_string(end_coord[0])
    
        for row in range(start_row, end_row + 1):
            for col_idx in range(start_col, end_col + 1):
                col = get_column_letter(col_idx)
                ws.cell('%s%s' % (col, row)).style = style
    
    workbook = Workbook()
    ws = workbook.active
    ws.merge_cells('B2:F2')
    my_cell = ws.cell('B2')
    my_cell.value = "My Cell"
    style_range(ws, 'B2:F2', Style(alignment=Alignment(horizontal='center'),
                                   border=Border(top=Side(border_style='thin', color=colors.BLACK),
                                                 left=Side(border_style='thin', color=colors.BLACK),
                                                 bottom=Side(border_style='thin', color=colors.BLACK),
                                                 right=Side(border_style='thin', color=colors.BLACK), )),)
    

    I got ...

    cell_example.jpg

  5. CharlieC

    cells_from_range() already does most of the work for that. You don't really another function beyond that.

    But there's still a problem that the cells are created and, therefore, can be assigned and read values which could lead to problems.

  6. Brent O'Connor reporter

    I don't understand openpyxl at the lower level yet, so I'm not clear on what issues could arise. For my use-case it seems to be working well at the moment. I would love a better solution if you had one. Thank you for cells_from_rannge(), however I searched the code and I'm not finding it.

  7. CharlieC

    It's great that it's working for you I was just trying to point out some of the potential issues. What about if you give one of the formatted cells a fill?

    It's worth noting that the formatting will be probably stripped from such a file.

    A more robust solution might copy the model of shared-formulae.

  8. Sergey Pikhovkin

    To solve this problem it is necessary to comment out code into Worksheet.merge_cells method in openpyxl/worksheet/worksheet.py

    cells = cells_from_range(range_string)
    # only the top-left cell is preserved
    for c in islice(chain.from_iterable(cells), 1, None):
        if c in self._cells:
            del self._cells[c]
    

    Styles are not displayed, because no cells, which are attached styles.

  9. Sergey Pikhovkin

    Your code will not work, because openpyxl loses the style of the last cell in the merged range, i.e. the right border of the last cell will not be traced because right border is not present at the first cell in the merged range.

  10. Sergey Pikhovkin

    I think that the programmer himself must follow the styles merged cells when interacting with them. As an example, this is done with inserts rows - the programmer himself tracks where and what has changed. Just openpyxl shall not remove cells.

  11. Guillaume RYCKELYNCK

    Maybe a temporary solution to work with border on merged cells : define border on neighboring cells. I merge C2:F2 and define border top on C3:F3, border right on B2 and so on. I simply use it to get XLSX file (template), fill in with values and save it as new XLSX file. I don't know if it's the good place for this but I hope that can help!

  12. Franco Solleza

    I'm going to raise this again and ask - so it seems like as demonstrated, a temporary fix is to prevent cells from being deleted in openpyxl/worksheet/worksheet.py. However, deleting them also seems a little aggressive? Is it possible to change the accessibility of those cells? For example, add a method to openpyxl/cell/cell.py that says that if cell is not accessible, a setter won't set a value?

    Alternatively, I've tried the following:

    sheet.merge_cells("A1:E1")
    sheet.cell("A1").value = "Test"
    sheet.cell("D1").value = "Test2"  # setting a value in the range of a merge cell
    

    And excel (2013) seems to handle it fine, by not displaying "Test2" in cell D1. When I UNMERGE the merge cell, "Test2" shows up in the appropriate cell. Any thoughts on this or does this help in a potential implementation fix? I'm guessing that based on what we've seen above, this is all expected behavior?

  13. CharlieC

    We're still waiting for an explanation on what should be expected from Microsoft. Until then deleting merged cells is the only way to avoid ambiguity.

  14. CharlieC

    Yes, sorry about that. Please feel free to submit another bug report to the specification committee (no need to mention openpyxl).

    The specification currently says is this:

    § 18.3.1.55
    
    This example shows that three ranges are merged. The formatting and content for the merged range is always stored in the top left cell.
    

    This is how openpyxl behaves.

  15. kseehart

    This is not resolved. I'm a bit frustrated because there are a dozen or so valid bugs marked as duplicates, indicating that people care about this issue. Several of the "duplicates" describe a situation that is more unambiguously a real bug, such as 533 and 700.

    In one sense, I'd agree that they are duplicates in the sense that they are symptoms of the same defect, and would be repaired by the same patch. But the workaround that serves as the resolution of this bug does not resolve the supposed duplicates, where the damage to the styles is incurred passively when the file is loaded, so there really isn't a reasonable place to code the workaround. Because of this, the combination of marking bugs such as 533 and 700 as duplicates and closing this bug as "resolved" is problematic.

    In other words, that workaround tells how to style merged cells, but not how to prevent the destruction of existing formatting.

    To reproduce the more unambiguous manifestation of this bug, see 533 or 700.

    • Use Excel to make a spreadsheet with a merged set of cells with a border around it.
    • Write a short python script to load and immediately save the spreadsheet.
    • Note that most of the border is destroyed.

    I consider it axiomatic that if you load a spreadsheet and then save it, and the result is different, that is a bug. Therefore, "Won't fix" is more appropriate than "Resolved".

    Better yet, how about applying a perfectly good fix that was proposed, but previously rejected. I credit Sergey Pikhovkin with the fix.

    To solve this problem it is necessary to comment out code into Worksheet.merge_cells method in openpyxl/worksheet/worksheet.py
    cells = cells_from_range(range_string)
    # only the top-left cell is preserved
    for c in islice(chain.from_iterable(cells), 1, None):
        if c in self._cells:
            del self._cells[c]
    Styles are not displayed, because no cells, which are attached styles.
    

    Yes, I have read all the commentary, and I get that there is a motivation for those three lines of code. However the motivation appears to be prophylactic, in that it defends against a possible user error (e.g. expecting to be able to make use of "nonexistent" cells. I ask you to consider the notion that an actual bug has precedence over a hypothetical user error.

    In the meantime, I offer this monkey-patch solution to the community. This is what I am currently using, and it works great. But it would be nicer if Sergey's fix were adopted into the actual code base.

    from itertools import product
    import types
    import openpyxl
    from openpyxl import worksheet
    from openpyxl.utils import range_boundaries
    
    
    def patch_worksheet():
        """This monkeypatches Worksheet.merge_cells to remove cell deletion bug
        https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
        Thank you to Sergey Pikhovkin for the fix
        """
    
        def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
            """ Set merge on a cell range.  Range is a cell range (e.g. A1:E1)
            This is monkeypatched to remove cell deletion bug
            https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
            """
            if not range_string and not all((start_row, start_column, end_row, end_column)):
                msg = "You have to provide a value either for 'coordinate' or for\
                'start_row', 'start_column', 'end_row' *and* 'end_column'"
                raise ValueError(msg)
            elif not range_string:
                range_string = '%s%s:%s%s' % (get_column_letter(start_column),
                                              start_row,
                                              get_column_letter(end_column),
                                              end_row)
            elif ":" not in range_string:
                if COORD_RE.match(range_string):
                    return  # Single cell, do nothing
                raise ValueError("Range must be a cell range (e.g. A1:E1)")
            else:
                range_string = range_string.replace('$', '')
    
            if range_string not in self._merged_cells:
                self._merged_cells.append(range_string)
    
    
            # The following is removed by this monkeypatch:
    
            # min_col, min_row, max_col, max_row = range_boundaries(range_string)
            # rows = range(min_row, max_row+1)
            # cols = range(min_col, max_col+1)
            # cells = product(rows, cols)
    
            # all but the top-left cell are removed
            #for c in islice(cells, 1, None):
                #if c in self._cells:
                    #del self._cells[c]
    
        # Apply monkey patch
        m = types.MethodType(merge_cells, None, worksheet.Worksheet)
        worksheet.Worksheet.merge_cells = m
    
    
    
    patch_worksheet()
    

    Here's my philosophical justification for Sergey's fix:

    The three lines of code in question are based on a particular interpretation of the model, where the act of merging cells includes the implicit destruction of the underlying cells (as opposed to erasing the content of the cells). Thus the lines are apparently justified because it would seem to be logically incorrect for the model to include cells that "don't exist". Doing things with nonexistent cells could yield unexpected results.

    An alternative interpretation of the model, is to consider all of the cells to be present in the model, but hidden "behind" the upper-left cell, which is expanded to occupy the merged region. Note that borders of the hidden cells that line up with the borders of the merged region are visible (this is why borders of merged cells are destroyed by the current version of openpyxl).

    The latter model has the virtue of being the actual model implemented by Excel. It is an afterthought that Excel happens to implement some additional logic to erase the hidden values and formatting, and that the user interface does not provide access to the hidden cells. Nevertheless, although the values and most of the formatting are erased, the hidden cells remain part of the model, and indeed are used for the purpose of border formatting.

    Despite my frustration around this particular issue, I'd like to express my gratitude for a really awesome tool, without which my work would be much more difficult. Thank you!

  16. kseehart

    Addendum:

    Actually, to simulate Excel behavior, an improvement would be the following: - erase values of the merged cells (except the top left) - copy non-border formatting from the upper-left cell to the range of merged cells - "merge" the border formatting across merged cells.

    That last one may be a little more complex, but there are a couple clear constraints: - For each side, all of the exposed borders should match. - Non-exposed borders should be cleared.

    This would be an improvement on my patch. Maybe if I have time I'll make a better patch, but I'm pretty sure the above suggestion is an improvement over the current situation.

  17. kseehart

    Charlie Clark wrote: Sergey Pikhovkin there is quite obviously a problem with the specification which is why we've raised an issue with the ECMA Committee. openpyxl does not remove cells that have already been merged: they don't exist. 2015-01-23

    Sorry, but that is just not true. Take a closer look. The merged cells do exist (in the Excel reference implementation), and openpyxl does indeed remove them (unless Sergey's patch is applied). See https://groups.google.com/forum/#!topic/openpyxl-users/6G7lLDJsy8E for details and empirical evidence.

    Not only do these cells exist, but they all have the capacity to contain values and arbitrary formatting, even though under normal circumstances all but the top-left will have a value of None. Such constraints are a matter of user interface and business logic, not the model.

  18. db2053

    For openxlpy 2.5, patch_worksheet need some adaptations :

    self._merged_cells => self.merged_cells

    self._merged_cells.append => self._merged_cells.add

    new code version :

    def patch_worksheet():
        """This monkeypatches Worksheet.merge_cells to remove cell deletion bug
        https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
        Thank you to Sergey Pikhovkin for the fix
        """
    
        def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
            """ Set merge on a cell range.  Range is a cell range (e.g. A1:E1)
            This is monkeypatched to remove cell deletion bug
            https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
            """
            if not range_string and not all((start_row, start_column, end_row, end_column)):
                msg = "You have to provide a value either for 'coordinate' or for\
                'start_row', 'start_column', 'end_row' *and* 'end_column'"
                raise ValueError(msg)
            elif not range_string:
                range_string = '%s%s:%s%s' % (get_column_letter(start_column),
                                              start_row,
                                              get_column_letter(end_column),
                                              end_row)
            elif ":" not in range_string:
                if COORD_RE.match(range_string):
                    return  # Single cell, do nothing
                raise ValueError("Range must be a cell range (e.g. A1:E1)")
            else:
                range_string = range_string.replace('$', '')
    
            if range_string not in self.merged_cells:
                self.merged_cells.add(range_string)
    
    
            # The following is removed by this monkeypatch:
    
            # min_col, min_row, max_col, max_row = range_boundaries(range_string)
            # rows = range(min_row, max_row+1)
            # cols = range(min_col, max_col+1)
            # cells = product(rows, cols)
    
            # all but the top-left cell are removed
            #for c in islice(cells, 1, None):
                #if c in self._cells:
                    #del self._cells[c]
    
        # Apply monkey patch
        worksheet.Worksheet.merge_cells = merge_cells
    
    patch_worksheet()
    
  19. David Wheeler

    It would be good to know if this fix will cover my use case, which is updating cell values (only - not the formatting) in a worksheet that is already styled. This is as opposed to explicitly styling merged cells.

  20. David Wheeler

    I checked out 2.6 and found (in my use case at any rate) it visually corrects the formatting of the merged cells, but it does it quite differently to the way Microsoft is doing it (or would have done it) under the hood. I would recommend taking a look at that because if openpyxl is doing things differently to Microsoft it may cause similar kinds of issues in the future. To analyse this I did the following: 1. create a "genuine" Excel spreadsheet with merged cells 2. unzip that and examine the XML 3. update that spreadsheet using openpyxl 2.5.4 - not doing ANY formatting, but modifying the values of cells including the cell in the merged range. The use case is a language translation script, and there is not need to change the formatting. 4. unzip and examine XML 5. repeat for openpyxl 2.6 I have a document that shows the results. I can't see how to upload that for your review.

  21. CharlieC

    You should be able to attach files to the issue. Thanks for the comparison. I've seen Excel files. The problem is that Excel's behavious is both undefined and inefficient. In terms of the implementation, it is the lack of detail in the specification that causes problems: simply reverse engineering is not an option.

    Opening and saving Excel files invariably leads to recombination of data so that the structure of files quickly diverges between implementations Where possible, openpyxl implements optimisations to reduce redundancy, especially in styles.

  22. David Wheeler

    Understand your comments. Microsoft is late to the open source party, and publishing specs would not be in their DNA.

    Having said that, it seems that PR #261 does not address the <worksheet> <dimension> "ref" attribute not capturing empty cells in a merged cell range that extends beyond the cells that have data in. That seems like it would be easy enough to fix.

    PR #261 allows the cells in the merged cell range that were being deleted in the old code to continue to exist, so that works. What it goes on to do is to enforce formatting of those cells when they were already formatted in the input file. That may not have been necessary because the formatting was being lost as a result of the deletion of the cells.

  23. CharlieC

    The dimensions are a moot point. This is an optional "optimisation" in the format that turns out to be more or less useless and is, therefore ignored by all streaming producers. In standard mode openpyxl always knows the dimensions. As the extra cells are only used to hold the formatting they're not relevant in read-only mode. For openpyxl the important thing is that the cells are instances of MergedCell in case anything tries to read them: they're an artefact of formatting and not really worksheet contents.

    If you think changes need making then I'd prefer to continue the discussion on the basis of a concrete PR.

  24. David

    This patch is not working for me, surely I'm doing anything wrong, but I don't know what. I'm using 2.6 version.

    I have this same issue of styling merged cells, but in my case I load an existing worksheet and after saving I lose the styles of merged cells.

    I apply this patch just after importing openpyxl, but again, merced cells still appear with the style stored in top-left cell.

    Do you know if I'm forgetting anything?

    Thanks, David

  25. David Wheeler

    Hi David,

    Not sure if it is an option for you, but you may want to look back at my original PR #272. This is a very minor change to the library which causes it NOT to delete the other cells in the merged cell range in the first place. You will find they are still there when you save the file.

    I understand where the openpyxl team is coming from with the reluctance to rush the more philosophically correct change that will come in 2.6 into the production code. I completely agree that the approach this team has taken to storing merged cells is the way to go, and Microsoft's method is clunky. Indeed the openpyxl method should become part of the OpenOffice standards.

    Having said that, it is a pain in the meantime because most people use Excel, one way or another. You can try deleting those two lines as I proposed in PR #272 and see if it works for you. It did for me.

    David

  26. CharlieC

    I think the problem is people who cannot either monkey patch or work with a checkout.

    @David Wheeler actually ODF, the OpenOffice specification does things differently and uses and explicit overlay for a range of merged cells. This is fine because affects the UI only.

  27. Simon

    Here's a monkey patch working with Python 3:

    def patch_worksheet():
        """This monkeypatches Worksheet.merge_cells to remove cell deletion bug
        https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
        """
        # apply patch 1
    
        def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
            cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row,
                          max_col=end_column, max_row=end_row)
            self.merged_cells.add(cr.coord)
            # self._clean_merge_range(cr)
    
        Worksheet.merge_cells = merge_cells
    
    
        # apply patch 2
    
        def parse_merge(self, element):
            merged = MergeCells.from_tree(element)
            self.ws.merged_cells.ranges = merged.mergeCell
            # for cr in merged.mergeCell:
            #     self.ws._clean_merge_range(cr)
    
        WorkSheetParser.parse_merge = parse_merge
    
  28. Philip Curtis

    Hello,

    I'm working on an 11-page SS and I'm having the 'merged cell-loss of border' issue. I've applied the 'official' patch located here without success, still losing the borders. I believe I had the code correct because I am getting the gradientFill, alignment, etc.

    One consistent condition I've noticed is the problem only appears when bringing in multiple pages Then the 'merged cell borders' are lost on all pages except the last page. I want to emphasize this point because I have repeated this experiment many times with always the same results.

    Even using two pages leaves the 1st page without 'merged cell borders' and the 2nd page renders perfectly. And here is something else interesting: If I remove the last page and render only the page which had the 'merged cell borders' error, that same page renders perfectly with all borders. And I've repeated all this multiple times with different pages and same results. This tells me each page is coded correctly but something else is going on behind the scenes in the code which I am missing.

    Does this help at all or any ideas on what I could try? I'm very active working on this SS and I'm open to suggestions.

    Thanks, Phil

  29. David Wheeler

    Hi Philip,

    The easiest fix if you have an editable copy of the library (pip -e) is to implement the change I proposed in PR #272. This is where the damage is done, and by deleting or commenting out those two lines all will be well with your spreadsheet.

    I am not aware of any side-effects of that change.

    Best regards, David

  30. Philip Curtis

    It looks like that's working !! I have page 01 and page 11 rendering perfectly with pages 2-10 also imported in but no data yet. I'd like to follow the thread on what these two lines are doing exactly, especially considering Charlies reaction.

    253         # for cr in merged.mergeCell:
    254             # self.ws._clean_merge_range(cr)
    

    Tonight, however, I need to make some headway on this project I am behind on <;

    Thank you,

    Phil

  31. David Wheeler

    I'll let Charlie explain why this is not an acceptable approach. My understanding is that having the merged cells lurking in the data prior to saving and exiting could impact (slow down?) people with monster spreadsheets who just wanted to crunch numbers and crank out reports. I don't fully understand it.

    For people like me, and presumably you and others who have commented on this discussion over the years (yes, years - it recently had its 4th birthday), it is desirable to have an Excel template with elegant formatting that you can populate with data that was collected from somewhere. In my case it cropped up when I was working on a language translator - see here.

    There are a number of fixes around that deal with the issue one way or another, but for the application you and I have the simplest is to allow the merged cells to continue to exist after reading the file, which is what PR #272 does.

    If someone had all the time in the world they could study the ODF and perhaps come up with a PR that would satisfy the various constituencies.

    Best regards, David

  32. Marin Jurjević

    I've had some problems with imports in 2.5.12 version, so I am putting correct imports with Simon's code. Besides that patch works smoothly :)

    from openpyxl.worksheet import Worksheet
    from openpyxl.reader.worksheet import WorkSheetParser
    from openpyxl.worksheet.merge import MergeCells
    from openpyxl.worksheet.cell_range import CellRange
    from openpyxl.utils import range_boundaries
    
    def patch_worksheet():
        """This monkeypatches Worksheet.merge_cells to remove cell deletion bug
        https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
        """
        # apply patch 1
    
        def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
            cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row,
                          max_col=end_column, max_row=end_row)
            self.merged_cells.add(cr.coord)
            # self._clean_merge_range(cr)
    
        Worksheet.merge_cells = merge_cells
    
    
        # apply patch 2
    
        def parse_merge(self, element):
            merged = MergeCells.from_tree(element)
            self.ws.merged_cells.ranges = merged.mergeCell
            # for cr in merged.mergeCell:
            #     self.ws._clean_merge_range(cr)
    
        WorkSheetParser.parse_merge = parse_merge
    
  33. kiran kotari

    I had a problem on updating the existing excel which contains merged cell, on save style got screwed

    I believe the solution given for new merge entries not for existing

    I following code solved my problem:- called just before the save obj.update_borders(workbook)

        def get_border(self, border_style=None, color=None):
            return Border(left=Side(border_style=border_style, color=color),
                                     right=Side(border_style=border_style, color=color),
                                     top=Side(border_style=border_style, color=color),
                                     bottom=Side(border_style=border_style, color=color), )
    
        def update_style(self, ws, cell_range):
            start_cell, end_cell = cell_range.split(':')
            start_coord = coordinate_from_string(start_cell)
            end_coord = coordinate_from_string(end_cell)
    
            start_row = start_coord[1]
            end_row = end_coord[1]
    
            start_col = column_index_from_string(start_coord[0])
            end_col = column_index_from_string(end_coord[0])
    
            border_style = ws.cell(row=start_row, column=start_col).border.left.style
            color = ws.cell(row=start_row, column=start_col).border.left.color
    
            cellborder = self.get_border(border_style, color)
    
            for row in range(start_row, end_row + 1):
                for col in range(start_col, end_col + 1):
                    ws.cell(row=row, column=col).border = cellborder
    
        def update_borders(self, wb):
            for sheet in wb.sheetnames:
                ws = wb[sheet]
                for each_range in ws.merged_cell_ranges:
                    self.update_style(ws, each_range)
    
  34. kseehart

    Yup, this is a very old and annoying problem, and there is a perfectly good fix. This has been going on for four years.

    Sadly, Charlie is not willing to apply the patch to the main branch, and nobody else has the time to manage a fork. My understanding is that Charlie has a technical objection to perceived design flaw in the way Excel redundantly represents the style data in the presence of merged cells, and his code actively deletes the redundancy even though the end result is functionally destructive.

    The patch, which is described earlier in this thread, involves removing a few lines that Charlie added for the purpose of removing the redundant cells. Those lines that the patch removes have no benefit to functionality whatsoever, other than slight memory and speed improvement that are unlikely to amount to anything close to 1% performance improvement in real situations. So it's really a philosophical issue, not a practical one. What matters to Charlie is that the Excel design is bad, so being compatible with that design is simply not an option.

    Also, I don't really get why this ticket is "resolved". "Won't Fix" would be more honest. Charlies proposed workaround technically solves this particular ticket, but does not solve the underlying problem and does not resolve the numerous supposed "duplicates" of this ticket. The duplicates represent a generalization on this ticket. In particular, simply loading and then saving a spreadsheet that contains formatted merged cells will destroy the formatting (borders in particular), so Charlies workaround does not apply.

    BTW, I have a different take on the interpretation that the so-called dummy cells are redundant. I think of the merger of cells as hiding some of the formatting of cells that are "underneath" the merged cells. So the cells that are merged still exist, but are hidden. One can argue about whether this design is bad, but as I see it, what matters is that the Excel design is logically internally consistent and predictable even though they don't conform to a particular interpretation. And this model is also followed by OpenOffice.

    In summary: Charlie doesn't like the idea of the hidden cells, so he actively deletes them. The fix is to remove the lines of code that delete the hidden cells. If working code is more important than philosophical objection, this patch should be applied to the trunk.

    If everyone disagrees with you, even if you don't like it, it's a good time to take a step back and take another look. I also find it really hard to do this sometimes, but when I do take a step back, I like the results. It's a win for everyone. What do you have to lose?

    Regards, Ken

  35. Ken Seehart

    No kidding. The openpyxl.load_workbook().save() point has been made in many of the supposed "duplicates" of this ticket (this particular ticket is sort of "resolved" by the Charlie's workaround, but the "duplicates" are certainly not, as many have pointed out, so these "duplicate" should be more accurately marked as "won't fix"). I wish I had more time for this. If I did, I'd go ahead and fork the project.

    In any case, CharlieC has some valid philosophical concerns about the proposed fix. Before I proceed, I would want to know if these concerns actually affect anyone. In other words, are there any actual or hypothetical use cases in which the proposed fix would yield undesirable results that are visible to an end user? I'm not aware of any such use cases, but I'm open to the possibility that CharlieC has some understanding that I am lacking, since after all, I didn't write the code. But as far as I know, Charlie's concern might just be the presence of redundant data in the model due to poor design my Microsoft. I don't disagree with that assessment, but I'm more interested in working code.

  36. Riley Clement

    I've been reading about this issue all day, and I get the impression it was fixed in 2.6:

    • 2.6-a1 (2018-11-21) Major changes

      Implement robust for merged cells so that these can be formatted the way Excel does without confusion. Thanks to Magnus Schieder.

    I'm trying to get the "Styling Merged Cells" example on https://openpyxl.readthedocs.io/en/stable/styles.html working, with Python 3.6.8 and openpyxl 2.6.2, but I it doesn't work for me - only the top left cell in the merged cell range gets the border, the rest of the cells in the range do not.

    Is it supposed to be working? I've also tried implementing the various monkey patches that have been suggested (and verified that the merge_cells method is being overridden) but to no avail.

  37. Log in to comment