Set cells merged and set its value, but it not work？

Question

//use EPPlus.dll

using OfficeOpenXml

string path = @"C:\Users\Superman\Desktop\recc\1996.xlsx";
            ExcelPackage package = new ExcelPackage(new FileInfo(path));
            var sheet3 = package.Workbook.Worksheets[3];
            sheet3.Cells["A1:B5"].Merge = true;
            var mergedId = sheet3.MergedCells[1, 1];
            sheet3.Cells[mergedId].First().Value = "123"; // error: System.InvalidOperationException : Sequence does not contain any elements
            package.Save();

What's wrong? How to do it?

Answer 1

To answer why the exception from using the First() method - I would bet money that your sheet3 in excel is empty. Remember that the Cells object only contains references to cell that have actual content. But if all of the cells in excel are empty then so is the Cells collection in EPPlus.

For example, this works fine when creating a brand new sheet:

using (var package = new ExcelPackage(fi))
{
    var brandNewSheet = package.Workbook.Worksheets.Add("BrandNewSheet");
    brandNewSheet.Cells["A1"].LoadFromCollection(new[] {"A", "B", "C", "D", "E"});
    brandNewSheet.Cells["B1"].LoadFromCollection(new[] {"A", "B", "C", "D", "E"});
    brandNewSheet.Cells["A1:B5"].Merge = true;
    var mergedId = brandNewSheet.MergedCells[1, 1];
    brandNewSheet.Cells[mergedId].First().Value = "123";
    package.Save();
}

But if you comment out the LoadFromCollection calls you will get the runtime exception:

using (var package = new ExcelPackage(fi))
{
    var brandNewSheet = package.Workbook.Worksheets.Add("BrandNewSheet");
    //brandNewSheet.Cells["A1"].LoadFromCollection(new[] {"A", "B", "C", "D", "E"});
    //brandNewSheet.Cells["B1"].LoadFromCollection(new[] {"A", "B", "C", "D", "E"});
    brandNewSheet.Cells["A1:B5"].Merge = true;
    var mergedId = brandNewSheet.MergedCells[1, 1];
    brandNewSheet.Cells[mergedId].First().Value = "123"; //Cells is empty so: System.InvalidOperationException: Sequence contains no elements
    package.Save();
}

As others have explain, there is no need to call First() to get what you want but figured I was at least address that.

Answer 2

just a suggestion: use "using"

and a small change in your code:

    using (ExcelPackage package = new ExcelPackage(new FileInfo(path)))
    {
        var sheet3 = package.Workbook.Worksheets[3];
        sheet3.Cells["A1:B5"].Merge = true;
        var mergedId = sheet3.MergedCells[1, 1];
        sheet3.Cells["A1"].Value = "123"; 
        package.Save();
    }

I think the range wasn't recognized anymore, because you have merged it

EDIT1:

this code works for me like a charm. I think you don't need .First()

using (ExcelPackage package = new ExcelPackage(new FileInfo(path)))
            {
                var sheet3 = package.Workbook.Worksheets[3];
                sheet3.Cells["A1:B5"].Merge = true;
                var mergedId = sheet3.MergedCells[1, 1];
                sheet3.Cells[mergedId].Value = "123";
                package.Save();
            }