If I have a reference to a cell on another sheet and cut/paste the referenced cell, the reference will update to point to the new location.
If I use Range.MoveTo, this doesn’t happen. If the reference is on the same sheet, it will update, but references on other sheets won’t.
using Aspose.Cells;
namespace MoveTo
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook(“input.xlsx”);
Range range = workbook.Worksheets[“Sheet1”].Cells.CreateRange(“B2”);
range.MoveTo(1, 1);
workbook.Save(“output.xlsx”);
}
}
}
Hi Piers,
Thank you for sharing the sample.
We have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 17.02.0, and we are able to notice the said behavior. In order to further investigate the matter, we have raised this incident as CELLSNET-45154 in our bug tracking system. Please spare us little time to properly evaluate the case and revert back with updates in this regard.
Hi again,
This is to update you that the ticket logged earlier as CELLSNET-45154 has been marked resolved. We will shortly share the fix here for you testing.
Thanks Babar, much appreciated!
This seems to have fixed the issue. Thank you for the speedy resolution!
Hi again,
Thank you for the confirmation. It is good to know that you are up & running again. Please feel free to contact us back in case you need our further assistance with Aspose APIs.
Hi Babar, one more thing. I’ve noticed when using MoveTo on a named range, the range names don’t move - both the name of the range being moved and any range names contained inside that range. This works when cutting and pasting using Excel.
Hi Piers,
Thank you for writing back.
I have tried your recently shared scenario while using the latest version of Aspose.Cells for .NET 17.02.3 and following piece of code. I am afraid, I am not able to see the mentioned behaviour as the named range has been moved correctly. Could you please give a try to the latest version? In case the problem persists, please provide us code snippet along with sample spreadsheet to demonstrate the issue.
C#
Workbook workbook = new Workbook();
Range range = workbook.Worksheets[“Sheet1”].Cells.CreateRange(“A1”);
range.Name = “range”;
range.PutValue(“test”, false, false);
range.MoveTo(1, 1);
workbook.Save(dir + “output.xlsx”);
The input file has three ranges inside the named range called 'range'. Moving this range causes the three ranges contained inside to shrink to being one cell long instead of three.
The outer named range does seem to move as expected here though, so there might be another issue which I shall try and isolate.
Workbook workbook = new Workbook("input.xlsx");
Range blockRange = workbook.Worksheets.GetRangeByName("range");
blockRange.MoveTo(15, 1);
workbook.Save("output.xlsx");
Hi again,
Thank you for sharing the samples. I have evaluated the case while using the latest version of Aspose.Cells for .NET 17.02.3, and I am able to notice the said problem, that is; inner ranges seems to have lost size (cell area) when outer range has been moved. Moreover, I have tested the scenario in Excel to get the correct results (comparison snapshot attached). In order to further investigate the matter, I have raised this incident as CELLSNET-45171 in our bug tracking system. Please spare us little time to properly analyze the scenario and get back with updates in this regard.
Hi again,
This is to update you that the ticket logged earlier as CELLSNET-45171 has been marked resolved. We will shortly share the fix here for your testing.
Hi,
Thanks for using Aspose.Cells.
Please download and try the following latest fix
and let us know your feedback.
That seems to be working fine. Thanks.
Hi,
Good to know that your issue is sorted out by the new fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
Thank you.
One more issue relating to this.
Using the same code as above. At C3 there is a dropdown list and at D3-D5 there is conditional formatting, neither of which are moved with the rest of the contents/formatting of the cell.
Hi,
Thanks for the template file and details.
After an initial test, I observed the issue as you mentioned by using your new file and following sample code. I found that when moving the range to another location, Range.MoveTo does not move data validation and conditional formattings to its destination location. If we check the same scenario in MS Excel manually, it maintains the data validation and conditional formattings with data fine:
e.gSample code: Workbook workbook = new Workbook(“e:\test2\input.xlsx”); Range blockRange = workbook.Worksheets.GetRangeByName(“range”); blockRange.MoveTo(7, CellsHelper.ColumnNameToIndex(“H”)); //moving to H8 workbook.Save(“e:\test2\out1.xlsx”);
I have logged a ticket with an id “CELLSNET-45197” for your issue. We will look into it soon.
Once we have an update on it, we will let you know here.
Thank you.
Hi,
This is to inform you that we have fixed your issue “CELLSNET-45197” now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
Thank you.
Hi,
Thanks for using Aspose.Cells.
Please download and try the following latest fix
and let us know your feedback.
The conditional formatting seems to work fine, but not the data validation list. It should be at C16 in the output file, but is still at C3.