Get Merged Cell Area with EPPLus

Question

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")

Marquis

Answer 1

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)

Illene

Answer 2

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.

Checkrein

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" – Illene

please check the updated answer, I tested it with many examples and it works – Checkrein

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? – Alternation

[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. – Checkrein

Answer 3

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();
    }
}

Priesthood

Answer 4

This will provide you exact width of merged cells:

workSheet.Cells[workSheet.MergedCells[row, col]].Columns 

Gq