Best Practice for merged cells operations (copy, paste)

Aspose.Cells Product Family
1

Hello Support,

I’m wondering what’s the best way to deal with merged cells and copy-paste operations. Actually I’m trying to copy a “standard” column with values inside a “merged” column where every row contains merged cells. In images: this is the source https://i.imgur.com/iO6HkTz.png , this is the destination https://i.imgur.com/1jiGAN8.png and this is what I’m expecting https://i.imgur.com/xpM4yqt.png .
This is what I’ve got https://i.imgur.com/jmNLBNn.png . Please notice that involved cells are correctly merged but the visualization is remarcable. This is the code I’ve wrote:

        Workbook source = new Workbook(Path.Combine(Path.GetDirectoryName(location), "Source.xlsx"));
        Workbook workbook = new Workbook(Path.Combine(Path.GetDirectoryName(location), "Destination.xlsx"));

        Worksheet ws = workbook.Worksheets[0];
        for (int i = 0; i < 50; i++)  //creating 50 merged cells
        {
            var r = ws.Cells.CreateRange(i, 0, 1, 2);
            r.Merge();
        }
        
        Aspose.Cells.Range sourceRange = source.Worksheets[0].Cells.CreateRange(1,0,10,1); //getting 10 rows of first column
        Aspose.Cells.Range destRange = ws.Cells.CreateRange(3,0, 10, 1);    //using 10 rows of destination sheet

        PasteOptions options = new PasteOptions();
        options.PasteType = PasteType.All;
        destRange.Copy(sourceRange, options); //copying the data

        workbook.Save(Path.Combine(Path.GetDirectoryName(location), "result.xlsx"));

Please let me know where I’m wrong. Best regards

2

@andreagasparin,

Thanks for your query.

We have tried to reproduce this issue using own sample files but same scenario is not observed. Please share your sample Source.xlsx, Destination.xlsx and result.xlsx files with us for our testing. We will reproduce the problem and provide our feedback after analysis.

3

Hi ahsaniqbalsidiqui,

MergedCopy.zip (21.2 KB)
Here’s an archive containing the materials. I’m currently using aspose.cells v18.7. Thanks for support.

4

@andreagasparin,

I have tried to perform same task using Excel 2016 and then modified the code according to the steps taken in Excel. Try this code at your end, and provide the feedback.

Workbook source = new Workbook(@"Source.xlsx");
Workbook destination = new Workbook(@"Destination.xlsx");

var ws = destination.Worksheets[0];
Aspose.Cells.Style stl = destination.CreateStyle();
stl.HorizontalAlignment = TextAlignmentType.Center;
for (var i = 0; i < 50; i++) //creating 50 merged cells
{
    var r = ws.Cells.CreateRange(i, 0, 1, 2);
    r.Merge();
    r.SetStyle(stl);
}

var sourceRange = source.Worksheets[0].Cells.CreateRange(0, 0, 10, 1); //getting 10 rows of first column
var destRange = ws.Cells.CreateRange(2, 0, 10, 1); //using 10 rows of destination sheet

var options = new PasteOptions();
options.PasteType = PasteType.Formulas;//This option is selected similar to Excel
destRange.SetStyle(stl);
destRange.Copy(sourceRange, options); //copying the data
destination.Save(@"resultDefault.xlsx");

If your issue is not resolved, you may perform this task using Excel and share the detailed steps with us to achieve this output using Excel. We will analyze it and share our feedback here.

Output.PNG (2.1 KB)

5

hi @ahsaniqbalsidiqui,

my target is to get a sheet with the merged cells of (destination) and values and format of (source) - look at this attachment Target.zip (7.0 KB)

I’ve been inspired by your last solution and I’ve tried this one:

        var sourceRange = source.Worksheets[0].Cells.CreateRange(0, 0, 10, 1); //getting 10 rows of first column
        var destRange = ws.Cells.CreateRange(2, 0, 10, 1); //using 10 rows of destination sheet

        var style = sourceRange.GetCellOrNull(1, 0).GetStyle();//getting the wanted style

        var options = new PasteOptions();
        options.PasteType = PasteType.Formulas; //This option is selected similar to Excel
        destRange.SetStyle(style);
        destRange.Copy(sourceRange, options); //copying the data

        foreach (CellArea mergedcell in ws.Cells.MergedCells)
        {
            ws.Cells.CreateRange(mergedcell.StartRow, mergedcell.StartColumn,
                    1 + mergedcell.EndRow - mergedcell.StartRow, 1 + mergedcell.EndColumn - mergedcell.StartColumn)
                .SetStyle(style); //appling the wanted style to the merged cell range
        }

Is this the right way to export cells style to a merged cell?

6

@andreagasparin,

It seems to be correct as it is creating the required output. Please write back to us if you have any issue along with the steps which can be performed in Excel (without using Aspose.Cells anywhere) to achieve the same required output.