Unmerge and clear cells in epplus 4.1

Question

I had no luck deleting rows in excel so now I try to clear their content and still get the error: "Can't delete/overwrite merged cells. A range is partly merged with the another merged range. A57788:J57788".

Columns 1-10 are really merged, but how do I unmerge them?

Here's my code:

cntr = 0;
while (ws.Cells[RowNum + cntr, 1].Value == null || !ws.Cells[RowNum + cntr, 1].Value.ToString().StartsWith("Report generation date"))
{
    ws.Cells[RowNum + cntr, 1, RowNum + cntr, 18].Value = "";
    ws.Cells[RowNum + cntr, 1, RowNum + cntr, 10].Merge = false;
    for (int i = 1; i < 17; i++)
    {
        ws.Cells[RowNum + cntr, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.None);
        ws.Cells[RowNum + cntr, i].Clear();
    }
    cntr++;
}
//ws.DeleteRow(RowNum, cntr);

Answer 1

The thing is you can not unmerge a single cell in a range, you have to unmerge the whole range.

To do that you can get the merged range that a cell belongs to using my solution here:

 public string GetMergedRange(ExcelWorksheet worksheet, string cellAddress)
    {
        ExcelWorksheet.MergeCellsCollection mergedCells = worksheet.MergedCells;
        foreach (var merged in mergedCells)
        {
            ExcelRange range = worksheet.Cells[merged];
            ExcelCellAddress cell = new ExcelCellAddress(cellAddress);
            if (range.Start.Row<=cell.Row && range.Start.Column <= cell.Column)
            {
                if (range.End.Row >= cell.Row && range.End.Column >= cell.Column)
                {
                    return merged.ToString();
                }
            }
        }
        return "";
    }

The second step is unmerging the whole range using:

public void DeleteCell(ExcelWorksheet worksheet, string cellAddress)
    {
        if (worksheet.Cells[cellAddress].Merge == true)
        {
            string range = GetMergedRange(worksheet, cellAddress); //get range of merged cells
            worksheet.Cells[range].Merge = false; //unmerge range
            worksheet.Cells[cellAddress].Clear(); //clear value
        }
    }

This way will cost you to lose merging of the other cells, and their value, to overcome this you can save value before clearing and unmerging then you can write it back, something like:

public void DeleteCell(ExcelWorksheet worksheet, string cellAddress)
    {
        if (worksheet.Cells[cellAddress].Merge == true)
        {
            var value = worksheet.Cells[cellAddress].Value;
            string range = GetMergedRange(worksheet, cellAddress); //get range of merged cells
            worksheet.Cells[range].Merge = false; //unmerge range
            worksheet.Cells[cellAddress].Clear(); //clear value
            //merge the cells you want again.
            //fill the value in cells again
        }
    }