Alternative to Cut/Paste for Ranges in .NET

Aspose.Cells Product Family
1

I have a program that currently uses Interop’s cut/paste functionality to change the ordering of entire columns within a worksheet while updating the references (defined names, conditional formatting, formulas, etc.). Is there a way that I’m missing to do this? I’ve tried the Range.MoveTo() method but it’s caused tons of issues.

2

@alexmclean,

You may please give a try to the sample code below which performs following steps:

  1. Open workbook

  2. Get first worksheet

  3. Insert column at D

  4. Set PasteType to Default so that it mimics the “All” behavior of Excel

  5. Copy data from column B to D using above mentioned paste options

  6. Delete column B

  7. Save workbook

    Workbook wb = new Workbook(@“Book1.xlsx”);
    Worksheet ws = wb.Worksheets[0];

    //Insert column at D
    wb.Worksheets[0].Cells.InsertColumns(3, 1, true);

    // Set PasteOptions
    PasteOptions pasteOptions = new PasteOptions();
    pasteOptions.PasteType = PasteType.Default;
    pasteOptions.OnlyVisibleCells = true;

    //Copy Data from column B to D
    ws.Cells.CopyColumns(ws.Cells, 1, 3, 1, pasteOptions);

    //Delete column B
    ws.Cells.DeleteColumns(1, 1, true);
    wb.Save(@“Book2.xlsx”);

Book1.zip (7.1 KB)
Book2.zip (7.4 KB)
CutPasteOperationImage.zip (14.3 KB)

3

This carries over the conditional formatting, but if you placed a defined name on the range that you’re copying, it won’t get moved over properly.

4

@alexmclean,

Please share your sample file and runnable code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.

5

@ahsaniqbalsidiqui

Here’s what I’m using to test your method. As you will see, the defined name “NamedRange” will be #REF in book2.

            Workbook wb = new Workbook();
        Worksheet ws = wb.Worksheets[0];

        ws.Cells[0, 2].Value = 1;
        ws.Cells[1, 2].Value = 2;
        ws.Cells[2, 2].Value = 3;

        ws.Cells.CreateRange(0, 2, 3, 1).Name = "NamedRange";

        wb.Save(
            Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
                "book1.xlsx"));

        PasteOptions pasteOptions = new PasteOptions();
        pasteOptions.PasteType = PasteType.Default;
        pasteOptions.OnlyVisibleCells = true;

        ws.Cells.CopyColumns(ws.Cells, 2, 1, 1, pasteOptions);
        ws.Cells.DeleteColumns(2, 1, true);

        wb.Save(
            Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
                "book2.xlsx"));
6

@alexmclean,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46643 - Named ranges are not copied while copying a column
7

@alexmclean,

We evaluated your issue further. We found MS Excel does not copy the named range when copying columns, please check it in MS Excel manually. Aspose.Cells works as the same way as MS Excel does, so we have no plan to fix your issue.

Thanks for your understanding!

8

Thank you @Amjad_Sahi. While I understand that the Aspose copy method follows the MS Excel method, The reasoning for posting in this topic was to see if there is a way to mimic how Excel cuts (different than copy). Is there any plan to include this in Aspose?

9

@alexmclean,

I am afraid, we might not support or fix it. The reason is it will violate Ms Excel standards and it might affect some other users who mimic MS Excel behavior when copying rows and columns in Excel sheets.

10

@Amjad_Sahi,

I can understand why you would not want to change the current copy/paste methodology. However, cut and copy are two different operations in MS Excel.

See these:

11

@alexmclean,

I have logged your comments with the ticket for our reference and will look into it in detail. I will write back here once any feedback is ready to share.

12

Thank you! Looking forward to your feedback while we are in the trial process of your product.

13

@alexmclean,

You are welcome.

14

@ahsaniqbalsidiqui any update on this?

15

@alexmclean,

This new feature request is still pending and not planned yet, therefore no ETA can be provided. We will write back here once any feedback is ready to share.

16

@alexmclean,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46643”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

17

@alexmclean,

Please try our latest version/fix: Aspose.Cells for .NET v19.3.6 (attached)
Your issue should be fixed in it.
Let us know your feedback.
Aspose.Cells19.3.6 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.3.6 For .Net4.0.Zip (4.9 MB)

18

@Amjad_Sahi

Thanks for this. It looks like what you’ve done is ignored the named range in the copy and paste. What I’m looking for is the named range to move with the data I’m moving, which is what a cut/paste operation would do in Excel. Is there something I’m missing here?

19

@alexmclean,

We have added Cells.InsertCutCells API in the latest version/fix which you may try to use to accomplish your task, it also considers named ranges to be copied/moved accordingly. See the sample code for your reference:
e.g
Sample code:

    Workbook wb = new Workbook();
                Worksheet ws = wb.Worksheets[0];

                ws.Cells[0, 2].Value = 1;
                ws.Cells[1, 2].Value = 2;
                ws.Cells[2, 2].Value = 3;
                ws.Cells[2, 3].Value = 4;
                ws.Cells.CreateRange(0, 2, 3, 1).Name = "NamedRange";

            Range cut = ws.Cells.CreateRange("C:C");
            ws.Cells.InsertCutCells(cut, 0, 1, ShiftType.Right);

Hope, this helps a bit.

20

The issues you have found earlier (filed as CELLSNET-46643) have been fixed in Aspose.Cells for .NET v19.4. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi