When I use the insertCutCells method exactly as outlined here and I provide the ShiftType.NONE option, I always get the error:
com.aspose.cells.CellsException: Invalid shift option.
I want to do a cut, but not insert the cells, rather just paste them. What I am trying to do in Aspose is the same as in desktop Excel: 1) selecting range 2) shortcut ctrl + x 3) selecting another range 4) shortcut ctrl + v
The error you’re encountering, CellsException: Invalid shift option, typically occurs when the specified shift option in the insertCutCells method is not compatible with the operation you are trying to perform.
In your case, if you want to mimic the Ctrl + X and Ctrl + V functionality from Excel without actually inserting the cells but just pasting them, you should use the ShiftType option correctly. The ShiftType.NONE is not valid for this operation as it expects a valid shift option when inserting cut cells.
To perform a cut and paste operation similar to Excel, you can follow these steps:
Cells.cut() to cut the range you want.Cells.insertCutCells() with a valid ShiftType. For example, you might want to use ShiftType.DOWN or ShiftType.RIGHT depending on where you want the cut cells to be placed.Here’s an example code snippet:
// Assuming 'workbook' is your Workbook object and 'sourceRange' and 'destinationCell' are defined
Cells cells = workbook.getWorksheets().get(0).getCells();
Range sourceRange = cells.createRange("A1:B2"); // Range to cut
Cell destinationCell = cells.get("C1"); // Destination cell
// Cut the cells
sourceRange.cut();
// Insert cut cells at the destination cell
cells.insertCutCells(destinationCell.getName(), ShiftType.DOWN); // Use ShiftType.DOWN or ShiftType.RIGHT
This should help you achieve the desired cut and paste functionality without encountering the error. Make sure to adjust the shift type based on your specific needs.
If you have further questions or need additional help, feel free to ask!
Thank you Professionalize.Discourse robot. I am asking for general guidance here and have confirmed the error I am receiving is expected. You did not though, provide me with a way to create the cut + paste behavior that solves my case. I dont want to shift any cells when I paste my cut cells.
@cwedwards92
The ShiftType.NONE enumeration represents a copy operation without any shift operation. As an alternative solution, you can use range copying to meet the requirements. Please refer to the following document.
Hi John, thanks for your response. Unfortunately I don’t think your workaround will work for us because of the formula cell reference behavior. The updating of dependent formulas and formulas in the target moved range work differently in cut vs copy. I’m looking for the cut formula update behavior without any inserting. Is there any other solution that you can provide?
@cwedwards92
Thank you for your feedback. Range copying will also copy formulas at the same time. Would you like to provide example files to illustrate the difference between the two? If you could provide sample files, screenshots, and detailed explanations, it would be very helpful for us to locate the issue. We will check it soon.
@cwedwards92
By further researching and creating example files for testing, we can reproduce the formula issue. “Copy range and paste” differs from “cut range and paste” in terms of formula processing. Please refer to the attachment. copy cut and paste.zip (7.8 KB)
We can reproduce the issue by testing on the latest version v25.6 using the following sample code. CellsException(Invalid shift option) occurs when calling Cells.insertCutCells method with ShiftType.NONE.
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getCells().get(0, 2).setValue(1);
worksheet.getCells().get(1, 2).setFormula("=SUM(C3:C4)");
worksheet.getCells().get(2, 2).setValue(3);
worksheet.getCells().get(3, 2).setValue(4);
worksheet.getCells().createRange(0, 2, 3, 1).setName("NamedRange");
Range cut = worksheet.getCells().createRange("C1:C2");
worksheet.getCells().insertCutCells(cut, 0, 1, ShiftType.NONE);
workbook.save(filePath + "CutAndPasteCells.xlsx");
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-46407
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
We are pleased to inform you that your issue (Ticket ID: “CELLSJAVA-46407”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.7) that we plan to release in the first half of July 2025. You will be notified when the next version is published.
@cwedwards92
CELLSJAVA46407.zip (6.1 KB)
Please check above file which generated with inner hot fix 25.6.1 and codes in previous post.
Really appreciate the quick response and turnaround on this guys, thank you
You are welcome. The inner fix will be the part of the upcoming release (Aspose.Cells v25.7) that we plan to release in the second week of July 2025.
The issues you have found earlier (filed as CELLSJAVA-46407) have been fixed in Aspose.Cells for Java 25.7.