Cells in range cannot be merged because cells in range have already been merged

Aspose.Cells Product Family
1

Hi Aspose Team,

I get this exception while performing a merge on already merged cells with different range.

I investigated the issue and understood that aspose does not support merge for already merged cells similar to MS excel. But, we are gradually moving our source code from VBA to Aspose and the same piece of code worked well in VBA and it was able to merge the cells again with out throwing any exception.

Could you please advice whether we have any workaround?

Thanks!

2

Hi,

When cells are already merged, you cannot merged with another cell(s). You need to use different technique. This technique will use CellArea object.

I have illustrated this technique in this thread. The code is in VB.NET though. But I can convert it in C# for your help if you need it.

3

Thanks for the reply Shakeel Faiz. It is very informative.

Could you give me a utility method which would essentially merge the range (even if it is already merged)?
In my scenario, I cannot look at the previous cell whether it is merged or not. The issue is, say if I have a merged range from A3 -> H3 and I want to merge from C3-> J3, the sample may not work as it checks for previous cells. Also, I do not have the liberty to declare which are the cells that should be merged in my source code. the indexes gets generated automatically from external system.

To give you a detailed requirement, I would want a merge method, which would take a range as input, identifies whether there are cells that are already merged (if yes), then find the merged range from the cell and unmerge it and merge it using the parameter (range) sent to the method.

Could you help me with a source code which performs this - in C# though?

Pseudocode below -

void OverloadedMerge(Range targetMergeRange)

{

-- Iterate and check whether any of the cells are merged

--- if yes, unmerge the range of the cells which is already merged

--- perform this action so that the entire targetMergeRange does not have internal merge

-- Perform required targetMergeRange
}

Thanks!

4

Hi,

I think, this code will achieve your functionality.

Please see source and output file and screenshot.

The idea is to use Range.Union() method. Other methods that might be interested for you are Range.Intersect() and Range.IsIntersect().

C#


string filePath = @“F:\Downloads\issue\sourceMergeTwoRanges.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Range r1 = worksheet.Cells.CreateRange(“A1”, “H3”);

Range r2 = worksheet.Cells.CreateRange(“C3”, “J3”);


ArrayList lst1 = r1.Union(r2);

ArrayList lst2 = r2.Union(r1);


foreach (Range obj in lst1)

{

obj.Merge();

}


foreach (Range obj in lst2)

{

obj.Merge();

}


workbook.Save(filePath + “.out.xlsx”)


Screenshot:
5

Thanks for the response. how to check whether a range is merged or not?

say i have a method and I'm getting a range as a parameter

void testmethod(Range range)
{

if(range.(????? isMerged){

range.unMerge();
}
}

Instead of creating two merges, my issue is already the first range is merged and I want to remove the first range(r1) dynamically and add r2 so that it does not throw any exception. please note that the dimensions of r1 is not known to me.

For that, I should be looping through all the ranges in the sheet and find out whether it intersects with my current new range and also check whether the previous range is having merged cells and if yes then add the new range (r2) in this example

some code i wrote based on your suggestion and it does not work.Here the formatRange is the parameter that comes to this method.

for (int rangeCount = 0; rangeCount < ReportCells.Ranges.Count; rangeCount++)

{

if (formatRange.IsIntersect(ReportCells.Ranges[rangeCount]))

{

// Should validate whether ReportCells.Ranges[rangeCount] is merged? if yes remove it. so that it does not restrict the new range merge - PENDING

// I dont see a reason for union to join these two ranges. because the reportcells.ranges keeps increasing while debugging the loop

list = formatRange.Union(ReportCells.Ranges[rangeCount]);

foreach (Range range in list)

{

if (range != null)

// My requirement is to unmerge the previous range so that the new range works

range.UnMerge();

}

}

}

6

Hi,

Please create screenshots and sample input as well as output files and attach here. This will be helpful for me to sort out your problem.

7

Yellow one – merge range 1 (E5:F5)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Red one – merge range 2(E6:H6)

Attached the merged spreadsheet and also the screen shot.

Suppose, I want to merge between E4:F8, I would want the yellow merge and red merge to be removed dynamically so that the merge for E4:F8 works. In this case, I know that we have only two conflicting merges, but in my scenario there could be numerous conflicting merges and I have to identify it dynamically and resolve it.

If you do it, vba it will work fine. The highlighted code in red will work fine.

The vba code is as below –

Sub Macro1()

'

' Macro1 Macro

'

'

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Merge

Range("E6:H6").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Merge

Range("C5:F5").Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

Range("E6:H6").Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 255

.TintAndShade = 0

.PatternTintAndShade = 0

End With

Range("E4:F8").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Merge

End Sub

C# code –

public ReportFormatter(string file)

{

ReportWorkbook = new Workbook(file);

ReportWorksheet = ReportWorkbook.Worksheets["Sheet1"];

ReportCells = ReportWorksheet.Cells;

}

public void FormatMethod()

{

Range mergeRange = ReportCells.CreateRange("E4", "F8");

mergeRange.Merge();

ReportWorkbook.Save("C:\bulb.xls");

}

Please note that your previous sample workaround is based on previous cell and you check whether it is merged are not.

Thanks!

8

Hi,

Well, I think for your need, you may get all the merged cells in a worksheet to un-merge them first. Then, you may create your desired ranges and merge them for your requirements. See the following sample code if it suits your need.

Sample code:
Workbook wkBook = new Workbook(@“e:\test2\merge-issue.xls”);
Worksheet wkSheet = wkBook.Worksheets[0];

foreach (CellArea cellAreas in wkSheet.Cells.MergedCells)
{
wkSheet.Cells.UnMerge(cellAreas.StartRow, cellAreas.StartColumn, cellAreas.EndRow - cellAreas.StartRow + 1, cellAreas.EndColumn - cellAreas.StartColumn + 1);
}


Range mergeRange = wkSheet.Cells.CreateRange(“C4”, “H8”);
mergeRange.Merge();
wkBook.Save(“e:\test2\outputMergeIssue1.xls”);


Thank you.


9

Thanks for the note. I would want to unmerge only the conflicting merge and it should not impact other merged cells in worksheet. How to identify only the conflicting merges that are pertaining to the current merge range and then tell the aspose object to merge the conflicting merges and perform this operation.

Say, in my previous example, i wanted to merge between e4 and f8. I might have some other useful merge in other location on the same worksheet and wouldn't want my current range to unmerge everything, but in this case, I have to identify the merge that is conflicting to the current position and identify the merge range[] and resolve it.

Please run the macro and see that the conflicting merges are auto resolved in VBA. I need similar functionality on aspose side.

Thanks!

10

Hi,

Thanks for your information.

We are looking into add a new method to support your need because the result is not same as excepted by codes.

You want to merge between E4 and F8 in your example, but C4 and H8 is merged in the result and all other conflicting merged cells are removed.

This issue has been logged as CELLSNET-25919.

11

Hi,

Please try Aspose.Cells for .NET (Latest Version) .

We have added a new version of the method Cells.Merge(int,int,int,int, true) method. Here true refers to mergeConflict Boolean parameter.

Thank you.

12

The issues you have found earlier (filed as CELLSNET-25919) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.