Set merge cells and set its value, but it doesn't 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 happened? How to do it?

Answer 1

To answer why the exception uses the First() method - I would bet the money that your sheet3 in excel is empty. Remember that a Cells object contains only links to a cell with actual content. But if all the cells in excel are empty, then the Cells collection in EPPlus.

For example, this works great when creating a 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 calls to LoadFromCollection , you get an exception at runtime:

 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 explain, there is no need to call First() to get what you want, but you believe that I at least addressed it.

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 was no longer recognized because you leaked it

EDIT1:

this code works for me like a charm. I think you do not 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(); } 

Answer 3

If you comment out a line that generates an error, are the cells in the resulting sheet connected? If they are, try simply assigning a value to cell A1 and see if this works.

Hope this helps

Answer 4

You already have the built-in ExcelRange in the above code example:

 sheet3.Cells["A1:B5"].Merge = true; 

So, save the link and use it to set the value of the merged cell:

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

Now there is no need to calculate or hard-code addresses or indexes. In other words, when you did this:

 var mergedId = sheet3.MergedCells[1, 1]; 

the code throws a System.InvalidOperationException because mergedId is A1:B5 .

And vice versa:

 range.Start.Address // A1 

sets the cell value correctly.