Free Support Forum - aspose.com

workbook.CalculateFormula terminates Application without a chance to handle Exception in workbook.CalculateFormula

Hi, we have an Excel Sheet that terminates the whole Application without any chance to handle the Exception when workbook.CalculateFormula() is called.
We can avoid this using a limited Stack Size of 150, but we rather would leave this open and detect the problem so we can notify our users who submit these notebooks.
Is there a way to detect and handle these problems with too complex or too large Workboks?
How can we give you this Workbook to investigate the Problem?
Regards…
Matthias

@mheuer

Thanks for your posting and using Aspose APIs.

Please provide us your Excel file which is terminating the application as well as the sample code to replicate the issue. We will look into it deeper and let you know if there is any solution or workaround for you.

If the file is less than 3 MB, you can attach it with your post. If it is larger, then you can create parts of it using WinRAR tool and change the parts extension to .zip and attach here.

Finally, you can use DropBox or GoogleDrive etc. to upload your file and provide us the download link here.

Hi again, I hope this reaches you well. Your Forum Software does not really explain how to add attachments, it seems like drag & drop do the job…
Killer.zip (59.1 KB)

@mheuer

We have tested your file with the most recent version and it worked fine. Please download the most recent version and try it and let us know your feedback.

If your issue still occurs, then please provide us some simple console application project that replicates your issue with the recent version.

Thank you for checking this, we are already using the latest version 17.8 but the Problem happens only from within an Event in our Web Application.
I know that the Stack Size in an ASP.NET Web Application is more limited than in an Executable.
We can avoid this using a limited CalculationOptions.CalcStackSize of 150, but we rather would leave this open and detect the problem so we can notify our users who submit these notebooks.
Is there a way to detect and handle (instead of avoiding) these problems with too complex or too large Workboks?

@mheuer

Please see this link, it states that you can increase the stack size of the thread instead of entire application. It means, whenever you need to do something, you create a thread with increased stack size as much as you like and execute Aspose.Cells code there. It should solve your problem.

How to Fix the System.StackOverFlowException on Workbook.CalculateFormula?

Ah, thank you, I didn’t know that. Now is there a chance to determine which Stack Size is needed for a certain Workbook?

@mheuer

I am afraid, currently there is no such API that could tell us which stack size is needed for a certain workbook, so I have logged your question in our database for product team consideration. We will evaluate it and let you know, if it is possible or feasible or if there is any solution or workaround.

It has been logged as

  • CELLSNET-45614 - Determine Which Stack Size is needed for a certain Workbook

Well thank you very much for your efforts.
We will warn our users and possibly perform Calculation in a separate Thread.

Best regards…
Matthias

@mheuer

Yes, this could be a good reason. We will keep you updated about any news regarding the logged ticket. Thank you and have a good day.

@mheuer,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-45614”). We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@mheuer

Please download and try the latest fix and let us know your feedback.

Please also read the following instructions related to this fix.


We have made improvements for the stack used by formula calculation. With the new fix, user should be able to get the formula calculated successfully for the given template file without specifying smaller stack size. For user’s purpose of finding out such kind of workbooks that may cause this issue, we also provide new APIs for user to track the formula calculation:

Class: AbstractCalculationMonitor
Property: CalculationOptions.CalculationMonitor

Users may trace the stack size by themselves using those APIs(Just an example, checking the stack for every cell will degrade the performance evidently):

public class MyCalculationMonitor : AbstractCalculationMonitor
{
 public override void BeforeCalculate(int sheetIndex, int rowIndex, int colIndex)
 {
	 if(new StackTrace(false).FrameCount > 2000)
	 {
		 throw new Exception("Stop the formula calculation because risk of StackOverflowException");
	 }
 }
}

Wow, thank you for this. We will need to schedule evaluation though.
We will inform you about the results.
I suppose creating a Stacktrace for each calculated Cell may be costly.
It would be nice if the BeforeCalculate Method would pass its own Recusion Counter if it has one.

Best Regards…
Matthias

@mheuer

It is good to know that you liked the solution. We have also logged your comment in our database against this ticket for product team further evaluation. Once, we will have some news for you, we will update you asap.

@mheuer

We are afraid we have no better way to get the stack size used at runtime. The code we provided is just for example. As we said, it is sure the performance will be degraded significantly. And just because of this reason, we cannot do it automatically in our calculation engine for all users. For recursion counter, what we can provide is the cell count in recursive calculation (user also can calculate it by themselves in implementation of CalculationMonitor). We think the code can be optimized by users according to their different scenarios and requirements. Such as checking the stack when the recursive cells count reaches certain number, gathering the average increase rate of stack for one recursive cell and determine the frequency to check the stack, …etc.

Oh well, thats some ideas that we will follow.
Thanks again…
Matthias

The issues you have found earlier (filed as CELLSNET-45614) have been fixed in Aspose.Cells for .NET 17.9.