Get Merged Cell Area with EPPLus

epplus merge cells and center
epplus loadfromcollection column order
epplus c# cell merge
epplus autofilter
epplus filter rows
epplus pivot table filter
epplus sort
epplus load from collection example

I'm using EPPlus to read excel files.

I have a single cell that is part of merged cells. How do I get the merged range that this cell is part of?

For example:

Assume Range ("A1:C1") has been merged.

Given Range "B1" it's Merge property will be true but there isn't a way to get the merged range given a single cell.

How do you get the merged range?

I was hoping for a .MergedRange which would return Range("A1:C1")

There is no such property out of the box but the worksheet has a MergedCells property with an array of all the merged cell addresses in the worksheet and a GetMergeCellId() method which will give you the index for a given cell address.

We can therefore combine these into a little extension method you can use to get the address. Something like this:

public static string GetMergedRangeAddress(this ExcelRange @this)
{
    if (@this.Merge)
    {
        var idx = @this.Worksheet.GetMergeCellId(@this.Start.Row, @this.Start.Column);
        return @this.Worksheet.MergedCells[idx-1]; //the array is 0-indexed but the mergeId is 1-indexed...
    }
    else
    {
        return @this.Address;
    }
}

which you can use as follows:

using (var excel = new ExcelPackage(new FileInfo("inputFile.xlsx")))
{
    var ws = excel.Workbook.Worksheets["sheet1"];
    var b3address = ws.Cells["B3"].GetMergedRangeAddress();

}

(Note that in the event that you use this method on a multi-celled range it will return the merged cell address for the first cell in the range only)

epplus, Merging cells. //By range address worksheet.Cells["A1:B5"].Merge = true; //By indexes worksheet.Cells[1,1,5,2].Merge = true;� epplus documentation: Merge Cells. This modified text is an extract of the original Stack Overflow Documentation created by following contributors and released under CC BY-SA 3.0

You can get all merged cells from worksheet, hence you can find the merged range a specific cell belongs to using the following:

 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 "";
    }

Update:

Turns out that there is a much easier way using EPPLUS, just do the following:

var mergedadress = worksheet.MergedCells[row, column];

For example, if B1 is in a merged range "A1:C1":

 var mergedadress = worksheet.MergedCells[1, 2]; //value of mergedadress will be "A1:C1".

2 is the column number because B is the 2nd column.

epplus, The Excel needs to be readable, so we are going to merge cells with The field that is getting sorted is the Salesperson field and the criteria for� Method 3: Finding Merged Cells using another Macro (Shading the Merged Cells): If you didn’t liked the above method then we have another option. In this method we will find the merged cell in the active worksheet and then we will color them. This can help you to easily catch the merged cells as they will be of a different color.

Not a direct answer as Stewart's answer is perfect, but I was lead here looking for a way to get the value of a cell, whether it's part of a larger merged cell or not, so I improved on Stewart's code:

public static string GetVal(this ExcelRange @this)
{
    if (@this.Merge)
    {
        var idx = @this.Worksheet.GetMergeCellId(@this.Start.Row, @this.Start.Column);
        string mergedCellAddress = @this.Worksheet.MergedCells[idx - 1];
        string firstCellAddress = @this.Worksheet.Cells[mergedCellAddress].Start.Address;
        return @this.Worksheet.Cells[firstCellAddress].Value?.ToString()?.Trim() ?? "";
    }
    else
    {
        return @this.Value?.ToString()?.Trim() ?? "";
    }
}

And call it like this

var worksheet = package.Workbook.Worksheets[i];
var rowCount = worksheet.Dimension.Rows;
var columnCount = worksheet.Dimension.Columns;

for (int row = 1; row <= rowCount; row++)
{
    for (int col = 1; col <= columnCount; col++)
    {
        string val = worksheet.Cells[row, col].GetVal();
    }
}

Merge Cells, To get all merged cells in the specified cell range, use the CellRange. GetMergedRanges method. C#; VB.NET. // Split all merged cells in the� Spire.XLS provides a class named Worksheet, it contains a MergedCells property which makes it easy for us to obtain the merged cells in a worksheet. This property will return an array of the merged cell ranges, after that we can do any desired operations such as unmerge and apply formatting on the cells in the ranges.

Advanced Excels With EPPlus, I'm using EPPlus to read excel files. I have a single cell that is part of merged cells. How do I get the merged range that this cell is part of? I have huge amounts of data in the cells (exceeding the single cell height limit of 409.5). The problem is this VBA runs with that same limitation. So some of my data gets cut off even though the rows are merged and the combined cell height allowance is 819, since the VBA adjusts the cell height based off the single ZZ1 cell.

How to: Merge Cells or Split Merged Cells, Hi Frnds, I need to merge the dynamically when my first column values are same in EXCEL Generation using EPPLUS. My column data was change when Please help me to get resolved. Attachment: EMP_Details.zip. Get Merged Cell Area with EPPLus. 0. Unmerge and clear cells in epplus 4.1. Hot Network Questions What was the US government's official plan for dealing with future

https://github.com/ylatuya/EPPlus/blob/master/EPPl, RichText on drawings can now handle paragraphs to get line breaks. Merged cells got cleared if a value was set over multiple cells; RichText causes xml� Hi, I am trying to merge two cells (a4 & a5) into another worksheet and for the formula to copy down (i.e. cell a1 has merged a4/a5, a2 has merged b4/b5, a3 has merged c4/d4 etch

Comments
  • This doesn't work I am afraid. merged is just a string so .Contains(cellAddress) checks if "B1" is a substring of "A1:C3" (which it's not) so returns an empty string whereas we would like it to return "A1:C3"
  • please check the updated answer, I tested it with many examples and it works
  • Sorry I haven't understood what the [1,2] is in MergedCells. I have merged columns G,H,I but the values I want are in G. How can I use this?
  • [1,2] are row, column. so if you want to check merged range cell H4 belongs to you can write var mergedadress = worksheet.MergedCells[4, 8]; because it is row 4 and H is the 8th column.