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);
}
}
}