Free Support Forum - aspose.com

Cells don't update after InsertRange

Hi
I am using Aspose.Cells for .Net
Suppose we we have sheet with next values

I’ve loaded workbook
var worksheet = new Workbook(filePath, new LoadOptions)[0];

I’ve copied references of all non null cells to some array

         for (var i = firstRow; i <= lastRow; i++)
            {
                cells[i] = new Cell[width];
                for (var j = firstColumn; j <= lastColumn; j++)
                {
                    cells[i][j] = worksheet.Cells[i, j];
                }
            }
            return new Table(cells);

        public Table(Cell[][] elements, Worksheet worksheet)
        {
            Elements = elements;
        }

I am inserting range of empty cells to aspose sheet
worksheet.Cells.InsertRange(CellArea.CreateCellArea(0, 0, 1, 1), 1, ShiftType.Down, true);

And I am expecting that cell objects in the array will update their adresses like they do when I insert row worksheet.Cells.InsertRow or column worksheet.Cells.InsertColumn. But unfortunately cells don’t update their adresses after method InsertRange even for parameter updateReferences = true. Below couple of screenshots to illustrate this (after calling method InsertRange)

So, my question is how to update cells in array afterInsertRange according new adresses in worksheet.

Any help would be greatly appreciated.

Thanks

@andrryaka,

Thanks for the details.

Please try our latest version/fix: Aspose.Cells for .NET v18.8.

If you still find the issue, kindly do provide your sample code (runnable) and attach your template file (if other than your attached screenshot) to reproduce the issue, we will check it soon.

I’ve tried it for 18.8 - it is still reproduced
Please find added sample project and test file attached

using System;
using Aspose.Cells;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace AsposeInsertRange1
{
    class Program
    {
        static void Main(string[] args)
        {
            var worksheet = new Workbook("C:\\test1.xlsx", new LoadOptions()).Worksheets[0];
            var cells = new Cell[3];
            for (var i = 0; i < cells.Length; i++)
            {
                cells[i] = worksheet.Cells.Rows[0][i];
            }

            Console.WriteLine("Before InsertRange");
            foreach (var cell in cells)
            {
                Console.Write(cell.StringValue + " ");
            }
            Console.WriteLine("");

            worksheet.Cells.InsertRange(CellArea.CreateCellArea(0, 0, 0, 1), ShiftType.Down);

            Console.WriteLine("After InsertRange");
            Console.WriteLine("Worksheet line 0");
            for (var i = 0; i < 3; i++)
            {
                Console.Write(worksheet.Cells.Rows[0][i].StringValue + " ");
            }
            Console.WriteLine("");
            Console.WriteLine("Worksheet line 1");
            for (var i = 0; i < 3; i++)
            {
                Console.Write(worksheet.Cells.Rows[1][i].StringValue + " ");
            }
            Console.WriteLine("");

            Console.WriteLine("Array");
            foreach (var cell in cells)
            {
                Console.Write(cell.StringValue + " ");
            }

            Console.ReadKey();
        }
    }
}

test1.zip (5.3 KB)

@andrryaka,

Thanks for your query.

I have tried the sample code and observed the same behavior. It returns objects by value rather than by reference which are not updated after calling InsertRange function. It seems that you need to update your cells array after each range insertion or refer to different cells by name or row/column index instead of values saved in arrays.

Thanks. This was oversimplified example. I need to store original cell structure in arrays to have ability to compare cells by values. Aspose updates cell’s values in case of row or column insertion. Is any chance to get same behaviour for InsertRange? We can see required behaviour in example below

using System;
using Aspose.Cells;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace AsposeInsertRange1
{
    class Program
    {
        static void Main(string[] args)
        {
            var worksheet = new Workbook("C:\\test1.xlsx", new LoadOptions()).Worksheets[0];
            var cells = new Cell[3];
            for (var i = 0; i < cells.Length; i++)
            {
                cells[i] = worksheet.Cells.Rows[0][i];
            }

            Console.WriteLine("Before InsertRange");
            foreach (var cell in cells)
            {
                Console.Write(cell.StringValue + " ");
            }
            Console.WriteLine("");

            //worksheet.Cells.InsertRange(CellArea.CreateCellArea(0, 0, 0, 1), ShiftType.Down);
            worksheet.Cells.InsertRow(0);

            Console.WriteLine("After InsertRange");
            Console.WriteLine("Worksheet line 0");
            for (var i = 0; i < 3; i++)
            {
                Console.Write(worksheet.Cells.Rows[0][i].StringValue + " ");
            }
            Console.WriteLine("");
            Console.WriteLine("Worksheet line 1");
            for (var i = 0; i < 3; i++)
            {
                Console.Write(worksheet.Cells.Rows[1][i].StringValue + " ");
            }
            Console.WriteLine("");

            Console.WriteLine("Array row numbers");
            foreach (var cell in cells)
            {
                Console.Write(cell.Row + " ");
            }

            Console.ReadKey();
        }
    }
}

@andrryaka,

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-46288 - Cells references not updated after InsertRange

Thank you very much for this. Is there any ability to have some eta for this issue? The reason I’m asking about this is that we have release soon and we are thinking if we can wait for fix for this or we need to put some workaround from our side. And this workaround will be a little bit weird. So I’ll be really appreciated for some estimates for this issue.

@andrryaka,

I am afraid that ETA cannot be provided as this issue is logged too recently. We normally provide solution in a week time but based on complexity of the issue, this time may increase. We will provide ETA as soon as this issue is analyzed and some feedback is ready to share.

I understood, thanks. I look forward to hearing from you at your earliest convenience

@andrryaka,

Although we have logged the ticket for it and we will analyze it thoroughly but I suspect this is not an issue rather the behavior of the APIs. Anyways, once we have an update on the issue, we will let you know here.

FYI, Cell objects instantiated by user’s invocation won’t be kept and managed in memory, this might be due to performance grounds as we decided. So, after inserting/deleting range operations, their position(row/column index) may not be updated accordingly. For example, for following code (the sample code is provided in Java but the behavior is same in .NET):
e.g
Sample code:

_ Cell cell = cells.get(“A2”);_
_ System.out.println(cell.getName() + “:” + cell.getValue());_
_ cells.insertRange(CellArea.createCellArea(“A1”, “A1”), ShiftType.DOWN);_
_ System.out.println(cell.getName() + “:” + cell.getValue());_

the cell object will become invalid or it will still refer to A2 with other value. For such kind of situation, a user needs to get Row/Cell object again from cells collection to get the correct results:
e.g
Sample code:

_ Cell cell = cells.get(“A2”);_
_ System.out.println(cell.getName() + “:” + cell.getValue());_
_ cells.insertRange(CellArea.createCellArea(“A1”, “A1”), ShiftType.DOWN);_
_ cell = cells.get(“A3”);_
_ System.out.println(cell.getName() + “:” + cell.getValue());_

I think, as a workaround, you may try to add the lines something similar to the lines in bold:
e.g
Sample code:

var worksheet = new Workbook(“e:\test2\test1.xlsx”, new LoadOptions()).Worksheets[0];
var cells = new Cell[3];
for (var i = 0; i < cells.Length; i++)
{
cells[i] = worksheet.Cells.Rows[0][i];
}

        Console.WriteLine("Before InsertRange");
        foreach (var cell in cells)
        {
            Console.Write(cell.StringValue + " ");
        }
        Console.WriteLine("");

        worksheet.Cells.InsertRange(CellArea.CreateCellArea(0, 0, 0, 1), ShiftType.Down);

        Console.WriteLine("After InsertRange");
        Console.WriteLine("Worksheet line 0");
        for (var i = 0; i < 3; i++)
        {
            Console.Write(worksheet.Cells.Rows[0][i].StringValue + " ");
        }
        Console.WriteLine("");
        Console.WriteLine("Worksheet line 1");
        for (var i = 0; i < 3; i++)
        {
            Console.Write(worksheet.Cells.Rows[1][i].StringValue + " ");
        }
        Console.WriteLine("");

        Console.WriteLine("Array row numbers");

//re-insert updated cells after insertion or the range.
for (var i = 0; i < cells.Length; i++)
{
cells[i] = worksheet.Cells.Rows[1][i];
}

        foreach (var cell in cells)
        {
            Console.Write(cell.Row + " ");
        }

        Console.ReadKey();

Thank you for quick response and exhaustive explanation

@andrryaka,

We evaluated your issue further. As I told you it is the behavior (rather expected behavior) of the Aspose.Cells APIs. InsertRange will cause inner data model of Cell object changed further, we are afraid we cannot make it synchronized when inserting range same as when inserting rows. So, you have to adopt your approach or workaround to cope with it by yourselves.

Thanks, will do