Missing border edges on different cells on open and save existing excel file

Issue #378 duplicate
Sebastian Kobylarz
created an issue

After opening, editing and saving an existing workbook, some none-edited cell miss some of their border lines. An issue that has been seen with merged cells and cells with e.g bold style assigned but with no value.

See attached image for an example of the described issue.

Comments (17)

  1. Tim Nicholson

    I used the following code with openpyxl 2.1.2

    from openpyxl import load_workbook
    
    wb = load_workbook('old.xlsx')
    print wb.get_sheet_names()
    wb.save('new.xlsx')
    

    The 'new' file has lost the borders for all merged cells. This worked on version 2.0.5 of openpyxl.

  2. Steven Klass

    Charlie,

    I'm not sure this is a duplicate of #348 as that was closed back in July and this was reported in less than a week ago. You mention it can't be solved without a hack - can you share what that might look like?

    Thanks!!

  3. CharlieC

    It is a duplicate because it's about merged cells. The hack is to create some dummy cells and format them as edges for a box. It's a hack because any style applied to any of the dummy cells will be visible in the generated file.

    For a general solution for roundtripping I'm waiting for clarification from ECMA as to what is supposed to happen. I suspect we'll have to add a MergedCell which can have some formatting but no values, and maybe a function for managing the formatting for a range of MergedCells.

  4. Eric Amorde

    I stumbled across this issue as I was in the process of creating a new issue.

    Right now, to preserve existing merged cell properties I set the style of every cell in the merged range to what it is supposed to be.. Which means I'm recreating the cells that were deleted when they were merged and I'm also hard-coding styles that are supposed to be in the spreadsheet I'm reading in. Although that is not ideal, I'd rather use more memory to get the desired result. What do you think?

    Here's a rough implementation of what I mean:

    # ... at the end of worksheet.merge_cells
    # ...
        first_cell = range_string.split(':')[0]
        style = self[first_cell].style
        # only the top-left cell is preserved
        for c in islice(chain.from_iterable(cells), 1, None):
            if c in self._cells:
                self[c].style = style # instead of deleting the cell, add the top-left-most style to it
                # del self._cells[c]
    

    I've tested this code and it works, but I'm sure I'm missing something and that some improvements could be made.

  5. CharlieC

    @Eric Amorde the problem with that solution is that it won't work with borders, where you basically need undocumented black magic.

    The general problem with anything that creates cells is what to do with the value? I've thought about creating a special MergedCell type which has no value but can be styled. But I'm going to wait for clarification from the ECMA group responsible for the specification.

  6. Eric Amorde

    Interesting, I only tested with a top border to see if that worked and it does, but it doesn't catch the other sides correctly. My bad.

    I guess I'll be waiting as well. For now I'll just keep doing what I've been doing.

  7. kseehart

    "The general problem with anything that creates cells is what to do with the value?"

    Why is that a problem? What happens if you allow the cells to have values, and just ignore them? To be clean, you would need to specify in the docs that this is not advised, and that assigning values to merged cells (other than the upper left) will have "undefined behavior". Such documented ambiguity has loads of precedent, and really is not so horrible in practice. At least I would say a lesser evil than load+save being lossy.

    Am I missing something? (a sincere question; I am completely willing to learn something that I am overlooking).

  8. Log in to comment