1

Closed

Column.Delete does not update MergedCells reference

description

If you have merged cells A1:A5, and then delete column 3, the merged cell doesnt recognize that the merged column count should have changed. Meaning, the formatting continues to the new A5 (which was previously A6) - and whatever formatting was in A6 gets overwritten (or hidden).
Similarly, when you have cells A3:A5 merged, and you delete column 1, the merged cells are still A3:A5 rather than A2:A4. The data and formatting flows with the columns on the delete, but the Merge reference does not.
Closed Apr 25 at 8:39 PM by igitur
Please try the latest version of ClosedXML (v0.87.1 at this stage). If you still experience the issue, log it on the new GitHub page.

comments

MDeLeon wrote Jun 6, 2012 at 3:59 AM

The first one is a problem. The second one works as expected:

var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Sheet1");
ws.Range("C1:E1").Merge();
ws.Cell("B1").Value = "X";
ws.Cell("F1").Value = "X";
ws.FirstColumn().Delete();

standers wrote Jun 6, 2012 at 4:09 PM

My apologies. I must have some other references messed up on the second issue.
With regards to the first, I have updated your source that I downloaded (74918) with a fix that seems to work. If you want to take a look and let me know if there are fringe cases where this wont work, or if it could be optimized better for performance, I'd love to hear it!

This code is in XLRangeBase.Delete().
        //original code removes the MergedRange if it is completely contained within the range to be deleted
        var mergesToRemove = Worksheet.Internals.MergedRanges.Where(Contains).ToList();
        mergesToRemove.ForEach(r => Worksheet.Internals.MergedRanges.Remove(r));

        //****Update - need to update MergedRange if there is one that is not completely contained
        var mergeToUpdate = Worksheet.Internals.MergedRanges.FirstOrDefault(mr => Intersects(mr));
        if (mergeToUpdate != null)
        {
            int startRow = mergeToUpdate.FirstRow().RowNumber();
            int startCol = mergeToUpdate.FirstColumn().ColumnNumber();
            int endRow = mergeToUpdate.LastRow().RowNumber();
            int endCol = mergeToUpdate.LastColumn().ColumnNumber();

            if (shiftedRangeFormula.FirstColumn() == mergeToUpdate.FirstColumn())
                startCol += numberOfColumns;
            else if (endCol > startCol + numberOfColumns)
                endCol -= numberOfColumns;

            if (shiftedRangeFormula.FirstRow() == mergeToUpdate.FirstRow())
                startRow += numberOfRows;
            else if (endRow > startRow + numberOfRows)
                endRow -= numberOfRows;

            XLRange newMerge = Worksheet.Range(startRow, startCol, endRow, endCol);
            Worksheet.Internals.MergedRanges.Remove(mergeToUpdate);
            Worksheet.Internals.MergedRanges.Add(newMerge);
        }
        //****End Update

standers wrote Jun 6, 2012 at 4:12 PM

the update code is obviously in addition to the original code, not in place of it. :)

standers wrote Jun 7, 2012 at 3:37 PM

FWIW - the reason I was seeing the second issue was b/c I had disabled Event tracking in the constructor (I saw that recommended somewhere for improved performance).
Makes sense now, but was hard to identify the cause.

MDeLeon wrote Jun 7, 2012 at 8:00 PM

Now you're confusing me because the following doesn't work for me (the first issue)

var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Sheet1");
ws.Range("C1:E1").Merge();
ws.Cell("B1").Value = "X";
ws.Cell("F1").Value = "X";
ws.Column("D").Delete();

MDeLeon wrote Jun 7, 2012 at 8:02 PM

lol, my bad, you were talking about the second issue, not the first :)

Sorry.

kkvid007 wrote Jul 28, 2012 at 8:59 PM

Neither of them work for me.. :( :-(