InsertRange breaks formula referencing entire column

In cell A1 I have the following formula:

=SUM(A2:INDEX(A:A;ROW()+1))

If I insert a new cell in A2 using InsertRange(ShiftType.Down) the formula is changed to
=SUM(A3:INDEX(A$1:A$1048577,ROW()+1))

There are two problems with this:
1. The A:A has been changed to A$1:A$1048577, which looks "ugly"
2. 1048577 is obviously above the row limit (I use Office 2007), so Excel reports "Excel found unreadable content… Do you want to recover this workbook?"

By extracting the XLSX-file using 7Zip I can see the actual formula generated (see attached Sheet1.xml).

I am attaching the designer document (InsertInRangeAndIndex.xlsx), the result (InsertInRangeAndIndex_result.xlsx) and the file from 7Zip (Sheet1.xml).

Code follows below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Diagnostics;
using System.Collections;

namespace Infoweaver.Forms.AsposeTest
{
class InsertInRangeAndIndex
{
public static void DoIt()
{
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + “InsertInRangeAndIndex.xlsx”);
designer.Workbook = workbook;

Cells sourceCells = workbook.Worksheets[0].Cells;
Range sourceRange = sourceCells.CreateRange(0, 0, 1, 1);

CellArea ca = new CellArea();
ca.StartRow = 1;
ca.EndRow = ca.StartRow;
ca.StartColumn = 0;
ca.EndColumn = 0;
sourceCells.InsertRange(ca, 1, ShiftType.Down, true);

string output = Constants.destPath + “InsertInRangeAndIndex_result.xlsx”;
workbook.Save(output);
Process.Start(output);
}
}
}

Hi,


After an initial test, I can find the issue as you have mentioned. I have logged a ticket with an ID: CELLSNET-40164. We will look into it soon.

Thank you.

Hi,

Please download and try this fix: Aspose.Cells for .NET v7.0.3.6

Works like a charm, thanks!

The issues you have found earlier (filed as CELLSNET-40164) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.