In copying a particular range of cells from one workbook to another, I found that cells with formula text containing a local absolute cell reference, e.g. =$F$3, do not get adjusted according to the location of the destination range in the new workbook, while Excel Interop/VBA currently does this automatically. Admittedly, I recognize that the Interop/VBA behavior is different than Excel’s behavior interactively, but I think in this case, since developers are looking to replace Interop/VBA with Aspose, the automatic adjustment should be part of how Aspose handles these formulas.
It sounds like you’re encountering an issue with how Aspose.Cells handles local absolute cell references when copying ranges between workbooks. Unlike Excel Interop/VBA, which automatically adjusts these references based on the destination range, Aspose.Cells may not perform this adjustment by default.
To resolve this issue, you can utilize the CopyOptions class when copying cells. Specifically, set the ReferToDestinationSheet property to true. This option ensures that the copied formulas refer to the destination sheet, which will adjust the references correctly.
Here’s a minimal code example to illustrate how you can implement this:
using Aspose.Cells;
public class CopyCellsExample
{
public static void CopyCellsWithOptions()
{
// Load the source workbook
Workbook sourceWorkbook = new Workbook("source.xlsx");
Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
// Load the destination workbook
Workbook destWorkbook = new Workbook("destination.xlsx");
Worksheet destSheet = destWorkbook.Worksheets[0];
// Create CopyOptions
CopyOptions options = new CopyOptions
{
ReferToDestinationSheet = true // Adjust references to the destination sheet
};
// Copy the range from source to destination
destSheet.Cells.CopyRange(sourceSheet.Cells, "A1", "A10", options);
// Save the destination workbook
destWorkbook.Save("destination_updated.xlsx");
}
}
This code snippet demonstrates how to copy a range of cells from one workbook to another while ensuring that the references are adjusted according to the destination sheet.
If you continue to experience issues, please verify the specific formulas in your source workbook to ensure they are set up correctly for copying. If you have any further questions or need additional assistance, feel free to reach out!
Worksheet.Cells doesn’t have a CopyRange method, AI bot, but nice try. Frankly, Range.Copy should be able to handle such a method call and accept CopyOptions as a parameter, not just PasteOptions, and the ReferToDestinationSheet behavior should be extended to copying a range from a worksheet in one workbook to another worksheet in a different workbook.
We apologize that certain APIs referenced in the AI bot’s provided code snippet are either invalid or unavailable.
Kindly note that Aspose.Cells follows MS Excel standards and specifications when copying worksheet ranges between spreadsheets using the Range.Copy() method. Therefore, its behavior is designed to mirror how the task would be performed manually in MS Excel. Additionally, you can utilize CopyOptions when working with the Worksheet.Copy() method. If you still believe there is an issue with Aspose.Cells APIs, we would appreciate it if you could share your complete sample (runnable) code or a console application along with the sample files. We will review it soon.
PS. please zip the sample app or resource files prior attaching here.
Hi Amjad,
Thanks for replying to me so quickly. I’ve done some more research on my end and I’ve discovered the true issue here. There’s actually a behavioral difference in Excel between copying a range and cutting a range, and it’s the latter that was being done in the previous Excel Interop code I’d been using in my C# program. Cutting will adjust any formulas using local absolute cell references when the range is pasted to the new location, but copying doesn’t. I’ve figured out how to utilize InsertCutCells for this purpose.
However, in the course of unit testing, I seem to have uncovered an actual bug with this code:
using Aspose.Cells;
Workbook srcWorkbook = new(@"C:\Swift\Templates\Dev\Input_STRUCTURE.xlsx");
var srcRange = srcWorkbook.Worksheets.Names.First(
n => n.Text.Equals("STRUCTURE", StringComparison.OrdinalIgnoreCase)).GetRange();
var srcGreyWorksheet = srcWorkbook.Worksheets["Grey"];
var srcGreyRange = srcGreyWorksheet.Cells.CreateRange(srcRange.Address);
Workbook targetWbk = new(@"C:\Swift\Templates\Dev\InputCombined_Stoch.xlsx");
var inputSheet = targetWbk.Worksheets["Input"];
if (inputSheet != null)
{
targetWbk.Worksheets.RemoveAt("Input");
}
inputSheet = targetWbk.Worksheets.Insert(0, SheetType.Worksheet, "Input");
var greySheet = targetWbk.Worksheets["Grey"];
if (greySheet != null)
{
targetWbk.Worksheets.RemoveAt("Grey");
}
greySheet = targetWbk.Worksheets.Insert(1, SheetType.Worksheet, "Grey");
inputSheet.Cells.InsertCutCells(srcRange, 0, 0, ShiftType.None);
greySheet.Cells.InsertCutCells(srcGreyRange, 0, 0, ShiftType.None);
With this code using the attached zipped files, I get a NullReferenceException when the program reaches the last line. If I change things to start with a blank workbook or if I remove the Simulation worksheet, then it works fine, so it seems there’s a bug with InsertCutCells. Please let me know when you reproduce this and if you need any more information. I hope you can get a fix for this in place with this month’s release.
Dev.zip (1.2 MB)
@bytefyre
By testing with sample file and code on the latest version v25.8, we can reproduce the issue. NullReferenceException occurs when calling Cells.InsertCutCells method.
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): CELLSNET-58972
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.
@bytefyre
We have fixed this issue , please check the attached result which is generated by inner hot fix.
CELLSNET-58972.zip (69.1 KB)
Moreover, the fix/improvement will be included in the upcoming (next) release (Aspose.Cells v25.9) scheduled for the next week of September 2025. We will notify you as soon as the new version is released.
I appear to have identified another bug with InsertCutCells. It appears that cells with formula text that uses relative cell references do not have their formulas carried over and adjusted when copied to a new location. I’ve provided the following sample code:
using Aspose.Cells;
Workbook srcWorkbook = new(@"C:\Swift\Templates\Dev\Input_FASG_LIABILITY.xlsx");
var srcRange = srcWorkbook.Worksheets.Names.First(
n => n.Text.Equals("FASG_LIABILITY", StringComparison.OrdinalIgnoreCase)).GetRange();
var srcGreyWorksheet = srcWorkbook.Worksheets["Grey"];
var srcGreyRange = srcGreyWorksheet.Cells.CreateRange(srcRange.Address);
Workbook targetWbk = new();
var inputSheet = targetWbk.Worksheets["Input"];
if (inputSheet != null)
{
targetWbk.Worksheets.RemoveAt("Input");
}
inputSheet = targetWbk.Worksheets.Insert(0, SheetType.Worksheet, "Input");
var greySheet = targetWbk.Worksheets["Grey"];
if (greySheet != null)
{
targetWbk.Worksheets.RemoveAt("Grey");
}
greySheet = targetWbk.Worksheets.Insert(1, SheetType.Worksheet, "Grey");
Console.WriteLine($"Source A144 formula: {srcRange[142, 0].Formula}");
Console.WriteLine($"Source A144 value: {srcRange[142, 0].Value}");
Console.WriteLine();
inputSheet.Cells.InsertCutCells(srcRange, 3764, 0, ShiftType.None);
greySheet.Cells.InsertCutCells(srcGreyRange, 3764, 0, ShiftType.None);
Console.WriteLine($"Target A3907 formula: {inputSheet.Cells["A3907"].Formula}");
Console.WriteLine($"Target A3907 value: {inputSheet.Cells["A3907"].Value}");
This produces the following console output:
Source A144 formula: =A143+1
Source A144 value: 82.5
Target A3907 formula:
Target A3907 value: 82.5
Please use the attached file below to reproduce.
Input_FASG_LIABILITY.zip (110.7 KB)
Thanks for the template Excel file and details.
I reproduced the issue as you mentioned by using your sample Excel file and code snippet. I found an issue with Cells.InsertCutCells method as formulas with relative references aren’t carried over when copied to a new location.
We require thorough evaluation of the issue. 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): CELLSNET-58978
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.
Please note, the fixes/enhancements for the issues (“CELLSNET-58972” and “CELLSNET-58978”) are included in the upcoming release (Aspose.Cells v25.9), which will be released before the end of this week of September 2025. We will notify you once the new version is available.
The issues you have found earlier (filed as CELLSNET-58972,CELLSNET-58978) have been fixed in this update. This message was posted using Bugs notification tool by leoluo