Array formula becomes standard formula after deleting row in .NET

Aspose.Cells Product Family
1

After I delete a row, my array formulas become "standard" formula.

I attached the files after I deleted some rows.

I use the version 4.0.1.7

I also have a problem with the AutoFitColumn, it throws a NullReferenceException on many rows. I didn't have this problem when I used the version 4.0.1.4.

Thank you

Alexandre

2

For formula issue, I have found the problem and will fix it soon.

For AutoFitColumn problem, I don't find it. Could you please post your sample code here?

Following is my code:

Workbook workbook = new Workbook();

workbook.Open("d:\\test\\after.xls");
int count = workbook.Worksheets[0].Cells.MaxDataColumn;
for(int i = 0; i < count; i ++)
workbook.Worksheets[0].AutoFitColumn(i);
workbook.Save("d:\\test\\abc.xls");

3

Sorry it's my mistake.

I have the error with the AutoFitRow, not with AutoFitColumn.

Thank you.

4

Please try this attached fix.

5

Thank you, it fixed the 2 problems.

I have an other question.

I use absolute formula and also some mixed (absolute and reference) formula. When I copy those cells, the formula is not the same in the destination cell than the source cell like there was no $ in the formula. What can I do to fix that? If I was using only absolute formula I could get the formula of the source cell and set it to the destination cell but I also use mixed formula like this one

=SUM(IF($A$9:$A$22=5;IF($I$9:$I$22<>"";IF($F$9:$F$22<>-1;$I$9:$I$22*$D$9:$D$22;0);0);0))*$D8

the $D8 should change when I copy the formula on another row but the rest should not change.

I use it with arrayformula, but I tested it with normal formula and I have the same problem.

thank you

Alexandre

6

Please post your output file with this new fix to show this problem. I tried in my machine with your old files but don’t find this problem.

7

I have done a little sample

Aspose.Cells.Workbook w = new Aspose.Cells.Workbook();

Aspose.Cells.Worksheet xlSheet = w.Worksheets[0];

xlSheet.Cells[0,0].PutValue(234);

xlSheet.Cells[1,1].Formula = "=$A$1";

xlSheet.Cells[4,4].Copy(xlSheet.Cells[1,1]);

w.Save("C:\\testAspose.xls");

When I open Excel, the formula in the cell E5 is =$D$4 but it should be = $A$1... at least it's what I thought it would be.

I attached the file with the fix, you will get some circual reference,...

In the old version I was only setting the formula, not copying the cell so I didn't have this problem before.

Thank you

8

Please try this attached version.

9

It fixed the problem with the formula, but when I copy a cell with an array formula, in the destination cell, it appears like a standard formula.

Thank you

10

Please try this fix.

11

Sorry for the delay, I was out of the office.

The problem with the copy of the absolute formula is back (formula array... I didn't check for a normal formula), it copy like there was no "$" in the formula. But the formula in the destination cell appears like an array formula like it should be.

Thank you

12

How do you copy the formulas? I used the following test code and it works fine:

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\book1.xls");

Cells cells = workbook.Worksheets[0].Cells;
cells["l31"].Copy(cells["k31"]);

workbook.Save("d:\\test\\abc.xls");

Attached is my template file.

13

I copy the formula the same way you do.

It works with your code but I found the problem happens only when I copy the cell on a different row.

I used this line to copy the cell

cells["k35"].Copy(cells["k31"]);

Thank you.

14

Please try this attached version.

15

The problem is fixed. Thank you very much.