Move Merged cells to simulate insert and delete column

Question

Can I move merged cells to simulate insert/delete columns in the version EPPlus 3.1? I want to move cells but shows me an error when i try move merged cells.

Thanks!

Answer 1

Since no one responded I figured I would let you know what I found. Seems like it is not possible to do in EPPlus 3.1 or the newly release 4.0.1. I created this test in the source code:

[TestMethod]
public void Move_Merged_Cells_Test()
{
    //http://stackoverflow.com/questions/27230423/move-merged-cells-to-simulate-insert-and-delete-column

    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var package = new ExcelPackage(existingFile))
    {
        var workbook = package.Workbook;
        var worksheet = workbook.Worksheets.Add("newsheet");

        worksheet.Select("A1:C3");
        worksheet.SelectedRange.Merge = true;
        worksheet.SelectedRange.Value = "toto";

        //worksheet.Select("A1");
        worksheet.SelectedRange.Copy(worksheet.Cells["B1"]);
        worksheet.SelectedRange.Clear();

        package.Save();
    }
}

Which eventually hits method after going through (starting at the the test method) worksheet.SelectedRange.Copy(worksheet.Cells["B1"]) > .Clear() >Delete(this) > DeleteCheckMergedCells(Range):

private void DeleteCheckMergedCells(ExcelAddressBase Range)
{
    var removeItems = new List<string>();
    foreach (var addr in Worksheet.MergedCells)
    {
        var addrCol = Range.Collide(new ExcelAddress(Range.WorkSheet, addr));
        if (addrCol != eAddressCollition.No)
        {
            if (addrCol == eAddressCollition.Inside)
            {
                removeItems.Add(addr);
            }
            else
            {
                throw (new InvalidOperationException("Can't remove/overwrite cells that are merged"));
            }
        }
    }
    foreach (var item in removeItems)
    {
        Worksheet.MergedCells.Remove(item);
    }
}

Seems it specifically looks for merged cells and throws an exception. I checked 4.0.1 and it is the same problem (they refactored the procedures a bit).

So it seems the only way would be to unmerge, Copy, and remerge. Sorry I couldn't give you a better answer.

Ernie