0

I am using below method to copy value without formula from a range of excel to the same range (basically removing formula). This range has 7 cells but they are merged.

void Copy(IRange destination, PasteType pasteType, PasteOperation pasteOperation, bool skipBlanks, bool transpose);

I am getting InvalidOperationException with message "Operation is not valid for a partial merged cell."

SpreadsheetGear.Core Version - 8.0.63.102

This works well with single cell but not with merged cells.

sourceRange.Copy(targetRange, SpreadsheetGear.PasteType.Values, SpreadsheetGear.PasteOperation.None, false, false);
Viraj Sanghavi
  • 96
  • 5

1 Answers1

0

This behavior is by design, as SpreadsheetGear does not allow pasting into a range that includes partial merged cells. Instead, you would need to adapt the paste operation in some way so that the destination range fully includes all cells that are part of a merged range. There are some APIs in the IRange interface that can help you determine if a given IRange is part of a merged range and return adjusted IRanges to account for this:

  • IRange.MergeCells - Gets or sets the property which specifies whether the top left cell of this IRange is merged.
  • IRange.MergeCellsDefined - Returns true if the MergeCells property of all cells represented by this IRange is false or if all cells represented by this IRange are contained by the same merged cell area, otherwise false is returned.
  • IRange.MergeArea - Returns an IRange representing the merged cell area if the cell represented by this single cell IRange is part of a merged cell range, otherwise the original IRange is returned.
  • IRange.EntireMergeArea - Returns an IRange representing the area of the current IRange which is iteratively grown as needed until it includes no partial merged cells.

Below is some sample code that demonstrates how you can use some of the above APIs to deal with your case. Note depending on the ranges involved in the copy operation (single cell vs multi-cell), the approach might vary.

// Setup a workbook with some test data and a merged range.
IWorkbook workbook = Factory.GetWorkbook();
IRange cells = workbook.ActiveWorksheet.Cells;
cells["A1:B2"].Formula = "=ROW()+COLUMN()";
cells["A1:A7"].Merge();

//For a single cell copy operation
{
    IRange copyPasteRange = cells["A1"];

    // If IRange.MergeCells is true, A1 will be part of a larger merged range, so we 
    // should use the IRange.MergeArea property to expand A1 to include the entire 
    // merged range
    if (copyPasteRange.MergeCells)
        copyPasteRange = copyPasteRange.MergeArea;
    copyPasteRange.Copy(copyPasteRange, PasteType.Values, PasteOperation.None, false, false);
}

// For a multi-cell copy operation.  
{
    // Note in this case that A1 is part of merged range A1:A7 but B2 is not merged.  
    // How you deal with this depends on your requirements.  
    IRange copyPasteRange = cells["A1:B2"];

    // One way is to use IRange.EntireMergeArea which will iteratively grow the specified 
    // IRange until it fully includes all merged ranges, so in this case A1:B7.  
    // Obviously, this means B2:B7 will be copied as a side-effect, but this is what 
    // would happen if you tried to copy this range in Excel's UI (i.e., when attempting 
    // to select A1:B1 in Excel, they will always expand the selected range to A1:B7), 
    // so might be acceptable in some cases.
    if (copyPasteRange.Address != copyPasteRange.EntireMergeArea.Address)
        copyPasteRange = copyPasteRange.EntireMergeArea; // A1:B7
    copyPasteRange.Copy(copyPasteRange, PasteType.Values, PasteOperation.None, false, false);

    // Alternatively, you could loop through each cell in the originalCopyPaste 
    // range and copy each cell as needed.  I leave it up to you to flesh this out if 
    // it is necessary for your requirements.
    if (copyPasteRange.Address != copyPasteRange.EntireMergeArea.Address)
    {
        foreach (IRange cell in copyPasteRange)
        {
            // Paste by value for each cell, taking into account merged cells however you 
            // see fit.
        }
    }
}
Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
  • My source and target range is same. I have named range defined on columns AD to AI on 7th row which has formula. Basically I want to just keep final value and remove formula in generated excel. So when you say "partial merged cells", what is the difference between merged and partial merged ? – Viraj Sanghavi Oct 17 '19 at 14:15
  • In your case AD7:AI7 is the merged range in its entirety. Examples of partial merged cells would be just AD7, or just AE7, or AD7:AH7. AD6:AD8 would include the partial merged cell AD7. When pasting into a range that includes merged ranges, you need to ensure that those merged ranges are included in their entirety, not partially. – Tim Andersen Oct 17 '19 at 14:26
  • So if AD7:AI7 is fully merged cell range then I should not be getting this error. Right ? In my excel sheet there is only this one cell range. – Viraj Sanghavi Oct 17 '19 at 14:52
  • If you need to paste into the merged range AD7:AI7 then you should specify AD7:AI7 as both the source range and destination range in the copy routine, as my answer and example code above describes. – Tim Andersen Oct 17 '19 at 15:03