Deleting rows breaks merged cells

Question

I'm delete some row, and address of merged cell after this row is wrong. For example, there are merged cells "A78:C78". If I do

worksheet.DeleteRow(5)

Address of

worksheet.Cells[77,1]

is "A77". But

worksheet.MergedCells[77,1]

is null, but it should be "A77:C77".

Address of

worksheet.Cells[78,1]

is "A78". But

worksheet.MergedCells[78,1]

is "A77:C77", but it should be null. What's interesting and strange, there are merged cells "A77:C77" in the saved document in *.xlsx. I can't understand why this happens. Maybe it's necessary to somehow update the sheet after deleting the rows for the correct address of merged cells?

Answer 1

The solution to override this issue is saving the file before getting mergedcell Address again, I have tried the following code on the same example you provided, and results seem to be correct after package.Save().

 using (var package = new ExcelPackage(new System.IO.FileInfo(filePath)))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
            var address = worksheet.Cells[78, 1];//address is "A78"
            var mergedadress = worksheet.MergedCells[78, 1];//address is "A78:C78"
            worksheet.DeleteRow(5);
            address = worksheet.Cells[77, 1];//address is "A77"
            mergedaddress = worksheet.MergedCells[77, 1];//null
            address = worksheet.Cells[78, 1];//address is "A78"
            mergedaddress = worksheet.MergedCells[78, 1];//address is"A77:C77"
            package.Save();
        }

 using (var package = new ExcelPackage(new System.IO.FileInfo(filePath)))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
            var address = worksheet.Cells[77, 1];//address is "A77"
            var mergedaddress = worksheet.MergedCells[77, 1];//Address is "A77:C77"
            address = worksheet.Cells[78, 1];//address is "A78"
            mergedaddress = worksheet.MergedCells[78, 1];//null

            package.Save();
        }

I have used EPPLUS v4.1.1.0