This project has moved. For the latest updates, please go here.

How to tell if a cell is part of a range? (and work around)

May 25, 2011 at 10:13 AM

Hi,

I have a situation where I need to test if the results of a linq query are merged ranges.  I had a quick look through the source code and couldn't find a way to do this.  The merged property returns true even if the result is a single cell (shouldn't this be false for single cell ExcelRange?).  Also if the result is a merged range, the start and end properties are both the same.  Have I missed something or is this ability missing from EPP?

 I don't know the EPP source code at all and need to get something out quick, so I used an extension method. 

public static ExcelRangeBase GetMergedCells(this ExcelWorksheet worksheet, ExcelRangeBase toCheck)
        {
            if (worksheet != toCheck.Worksheet)
                throw new ArgumentException("Worksheets do not match", "toCheck");

            ExcelAddress toCheckAddress = new ExcelAddress(toCheck.Address);

            foreach (string mergedCells in worksheet.MergedCells)
            {
                ExcelAddress mergedAddress = new ExcelAddress(mergedCells);
                if (toCheckAddress.Start.Row >= mergedAddress.Start.Row
                    && toCheckAddress.End.Row <= mergedAddress.End.Row
                    && toCheckAddress.Start.Column >= mergedAddress.Start.Column
                    && toCheckAddress.End.Column <= mergedAddress.End.Column)
                {
                    return worksheet.Cells[mergedCells];
                }
            }

            return toCheck;
        }

Matt

Editor
May 25, 2011 at 1:05 PM

I dont know if I understand your problem, I tried to reproduce your problem but I am not successful. If a cell is part of a merge range, all cells will have merge=true and the same value. If you want to find your range you need to "move left" and "move right" from your cell to find the head and tail.

May 25, 2011 at 3:33 PM

I was wrong about merge returning true for non merged cells. 

I'm looking for the same functionality as MergeArea, if it exists in EPP.   If you look at the extension method above, you'll see that is pretty much what it does, if the cell is a member of a set of merged cells, it returns the range.  Looking at it now, it should really be an extension method on ExcelRangeBase instead of ExcelWorksheet and renamed to MergeArea to keep consistency with the Excel API.

If I was to move (up, left,right, down) until the value changes, I'd run into the risk of two different merged areas next to each other with the same value being treated as one.  Using the MergedCells property as a look up of the parent Worksheet removes that risk.

Editor
May 25, 2011 at 4:50 PM
Edited May 25, 2011 at 4:53 PM

Yes, for sure. When I said about all cell have same value it isnt for you use this in your method, I was only listing some behaviours there are identical in two merge cells for same range ( or not ). heheh =)

 

If you do this a lot, you can change the code for use an auxiliar structure to store all cell´s address merged in a range. So,  will be O(1). 

May 26, 2011 at 2:49 PM

I do this a lot for this application and I don't have control over the workbook! The customer isn't fussed about performance for the first few releases so when I do get round to optimising I'm sure there's a lot I can do :)  Hopefully a patch to add MergeArea to ExcelRangeBase will result from this (unless someone gets there first.