Custom formula gets removed after call workbook.RemoveExternalLinks()

Aspose.Cells Product Family
1

Hello!


It looks like method workbook.RemoveExternalLinks() removes custom formulas as well.

For example,
- cell A1 contains a link to external workbook;
- cell A2 contains a custom formula =ABC();
- after calling workbook.RemoveExternalLinks() value in A2 gets hardcoded.

Thanks,
Leo
2

Hi Leo,

Thanks for your posting and using Aspose.Cells.

I have tested this issue with the following code using the latest version: Aspose.Cells
for .NET v8.2.0.2 and did not find any issue. The custom formula =ABC() is still inside cell A1.

I have attached both the source and output Excel files for your reference.

C#


Workbook workbook = new Workbook(“source.xlsx”);


workbook.RemoveExternalLinks();


workbook.Save(“output.xlsx”);


3

Hi Shakeel!


Please find the excel file the issue can be reproduced against v8.2.0.2 with the following code:
            var options = new AC.LoadOptions()

{

ParsingFormulaOnOpen = true,

};

        <span style="color:blue;">var</span> fileName = <span style="color:#a31515;">@"21868.xlsx"</span>;
        <span style="color:blue;">var</span> workbook = <span style="color:blue;">new</span> AC.<span style="color:#2b91af;">Workbook</span>(fileName, options);
        
        workbook.RemoveExternalLinks();
        workbook.Save(<span style="color:#a31515;">@"output.xlsx"</span>);</pre></div><div>As a result, the formula gets removed from cell A3.</div><div><br></div><div><b>Please note</b> if you change loading option to ParsingFormulaOnOpen = <b>false </b>then you get a run-time exception.</div><div><br></div><div>Thanks,</div><div>Leo</div>
4

Hi Leo,

Thanks for your posting and using Aspose.Cells.

I did not find any custom formula inside your file 21868.xlsx and therefore was unable to reproduce this issue. However, when I set the ParsingFormulaOnOpen = false, it got the exception.

I have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-42962 - “Index was outside the bounds of the array” exception on Workbook.RemoveExternalLinks()

C#

var options = new LoadOptions()

{

ParsingFormulaOnOpen = false,

};


var fileName = @“21868.xlsx”;

var workbook = new Workbook(fileName, options);


workbook.RemoveExternalLinks();

workbook.Save(@“output1.xlsx”);


Exception:
at —..“Š(Byte[] , Int32 , Int32 , WorksheetCollection , Hashtable , Hashtable )
at ’’..“Š(Byte[] , Int32 , Int32 , WorksheetCollection , Hashtable , Hashtable )
at Aspose.Cells.RowCollection.RemoveExternalLinks(Hashtable , Hashtable )
at Aspose.Cells.Workbook.RemoveExternalLinks()

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in AsposeProject.exe

Additional information: Index was outside the bounds of the array.

5

The formula is in cell A3 - I just used a test Excel AddIn formula =test1(). After the sample code gets run the formula is removed and hardcoded with value.


Thanks,
Leo
6

Hi Leo,

Thanks for your clarification and using Aspose.Cells.

We were able to observe this issue. After the execution of the above code, the custom formula from cell A3 is removed and its value is hardcoded.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-42963 - Custom formula gets removed after call workbook.RemoveExternalLinks().

7

Hi Leo,

Thanks for using Aspose.Cells.

We have evaluated this issue further and we are afraid, we could not know whether an external link is addin.

So those formulas will be removed too after calling Workbook.RemoveExternalLinks().

8

Hi Shakeel!


It is a usual use case when:
- we create custom formulas. I use an xlam AddIn in my example just to simplify. In our project we use VSTO AddIn;
- users submit their data on server;
- on server-side we need to remove links to external references so that they are not accessible on the server-side. Along with that we calculate our custom formulas in workbooks using ICustomFunction.

It looks like there is a difference between external links and formulas that Aspose does not know:
- an external link =‘C:\Users…<b>[Source.xlsx]Sheet1’!$A$1
- a custom formula =test1()

The behavior of RemoveExternalLinks() just removes our custom formulas and prevents it from calculating.

Thanks,
Leo
9

Hi Leo,

Thanks for your posting and using Aspose.Cells.

Please provide a screenshot of file 21868.xlsx at your end. I have attached the screenshot of this file at my end which is showing how the external formula in cell A3 looks like in MS-Excel 2010.

It will help us investigate this issue more and we will see if we could support this feature or not.

10

Hi Shakeel!


This is how it looks for our customers.

Thanks,
Leo


11

Hi Leo,

Thanks for your screenshot and using Aspose.Cells.

We have reopened this issue for investigation. We will look into it and see if there is some way to differentiate between external links and external custom formulas and check if we can resolve this issue. Once, there is some update for you, we will let you know asap.

12

Hi Leo,

Thanks for your using Aspose.Cells.

We have fixed the issue (CELLSNET-42962).

Please download and try the latest fix: Aspose.Cells for .NET v8.2.0.3 and let us know your feedback.

13

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
14

Hi,

Thanks for your using Aspose.Cells.

We have fixed this issue (CELLSNET-42963 - Custom formula gets removed after call workbook.RemoveExternalLinks())

Please download and try the latest fix: Aspose.Cells for .NET v8.2.1.1 and let us know your feedback.

15

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.