Change in behaviour of named ranges since v5

Aspose.Cells Product Family
1

Hello,
We recently upgraded from version 5.3.3.1 to version 7.0.1.3. We’ve noticed some unexpected results since the upgrade. A reference to a named range used to be adjusted when rows were inserted; now it is not. The following test illustrates the change. This test worked with v5.3.3.1, but fails with 7.0.1 - the first row of the range stays at 2 after rows are inserted. Please note the named range has moved as expected, and if I get a new reference to it, it is correct, but the behaviour of the existing reference has changed. Was this change intentional? Do you have any suggestions?

Mishelle

2

Hi,

Please provide us your sample source code to replicate the problem along with any source file you are using.

Please use the latest version:
Aspose.Cells for Java v7.0.1.3

If we found any bug, we will report it and development team will fix it and we will update you asap.

3

Sorry, forgot the code:

public void CreateReferenceToRangeThenInsertAboveTheRange()
{
var workbook = new Workbook();
var range = workbook.Worksheets[0].Cells.CreateRange(“A3”, “E7”);
range.Name = “ThisRange”;
range = workbook.Worksheets.GetRangeByName(“ThisRange”);
Assert.That(range.FirstRow, Is.EqualTo(2));

workbook.Worksheets[0].Cells.InsertRows(2, 10);
Assert.That(range.FirstRow, Is.EqualTo(12));
}

4

Hi,

I have tested it with the latest version:
Aspose.Cells for .NET v7.0.1.5 and found the issue.

We have logged this issue in our database. We will fix it and update you asap.

This issue has been logged as CELLSNET-31013.

Below is my code with comments for a reference and the output.

C#


Workbook workbook = new Workbook();

var range = workbook.Worksheets[0].Cells.CreateRange(“A3”, “E7”);

range.Name = “ThisRange”;

range = workbook.Worksheets.GetRangeByName(“ThisRange”);


//The first row of range is 2 in Aspose.Cells (or 3 in Excel) so it is ok

bool result = (range.FirstRow == 2);


Debug.WriteLine(result);


//After inserting 10 rows, the first row of range should be 12 in Aspose.Cells (or 13 in Excel)

workbook.Worksheets[0].Cells.InsertRows(2, 10);


//But it returns false, so it is a bug

result = (range.FirstRow == 12);


Debug.WriteLine(result);


Output:
True
False

5

Hi,

We do not keep Range object in Cells.Ranges when calling Cells.InsertRange.

If you want to change ranges when insert rows, please call Cells.AddRange method.

C#


var workbook = new Workbook();


var range = workbook.Worksheets[0].Cells.CreateRange(“A3”, “E7”);


range.Name = “ThisRange”;


range = workbook.Worksheets.GetRangeByName(“ThisRange”);


workbook.Worksheets[0].Cells.AddRange(range);


Console.WriteLine(range.FirstRow);//, Is.EqualTo(2));


workbook.Worksheets[0].Cells.InsertRows(2, 10);


Console.WriteLine(range.FirstRow);//, Is.EqualTo(12));



6

I see, thank you for this suggestion. So there’s no way to make it work like it did in version 5?

I don’t really understand the point of the AddRange method. I mean, the range is already part of the sheet, so what am I adding it to? Am I adding it to a list or collection from which I can retrieve it again later? Do I need to know its index to retrieve it? Does it affect performance in any way if I add several ranges or large ranges, and then continue to manipulate the grid?

7

Hi,

Yes, it adds the range inside the Worksheet.Cells.Ranges collection. The insert ranges adds ranges only inside the memory but does not affect the final output workbook. Add range adds the range in a final output workbook too.

It does not affect the performance, you will need an index or name to retrieve it.