InsertRange and copying cell formulas

Aspose.Cells Product Family
1

Hello,

In excel, you can select a rectangle of cells. That rectangle has a small dot at its lower right corner. If you drag that dot with the mouse and pull it down, all formulas within that rectangle are replicated and adapted to the new cells. Is there a similar function within. Aspose Cells?

Is there even a function that moves the other cells down just like with InsertRange while replicating the formulas?

If not, does anybody know how to implement a similar functionality?

Thanks for any help - Richard Hafner

2

Hi Richard,

You can try to use Cells.CopyRow method or Cells.InsertRange method.

3

Hello Laurence,

thanks for the hint.

Attached you can find a small test app that principally does what I need (Method Form.Grow()). But I get a "File error" message while loading the modified xls in excel.

I attached the xls before and after modification of this test app, too.

Is there something I do wrong? Thanks for any help! :)

Greetings - Richie

4

Hello Laurence,

did you have time to take a look at my sample files?

Greetings - Richard Hafner

5

Please try this version.

6

Hello Laurence,

I tried the Version you sent to me. It does not produce a file load error.

However the Formulas of the cells below the inserted cell area is somehow messed up. I attached the Application that uses both versions of InsertRange.

I assume that all references should to the moved cells should be updated - independent of the fact that the cell that holds a reference is a moved cell or not - and independent of the fact that a reference is relative or absolute.

What do you think?

Greetings - Richard Hafner

7

Hi Richard,

I will check this issue. However, in MS Excel, when insert a range, references in formulas won't be updated.

8

Hi Laurence,

thank you for your efforts so far!

I attached an Excel file where I insert a range within a matrix of formulas. I use a german Excel but i hope that you can track what I did (see Worksheet "How i did it").

At least with my Excel, the references to the moved cells are compensated so that the resut value keeps unchanged. I assume that InsertRange should behave the same way?

Could it be possible that there is a configuration option somewhere in Excel that changes the behavior of this operation?

Greetings - Richie

9

Please try this attached version.

10

Hello Laurence,

I tried the attached version. The formulas are not messed up anymore and keep untouched. I guess this is the intended behavior of the InsertRange method.

However is it possible to get a method that adaptes the formula references in the way Excel does it when inserting cells? Like I described it in my previous post?

I attached my current test application together with the original file (Versuchskaninchen.xls), the current result (VersuchskaninchenTot.xls) and the needed result (VersuchskaninchenJa.xls). The coloring in the last file are not meant to be part of the expected result but to mark the inserted (green) and moved (yellow) cells.

Do you think that this can be implemented in Aspose.Cells? I would really appreciate it because otherwise I would need to implement a parser that scans all involved formulas in the workbook to transform the affected references.

Greetings - Richie

11

Dear Richie,

We will make it in Aspose.Cells. It will take about 2 weeks for this feature because we find it isn't an easy job.

12

Hello Laurence

Great! Thank you very much! Please leave a message when that feature has been implemented.

Greetings - Richie

13

Hello Laurence,

I would like to know if I can bargain for this feature on wednesday, august 09. We have a release date then and I need to know if I need to implement a workaround this issue.

Greetings - Richie

14

Hi Richie,

Please try this attached version.

15

Hello Laurence,

I tried the attached version and found out that it has nearly the same behavior as the version i used before. The attached example has the original file ("Versuchskaninchen.xls"), the current result when running the test application ("VersuchskaninchenTot.xls") and the result when doing the same with the excel application itself ("VersuchskaninchenJa.xls"). So from my point of view, the problem is not solved yet.

Please tell me if and when Aspose is going to support this feature.

Greetings - Richie

16

If you InsertRange as a whole, you will see it works fine:

Workbook wb=new Workbook();
wb.Open(@"d:\test\Versuchskaninchen.xls");
wb.Worksheets[2].Cells.InsertRange(GetCellArea(3, 3, 3, 5), 10, ShiftType.Down);
wb.Save(@"d:\test\abc.xls");

However, there is a bug if you InsertRange column by column. I will fix it ASAP. Tomorrow you will get a fix for this issue.

17

This is caused by a small bug. Please try this attached version.

18

Hello Laurence!

Thank you very much for that bugfix! Now the InsertRange works almost perfectly for me! I changed my algorithm to insert a range en bloc, if possible, and not column by column. Range references to the affected area are not treated correctly otherwise.

However, if there is a reference to the affected area from another worksheet, the reference is not updated yet. In the attached example, the reference in Tabelle2!A1 is not updated. I guess that this is not that big issue?

Greetings - Richie

19

Hi Richie,

For v4.0.1.8, even you InsertRange column by column, it will work fine.

InsertRange method doesn't update cross-worksheet reference. This is to improve peroformance. If it's not a big issue for you, you can use your own code to update them. We will add a new overloaded InsertRange method to update these kind of references.

20

Hi Laurence,

okay, then I will stick with the current version and wait for the new one that allows cross worksheet references. I expect that 3D references (such as "Table1:Table3!A1:C3") are going to be an issue as well.

Thank you very much again for your help! :) Please keep me informed!

Greetings - Richie